Android Simple SQLiteDatabase

Account List

New Account

Modify Account

Source Code can be downloaded below. Also, the syntaxhighlighter for the XML code, has some problems. For some reason, the JS and CSS I used modifies the XML I place. Just check with the code downloadable below for the correct xml files.



New Classes Used:
ListActivity
SQLiteOpenHelper

1. Create new Android Project.
-Activity Name: AccountList

2. Create View Classes and XML

2.1 Create account_list.xml

account_row.xml

2.2. Create ListActivity class AccountList

public class AccountList extends ListActivity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.account_list);
initComponents();
}
private void initComponents() {
}
}

2.3 Create account_detail.xml
Below is an example of combining two or more layouts.

2.4. Create Activity class AccountDetail

public class AccountDetail extends Activity {
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.account_detail);
initComponents();
}
private void initComponents() {
}
}

3. Create Model Classes
3.1 Create Account class

public class Account {
public static final String COL_ROW_ID = "_id";
public static final String COL_SERVER_NAME = "server_name";
public static final String COL_USERNAME = "username";
public static final String COL_PASSWORD = "password";
public static final String SQL_TABLE_NAME = "account_db";
public static final String SQL_CREATE_TABLE = "CREATE TABLE "
+ SQL_TABLE_NAME + " "
+ " (_id integer primary key autoincrement, "
+ "server_name text not null, username text not null, "
+ "password text not null); ";

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getServerName() {
return serverName;
}

public void setServerName(String serverName) {
this.serverName = serverName;
}

private String username;

private String password;

private String serverName;

private int id;
}

3.2 Create ActiveRecord interface
This is the interface that will be extend by POJO to make it a sort-of Active Domain. I'm not really experienced with the Active Domain pattern but this is my understanding of it. So just correct me if I am wrong and I'll change the code.

public interface ActiveRecord {
public long save();
public boolean delete();
public void load(Activity activity);
public Cursor retrieveAll();
public void setSQLiteDatabase(SQLiteDatabase sqliteDb);
}

3.3 Extend SQLiteOpenHelper
I derived MyDatabaseAdapter class from
Tutorial: A Notepad Application, However I made the MyDatabaseAdapter as a utility class instead of placing CRUD functions inside it. I made the Controller a sort-of Active Domain pattern so that the MyDatabaseAdapter can be re-used for future applications.

public class MyDatabaseAdapter extends SQLiteOpenHelper {
private static SQLiteDatabase sqliteDb;
private static MyDatabaseAdapter instance;

private static final String DATABASE_NAME = "simple_sqlite_db";
private static final int DATABASE_VERSION = 1;


private MyDatabaseAdapter(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(Account.SQL_CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(getClass().getSimpleName(), "Upgrading database from version " + oldVersion + " to "
       + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + Account.SQL_TABLE_NAME );
onCreate(db);
}

private static void initialize(Context context) {
if(instance == null) {
instance = new MyDatabaseAdapter(context, DATABASE_NAME, null, DATABASE_VERSION);
sqliteDb = instance.getWritableDatabase();
}
}

public static final MyDatabaseAdapter getInstance(Context context) {
initialize(context);
return instance;
}

public SQLiteDatabase getDatabase() {
return sqliteDb;
}

public void close() {
if(instance != null ) {
instance.close();
instance = null;
}
}
}

3.4 Create implementation of ActiveRecord on Account class
Here's the code that implements the CRUD methods of the ActiveRecord interface. I also learned that from the Notepad Tutorial, since the Android Docs, does not really explain how to use SQLiteDB, or maybe I just do not know where to look.

public class Account implements ActiveRecord {

public static final String COL_ROW_ID = "_id";
public static final String COL_SERVER_NAME = "server_name";
public static final String COL_USERNAME = "username";
public static final String COL_PASSWORD = "password";

public static final String SQL_TABLE_NAME = "account_db";
public static final String SQL_CREATE_TABLE = "CREATE TABLE "
+ SQL_TABLE_NAME + " "
+ " (_id integer primary key autoincrement, "
+ "server_name text not null, username text not null, "
+ "password text not null); ";

private SQLiteDatabase sqliteDatabase;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getServerName() {
return serverName;
}

public void setServerName(String serverName) {
this.serverName = serverName;
}

private String username;

private String password;

private String serverName;

private int id;

@Override
public void load(Activity activity) {
Cursor cursor = sqliteDatabase.query(true, SQL_TABLE_NAME,
new String[] { COL_ROW_ID, COL_SERVER_NAME, COL_USERNAME,
COL_PASSWORD }, COL_ROW_ID + "=" + id, null, null,
null, null, null);
if (cursor != null) {
cursor.moveToFirst();
activity.startManagingCursor(cursor);
setId(cursor.getInt(cursor
.getColumnIndex(COL_ROW_ID)));
setPassword(cursor.getString(cursor
.getColumnIndex(COL_PASSWORD)));
setUsername(cursor.getString(cursor
.getColumnIndex(COL_USERNAME)));
setServerName(cursor.getString(cursor
.getColumnIndex(COL_SERVER_NAME)));
}
}

@Override
public Cursor retrieveAll() {
return sqliteDatabase.query(SQL_TABLE_NAME, new String[] { COL_ROW_ID,
COL_SERVER_NAME, COL_USERNAME, COL_PASSWORD }, null, null,
null, null, null);
}

@Override
public long save() {
ContentValues values = new ContentValues();
if (id <= 0) {    values.put(COL_SERVER_NAME, serverName);    values.put(COL_USERNAME, username);    values.put(COL_PASSWORD, password);    return sqliteDatabase.insert(SQL_TABLE_NAME, null, values);   } else {    values.put(COL_SERVER_NAME, serverName);    values.put(COL_USERNAME, username);    values.put(COL_PASSWORD, password);    return sqliteDatabase.update(SQL_TABLE_NAME, values, COL_ROW_ID      + "=" + id, null);   }  }   public boolean delete() {   return sqliteDatabase.delete(SQL_TABLE_NAME, COL_ROW_ID + "=" + id,     null) > 0;
}

@Override
public void setSQLiteDatabase(SQLiteDatabase sqliteDatabase) {
this.sqliteDatabase = sqliteDatabase;
}
}

4. Create Controllers
4.1 AccountList Controllers
For the AccountList I used the OptionsMenu.

public class AccountList extends ListActivity {
/** Called when the activity is first created. */
private MyDatabaseAdapter myDatabaseAdapter;

private static final int INTENT_NEXT_SCREEN = 0;
public static final String INTENT_EXTRA_SELECTED_ROW = "SELECTED_ROW";

private static final int INSERT_ID = Menu.FIRST;
private static final int DELETE_ID = Menu.FIRST + 1;
private static final int EXIT_ID = DELETE_ID + 1;
private Account account = new Account();
private Intent intent;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.account_list);
myDatabaseAdapter = MyDatabaseAdapter.getInstance(this);
intent = new Intent(this, AccountDetail.class);
initComponents();
}

