RSS

Database@Android: Part 1

10 Feb


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Introduction to Database@Android

Creating a private database in Android is made very easy by the SQLiteDatabase (android.database.sqlite.SQLiteDatabase) class and its various user-friendly APIs.

This blog article explains the important SQLiteDatabase APIs needed for building a database-dependent Android application.

  1. Create the SQLiteDatabase object:
     
    Before you use any SQLiteDatabase class APIs, of course you need to create an object of that class (say, dbTest in this case). 

    First, declare the object at appropriate place. In this example I have declared it at Activity level so that its visible to all Activity life cycle functions. This is just one way of doing, you can declare based on your application need.

    SQLiteDatabase dbTest;

    Next logical step is to create the object. Again, the object can be created at different time as per need. I would suggest to defer it till you require it.

    There are many options based on which Class you are using to create the database.

    • openDatabase()
    • openOrCreateDatabase()

    Searching for API listed above at Android’s SDK reference shows various classes which publish these two APIs to create a SQLite database.

    Here, I have used android.app.ListActivity‘s openOrCreateDatabase() to create the database. This needs three arguments:

    • String : Name of the database,
    • int : Database mode. Following are the self-explanatory modes:
      • MODE_PRIVATE
      • MODE_WORLD_READABLE
      • MODE_WORLD_WRITEABLE
    • SQLiteDatabase.CursorFactory : Factory object to instantiate a cursor when query is called

    Care: As many of the SQLiteDatabase APIs throw android.database.SQLException, use of try block becomes essential.

    dbTest = this.openOrCreateDatabase("DB_TEST", MODE_PRIVATE, null);

  2. Execute Structure Query Language (SQL) statements:
     
    SQLiteDatabase class has execSQL() API which executes a single SQL statement that is not a query, like, CREATE, DELETE, INSERT, etc. 

    • Create a table:
    • Following statement creates a table in the database.

      dbTest.execSQL(
      "CREATE TABLE IF NOT EXISTS " +
      "Employees" +
      " (LastName VARCHAR, FirstName VARCHAR, Place VARCHAR, Age INT(3));" );

    • Insert a record:
    • Following statement inserts a record in the table.

      dbTest.execSQL(
      "INSERT INTO " +
      "Employees" +
      " VALUES ('LName1', 'FName1', 'Place1', 25);");

    • Delete a record:
    • Following statement would delete all records from the table.

      dbTest.execSQL(
      "DELETE FROM " + "Employees" );

  3. Execute Structure Query Language (SQL) Queries:
     
    Executing an SQL query may result in some data output, commonly known as result set. SQLiteDatabase class has rawQuery() API which runs the provided SQL query and returns a android.database.Cursor
    over the result set. Using Cursor we can iterate through the result set. 

    Following query retrieves FirstName, LastName and Age from Employees table for all employees whose age is more than or equal to 21. It limits the Cursor with first 10 entries in the result set. The LIMIT option can be used for pagination.

    Cursor cursor = dbTest.rawQuery(
    "SELECT FirstName, LastName, Age FROM " +
    "Employees" +
    " WHERE Age >= 21 LIMIT 10 ", null);

  4. Using Cursor to iterate the result set:
     
    Cursor class has very useful APIs to navigate the result set, like moveToFirst(), moveToLast(), moveToNext(), etc. and retrieve data of various standard data types, like, getString(), getShort(), getInt(), getFloat(), getLong(), etc.. 

    In below snippet while I retrieve the data that I need, I add it to an ArrayList object (queryResult) of String type.
    Later I use a simple_list_item_1 list (available by default) to show the data.


     

    ArrayList<String> queryResult = new ArrayList<String>();
    
    if(cursor != null) {
        if(cursor.moveToFirst()) {
            do {
                String fName = cursor.getString(cursor.getColumnIndex("FirstName"));
                int age = cursor.getInt(cursor.getColumnIndex("Age"));
                queryResult.add("" + fName + ", Age: " + age);
            } while (cursor.moveToNext());
        }
    }
    
    this.setListAdapter(new ArrayAdapter<String> (this, android.R.layout.simple_list_item_1, queryResult));

     


     

  5. Closing the database:
     
    Finally, once all the intended operations are done, the opened database can be closed using a close() call.

    dbTest.close();

  6. Complete application with source in “Database@Android: Part 2” (Coming soon!)

Advertisements
 
1 Comment

Posted by on February 10, 2011 in Android, SQL, Technical

 

Tags: , , , ,

One response to “Database@Android: Part 1

  1. katie

    March 1, 2011 at 2:36 pm

    yeah nice

     

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: