In this development article we are going to cover a very important topic in any platform application; data storage. Without it our applications become useless and worth next to nothing. With data our applications are more valuable to the end user as they come to rely on it. Try going a week disconnected from the web with you tablet, laptop or whatever electronic device you may have and see how long that lasts. Jump past the break to see how we are using SQLite to store a database locally on the blackberry’s SD card (make sure you have one in your device or emulate one on your simulator for this tutorial)

For those that were with us in the previous tutorial, we went over how to add menu items and an event handler. If you have no idea what we are talking about, please go back to the previous tutorial entitled “Blackbery Development 101 – Improved Graphical User Interface”. This is because we go through that one and code event handlers and menu items that we utilize in this development tutorial. Now that we have the prerequisites down we can start showering ourselves in code.


// MyApp.java

package com.binarywasteland.contaclientinformation;

 

import net.rim.device.api.io.MalformedURIException;

import net.rim.device.api.ui.UiApplication;

 

/**

* This class extends the UiApplication class, providing a

* graphical user interface.

*/

public class MyApp extends UiApplication

{

/**

* Entry point for application

* @param args Command line arguments (not used)

* @throws MalformedURIException

* @throws IllegalArgumentException

*/

public static void main(String[] args) throws IllegalArgumentException, MalformedURIException

{

 

// Create a new instance of the application and make the currently

// running thread the application's event dispatch thread.

MyApp theApp = new MyApp();

theApp.enterEventDispatcher();

}

 

 

/**

* Creates a new MyApp object

* @throws MalformedURIException

* @throws IllegalArgumentException

*/

public MyApp() throws IllegalArgumentException, MalformedURIException

{

// Push a screen onto the UI stack for rendering.

pushScreen(new MyScreen());

}

}

You should recognize the code above as most of what was given to us when we created the project originally. What we have done is add throws exceptions on the top of all method headers and the main event. These exceptions are IllegalArgumentException and MalformedURIException which we end up needing in the MyScreen.java later on. For tight now it helps us not error out when we try and run the simulator or on the device.


// MyScreen.java

package com.binarywasteland.contaclientinformation;

 

import net.rim.device.api.ui.Field;

import net.rim.device.api.ui.FieldChangeListener;

import net.rim.device.api.ui.MenuItem;

import net.rim.device.api.ui.component.BasicEditField;

import net.rim.device.api.ui.component.ButtonField;

import net.rim.device.api.ui.component.Dialog;

import net.rim.device.api.ui.component.SeparatorField;

import net.rim.device.api.ui.container.MainScreen;

import net.rim.device.api.ui.container.VerticalFieldManager;

import net.rim.device.api.database.*;

import net.rim.device.api.io.*;

In the above we are importing some important API’s. Specifically, import net.rim.device.api.database.*; and import net.rim.device.api.io.*;. The database import is used for the database calls that we are going to make below. The IO is used for any calls we will make regarding input and output. With all of the imports ready to go we can now proceed to start coding for the SQLite database unhindered.


/**

* A class extending the MainScreen class, which provides default standard

* behavior for BlackBerry GUI applications.

*/

public final class MyScreen extends MainScreen

