Teams Winter 2011/team1/BlackBerry/Use SQLite

11. Use Database and SQLite to Store Data

11.1 Add SDCard to Simulator

To be able to have data preserved on database, first we need to make sure that simulator can simulate SDCard, so that we can run our application with database. For this purpose first create a folder on your file system. Then run the simulator and from the top menu choose: Simulate > Change SD Card... .
Then Mount Directory, and select the folder you just created. Every time you restart the simulator, before running the app you need to make sure that the folder is mounted as SD Card. later on you will see that the database file gets created there.

11.2 Create Database File

We would like to create a database file including out Student Entity, so that we can put it in the project. Then every time the app runs, first it checks if it is the first time run of the app on the device or simulator. If so, the app will copy the file in to the SDCard. Next time that you run the application, it will know that the file is there, so will not override it.

For this purpose, create a simple blackberry project with only the application object. We do not need any screen here. call it "DBCreator". and this will need to only have a class called MyApp.java which extends UIApplication. Here is the code for this class:

package mypackage;

import java.util.Enumeration;
import javax.microedition.io.file.FileSystemRegistry;
import net.rim.device.api.ui.*;
import net.rim.device.api.ui.component.*;
import net.rim.device.api.database.*;
import net.rim.device.api.io.*;

/**
 * This class extends the UiApplication class, providing a
 * graphical user interface.
 */
public class MyApp extends UiApplication
{
      private static String DB_NAME = "SQLiteStudentList";
     
