SQLite on Android: Executing an Entire SQL Script

The SQLite API on Android doesn't expose the read command that can be used on the command line to run SQL files against the database. The only option left is to use execSQL(). The problem with this is execSQL() will only process one SQL statement and return. Meaning you must break your SQL script into multiple parts and pass each part to execSQL() in sequence.

The below is my solution to this. It is an SQLiteOpenHelper implementation that reads the SQL file into a buffer. Once a line is found that ends with a semi-colon (;) indicating the end of an SQL statement, the buffer is passed to execSQL() and cleared ready for the next statement. If you need this outside of an SQLiteOpenHelper, see the onCreate() method body.

Note: The below has been tested on SQLite3 on Android versions 6-8 and has been tested performing table creation, record insertion and foreign key constraints which covers my needs.

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

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.HashMap;


/**
 * {@link SQLiteOpenHelper} implementation that creates and upgrades the
 * application database.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "appdata.db";
    private static final int DB_VERSION = 1;
    private static final String TAG = "DatabaseHelper";

    private Context mContext;

    public SfDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Database creation scripts should be placed in assets/sql
        String createDbFile = "sql/db_create_v" + DB_VERSION + ".sql";
        Log.v(TAG, "Creating new database from " + createDbFile + ".");

        AssetManager am = mContext.getAssets();
        StringBuilder createStatement = new StringBuilder();
        long startTime = System.currentTimeMillis();
        db.beginTransaction();
        try {
            InputStream is = am.open(createDbFile);
            InputStreamReader isr = new InputStreamReader(is);
            BufferedReader br = new BufferedReader(isr);
            String line;
            /* Cache the file line by line, when the line ends with a
             * semi-colon followed by a line break (end of a SQL command),
             * execute it against the database and move on. */
            while ((line = br.readLine()) != null) {
                String lineTrimmed = line.trim();
                if (lineTrimmed.length() == 0)
                    continue;
                createStatement.append(line).append("\r\n");
                if (lineTrimmed.endsWith(";")) {
                    Log.d(TAG, "Executing SQL: \r\n" + createStatement.toString());
                    db.execSQL(createStatement.toString());
                    createStatement.setLength(0);
                }
            }
            br.close();
        } catch (IOException e) {
            Log.e(TAG, "IOException thrown while attempting to "
                    + "create database from " + createDbFile + ".");
            return;
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        Log.i(TAG, "New database created from script "
                + createDbFile + " in " +
                (System.currentTimeMillis() - startTime) +"ms.");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int from, int to) {
        // Nothing to do yet!
    }
}