Database programming with SQLite

If you're like me, you probably have several different accounts for different things, you email, your paypal, your dropbox and so on. I often have to use different passwords as the password requirements differ from account to account. I decided to work on a simple app that allows me to store all the accounts logins and passwords and, in order to do so, I have to implement database programming.

This post is about SQL and the "lite" version we find on Android devices.

In my project, a created a class Account, which will create objects with the information we want to store.

The class is displayed below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

public class Account { private String thePassword; private String title; private String id; public String getId() { return id; } public void setId(String id) { this.id = id; } public Account(String t, String id, String psw) { thePassword = psw; title = t; this.id = id; } public String getThePassword() { return thePassword; } public void setThePassword(String thePassword) { this.thePassword = thePassword; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } }

Nothing special at all, the class contains 3 String parameters for the information needed.

Now we can create our database helper class. The class itself is called PswDatabase, I will show you each method one by one.

Class declarataion and parameters

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

public class PswDatabase extends SQLiteOpenHelper { private static List<IDatabaseListener> databaseListeners; private static PswDatabase instance; private static final String DATABASE_NAME = "passwords_database"; private static final String TABLE_NAME = "passwords_table"; private static final int DATABASE_VERSION = 1; private final String COLUMN_ID = "id"; private final String COLUMN_PSW_ID = "login"; private final String COLUMN_PSW = "password"; private final String COLUMN_TITLE = "title"; }

The class will extend SQLiteOpenHelper and these above are the parameters needed.
Since I have only one database and I wanted to be able to call it from anywhere in the project I made this class a singleton, which is why you see the instance object on line 3.

Constructor, getInstance, on create and on upgrade

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

public PswDatabase(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } public static synchronized PswDatabase getInstance() { if(instance==null) { databaseListeners = new ArrayList<IDatabaseListener>(); instance = new PswDatabase(MainActivity.context); } return instance; } @Override public void onCreate(SQLiteDatabase db) { String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_NAME + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," // cursror 0 + COLUMN_TITLE + " TEXT," // cursror 1 + COLUMN_PSW_ID + " TEXT, " // cursror 2 + COLUMN_PSW + " TEXT" + ")"; // cursror 3 db.execSQL(CREATE_CONTACTS_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); }

All these methods (except for getInstance) are automatically inherited from SQLiteOpenHelper and we must implement a body for them.

The constructor simply call the base class constructor, passing the name and version of the database.

The getInstance() method will check if the instance object has already been instantiated. If not, it will call the constructor in order to create the object, passing MainActivity.this as context (static parameter) and it will finally return the object itself.

In onCreate(...) we execute our first sql statement, which will need to create the table and set its columns along with their variable type stored. Finally, onUpgrade(...), which is usually called whenever the database needs upgrading. This is a very standard implementation of sql in Android and there is not much I can add to the already many tutorials out there. The best learning source is of course the Android dev website and its database programming tutorial.

Adding, deleting, updating records

Here is how we add a new account to our database. The method checks first to make sure there is no other account stored with the same title, as the application will not accept 2 entries with the same title parameter.

In case a record with the same title is found, the application will notify the user (via Toast) of the event and won't add any new row in the database.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

public boolean addAccount(Account a) { //Check for Title. Two accounts with matching titles are not allowed Account pass = getAccountByTitle(a.getTitle()); if(pass!=null) { Toast.makeText(MainActivity.appContext,"Cannot duplicate titles",Toast.LENGTH_LONG).show(); return false; } SQLiteDatabase db = getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put(COLUMN_TITLE,a.getTitle()); cv.put(COLUMN_PSW_ID,a.getId()); cv.put(COLUMN_PSW, a.getThePassword()); db.insert(TABLE_NAME, null, cv); db.close(); return true; }

Below, the method in charge of deleting records:

1 2 3 4 5 6 7

public void deleteAccountByTitle(String t) { SQLiteDatabase db = getWritableDatabase(); db.delete(TABLE_NAME,COLUMN_TITLE + " = ?",new String[]{t}); db.close(); }

Throughout the application, all the records are identified by their title. The delete method of the database object will delete any entry in the table of name TABLE_NAME, where the title column (COLUMN_TITLE) is equal to the parameter passed vie the String array (String[]{t}). So, when we pass a title, the record associated with it will be removed from the database.

Replacing a record:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

public void replaceAccount(String toReplacePasswordTitle, Account replaceWith) { SQLiteDatabase db = getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put(COLUMN_TITLE,replaceWith.getTitle()); cv.put(COLUMN_PSW_ID,replaceWith.getId()); cv.put(COLUMN_PSW, replaceWith.getThePassword()); db.update(TABLE_NAME, cv, COLUMN_TITLE + " = ?", new String[]{toReplacePasswordTitle}); db.close(); }

In a similar way, we select the record by its title and update its colums using the content values, just like we did when adding a new account.

Retrieving information

At last, a few methods to retrieve the information stored.

First, if we want to return a single Account object:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

public Account getAccountByTitle(String t) { Account a; String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COLUMN_TITLE + "= ?"; SQLiteDatabase db = getReadableDatabase(); Cursor cursor = db.rawQuery(q,new String[]{t}); if(cursor.moveToFirst()) { do { a = new Account(cursor.getString(1),cursor.getString(2),cursor.getString(3)); return a; //break the loop at first password found }while(cursor.moveToNext()); } else { Toast.makeText(MainActivity.appContext,"Could not find the account with title -" + t + "-",Toast.LENGTH_LONG).show(); return null; } }

We initially create an empty account object, a.

The sql string will select any record (SELECT *) from the table named TABLE_NAME ,where the coulm title (COLUMN_TITLE) is equal to the parameter t, passed in a String array on line 7.

this line will return a cursor, which is a pointer to a position in the database.

We move the cursor to its first position assuming it has found something according to the parameters passed to it. If it hasn't, we will notify the user that account (with title t) is not in the table.

If the cursor has found something, we instantiate a new Account object passing the values obtaint from the cursor while moving it from colum to column (cursor.getString(int columnIndex)). The first column (0) is the integer value COLUMN_ID, which we are not using. The second column is the title, then the password_id (which I should have called account_id really...) and finally the password. See the comments in the onCreate method of this class.

As soon as the object is instantiated, we can return it and interrupt the do-while loop.

One other thing we might need is to retrieve a list of all the accounts in the database. Here's how:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

public List<Account> getAccountList() { List<Account> list = new ArrayList<Account>(); SQLiteDatabase db = getReadableDatabase(); Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME,null); if(cursor.moveToFirst()) { do{ Account account = new Account(cursor.getString(1),cursor.getString(2),cursor.getString(3)); list.add(account); }while (cursor.moveToNext()); } return list; }

Again, we create a cursor which this time, however, has no conditions. It will select anything from the table. So, for every row starting from the first one available (cursor.moveToFirst()) we instantiate an Account object and add it to the list. when cursor.moveToNext() returns false, means that there are no more rows in the database, and the do-while loop will end so we can finally return the list.

Conclusion

This is a pretty standard implementation of database programming in Android. In the next post, I'm going to place all the records in a ListView so we can display them to the user.