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();
       }
    }
}

About henry416
I am a computer technology explorer and an university student based on Toronto. If you have any question, please feel free to discuss and comment here

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