private void initComponents() {
account.setSQLiteDatabase(myDatabaseAdapter.getDatabase());
Cursor recordsCursor = account.retrieveAll();
startManagingCursor(recordsCursor);
String[] from = new String[] { Account.COL_SERVER_NAME };
int[] to = new int[] { R.id.tfServerName };
SimpleCursorAdapter records = new SimpleCursorAdapter(this,
R.layout.account_row, recordsCursor, from, to);
setListAdapter(records);
}

@Override
public boolean onMenuItemSelected(int featureId, MenuItem item) {
switch (item.getItemId()) {
case INSERT_ID:
createRecord();
return true;
case DELETE_ID:
account.setId((int) getListView().getSelectedItemId());
account.delete();
initComponents();
return true;
case EXIT_ID:
finish();
}
return super.onMenuItemSelected(featureId, item);
}

private void createRecord() {
intent.putExtra(INTENT_EXTRA_SELECTED_ROW, 0);
startActivityForResult(intent, INTENT_NEXT_SCREEN);
}

@Override
protected void onListItemClick(ListView l, View v, int position, long id) {
super.onListItemClick(l, v, position, id);
Log.v(getClass().getSimpleName(), "id=" + id);
intent.putExtra(INTENT_EXTRA_SELECTED_ROW, id);
startActivityForResult(intent, INTENT_NEXT_SCREEN);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
super.onCreateOptionsMenu(menu);
menu.add(0, INSERT_ID, 0, R.string.btnAdd);
menu.add(0, DELETE_ID, 0, R.string.btnDelete);
menu.add(0, EXIT_ID, 0, R.string.btnExit);
return true;
}

@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
initComponents();
}
}

4.2 AccountDetail Controllers

public class AccountDetail extends Activity implements OnClickListener {

private MyDatabaseAdapter myDatabaseAdapter;
private long selectedRow;
private TextView tvId;
private EditText etServerName, etUserName, etPassword;
private Button btnSave, btnCancel;
private Account account;

public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.account_detail);
myDatabaseAdapter = MyDatabaseAdapter.getInstance(this);
initComponents();
}

private void initComponents() {
selectedRow = getIntent().getLongExtra(
AccountList.INTENT_EXTRA_SELECTED_ROW, 0);
tvId = (TextView) findViewById(R.id.tvId);
etServerName = (EditText) findViewById(R.id.etServerName);
etUserName = (EditText) findViewById(R.id.etUsername);
etPassword = (EditText) findViewById(R.id.etPassword);

account = new Account();
account.setSQLiteDatabase(myDatabaseAdapter.getDatabase());
Log.v(getClass().getSimpleName(), "selectedRow=" + selectedRow);
account.setId((int) selectedRow);
if (selectedRow > 0) {
account.load(this);
}
Log.v(getClass().getSimpleName(), "account.getId()=" + account.getId());
if (account.getId() > 0) {
tvId.setText(account.getId() + "");
etServerName.setText(account.getServerName());
etUserName.setText(account.getUsername());
etPassword.setText(account.getPassword());
} else {
tvId.setText("new");
}
btnSave = (Button) findViewById(R.id.btnSave);
btnSave.setOnClickListener(this);
btnCancel = (Button) findViewById(R.id.btnCancel);
btnCancel.setOnClickListener(this);
}

Source Code
If you find something confusing from this tutorial, just post a comment, and I'll fix it for you.


In order to display multiple items on the list, just edit account_row.xml as shown below.

 android:layout_width="fill_parent" android:layout_height="wrap_content">
      android:layout_width="wrap_content" android:layout_alignParentRight="false"
     android:layout_height="wrap_content" android:text="Server Name" />
      android:layout_width="wrap_content"
     android:layout_height="wrap_content" android:layout_toRightOf="@id/tfServerName" 
     android:text="Username"/> 

Also modify AccountList.java to display the fields you want.

String[] from = new String[] { Account.COL_SERVER_NAME, Account.COL_USERNAME};
int[] to = new int[] { R.id.tfServerName, R.id.tfUsername };