In the previous tutorial we covered creating a SQLite database, creating a table for our data to be stored in if the table did not already exist. We also introduced inserting information into the database and using a SELECT statement to COUNT the number of rows currently in the database. All of this is well and good for those who like thinking their data is safe in the database without first verifying it. We belong to the latter, in the sense that we like our data verified before we call it a day.

In this tutorial we are going to implement SELECT, UPDATE and DELETE statements to verify that data can be manipulated in our SQLite database. We also realize, in the previous tutorial, that code was mostly strewn about our page and we decided to do a little house keeping and wrap some code up and extrapolate it into some actual reusable methods and functions. You may notice that some of your code we wrote last tutorial does not add up and we ask that you check these methods first before leaving a comment. Jump past the break to learn more about SQLite Database manipulation on the Blackberry below.


// MyApp.java

package com.binarywasteland.contaclientinformation;

import java.util.Enumeration;

import javax.microedition.io.file.FileSystemRegistry;

import net.rim.device.api.io.MalformedURIException;
import net.rim.device.api.ui.UiApplication;
import net.rim.device.api.ui.component.Dialog;

/**
* 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
{

// 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
{
// Determine if an SDCard is present
boolean sdCardPresent = false;
String root = null;
Enumeration e = FileSystemRegistry.listRoots();
while (e.hasMoreElements())
{
root = (String)e.nextElement();
if(root.equalsIgnoreCase("sdcard/"))
{
sdCardPresent = true;
}
}
if(!sdCardPresent)
{
UiApplication.getUiApplication().invokeLater(new Runnable()
{
public void run()
{
Dialog.alert("This application requires an SD card to be present. Exiting application...");
System.exit(0);
}
});
}
else
{
// Push a screen onto the UI stack for rendering.
pushScreen(new MyScreen());
}
}
}

In the above code we have changed it to include a check to make sure a SD card is in fact inserted into the device as it is needed to create the database and store data. We do this by creating a boolean object called sdCardPresent that is initially set to false. At the same time, under the Boolean object we want a string object to be named root and be initialized to null. After this is done we use an Enumerator to assist us in collecting the Root nodes of the File System Directory.

 

With the Enumerator filled with the File System Directory root nodes we implement a while statement to tell the loop to play until there are no more root nodes. Inside the while loop we make the root string object equal to the NextElement and then cast into a String type. We then want to run a conditional statement to determine whether the resulting string (ignoring upper or lower case) is equal to “sdcard/”. If it is then we are going to change sdCardPresent to true which will allow is to continue using the application. Otherwise, we are going to create a new runnable object and insert it into the event queue. This runnable object will exit the application after the user presses ok on a dialog box we show to them.


// 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.*;

/**
* A class extending the MainScreen class, which provides default standard
* behavior for BlackBerry GUI applications.
*/
public final class MyScreen extends MainScreen
{
// Global Variables
Database db;
BasicEditField _id = new BasicEditField("ID:", "");
BasicEditField _firstName = new BasicEditField("First Name:", "");
BasicEditField _middleName = new BasicEditField("Middle Name:", "");
BasicEditField _lastName = new BasicEditField("Last Name:", "");
BasicEditField _address1 = new BasicEditField("Address 1:", "");
BasicEditField _address2 = new BasicEditField("Address 2:", "");
BasicEditField _city = new BasicEditField("City:", "");
BasicEditField _provstate = new BasicEditField("Province/State:", "");
BasicEditField _zippostalcode = new BasicEditField("Postal/Zip Code:", "");
BasicEditField _country = new BasicEditField("Country:", "");
BasicEditField _phone = new BasicEditField("Phone:", "");
BasicEditField _email = new BasicEditField("Email:", "");

As you can see here we have taken the BasicEditField’s from within the public MyScreen and extrapolated them out to become global variables. This is key for a few different reasons. In the previous tutorial we had to declare our BasicEditField and make it a constant using the final keyword. Now this is no longer a problem that we have extrapolated them to be global variables and can be used in all methods. This is the other good use of making them global variables is that methods do not need to be parameterized but can instead just be referenced within our methods.

/**
* Creates a new MyScreen object
* @throws MalformedURIException
* @throws IllegalArgumentException
*/
public MyScreen() throws IllegalArgumentException
{
// Set the displayed title of the screen
setTitle("Client Information");
createDB();

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

VerticalFieldManager vfm = new VerticalFieldManager();
vfm.add(_id);
vfm.add(new SeparatorField());
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());

_submit.setChangeListener(new FieldChangeListener()
{
public void fieldChanged(Field field, int context)
{
addClients();
}
});

vfm.add(_submit);
add(vfm);

// Adding Menu Item To Application
addMenuItem(_numClients);
addMenuItem(_getClient);
addMenuItem(_updateClient);
addMenuItem(_deleteClient);
}

