Creating a SQLite database in Android

This example will show you how to create a somewhat abstracted SQLite adapter on Android. This adapter can then be utilized by your program to do common database functions such as, querying and searching. I start by creating the class DBAdapter in my Android project and declaring the variable data necessary to create the database.

EDIT: The code has been modified since it originally assumed that a key option would be passed for every key. It will now work if no key options are passed. Additionally, “null” can also be passed to it if no key options are desired.

public class DBAdapter{

	private static final String DATABASE_NAME = "example.db";
	private String DATABASE_TABLE;

	private static final int DATABASE_VERSION = 1;

DATABASE_NAME is of course the name of the database we will be creating. This is usually done one database per program. The database can be found in /data/data/
/databases/.
DATABASE_TABLE will be the name of the table you will be creating. We will make it so that wen a new instance of the database is created, the table name has to be passed so that new tables can be created without the need to create a new class, or creating static table assignments.
DATABASE_VERSION is the version of the database we will be creating; 1 is fine here.

Now we must create the variables to set our table structure.

// Index Key column
public static final String KEY_ID = "_id";

// Name of the column index of each column in DB
public  ArrayList<String> TABLE_KEYS =  new ArrayList<String>();
public  ArrayList<String> TABLE_OPTIONS = new ArrayList<String>();
public  final String KEY_TIMESTAMP = "timeStamp";
public  final int TIMESTAMP_COLUMN = 1;

We first create our primary key value which will have the row numbers of the data entered in our table. This is the KEY_ID string, which contains “_id”. The actual column heading that will show up on the table will be “_id”. In order to abstract this helper as much as possible, the keys and options for the keys will be passed in as ArrayLists of string objects from the class that calls the DBAdapter. TABLE_KEYS refers to the column name, and TABLE_OPTIONS refers to the options for that column (these can be “text not null”, setting the column type to “integer”, etc.). I always like creating a column which contains a timestamp in case I want to sort the data by time, which is what the KEY_TIMESTAMP is. TIMESTAMP_COLUMN sets the column number of the timestamp column to 1, which makes it easier to select data later on. This is not necessary since you can get the column numbers by searching for the column name (I’ll go over this later).

// Create new database
	private String DATABASE_CREATE;

	// Variable to hold database instant
	private SQLiteDatabase db;

	// Database open/upgrade helper
	private myDBHelper dbHelper;

DATABASE_CREATE This variable will contain the string we will use to create the database. This is where we will set our parameters and key names.
db is the database instance we will create.
dbHelper is an instance of the class myDBHelper which we will create later within this class. This is what actually initiates the database and creates tables if needed, etc. (This extends SQLiteOpenHelper).

	public DBAdapter(Context context, String table, ArrayList<String> keys, ArrayList<String> options){
		// Start initializing all of the variables
		DATABASE_TABLE = table;
		TABLE_KEYS = (ArrayList<String>)keys.clone();
		TABLE_OPTIONS = options;

		String keyString = "";
		for(int i = 0; TABLE_KEYS.size() > i; i++){

			// Add commas to the options elements if there is a next value.
			if(i + 1 < TABLE_OPTIONS.size() && TABLE_OPTIONS.get(i) != null){
				TABLE_OPTIONS.set(i, TABLE_OPTIONS.get(i) + ",");
			}else if (i + 1 == TABLE_OPTIONS.size() && TABLE_OPTIONS.get(i) != null) {
				if(i + 1 < TABLE_KEYS.size()){
					TABLE_OPTIONS.set(i, TABLE_OPTIONS.get(i) + ",");
				}else {
					TABLE_KEYS.set(i, TABLE_KEYS.get(i) + "");
				}
			}else if (i + 1 != TABLE_KEYS.size()) {
				TABLE_KEYS.set(i, TABLE_KEYS.get(i) + ",");
			}else {
				TABLE_KEYS.set(i, TABLE_KEYS.get(i) + "");
			}

			System.out.println(TABLE_OPTIONS.toString());
			System.out.println(TABLE_KEYS.toString());

			if(i + 1 <= TABLE_OPTIONS.size() && TABLE_OPTIONS.get(i) != null)
				keyString = keyString + " " + TABLE_KEYS.get(i) + " " + TABLE_OPTIONS.get(i);
			else if(i + 1 > TABLE_OPTIONS.size() || TABLE_OPTIONS.get(i) == null){
				keyString = keyString + " " + TABLE_KEYS.get(i);
			}
		}

		// Create the database creation string.
		DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE + " ("
		+ "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TIMESTAMP + "," + keyString + ");";

		// Create a new Helper
		dbHelper = new myDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION,
				DATABASE_TABLE, DATABASE_CREATE);
	}

