Android: Create tables in SQLite beautifully

November 13, 2014

I started Android Development a while back and this is the first time I’ve had to use SQLite. Coming from a Rails and Mongo background, you can imagine my horror when I had to create models (read ‘tables’) like this:

@Override
public void onCreate(SQLiteDatabase db) {
    // SQL statement to create book table
    String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "title TEXT, " +
            "author TEXT, " +
            "sales INTEGER )";

    // create books table
    db.execSQL(CREATE_BOOK_TABLE);
}

Okay, I know it’s not that bad. But what if you have to create multiple tables at a time? Or when there are a lot of fields? It can get confusing, and looks dirty. Code should be beautiful. So, I wrote a function so you could do something like this:

@Override
public void onCreate(SQLiteDatabase db) {
	createTable(db, TABLE_NAME, TABLE_FIELDS);
}

Beautiful, right? You just need to copy the createTable function in your SQLiteHelper.java and pass the db instance, a String of the table name and a HashMap of all your fields. Check it out:

public class SQLiteHelper extends SQLiteOpenHelper {
    private static final HashMap<String, String> BOOK_FIELDS = new HashMap<String, String>() { {
        put("title",     "TEXT");
        put("author",    "TEXT");
        put("sales",  "INTEGER");                          // Neatly write fields and their types
    }};  

    // ...

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTable(db, "books", BOOK_FIELDS);          // Create the table in a single, beautiful line.
    }

    // This function does all the dirty work
    static public void createTable(SQLiteDatabase db, String tableName, HashMap<String, String> fields) {
        String command = "CREATE TABLE " + tableName + " ( id INTEGER PRIMARY KEY AUTOINCREMENT";

        for (Map.Entry<String, String> entry : fields.entrySet())
            command = command + ", " + entry.getKey() + " " + entry.getValue();

        command = command + " )";
        db.execSQL(command);
    }
    
    // ...
}