Here we have the main event MyScreen that we loaded with creating the database, the table we are going to store the data in, as well as registering a button click which results in us adding the data to the tables and database we created. We have added a new BasicEditField to the mix called ID. This field will be crucial in UPDATE, SELECT and DELETE statements we will code farther down. We now want to move and refactor this code into methods that can be reused. The first method we have created is createDB() which we will cover below. Along with the createDB() method we have addClients() which we have refactored to add the data we have entered into the database. You will also see that we have added two new menu items that take care of updating, deleting and selecting data within our database.

private void createDB()
{
//Create Database if it does not already exist
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.openOrCreate(dbURI);
db.close();

createTable();
}
catch(Exception e)
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
}

Above is the createDB() that was mentioned in the above paragraph. Here we implement the try catch statement to surround creating a URI object to link to the database and see if it exists. If it does the open the database, only to close it because we can confirm it is there. If it is not created then do so and then close the database. We then run the method that can be seen below.

private void createTable()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.openOrCreate(dbURI);
Statement state = db.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();
db.close();
}
catch(Exception e)
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
}

private void addClients()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.openOrCreate(dbURI);
Statement st = 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() + "')");
st.prepare();
st.execute();
st.close();
db.close();

Dialog.alert("Data Added Successfully");

_id.setText("");
_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
System.out.println( e.getMessage() );
e.printStackTrace();
Dialog.alert(e.getMessage());
}
}
}

private MenuItem _numClients = new MenuItem("# Of Clients", 110, 10)
{
public void run()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.open(dbURI);

Statement state = db.createStatement("SELECT COUNT(*) FROM Clients");
state.prepare();
Cursor cursor = state.getCursor();
int count = 0;
Row row;
while(cursor.next())
{
row = cursor.getRow();
count = row.getInteger(0);
}
state.close();
cursor.close();
db.close();

System.out.print(count);
_id.setText(String.valueOf(count));
Dialog.alert("Number of entries: " + count);
}
catch ( Exception e )
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
finally
{
try
{
db.close();
}
catch (DatabaseIOException e)
{
// TODO Auto-generated catch block
System.out.println( e.getMessage() );
e.printStackTrace();
Dialog.alert(e.getMessage());
}
}
}
};

Here we have the method createTable() which we call within the createDB() method. This method is where we placed the code to check and see if a table exists called Clients. If it does then the method becomes useless and nothing will happen. If we do in fact need to create a table then the SQL statement will be run to CREATE TABLE IF NOT EXISTS and add in the columns we require.

 

You will also see that we have the addClient and numClient menu items. If you will recall, we coded and used these in the previous tutorial. The addClient method is used when the data has been completed and we want to commit it to the database. The numClient is used when the user wants to return the number of clients already in the database. The numClients method has been slightly modified to add the number of clients into the ID textbox.

private MenuItem _getClient = new MenuItem("Get Client By ID", 110, 10)
{
public void run()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.open(dbURI);

Statement state = db.createStatement("SELECT FirstName, MiddleName, LastName, Address1, " +
"Address2, City, ProvinceState, ZipPostalCode, Country, Phone, Email " +
"FROM Clients WHERE id = ?");
state.prepare();
state.bind(1, _id.getText());
Cursor cursor = state.getCursor();
int count = 0;
Row row;
while(cursor.next())
{
row = cursor.getRow();
_firstName.setText(row.getString(0));
_middleName.setText(row.getString(1));
_lastName.setText(row.getString(2));
_address1.setText(row.getString(3));
_address2.setText(row.getString(4));
_city.setText(row.getString(5));
_provstate.setText(row.getString(6));
_zippostalcode.setText(row.getString(7));
_country.setText(row.getString(8));
_phone.setText(row.getString(9));
_email.setText(row.getString(10));
}
state.close();
cursor.close();
db.close();
Dialog.alert("Entry Returned For: " + _id);
}
catch ( Exception e )
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
finally
{
try
{
db.close();
}
catch (DatabaseIOException e)
{
// TODO Auto-generated catch block
System.out.println( e.getMessage() );
e.printStackTrace();
Dialog.alert(e.getMessage());
}
}
}
};

