AbstractDb.java

package de.databasetemplate.db;

import java.util.HashMap;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

/**
 * This class provides basic functionality to use a database table.
 * 
 * @author Andreas Bender
 */
public abstract class AbstractDb {

	public static final String COL_ID     = "_id";
	
	protected SQLiteHelper dbHelper;
	protected SQLiteDatabase database;
	
	public AbstractDb(Context context) {
		this.dbHelper = new SQLiteHelper(context);
	}
	
	public void open() {
		database = dbHelper.getWritableDatabase();
	}
	
	public void close() {
		dbHelper.close();
	}
	
	/**
	 * Create a new entry.
	 * 
	 * @param values A HashMap containing Column - Value pairs.
	 *  new HashMap<String, Object>().put(COL_IP4, "127.0.0.1");
	 * @return The ID of the newly created entry or -1 if an error occured.
	 */
	public long create(HashMap<String, ?> values) {
		
		ContentValues storeValues = new ContentValues();

		for(Map.Entry<String, ?> entry : values.entrySet()) {
			String column = entry.getKey();
			if(isStringColumn(column)) {
				storeValues.put(column, (String) entry.getValue());
			} else if(isIntegerColumn(column)) {
				storeValues.put(column, (Integer) entry.getValue());
			} else {
				Log.w(getLogTag(), IdentitiesDb.class.getName() + 
						"- create: Trying to insert an unknown column type! " +
						"(Column: " + column + ")");
			}
		}
		
		return database.insert(getTableName(), null, storeValues);
	}
	
	public void delete(long id) {
		database.delete(getTableName(), COL_ID + " = " + id, null);
	}
	
	/**
	 * Update an existing entry.
	 * 
	 * @param id The id identifying the entry.
	 * @param values A HashMap containing Column - Value pairs.
	 *  new HashMap<String, Object>().put(COL_IP4, "127.0.0.1");
	 * @return True if any row has been changed. False otherwise.
	 */
	public boolean update(long id, HashMap<String, ?> values) {
		ContentValues storeValues = new ContentValues();
		
		for(Map.Entry<String, ?> entry : values.entrySet()) {
			String column = entry.getKey();
			if(isStringColumn(column)) {
				storeValues.put(column, (String) entry.getValue());
			} else if(isIntegerColumn(column)) {
				storeValues.put(column, (Integer) entry.getValue());
			} else if(isBlobColumn(column)) {
				storeValues.put(column, (byte[]) entry.getValue());
			} else {
				Log.w(getLogTag(), IdentitiesDb.class.getName() + 
						"- update: Trying to update an unknown column type! " +
						"(Column: " + column + ")");
			}
		}
		return database.update(getTableName(), storeValues, COL_ID+" = "+id, null) > 0;
	}
	
	/**
	 * Find an item based on its ID.
	 * 
	 * @param id The ID of the required item.
	 * @return A Cursor pointing to the required item or null if it's not present.
	 */
	public Cursor get(long id) {
		
        Cursor cursor = database.query(true, getTableName(), getColumns(),
                        COL_ID + "=" + id, null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
        return cursor;
		
	}
	
	/**
	 * Find all items of the table.
	 * 
	 * @return A Cursor pointing to the first item of the table.
	 */
    public Cursor all() {
    	return database.query(getTableName(), getColumns(), null, null, null, null, null);
    }
    
    public boolean isEmpty() {
    	return (all().getCount() == 0);
    }
    
    /**
     * @return All columns of the table, including the ID-column.
     */
    protected abstract String[] getColumns();
    
    /**
     * @return The name of the table.
     */
    protected abstract String getTableName();
    
    /**
     * @return The log tag used by LogCat, something like "MY_APP - DB"
     */
    protected abstract String getLogTag();
    
    /**
     * @param c The column which needs to be evaluated.
     * @return True if the column stores Strings, false otherwise.
     */
    protected abstract boolean isStringColumn(String c);

    /**
     * @param c The column which needs to be evaluated.
     * @return True if the column stores Integers, false otherwise.
     */
    protected abstract boolean isIntegerColumn(String c);
    
    /**
     * @param c The column which needs to be evaluated.
     * @return True if the column stores Blobs, false otherwise.
     */
    protected abstract boolean isBlobColumn(String c);
	
}

IdentitiesDb.java

package de.databasetemplate.db;

import android.content.ContentValues;
import android.content.Context;

public class IdentitiesDb extends AbstractDb{

	public static final String TABLE_NAME = "identities";
	public static final String COL_USER   = "user";
	public static final String COL_PW     = "pw";
	
	public static final String[] COLUMNS = {
		COL_ID, COL_USER, COL_PW
	};
	
	public IdentitiesDb(Context context) {
		super(context);
	}
	
	protected String[] getColumns() {
		return COLUMNS;
	}
	
	protected String getTableName() {
		return TABLE_NAME;
	}
	
	protected String getLogTag() {
		return "MY_APP";
	}
	
    protected boolean isStringColumn(String c) {
    	return (c == COL_USER || c == COL_PW);
    }
    
    protected boolean isIntegerColumn(String c) {
    	return (c == COL_ID);
    }
    
    public void createTestIdentities() {
    	
    	ContentValues v1 = new ContentValues();
    	v1.put(COL_USER, "hugo");
    	v1.put(COL_PW, "foobar");
    	database.insert(TABLE_NAME, null, v1);
    	
    	ContentValues v2 = new ContentValues();
    	v2.put(COL_USER, "testuser");
    	v2.put(COL_PW, "123456");
    	database.insert(TABLE_NAME, null, v2);
    }
	
}

SQLiteHelper.java

package de.databasetemplate.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This class manages database creation and updates.
 * 
 * Based on "Android SQLite Database and ContentProvider - Tutorial" by Lars
 * Vogel. ( http://www.vogella.com/articles/AndroidSQLite/article.html )
 * 
 * @author Andreas Bender
 *
 */
public class SQLiteHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "my_database.db";
	private static final int DATABASE_VERSION = 1;

	private static final String CREATE_IDENTITIES = "create table "
	  + IdentitiesDb.TABLE_NAME + "(" +
      IdentitiesDb.COL_ID + " integer primary key autoincrement, " +
	  IdentitiesDb.COL_USER + " text " +
      IdentitiesDb.COL_PW + " text " +
      ");";
	
	  public SQLiteHelper(Context context) {
	      super(context, DATABASE_NAME, null, DATABASE_VERSION);
	  }

	  @Override
	  public void onCreate(SQLiteDatabase database) {
	      database.execSQL(CREATE_IDENTITIES);
	  }

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

}