Blackberry Development 6: SQLite Database

All in One Test on SQLite DB

The specifications on the SQL syntax used by SQLite can be found in www.sqlite.org.

The following program tests the following operation on a SQLite DB:

  • create a db
  • create a table
  • insert a record
  • update the record
  • query the record
  • delete the record
  • delete the db
import net.rim.device.api.ui.component.*;
import net.rim.device.api.ui.container.*;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;
import net.rim.device.api.ui.*;

public class DBTest extends UiApplication
{
    public static void main(String[] args)
    {
        CreateDatabase theApp = new CreateDatabase();
        theApp.enterEventDispatcher();
    }

    public DBTest()
    {
        pushScreen(new DBTestScreen());
    }  
}

class DBTestScreen extends MainScreen
{
    Database d;
    public DBTestScreen()
    {
       LabelField title = new LabelField("SQLite DB Test",
                                          LabelField.ELLIPSIS |
                                          LabelField.USE_ALL_WIDTH);
       setTitle(title);
       add(new RichTextField("Test a database called " +
                             "MyTestDatabase.db on the SDCard."));
       try
       {
           // 1. create a SQLite DB
           URI myURI = URI.create("file:///SDCard/Databases/SQLite_Guide/" +
                                  "MyTestDatabase.db");
           d = DatabaseFactory.create(myURI);
           // 2. Create a table
           // d = DatabaseFactory.openOrCreate(myURI);
           Statement st = d.createStatement( "CREATE TABLE People ( " +
                                              "Name TEXT, " +
                                              "Age INTEGER )" );
           st.prepare();
           st.execute();
           st.close();
           //3 insert a record
           st = d.createStatement("INSERT INTO People(Name,Age) " +
                                             "VALUES ('John',37)");
           st.prepare();
           st.execute();
           st.close();
           //4 query the record
            Statement st = d.createStatement("SELECT Name,Age FROM People");

            st.prepare();
            Cursor c = st.getCursor();

            Row r;
            int i = 0;
            while(c.next())
            {
                r = c.getRow();
                i++;
                add(new RichTextField(i + ". Name = " + r.getString(0) +
                                          " , " +
                                          "Age = " + r.getInteger(1)));
            }
            if (i==0)
            {
                add(new RichTextField("No data in the People table."));
            }
            st.close();
           //5. update the record
           Statement st = d.createStatement("UPDATE People SET Age=38 " +
                                         "WHERE Name='John'");
           st.prepare();
           st.execute();
           st.close();
           //6. delete the record
            Statement st = d.createStatement("DELETE FROM People");
            st.prepare();
            st.execute();
            st.close();
           //7. Finally close the db
           d.close();
           //8. Delete the database
           DatabaseFactory.delete(myURI);
       }
       catch ( Exception e )
       {        
           System.out.println( e.getMessage() );
           e.printStackTrace();
       }
    }
}

Android SQLite Example

AndroidSQLite.java

package com.exercise.AndroidSQLite; import android.app.Activity; import android.os.Bundle; import android.widget.TextView; public class AndroidSQLite extends Activity { private SQLiteAdapter mySQLiteAdapter; /** Called when the activity is first created. */ @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.main); TextView listContent = (TextView)findViewById(R.id.contentlist); /* * Create/Open a SQLite database * and fill with dummy content * and close it */ mySQLiteAdapter = new SQLiteAdapter(this); mySQLiteAdapter.openToWrite(); mySQLiteAdapter.deleteAll(); mySQLiteAdapter.insert("ABCDE"); mySQLiteAdapter.insert("FGHIJK"); mySQLiteAdapter.insert("1234567"); mySQLiteAdapter.insert("890"); mySQLiteAdapter.insert("Testing"); mySQLiteAdapter.close(); /* * Open the same SQLite database * and read all it's content. */ mySQLiteAdapter = new SQLiteAdapter(this); mySQLiteAdapter.openToRead(); String contentRead = mySQLiteAdapter.queueAll(); mySQLiteAdapter.close(); listContent.setText(contentRead); } } 

SQLiteAdapter.java

package com.exercise.AndroidSQLite; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteDatabase.CursorFactory; public class SQLiteAdapter { public static final String MYDATABASE_NAME = "MY_DATABASE"; public static final String MYDATABASE_TABLE = "MY_TABLE"; public static final int MYDATABASE_VERSION = 1; public static final String KEY_CONTENT = "Content"; //create table MY_DATABASE (ID integer primary key, Content text not null); private static final String SCRIPT_CREATE_DATABASE = "create table " + MYDATABASE_TABLE + " (" + KEY_CONTENT + " text not null);"; private SQLiteHelper sqLiteHelper; private SQLiteDatabase sqLiteDatabase; private Context context; public SQLiteAdapter(Context c){ context = c; } public SQLiteAdapter openToRead() throws android.database.SQLException { sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION); sqLiteDatabase = sqLiteHelper.getReadableDatabase(); return this; } public SQLiteAdapter openToWrite() throws android.database.SQLException { sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION); sqLiteDatabase = sqLiteHelper.getWritableDatabase(); return this; } public void close(){ sqLiteHelper.close(); } public long insert(String content){ ContentValues contentValues = new ContentValues(); contentValues.put(KEY_CONTENT, content); return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues); } public int deleteAll(){ return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null); } public String queueAll(){ String[] columns = new String[]{KEY_CONTENT}; Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, null, null, null, null, null); String result = ""; int index_CONTENT = cursor.getColumnIndex(KEY_CONTENT); for(cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()){ result = result + cursor.getString(index_CONTENT) + "\n"; } return result; } public class SQLiteHelper extends SQLiteOpenHelper { public SQLiteHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase db) { // TODO Auto-generated method stub db.execSQL(SCRIPT_CREATE_DATABASE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } } } 

main.xml

<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:layout_width="fill_parent" android:layout_height="fill_parent" > <TextView android:layout_width="fill_parent" android:layout_height="wrap_content" android:text="@string/hello" /> <TextView android:id="@+id/contentlist" android:layout_width="fill_parent" android:layout_height="wrap_content"/> </LinearLayout>