Getting started: Sqlite on Android

I am writing this tutorial with some hesitation, i really don’t want to make another copy of this android SQLite tutorial That is wondering around the net. So this tutorial is a part or a series, after this “getting started” one, a more interesting one on how to implement some design patterns to work with your application and SQLite database.

Building a Database on an android system is not a very pleasant job but from some point of view it has its advantages. If you have worked with a database element manager like phpmyadmin visual studio or any other, you know its the easiest way to build a database, easy to modify and see the big picture, your able to insert data and edit the database content easily and the most important… it saves TIME. With sqlite on android you do not really have any way of visualizing your database, you will need to create it when your application runs for the first time. Thous who love the scripting business will disagree with me. I personally like getting things done (when i am not trying to study how things work and gain experience). However the idea of having all of your code (as well as the databases) in the same place is a good practice and simplifys things when developing.

Note: There are some ways of visualizing and edit your database with an external editor and inject it into your application but its not what this tutorial will be about. here we will see how to create a database for the android system use the method “GOD” intended. Then we will see how we can go about simplifying the solution so we will have a good and robust code what will be easy to manage and enlarge.

Lets create our Chocolate Database.
What were going to do is build a wrapper class that will extend SQLiteOpenHelper (A helper class for managing database creation and version management.). It will hold all the code necessary to create, delete, update and maintain the Database.

public class ChocolateDbHelper extends SQLiteOpenHelper {

public static final int dbVersion = 1; //dbVersion is used by SQLiteOpenHelper to know if an upgrade/downgrade is needed for the existing db

public static final string CHOCOLATE_TABLE_NAME = “chocolate_table”; public static final string CHOCOLATE_ID_COL = “chocolate_id”; public static final string CHOCOLATE_TYPE_COL = “chocolate_type”; public static final string CHOCOLATE_QUANTITY_COL = “chocolate_quantity”;

public static String dbCreateString = “CREATE TABLE ” + CHOCOLATE_TABLE_NAME  + ” ( ” + CHOCOLATE_ID_COL  + ” INTEGER PRIMARY KEY AUTOINCREMENT, ” + CHOCOLATE_TYPE_COL + ” INTEGER NOT NULL, ” + CHOCOLATE_QUANTITY_COL + ” REAL)” ;

public ChocolateDbHelper (Context context) {

super(context, dbName , null, dbVersion);

 }

@override
public void onCreate(SQLiteDatabase db) {

try{

db.execSQL(dbCreateString);

} catch (SQLException e) {

//throw some logs here.

}

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

try {

db.execSQL(“DROP TABLE IF EXISTS ” + CHOCOLATE_TABLE_NAME ); onCreate(db);

} catch (SQLException e) {

//produce some logs here

}

}

public long EnterNewChocolate(Chocolate chocolate) {

SQLiteDatabase db = null; long rowId = -1; try {

db = this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(CHOCOLATE_TYPE_COL , chocolate.getChocolateType()); cv.put(CHOCOLATE_QUANTITY_COL , chocolate.getQuantity()); rowId = db.insert(CHOCOLATE_TABLE_NAME , null, cv);

} finally {
return rowId;
}

}

About onCreate(SQLiteDatabase db):

This function will be called when the database is created for the first time.
The creation of tables/triggers and population of the database should happen here.

Variables:
db – The database.

About onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):

This function is called when the version of the database has changed to grater than the previous version (there’s also the onDowngrade).
When this function if called, oldVersion < newVersion.

There is no right or wrong way to manage this function, if you have a static database and need to upgrade it, just delete the old one and create the new one instead.
If you want to add to an existing database with out deleting it just post your SQL Statement there and hope you know what your doing.

Variables:
db – the database.
oldVersion – the old version of the database.
newVersion – the new version of the database.

The basics is right here, in fact you don’t need any more to effectively manage a small database.

Thanks for reading, i am working on the next chapter in the SQLite database tutorial.