Skip to content

Instantly share code, notes, and snippets.

@akmalxxx
Last active November 7, 2017 18:24
Show Gist options
  • Save akmalxxx/d225a88b116a1aadea35 to your computer and use it in GitHub Desktop.
Save akmalxxx/d225a88b116a1aadea35 to your computer and use it in GitHub Desktop.
SQLite Android Wrapper
/*
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;
}
}
/* 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 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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment