Android SQLite Example

Android SQLite is Android database and it is very useful tool for local data storage. Later, stored data can be synchronized with other, larger database. When we deal with the databases, it is always useful to have additional class that will send us information about events in try-catch structure.

Message-class

When we want to handle database in android operating system, we need to make class that will do the job. In this example let’s call this class MineSQLiteHelper. This class must extend SQLiteOpenHelper.

extends-SQLiteOpenHelper

Now, you must implement abstract method onCreate(SQLiteDatabase).

MineSqLiteHelper-Implement-methods

Two methods to implement are onCreate and onUpgrade

MineSQLiteHelper-methods-to-implement

But, there is still a problem: no default constructor available. So let’s make it. We will use super class in constructor with four parameters:

  • context
  • at the beginning use empty string “” (this is the name of the database)
  • null (custom cursor, if this parameter is null, cursor is default)
  • 1 (version of the data base, in the begging it is 1)

MineSQLiteHelper-Constructor

Now, let’s declare private static final – that means constant parameter that we will use later.

private static final String DB_NAME=”minedb”;
private static final int DB_VERSION = 1;

And let’s use this parameters in super class of the constructor method.

MineSQLiteHelper-parameters

We can also prepare query string for creating table in onCreate method. The code is following:

private static final String CREATE_TABLE = ” CREATE TABLE USERNAME ( _id INTEGER PRIMARY KEY AUTOINCREMENT USERNAME VARCHAR(150)); “;

  • Recommendation: use underscore in front of id (_id)

Use this SQL code in onCreate method as

db.execSQL(CREATE_TABLE);

And surround this execSQL method with try-catch structure in Windows operating system with key combination ALT+CTRL+T → 6.

ALT-CTRL-T-6-Surround-with-try-catch

In catch code send message if something goes wrong, but we need context variable. For testing purpose, we can use context in constructor as well.

context-in-constructor

For time being, we don’t bother when to use onUpgrade method. So we can freely drop table in onUpgrade method and then call onCreate.

private static final String DROP_TABLE = ” DROP TABLE IF EXISTS USERNAME “;

Apart from sending message in catch code, we can put messages in onCreate and in the constructor as well.

Messages-in-constructor-onCreate-onUpgrade

Now helper class is done. Let’s use it main class.

MineSQLiteHelper-object

And now let’s call SQLiteDatabase object in mineSQLiteHelper and use getWritableDatabase(); method.

public class MainActivity extends Activity {

MineSQLiteHelper mineSQLiteHelper;

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

mineSQLiteHelper = new MineSQLiteHelper(this);

SQLiteDatabase sqLiteDatabase = mineSQLiteHelper.getWritableDatabase();

}

}

And we have created our SQLite database in our application.

Screenshot_2016-04-21-15-03-37

For safety reason, we actually want another class that will be closely related with our helper class, but will protect data better. So let’s refactor our helper class.

Refactor-HelperClass

Change name of the class into MineSQLiteAdapter. Cut complete code in previous helper and current adapter class. Now paste it in as inner class, inner class declare as a class only. Paste whole code in there and extend it class.

Extends-helper-class-inside-Adapter-class

Now, we would like to have possibility to insert row into the table. So, add following code:
public class MineSQLiteAdapter  {

MineSQLiteHelper helper;

public MineSQLiteAdapter(Context context){

helper = new MineSQLiteHelper(context);

}

}

Again, for safety reason, make changes in SQL code, so that column name can be accessible.

private static final String TBL_NAME = “PEOPLE”;
private static final String CLMN_NAME = “USERNAME”;

private static final String CREATE_TABLE = ” CREATE TABLE ” + TBL_NAME + ” ( _id INTEGER PRIMERY KEY AUTOINCREMENT ” +  CLMN_NAME + ” VARCHAR(150)); “;

public void insertData(String name){

ContentValues contentValues = new ContentValues();

contentValues.put(MineSQLiteHelper.CLMN_NAME,name);

}

Let’s finalize method for inserting the data,

public void long insertData(String name){

SQLiteDatabase db = helper.getWritableDatabase();

ContentValues contentValues = new ContentValues();

contentValues.put(MineSQLiteHelper.CLMN_NAME,name);

long id = db.insert(MineSQLiteHelper.TBL_NAME,null,contentValues);

return id;

}

Make inner MineSQLiteHelper static. We can use now this inner MineSQLiteHelper class in main activity. Declare first:

MineSQLiteAdapter helper;

Then, in onCreate method instance new object helper.
helper = new MineSQLiteAdapter(this);
We can now insert some dummy data:
helper.insertData(“Some Name”);

Pay attention on three tiny but important details:

  • Declaration of helper object is inside of  MainActivity.java

MySQLiteAdapter helper;

  • Instance of helper object is in onCreate method

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

helper = new MySQLiteAdapter(this);

}

  • Usage of helper object is in the method of onClick method.

long id = helper.insertData(“Somename”);

Right-usage-of-helper-object

And most important detail is to change database version in the code if there was any error in previous call of helper object (and usually there was).

Change-database-version-is-important

You can download Android SQLite Example

External links:

Android SQLite on YouTube
Android SQLite on Developer
Android SQLite on Developer
Android SQLite on Androidhive

Posted in android Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*