February 17, 2011

Android SQLite Library Example Code

Posted in Android Development tagged , , , , , , 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.

Advertisements

5 Comments »

  1. Rock Yin said,

    Hi Lee,

    I have two questions with QCiPhone here:

    1. How can I download image from website, to the iPhone photo library?
    2. How can I select a image from iPhone photo library?

    Thanks,

    Rock

  2. It seems to me it is magnificent thought

  3. mradlmaier said,

    Just looked through the API… and have to say “Yipee! Never have seen such a simple and easy and straight forward database access API!”

    The only thing, which comes to my mind (because its my use case partially), why do I need an Activity object? In an Android widget, I don’t have access to an activity easily. Wouldn’t it be better to rely on a Context object instead? Or is there are particular reason for an Activity object?

    • tetontech said,

      Thanks. I have always tried for flexible, powerful, and simple.

      Hm…. I could have passed a Context object but selected Activity since Activity inherits from context. The only thing it is being used for is to gain access to the SQLite database. The context doesn’t save me any memory or save me from memory leaks so I went with Activity instead. That is what I’m using in some other libraries I’ve created and am currently working on releasing subclasses of Activity and AsyncTask that track themselves to ensure no memory leaks when using AsyncTask to do threading.

  4. For those looking how to hack using SQLite CLI (Command Line Interface) with adb shell in any Android device:

    How to build the sqlite3 binary and library yourself.

    I have put together some build scripts to compile SQLite for Android Native Code using the Android NDK. It builds the SQLite CLI in two versions: Statically and Dynamically Linked, as well as it’s Static and Shared Libraries. You may get the scripts from my GitHub and build the binaries yourself:

    https://github.com/stockrt/sqlite3-android

    Hope this will be useful for someone.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: