Executing multiple statements with SQLiteDatabase.execSQL

Question

I've followed a standard tutorial for building a database with Android. I've created a class called DbHelper which extends SQLiteOpenHelper. I've Overridden the create handler to execute a string.

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(DbDefinitions.DB_CREATE);
}

DbDefinitions.DB_CREATE is a static String I've created.

public static final String TABLE_MESSAGES = "messages";
public static final String TABLE_FRIENDS = "friends";

public static final String STATE_OK = "STATE_OK";

public static final String DB_CREATE = 
    "create table " + TABLE_MESSAGES + " (_id integer primary key, user_id integer not null, created_on integer, subject text not null, summary text not null, messagetext text null, read integer not null, status text not null default '" + STATE_OK + "'); " +
    "create table " + TABLE_FRIENDS + " (_id integer primary key, user_id integer not null, friend_id integer not null, created_on integer, status text not null default '" + STATE_OK + "');";

I'd like to use 1 String to execute multiple SQL statements. How can I do this as SQLiteDatabase.execSQL only allows 1 statement?

Answer 1

From the documentation of SQLiteDatabase and my experience in the past I think that it's not possible. But why don't you just split it up in single statements? It really is not a problem in your example. Or do you need it for a different use case?

Answer 2

That's not possible to do using the standard methods which comes with Android. So, if you want to execute batch of multiple SQL statements, you will have to create your own utility to do so. For instance, you can have something like this:

public void executeBatchSql(String sql){
    // use something like StringTokenizer to separate sql statements
    for each sql statement{
        database.execSQL(oneStatement);
    }
}

Though, what I'd do is something like this:

String sql1 = "create bla bla bla;";
String sql2 = "create foo bar;";
String[] statements = new String[]{sql1, sql2};

// then
for(String sql : statements){
    database.execSQL(sql);
}