Blackberry Development 6: SQLite Database
January 27, 2012 Leave a comment
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();
}
}
}
Henry Chen 