Sunday, October 19, 2008

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 };



14 comments:

Alex said...

hey this is great stuff, finally an example of a sqlite database that works! just a few questions though.. how would you set a TextView to set it's text as one of the saved inputs in the database? for example, lets say i added a new layout, and have 2 textviews on there. how would i make one textview set its text as account name and the other text view set its text as the server name?, thx.

java.padawan said...

I just updated my post.

You just need to modify account_row.xml and AccountList.java. See above.

Anonymous said...

Hi, i would like to find out where can i find the database for this example? which directory is the database stored?

Александр said...

Very nice idea use Active record.
Thank's.

Keith said...

Thanks for the great tutorial.

I need validation in my project's models. I re-implemented your AR interface as an abstract class to include a number of basic validation methods. If you are so inclined, check out my changes at:

http://www.zunisoft.com/?p=1062

Thanks again, you saved me a lot of time!

Nick Teo said...

Hi! thanks for the tutorial! it helped me a whole lot for my final year project in my polytechnic.

however, i've encountered an error in AccountList.java. There is a red line below

R.string.btnDelete

in

menu.add(0, DELETE_ID, 0, R.string.btnDelete);

i checked my R.java, and saw that btnDelete is not in the string class. I tried adding it manually, but eclipse just reverts it back to the original one. can you help please?

java.padawan said...

Hi nick,

you cannot modify R.java, this is generated.

If you need to change the string values, on strings.xml.

Here's the old source code.
http://mobileserver.byethost2.com/?attachment_id=31

Unknown said...

Great article. How i can show icons for each account in list view.
These icons should come from db and each account has different icon.
I mean one icon is associated with one account.

thanks

Unknown said...

I didnt understand what is this... its good if u tell us step by step like

1. How to create a database.
2. How to insert values dynamically in it.
3. How to fetch database values.
4. How to fetch database values and show it in the tableview or listview.

Your help will be highly appreciated if u help me in these above points.

Thanks,

Jimmy

java.padawan said...

Hi Jimmy,

CREATE DB
Look for MyDatabaseAdapter class
Account.SQL_CREATE_TABLE is an sql CREATE statement.

db.execSQL(Account.SQL_CREATE_TABLE);

On Account class
values.put(COL_SERVER_NAME, serverName);
values.put(COL_USERNAME, username);
values.put(COL_PASSWORD, password);
return sqliteDatabase.insert(SQL_TABLE_NAME, null, values);

As for retrieval, it's not as simple as a result set,

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

This will return a cursor object, just check the

public void load(Activity activity) on Account object

I hoped that made it clear.

maria said...

Very good article, a good starter..

but i have few confusions with the given source code.
I am not able to select a item in the list for delete, when i select an item, it goes to the edit screen. how can this be handled?

rg said...

Hi, This is a very helpful example. Thank you for posting.
I am having an issue trying to select an entry from the list to delete it. Once I click on an item, it takes me to the edit/add row screen. Please advise how to fix this so that the delete works.
Thanks.

bb-tahira said...

Thanks for the great tutorial.

i have issu in it,when ever run this code avd shw that APPLICATION STOPED UNEXPECTEDLLY.

plz help me out,
vry thanks in advance.

Anonymous said...

Hi! thanks for the great tutorial.! it helped me a lot.Thank you for posting.

Email

java.padawan@androidph.com