Above we have the menu item getClient. This menu item will retrieve the client specified when the id is provided on the menu item select. To do this we create the try catch statement as usual, create the URI object and open the database. Now what we want to do is create a SQL statement to retrieve only the information we want. I say this because if we were to use a statement like SELECT * FROM Clients WHERE = ? we would have the ID of the row returned. This is not necessary as we have already given the ID of the client we are looking for.

So instead we will specify which columns we want returned instead of using the asterisk ( * ). From there the SQL statement can proceed as normal, ending with WHERE id = ?. Some of you may be unfamiliar with why we are using the ? as opposed to our previous SQL statements where we input the text from from the textbox. The ? is a keyword for the SQLite commands to recognize as a variable that will be presented to it in a binding statement below. The binding will use an index to identify which ? we are referencing and the variable we want to insert into that space.

After this is complete we need to create a Cursor object. A cursor object provides read only access to the database results we want to return. We also want to create a Row object to contain the table names and variables we are wanting to access. We then want to loop through the results and make our row object equal to the cursor.getRow() method which will return our results. We can then reference our global variables and set the text of these using row.getString(). Inside of the () we want to insert a number from one to ten as that will allow us to reference the column number to return the data. After this is done we can close all of our variables and the method will end.

private MenuItem _updateClient = new MenuItem("Update Client With ID", 110, 10)
{
public void run()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.open(dbURI);

Statement state = db.createStatement("UPDATE Clients SET FirstName = ?, MiddleName = ?, LastName = ?, Address1 = ?, " +
"Address2 = ?, City = ?, ProvinceState = ?, ZipPostalCode = ?, Country = ?, Phone = ?, Email = ? " +
"WHERE id = ?");
state.prepare();
state.bind(1, _firstName.getText());
state.bind(2, _middleName.getText());
state.bind(3, _lastName.getText());
state.bind(4, _address1.getText());
state.bind(5, _address2.getText());
state.bind(6, _city.getText());
state.bind(7, _provstate.getText());
state.bind(8, _zippostalcode.getText());
state.bind(9, _country.getText());
state.bind(10, _phone.getText());
state.bind(11, _email.getText());
state.bind(12, _id.getText());
state.execute();
state.close();
db.close();
Dialog.alert("Entry #: " + _id + " updated!");
}
catch ( Exception e )
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
finally
{
try
{
db.close();
}
catch (DatabaseIOException e)
{
// TODO Auto-generated catch block
System.out.println( e.getMessage() );
e.printStackTrace();
Dialog.alert(e.getMessage());
}
}
}
};

private MenuItem _deleteClient = new MenuItem("Delete Client By ID", 110, 10)
{
public void run()
{
try
{
URI dbURI = URI.create("file:///SDCard/ClientInformationDB.db");
db = DatabaseFactory.open(dbURI);

Statement state = db.createStatement("DELETE FROM Clients WHERE id = ?");
state.prepare();
state.bind(1, _id.getText());
state.execute();
state.close();
db.close();
Dialog.alert("Entry #: " + _id + " deleted!");
}
catch ( Exception e )
{
System.out.println( e.getMessage() );
e.printStackTrace();
}
finally
{
try
{
db.close();
}
catch (DatabaseIOException e)
{
// TODO Auto-generated catch block
System.out.println( e.getMessage() );
e.printStackTrace();
Dialog.alert(e.getMessage());
}
}
}
};

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 next menu item we have up is updateClient which we use to update the client information. This method can be implemented in one of two ways. The first is to enter all of the information into the fields and enter in the ID we want to change and then choose the menu item to update the information in the database. The second option is to use the get Client By ID menu item to bring the information up on the screen and change the information and then use the update client menu item. To implement this method we need to create a SQL statement using UPDATE Clients SET VARIABLE NAME = ? WHERE id = ?. This is the outline of the statement we are going to use as you can see that we have extrapolated it from the basic statement to incorporate all columns we need updated. After the statement is complete we can prepare the statement, bind the variables and then execute the statement.

After that is the deleteClient() which is used to delete the client information after we give it the client id number. This is a fairly straightforward SQL statement with DELETE FROM Clients WHERE id = ? being the key statement we are going to use. After the SQL statement is complete we can prepare the SQL statement and bind the id variable. After that, all that needs to happen is to execute the statement and the data will be cleared from the database.

After this is complete we now have a working project that is fully functional. The application allows you to add a client, update current clients, select clients that we are looking for and delete clients we no longer need. If you can grasp the previous tutorial and this one then you are in good hands for creating a SQLite database and implementing all the features in your own application. Below is a gallery of the application in use. If you have any questions regarding what was coded here feel free to leave a comment below and we will return with an answer ASAP. Until the next development tutorial, Happy Hacking!

Gallery

Share This