This is the constructor for DBAdapter, which sets up all of the variables for further calls to other methods in this class. This opens the database if it exists or create it if it doesn’t. Additionally checks if the
table exists and creates it if it doesn’t. The ArrayLists of string objects for the key names and options are parsed, and commas are added where they need to be so that the DATABASE_CREATE creation string can be created. A clone of the keys ArrayList is created so that the changes we make (adding commas at the end of options and keys if there will be something following them) here will no affect the variable when we call it later. The last thing we do is instantiate the database by calling our dbHelper, which is the actual function that creates the database with the parameters which we have given it in our creation string, the database name, table name, etc.).

public DBAdapter open() throws SQLException {
		db = dbHelper.getWritableDatabase();
		return this;
	}

public void close() {
		db.close();
	}

These function allow opening and closing of the database. It is necessary to open the database before data can be written or read from it. Likewise it is necessary to close the database once one has finished with it, otherwise memory leaks occur.

public long insertEntry(ArrayList<String> key, ArrayList<String> value) {
		String timeStamp = new Timestamp(Calendar.getInstance().getTimeInMillis()).toString();
		ContentValues contentValues = new ContentValues();
		for(int i = 0; key.size() > i; i++){
			contentValues.put(key.get(i), value.get(i));
		}
		contentValues.put(KEY_TIMESTAMP, timeStamp);
		return db.insert(DATABASE_TABLE, null, contentValues);
	}

This method is used to insert and entry into the database. It takes in two variables of ArraList of string objects which contain the column names or keys and the data to be added into each key. When an entry is inserted this method also sets the timestamp value automatically.

public boolean removeEntry(long rowIndex) {
		return db.delete(DATABASE_TABLE, KEY_ID + "=" + rowIndex, null) > 0;
	}

This method removes a row of data from the database. The rowIndex must be provided. This can be acquired by running a query and getting the _id field from the result.

public Cursor getAllEntries(String[] columns, String selection, String[] selectionArgs,
			String groupBy, String having, String sortBy, String sortOption) {
		return db.query(DATABASE_TABLE, columns, selection, selectionArgs, groupBy,
				having, sortBy + " " + sortOption);
	}

This method runs a query and returns a cursor through all of the matching results.
columns is an string array of columns to be included in the result.
selection is the value to look for in the columns. A null will return all results.
selectionArgs is a string array of arguments for the selection. Can be null.
groupBy option to group the results by. Can be set to null.
having a filter declare which row groups to include in the cursor. Can be null.
sortBy column to sort the results by.
sortOption is how to sort the results, ASC for ascending, DESC for descending.

public int updateEntry(long rowIndex, ArrayList<String> key, ArrayList<String> value) {
		String timeStamp = new Timestamp(Calendar.getInstance().getTimeInMillis()).toString();
		String where = KEY_ID + "=" + rowIndex;
		ContentValues contentValues = new ContentValues();
		for(int i = 0; key.size() > i; i++){
			contentValues.put(key.get(i), value.get(i));
		}
		contentValues.put(KEY_TIMESTAMP, timeStamp);
		return db.update(DATABASE_TABLE, contentValues, where, null);
	}

This method updates a row in the database. This takes in the same parameters as the method to insert a new row, with the additional rowIndex variable, which is the number of the row to update.

public boolean clearTable() {
		return db.delete(DATABASE_TABLE, null, null) > 0;
	}

I’m not particularly fond of this method since it can cause more harm than good. However, I think that it’s good to have it for testing purposes. This of course clears all of the contents of a table.

private static class myDBHelper extends SQLiteOpenHelper {
		private String creationString;
		private String tableName;
		@SuppressWarnings("unused")
		SQLiteDatabase db;

		/**
		 * Creates a myDBHelper object.
		 * @param context The context where the access is needed
		 * @param name Name of database file
		 * @param factory A CursorFactory, or null to use default CursorFactory
		 * @param version Database version
		 * @param tableName Name of table within database
		 * @param creationString SQL String used to create the database
		 */
		public myDBHelper(Context context, String name, CursorFactory factory,
				int version, String tableName, String creationString) {
			super(context, name, factory, version);
			this.creationString = creationString;
			this.tableName = tableName;
		}

		/**
		 * Creates the database table.
		 * @param db The database used by this helper to create the table in
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(creationString);
		}

		/**
		 * This method determines if the database needs to be updated or not.
		 * @param db The database used by this helper
		 * @param oldVersion The old database version
		 * @param newVersion The new database version
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// Log the version upgrade
			Log.w("TaskDBAdapter", "Upgrading from version " + oldVersion +
					" to " + newVersion + ", which will destroy all old data");

			db.execSQL("DROP TABLE IF EXISTS " + tableName);
			onCreate(db);

		}

		/**
		 * Creates tables when the database is opened if the tables need to be created.
		 * @param db The database used by this helper
		 */
		@Override
		public void onOpen(SQLiteDatabase db) {
			db.execSQL(creationString);
		}

	}
}

This subclass is a helper for DBAdapter and does the job of creating the database and checking if the database needs an upgrade to new version depending on version number specified by DBAdapter.

For instructions on using this database adapter please continue to “Using a SQLite Database in Android”

You can download the full code below.

[ad#Google Adsense Text Ad Post]