SQLite Android Wrapper

DBHelper.java

/*
Source -> http://www.michenux.net/android-database-sqlite-creation-upgrade-245.html

- make sql file like below and save inside /assets/sql/
Create:		mydb-create.sql
Upgrades:	mydb-upgrade-101.sql, mydb-upgrade-102.sql

Do Not Edit (unless you know what you're doing)
*/

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Arrays;
import java.util.Date;
import java.text.SimpleDateFormat;

import android.content.Context;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;

public class DBHelper extends SQLiteOpenHelper {
	private static final String SQLDIR = "sql";
	private String mName;
    	private SQLiteDatabase mDb;
    	private Cursor mCursor;
	private Context mAppContext;

	public DBHelper(Context context, String name) {
		mAppContext = (context instanceof Activity) ? context.getApplicationContext() : context;
		int ver = getVersionCode();
		super(mAppContext, name+".db", null, ver);
		init(name, ver);
	}

	private void init(String name, int version) {
		mName = name;
		mDb = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			execSqlFile(mName + "-create.sql", db);
		} catch (Exception e) {
			throw new RuntimeException("Database creation failed", e);
		}
		onUpgrade(db, 1, getVersionCode()); //force upgrade on new db
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			String prefix = mName + "-upgrade-";
			String[] files = mAppContext.getAssets().list(SQLDIR);
			Arrays.sort(files);
			for (String sqlFile : files) {
				if (sqlFile.startsWith(prefix)) {
					int fileVersion = Integer.parseInt(sqlFile.substring(prefix.length(), sqlFile.length() - ".sql".length())); 
					if (fileVersion > oldVersion && fileVersion <= newVersion ) execSqlFile(sqlFile, db);
				}
			}
		} catch(Exception e) {
			throw new RuntimeException("Database upgrade failed", e);
		}
	}

	/* Parser hepler */
	private void execSqlFile(String sqlFile, SQLiteDatabase db) throws Exception {
		for (String sql : parseSqlFile(SQLDIR + "/" + sqlFile)) db.execSQL(sql);
	}

    public static List<String> parseSqlFile(String sqlFile) throws Exception {
        List<String> sqlIns = null ;
        InputStream is = mAppContext.getAssets().open(sqlFile);
        try {
            sqlIns = parseSqlFile(is);
        }
        finally {
            is.close();
        }
        return sqlIns;
    }

	public static List<String> parseSqlFile(InputStream is) throws Exception {
		String script = removeComments(is);
		return splitSqlScript(script, ';');
	}

	private static String removeComments(InputStream is) throws Exception {
		StringBuilder sql = new StringBuilder();
		InputStreamReader isReader = new InputStreamReader(is);
		try {
			BufferedReader buffReader = new BufferedReader(isReader);
			try {
				String line;
				String multiLineComment = null;
				while ((line = buffReader.readLine()) != null) {
					line = line.trim();

					if (multiLineComment == null) {
						if (line.startsWith("/*")) {
							if (!line.endsWith("}")) multiLineComment = "/*";
						} else if (line.startsWith("{")) {
							if (!line.endsWith("}")) multiLineComment = "{";
						} else if (!line.startsWith("--") && !line.equals("")) sql.append(line);
					} else if (multiLineComment.equals("/*")) {
						if (line.endsWith("*/")) multiLineComment = null;
					} else if (multiLineComment.equals("{")) {
						if (line.endsWith("}")) multiLineComment = null;
					}
				}
			} finally { buffReader.close(); }
		} finally { isReader.close(); }

		return sql.toString();
	}

	private static List<String> splitSqlScript(String script, char delim) {
		List<String> statements = new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		boolean inLiteral = false;
		char[] content = script.toCharArray();
		for (int i = 0; i < script.length(); i++) {
			if (content[i] == '\'') inLiteral = !inLiteral;
            if (content[i] == delim && !inLiteral) {
				if (sb.length() > 0) {
					statements.add(sb.toString().trim());
					sb = new StringBuilder();
				}
			} else sb.append(content[i]);
		}
		if (sb.length() > 0) statements.add(sb.toString().trim());
		return statements;
	}


	/* Operation helper */
	public SQLiteDatabase getDB() { return mDb; }

	public Cursor getCursor() { //shared cursor
		if (mCursor != null) { mCursor.close(); mCursor = null; }
		return mCursor;
	}

	public int getTotalRows(String table) {
		return (int) DatabaseUtils.queryNumEntries(mDb, table);
	}

	public Cursor getAllRows(String table) {
		Cursor cursor = mDb.rawQuery( "SELECT rowid _id, * FROM " + table, null);
		if (cursor != null) cursor.moveToFirst();
		return cursor;
	}

	public int deleteRow(String table, long rowId) { //return total deletes
		return mDb.delete(table, "rowid = " + Long.toString(rowId), null);
	}

	public static int getVersionCode() {
		android.content.pm.PackageInfo pi;
		try {
			pi= mAppContext.getPackageManager().getPackageInfo(mAppContext.getPackageName(), 0);
		} catch(Exception e){ return 1; }
		return pi.versionCode;
	}

}

DBUtil.java

/* Example singleton wrapper */

import android.content.Context;

public final class DBUtil {
	public static final String DATABASE_NAME = "mydb";
	private static DBHelper mHelper;

	public static DBHelper helper(Context context) {
		if (mHelper == null) mHelper = new DBHelper(context, DATABASE_NAME);
		return mHelper;
	}

}

example.java

/* Example usage */

public void queryTest() {
  Cursor cursor = DBUtil.helper(this).getDB().rawQuery("SELECT query FROM search_history ORDER BY last_query DESC LIMIT 20", null);

  cursor.moveToFirst();
	  while (!cursor.isAfterLast()) {
		  list.add(cursor.getString(0));
			 cursor.moveToNext();
	  }
  cursor.close();
}

public boolean addHistory(String q) {
	ContentValues values = new ContentValues();
	values.put("query", q);
	values.put("last_query", System.currentTimeMillis());
	return (DBUtil.helper(this).getDB().replace("search_history", null, values) != -1);
}