February 17, 2011

Android SQLite Library Example Code

Posted in Android Development tagged android, code, Example, library, open, source, SQLite at 11:00 pm by tetontech

I have just uploaded QCDBAccess.  It is a SQLite wrapper for Android that is easy to use, thread-safe, and allows you to use transactions if you choose.  It is the same code that has been available for QCAndroid hybrid application developers for a few years.  I thought I would pull it out and give it its own life for those that want to use it on their own.

You can download it from SourceForge and find out more about it, including the API, at the QuickConnectFamily site.

As with all of the things I’m making available I have tried to make this as easy to use, highly functional, and small as possible.

The jar file is only 8k in size but the library doesn’t restrict your use of the databases in any way.

Here is a simple example of how to use the library to do a query against a SQLite database file included in your applications’ assets directory.  Notice that I’m checking to see if the user has entered a name to query against in an EditText object from the user interface.  If they have then I’m going to use a prepared statement.  The EditText object in this example has been passed into the method in which this code exists.

String sql = “SELECT * FROM user”;

String[] statementParams = null;

EditText nameInput = (EditText)methodParameters.get(1);

String name = nameInput.getEditableText().toString().trim();

if(name.length() > 0){

sql += ” WHERE name = ?”;

statementParams = new String[1];

statementParams[0] = name;

}

try {

retVal = DataAccessObject.getData(theActivity, “demo.sqlite”, sql, statementParams);

} catch (DataAccessException e) {

e.printStackTrace();

}

The DataAccessResult variable retVal is going to be returned from this method later.  This DataAccessResult is a Bean that contains resultant field names, the records returned from the query, and a database error description if and only if a database error happened.

the getData method is passed the Activity with which the database is associated with.  This is usually the main Activity in an Android Application.  The second parameter is the name of the SQLite database file to be used.  The third parameter is SQLite SQL assembled either as a standard or prepared statement.  The last parameter is an array of Objects that are to be bound to the ? characters if the SQL is a prepared statement or null if it is not a prepared statement.

Inserting data is done in very much the same fashion.  Once again I have an EditText object in the UI from which I’m getting the name to be inserted into the table.  I’m also generating a unique identifier just to make this a little more interesting.  You could do an auto incremented id in your table if you want.

EditText nameInput = (EditText)parameters.get(1);

String nameToAdd = nameInput.getEditableText().toString().trim();

String[]statementParams = {UUID.randomUUID().toString(), nameToAdd};

DataAccessResult aResult = null;

try {

aResult = DataAccessObject.setData(theActivity, “demo.sqlite”,

“INSERT INTO user VALUES(?,?)”,

statementParams);

return aResult;

} catch (Exception e) {

e.printStackTrace();

}

Notice that once again I have chosen to use a prepared statement.  I’m doing this to avoid SQL insertion attacks.  It never hurts to avoid those.

If you are going to do several insertions you should use transactions to make sure you data stays clean.  There is a startTransaction method and an endTransaction method in the DataAccessObject.  Use those before and after multiple setData calls and you will be safe.

Permalink 4 Comments