    /**
     * Entry point for application
     * @param args Command line arguments (not used)
     */
    public static void main(String[] args) throws Exception
    {
        // 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
     */
    public MyApp()throws Exception
    {       
        // Check for the presence of the SDCard
          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
          {
            try{
                 // create a URI object with te file name and location   
              String dbLocation = "/SDCard/databases/DBCreator/";
              URI uri = URI.create(dbLocation + DB_NAME);

                 // open a database connection to that URI
              Database db = DatabaseFactory.openOrCreate(uri, new DatabaseSecurityOptions(false));

                  // create the table if it does not already exist
              Statement statement =
                   db.createStatement("CREATE TABLE IF NOT EXISTS Student(id INTEGER PRIMARY KEY, firstName TEXT, lastName TEXT, email TEXT, address TEXT)");
                statement.prepare();
               
               //execute the statement
                statement.execute();
                
               // close statement and database
                statement.close(); 
                db.close();         
             
            }catch(Exception ex ) {
                errorDialog(ex.getMessage());
                System.exit(0);   
          }
           
          }
    }
   
   // Error message dialog
    public static void errorDialog(final String message)
    {
        UiApplication.getUiApplication().invokeLater(new Runnable()
        {
            public void run()
            {
                Dialog.alert(message);
            }
        });
    }
}

Now run tha application. Then from the SDCard folder, drag the database file and drop it into your main project, "res" folder.

11.3 Add id to Student Class

Now in our main application project,we need to add id to this class because we are going to preserve our Student objects in database,: So add a private field and add public getter and setters:

public int getId(){
        return this.id;
        }
    public void setId(int id){
        this.id = id;
    }

11.4 Check for presence of SDCard

Each time our "Student View" application runs, we have to check for the presence of the SD card, and if it does not exist, we have to show a dialog message.

Therefore, add the following code to the ViewStudentApp.java class constructor:

public ViewStudentApp() throws Exception {
        //This part is from BB Sample application
         // 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
        {
            // the rest of code goes here

        }

11.5 Copy Database File into SDCard

Now that application knows SDCard is present, It should check if the database file exist, otherwise copy it from the application bundle into SDCard:

  else
        {
            String dbLocation = "/SDCard/databases/StudentsList/";
           
            // Create URI           
            uri = URI.create(dbLocation + DB_NAME);    
           
            // Open or create a plain text database.  This will create the
            // directory and file defined by the URI (if they do not already exist).
           db = DatabaseFactory.openOrCreate(uri, new DatabaseSecurityOptions(false)); 
           
            // Close the database in case it is blank and we need to write to the file
            db.close();
           
            // Open a connection to the database file       
            FileConnection fileConnection = (FileConnection)Connector.open("file://" + dbLocation + DB_NAME);   
           
            // If the file is blank, copy the pre-defined database from this
            // module to the SDCard.
            if(fileConnection.exists() && fileConnection.fileSize() == 0)
            {                     
                readAndWriteDatabaseFile(fileConnection);          
            }        
       
           // notice that the way we are callling the StudentList constructor is now different.
           // Later on you will see that we should modify that class:
          _studentList = new StudentList(db, uri);

          // here goes the rest of the constructor code

Then add the following method (readAndWriteDatabaseFile) to the file that actually reads the database file and puts it into the simulator:

 public void readAndWriteDatabaseFile(FileConnection fileConnection) throws IOException
        {       
            OutputStream outputStream = null;
            InputStream inputStream = null;      
                          
            // Open an input stream to the pre-defined encrypted database bundled
            // within this module.
            inputStream = getClass().getResourceAsStream("/" + DB_NAME);
           
            // Open an output stream to the newly created file
            outputStream = (OutputStream)fileConnection.openOutputStream();                                      
           
            // Read data from the input stream and write the data to the
            // output stream.           
            byte[] data = new byte[256];
            int length = 0;
            while (-1 != (length = inputStream.read(data)))
            {
                outputStream.write(data, 0, length);               
            }    
           
            // Close the connections
            if(fileConnection != null)
            {
                fileConnection.close();
            }
            if(outputStream != null)
            {
                outputStream.close();
            }
            if(inputStream != null)
            {
                inputStream.close();
            }           
        }

11.6 Create SQLiteManager Class

Now we need a class that will be the only class interacting with our database. It will take a Database object and a URI object to open database in the constructor. It will have methods to perform all the queries we need and at the end of each query will close the database.

So, create a class called: SQLiteManager, with 2 private fields, and a constructor that sets them and then tries to open database:

package cs.ecl.team1.project;



import java.util.Vector;
import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.DataTypeException;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.DatabaseFactory;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;
import net.rim.device.api.io.URI;
import net.rim.device.api.ui.UiApplication;
import net.rim.device.api.ui.component.Dialog;

public class SQLManager {
   
     private static Database db;
     private URI uri;

     public SQLManager(Database db, URI uri)
        {
            this.uri = uri;
            this.db = db;
            openDB();
        }

}

11.6.1 Open Database
Then we implement the openDB() method:

 void openDB()
      {       
          try
          {           
              this.db = DatabaseFactory.open(uri);
          }
          catch(DatabaseException dbe)
          {
              errorDialog(dbe.toString());
          }
      }

11.6.2 Close Database
Then we definitely need a closeDB() method to use later:

 static void closeDB()
      {       
          try
          {           
              db.close();
          }
          catch(DatabaseException dbe)
          {
              errorDialog(dbe.toString());
          }
      }

And as you noticed above, we are going to need a method called errorDialog():

 public static void errorDialog(final String message)
      {
          UiApplication.getUiApplication().invokeLater(new Runnable()
          {
              public void run()
              {
                  Dialog.alert(message);
              }
          });
      }

11.6.3 Insert New Student
The first query we need is a query that enables us add a new Student, so we need to implement a method for that:

 //ADD STUDENT
      Student addStudent(String fName, String lName, String email, String address){
          Student student = null;
          try
          {
       
         
         Statement statement = db.createStatement("INSERT INTO Student VALUES(NULL,?,?,?,?)");                    
         statement.prepare();  
  
         statement.bind(1,fName);
         statement.bind(2, lName);
         statement.bind(3, email);
         statement.bind(4, address);
      
         statement.execute();
         statement.close();
       
         statement = db.createStatement("SELECT id FROM Student WHERE firstName = ?  AND lastName = ?");
         statement.prepare();
         statement.bind(1, fName);  
         statement.bind(2, lName);
         Cursor cursor = statement.getCursor();                       
         if(cursor.next())
         {
             Row row = cursor.getRow();
             int id = row.getInteger(0);            
             student = new Student(id, fName, lName, email, address);                               
         }                
         cursor.close();
         statement.close();
          }catch(DatabaseException dbe)
          {
              errorDialog(dbe.toString());
          }   
          catch(DataTypeException dte)
          {
              errorDialog(dte.toString());
          }
         
          return student;
     }

11.6.4 Delete Student
Then add a method to delete student by id:

  // DELETE STUDENT
     
      void deleteStudent(int id)
      {
          try
          {
        
              Statement statement = db.createStatement("DELETE FROM Student WHERE id = ?");
              statement.prepare();           
              statement.bind(1, id);
              statement.execute(); 
              statement.close();
            
   
          }
          catch(DatabaseException dbe)
          {
            errorDialog(dbe.toString());
          }       
      }

11.6.5 Update Student
And a method to update Student:

 //UPDATE STUDENT
      void updateStudent(int id, String fName, String lName, String email, String address)
      {
          try
          {         
              Statement statement = db.createStatement("UPDATE Student SET firstName = ?, lastName= ?, email = ?, address = ?  WHERE id = ?");
              statement.prepare();               
              statement.bind(1, fName);
              statement.bind(2, lName);
              statement.bind(3, email);
              statement.bind(4, address);
              statement.bind(5, id); 
              statement.execute();                                          
              statement.close();
           
          }
          catch(DatabaseException dbe)
          {
            errorDialog(dbe.toString());  
          }               
      }

11.6.6 Select All Students
And obviously we will need a method that gets all the students from database and returns a vector of Student, so that we can populate our student list:

 Vector getStudents(){
          Vector students= new Vector();
         
          try
          {         
       
              Statement statement = db.createStatement("SELECT * FROM Student");
              statement.prepare();
              Cursor cursor = statement.getCursor();
             
              // Iterate through the the result set.  For each row, add a
              // new DirectoryItem object to the vector.
              while(cursor.next())
              {                   
                  Row row = cursor.getRow();
                 
                  int id = row.getInteger(0);
                 
                  String fName = row.getString(1);
                  String lName = row.getString(2);
                  String email = row.getString(3);                                                                
                  String address = row.getString(4);
                 
                  Student student = new Student(id, fName, lName, email, address);
                  students.addElement(student);                                 
              }
              statement.close();
              cursor.close();
            
          }
          catch(DatabaseException dbe)
          {
              errorDialog(dbe.toString());
          }
          catch(DataTypeException dte)
          {
             errorDialog(dte.toString());
          } 
         
          return students;   
         
      }

11.7 Modify StudentsList Class to use SQLiteManager

So far the StudentList class we have, consumes a vector to store students whiche are note preserved. We will modify the methods of this list to use the SQLiteManager and preserve the objects.
Add the following private fields to the StudentList Class:

    private Database db ;
    private URI uri;

Then modify its constructor to take and set thode fields:

public StudentList(Database db, URI uri) throws Exception {
        super(new StudentListComparator());
        this.db = db;
        this.uri = uri;
        SQLManager sqlManager = new SQLManager(db, uri);
        loadFrom(sqlManager.getStudents().elements()); 
        SQLManager.closeDB();
    }

11.7.1 Load list
As noticed above, the constructor then passes those objects to a new SQLiteManager object and gets students from the manager to load the list with. Then it closes the database.

11.7.2 Add Student
Now modify the addElement method:

/**
     * Adds a new element to the list.
     * @param element The element to be added.
     */
    void addElement(Object element) {
        Student student = (Student) element;
        SQLManager sqlManager = new SQLManager(db, uri);
        sqlManager.addStudent(student.getFirstname(), student.getLastName(), student.getEmail(), student.getAddress());
        doAdd(element);
        SQLManager.closeDB();
    }

11.7.3 Edit Student
And editElement method:

// added for Update:
    void updateElement(Object oldElement, Object newElement){
        Student student = (Student) newElement;
        SQLManager sqlManager = new SQLManager(db, uri);
        sqlManager.updateStudent(student.getId(), student.getFirstname(), student.getLastName(), student.getEmail(), student.getAddress());
        doUpdate(oldElement,newElement);
        SQLManager.closeDB();
    }

11.7.4 Delete Student

/**
     * Removes element from the list
     * @param element
     */
    void deleteElement(Object element) {
        Student student = (Student)element;
        SQLManager sqlManager = new SQLManager(db, uri);
        sqlManager.deleteStudent(student.getId());
        doRemove(element);
        SQLManager.closeDB();
    }

11.8 Run Student View Application

Now if you run the application, you will get the same functionality as before, but this time data is saved. Don't forget to add SDCard to simulator before running it.