{

/**

* Creates a new MyScreen object

* @throws MalformedURIException

* @throws IllegalArgumentException

*/

public MyScreen() throws IllegalArgumentException, MalformedURIException

{

// Set the displayed title of the screen

setTitle("Client Information");

 

// Create a Database object

Database dataBase = null;

final URI dbURI = URI.create("file:///SDCard/Databases/" +

"ClientInformationDB.db");

try

{

 

 

dataBase = DatabaseFactory.openOrCreate(dbURI);

Statement state = dataBase.createStatement( "CREATE TABLE IF NOT EXISTS Clients ( " +

"id INTEGER primary key" +

"FirstName TEXT, " +

"MiddleName TEXT, " +

"LastName TEXT, " +

"Address1 TEXT, " +

"Address2 TEXT, " +

"City TEXT, " +

"ProvinceState TEXT, " +

"ZipPostalCode TEXT, " +

"Country TEXT, " +

"Phone TEXT, " +

"Email TEXT )" );

state.prepare();

state.execute();

state.close();

dataBase.close();

}

catch ( Exception e )

{

System.out.println( e.getMessage() );

e.printStackTrace();

}

finally

{

try {

dataBase.close();

} catch (DatabaseIOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

Above we can see that we are beginning to create our database in the lengthy code block. To do this we create a Database onject and make it equal to null. We then want to create an URI with an absolute path to your database (including the database name). This URI has to be referencing somewhere on the media card or this will not work and you will see an error later on. After our URI object has been created we can then move on to utilize the DatabaseFactory to pass our URI to openOrCreate method which will check to see if the location exists and if it does we want to open it, if it does not then we want to create a new database with the name provided.

 

Next, we need create a table to store our data in. The problem being that we do not want to create a table every time the application starts. The fix being that instead of using a CREATE TABLE statement we use a CREATE TABLE IF NOT EXISTS statement with the rest of the code being the same. This allows us now to check and see whether the database exists and whether the table itself exists. After we have completed the SQL statement we can run the prepare() statement which will prepare the SQL statement for execution. After this is complete and execute statement to run the SQL and create the table within the database. We can then close() the statement as we no longer need it and then close the database as we are no longer needing it at this moment.


 

final BasicEditField _firstName = new BasicEditField("First Name:", "");

final BasicEditField _middleName = new BasicEditField("Middle Name:", "");

final BasicEditField _lastName = new BasicEditField("Last Name:", "");

final BasicEditField _address1 = new BasicEditField("Address 1:", "");

final BasicEditField _address2 = new BasicEditField("Address 2:", "");

final BasicEditField _city = new BasicEditField("City:", "");

final BasicEditField _provstate = new BasicEditField("Province/State:", "");

final BasicEditField _zippostalcode = new BasicEditField("Postal/Zip Code:", "");

final BasicEditField _country = new BasicEditField("Country:", "");

final BasicEditField _phone = new BasicEditField("Phone:", "");

final BasicEditField _email = new BasicEditField("Email:", "");

ButtonField _submit = new ButtonField("Submit", ButtonField.CONSUME_CLICK | ButtonField.NEVER_DIRTY | Field.FIELD_HCENTER | Field.FIELD_VCENTER);

 

VerticalFieldManager vfm = new VerticalFieldManager();

vfm.add(_firstName);

vfm.add(new SeparatorField());

vfm.add(_middleName);

vfm.add(new SeparatorField());

vfm.add(_lastName);

vfm.add(new SeparatorField());

vfm.add(_address1);

vfm.add(new SeparatorField());

vfm.add(_address2);

vfm.add(new SeparatorField());

vfm.add(_city);

vfm.add(new SeparatorField());

vfm.add(_provstate);

vfm.add(new SeparatorField());

vfm.add(_zippostalcode);

vfm.add(new SeparatorField());

vfm.add(_country);

vfm.add(new SeparatorField());

vfm.add(_phone);

vfm.add(new SeparatorField());

vfm.add(_email);

vfm.add(new SeparatorField());

The code above is from our previous two tutorials and can be viewed again at your leisure.


_submit.setChangeListener(new FieldChangeListener()

{

public void fieldChanged(Field field, int context)

{

Database db = null;

try

{

 

db = DatabaseFactory.open(dbURI);

 

Statement state = db.createStatement("INSERT INTO Clients(FirstName, MiddleName, LastName, " +

"Address1, Address2, " +

"City, ProvinceState, ZipPostalCode, " +

"Country, Phone, Email) " +

"VALUES ('" + _firstName.getText() + "', '" + _middleName.getText() + "', " +

"'" + _lastName.getText() + "', '" + _address1.getText() + "', " +

"'" + _address2.getText() + "', '" + _city.getText() + "', " +

"'" + _provstate.getText() + "', '" + _zippostalcode.getText() + "', " +

"'" + _country.getText() + "', '" + _phone.getText() + "', " +

"'" + _email.getText() + "')");

state.prepare();

state.execute();

state.close();

db.close();

Dialog.alert("Data Added Successfully");

_firstName.setText("");
_middleName.setText("");
_lastName.setText("");
_address1.setText("");
_address2.setText("");
_city.setText("");
_provstate.setText("");
_zippostalcode.setText("");
_country.setText("");
_phone.setText("");
_email.setText("");

}

catch ( Exception e )

{

 

System.out.println( e.getMessage() );

e.printStackTrace();

Dialog.alert(e.getMessage());

}

finally

{

try {

db.close();

} catch (DatabaseIOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

});

Here you can see we have the button and the event handler that we coded from the previous development tutorial. In that tutorial, we had the event handler throw an alert dialog give us a witty comment. All joking aside we want to delve in and make this button able to submit a row item to the database. To do this we are going to need those BasicEditField’s we created two tutorials ago to grab the text from them.

First though we need to create a new Database object called db. We set it to null and call the DatabaseFactory and set the database equal to the dbURI we gave up above. After this is complete we can make a Statement object, naming it state. We then need to give state a SQL statement to execute. Since this is the button event handler it seems only proper that we INSERT data into the SQLite database. You will notice in the SQL statement above that we call the getText()method on all of the BasicEditField’s and this is to return the text that has been inserted into them. Since all we are doing is inserting text into the database we need to surround the text we are entering with “ ‘ ”(the bolded one so as not to be confused with what I am talking about).

After this is done we can prepare the statement object for execution and then execute it. We have an alert that is to show up, once all of the preparation and execution of the SQL statement has completed to let us know there were no errors. You may also notice that after every try catch block we are making sure to add a finally statement to the block. This is because we want to make absolutely sure that our database objects are closed as you are allowed to have as many read sessions at one time on the database but only one read/write connection. The finally statement makes sure, no matter what, that the call to close the database is made (which we have to surround in another try catch block).


vfm.add(_submit);

add(vfm);

 

// Adding Menu Item To Application

addMenuItem(_menuItem);

}

 

private MenuItem _menuItem = new MenuItem("More Info", 110, 10)

{

public void run()

{

Database dataBase = null;

try {

final URI dbURI = URI.create("file:///SDCard/Databases/" +

"ClientInformationDB.db");

dataBase = DatabaseFactory.open(dbURI);

Statement state = dataBase.createStatement("SELECT COUNT(*) FROM Clients");

 

state.prepare();

Cursor cursor = state.getCursor();

 

Row row;

int count = 0;

while(cursor.next()) {

count = cursor.getRow().getInteger(0);

}

dataBase.close();

System.out.print(count);

Dialog.alert("Number of entries: " + count);

}

catch ( Exception e ) {

System.out.println( e.getMessage() );

e.printStackTrace();

}

finally

{

try {

dataBase.close();

} catch (DatabaseIOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

 

}

};

 

public void close()

{

 

if(Dialog.ask(4, "Do You Really Want To Leave Me?") == Dialog.OK)

super.close();

else

Dialog.alert("Thanks For Sticking Around!");

}

}

The main thing we want to focus on in the code at the start of the code block is the addMenuItem call we make. This as we determined in the previous tutorial is used to add a menu item to the menu. We actually want to modify the menu item we already created and take out what was embedded previously to put this menu item to good use! For this item we want to show the number of entries within the database. This may seem like a basic task but it will utilize the SELECT statement and prove that I have not been leading you on throughout the tutorial and the data actually exists! To do this we need a SQL statement implementing a “SELECT * ” with a twist. We are going to use the COUNT() function to count the number of entries and return that value to the user. After we prepare the SQL statement for execution we are going to do something a little different. We are going to use a Cursor object to iterate over the results of our SQL statement and return an integer to be displayed to the user. For this tutorial we have covered SELECT and INSERT statements and if you have any questions regarding this feel free to ask away in the comments below and we will answer ASAP.

Now that we have completed this tutorial you should be able to make a SQLite database and implement it in your own application. For those who still have comments or questions, feel free to leave a comment below and we will answer it ASAP. For those looking to see what we have planned next we are going to take what we have of the SQLite database and enhance it to use UPDATE and DELETE methods. Below is a gallery of what the application will look like. Until the next Blackberry Development tutorial article, Happy Hacking!

Gallery

[nggallery id=7]

Share This