android sqlite made easy tutorial

Hi!

Start using SQLite in your android app is very easy! SQLite is supported from start for any android app, you dont need to configure anything. There are some frameworks, but i found easier to use without them. Also using frameworks have some litimations, for example currently frameworks do not support encryption of the database.

Also check my second post about SQLite: http://tocrva.blogspot.com/2015/08/android-sqlite-best-practices-how-to.html

1) private field in the activity and add the needed imports:

private SQLiteDatabase db;

2) In onCreate method:

db = openOrCreateDatabase("dbname.db", MODE_PRIVATE, null);

As you might understand, this creates or opens a database named dbname.db in private mode. Private mode means that only your app will have access to this database. Note that the database itself is persisted in text file form and can be accesses easily from a rooted device or a emulator.

3) Now you must create a table inside your database:

db.execSQL("CREATE TABLE IF NOT EXISTS mytable (column1 VARCHAR, column2 VARCHAR);");

This also is self explanatory. Standart SQL sintax. The execSQL method is used to execute sql queries that do not return anything.

4) Inserting values, the best practice using ContentValues (inside try/catch):

ContentValues cv = new ContentValues();

cv.put("column1", "some value");

db.insert("mytable", null, cv);

5) Fetching values. You can provide a static query in the first argument of rawQuery method, or use array of arguments new String[]{"arg1", "arg2", "arg3"}, those will be put in places of "?" in the query:

Cursor c = db.rawQuery("select * from mytable where column1 = ?", new String[]{"some value"});

c.moveToPosition(-1);

while (c.moveToNext()) {

    String column1value = c.getString(0);

}

c.close();

6) Performance optimisation for cycled operations:

db.beginTransaction();

try {

    for(){

        //some insert query

    }

    db.yieldIfContendedSafely();

    db.setTransactionSuccessful();

}catch(Exception exc){

    exc.printStackTrace();

}

finally {

    db.endTransaction();

}

7) A good practice would be to close the db when you will not use it anymore, for example in activity's onPause method:

db.close();