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