ADF Mobile: SQLite in ADF Mobile

If you want to save data locally (in ADF Mobile), SQLite is the way to go. SQLite is a lightweight, portable and ACID-compliant relational database management system.
There are 2 ways of getting it (the database) in your application, by using a .sql script or by creating the .db itself and adding it to your application. I will show you the .sql way.

One of the most important things to remember is that at any given time, a single instance of the SQLite database may have either a single read-write connection or multiple read-only connections.
Although SQLite complies with the SQL92 standard, there are some restrictions but I refer you to the link below for more info about that and other subjects.

Read more about SQLite in ADF Mobile here : http://docs.oracle.com/cd/E35521_01/doc.111230/e24475/amxdatabase.htm#CIAEIGEJ 

Practical part

Use case

I will show you a couple of things in this post.

• Create a .db by using a .sql script (when your application starts).
• How to manage the connection of your SQLite DB.
• Security regarding the .db
• Doing operations on your DB (SELECT, INSERT, DELETE and UPDATE)

Let's start! 

1) Before we start, we will need an .sql script. Once you obtained/created one, place it in the following folder: 
yourApplicationName/.adf/META-INF/your_script_here
In your JDev, you will see it in the Application Resources > Descriptors > ADF META-INF (you may need to refresh) (this is an example: click).

2) When you create an 'ADF Mobile Application' in your JDeveloper, you will find the LifeCycleListenerImpl.java in your ApplicationController project (it is generated by default). We will use this class to create the DB (if needed) by reading the .sql script we provided. Note that the database name = the script name! 
Also don't forget to register this class to your application (because somehow this isn't done by default). This can be done by opening adfmf-application.xml (Situated in Application Resources > Descriptors >ADF META INF > here) and add the class in 'Lifecycle Even Listener' like in the picture below:

 Add this to the start() method. It checks if the database already exist, if not it will call a method which will create it.
 

     String databaseName = "F1_DB";  
     File dbFile = new File(AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory)+"/"+databaseName+".db");  
     if(!dbFile.exists())  
     {  
        try {  
          this.initializeDatabase(databaseName);  
        } catch (Exception e) {  
          System.out.println(e.getMessage());  
        }  
      }  

 The method below is a custom method. It reads the .sql script and executes it.

 private static void initializeDatabase(String databaseName) throws Exception  
   {  
     List stmts = null;  
     try {  
       ClassLoader cl = Thread.currentThread().getContextClassLoader();  
       InputStream is = cl.getResourceAsStream(".adf/META-INF/"+databaseName+".sql");  
       if (is == null) {  
         // .sql script not found  
       }  
       BufferedReader bReader = new BufferedReader(new InputStreamReader(is));  
       stmts = new ArrayList();  
       String strstmt = "";  
       String ln = bReader.readLine();  
       while (ln != null) {  
         if (ln.startsWith("REM") || ln.startsWith("COMMIT")) {  
           ln = bReader.readLine();  
           continue;  
         }  
         strstmt = strstmt + ln;  
         if (strstmt.endsWith(";")) {  
           System.out.println(strstmt);  
           stmts.add(strstmt);  
           strstmt = "";  
           ln = bReader.readLine();  
           continue;  
         }  
         ln = bReader.readLine();  
       }  
       String Dir = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);  
       String connStr = "jdbc:sqlite:" + Dir + "/"+databaseName+".db";  
       Connection conn = null;  
       conn = new SQLite.JDBCDataSource(connStr).getConnection();  
       conn.setAutoCommit(false);  
       for (int i = 0; i < stmts.size(); i++)  
       {  
         Statement pStmt = conn.createStatement();  
         pStmt.executeUpdate((String)stmts.get(i));  
       }  
       conn.commit();  
       conn.close();  
     } catch (Exception ex)  
      {  
       ex.printStackTrace();  
      }  
     }  

3) Now that our database is/will be created, we need to handle the connection. You can find the source code below. It provides a method to retrieve the Connection and a method to close the connection. 

 public class DBConnectionFactory {  
   public DBConnectionFactory() {  
     super();  
   }  
   protected static Connection conn = null;  
   protected static String DBname = "F1_DB";  
   public static Connection getConnection() throws Exception  
   {  
     if (conn == null) {  
       try {  
         String Dir = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);  
         String connStr = "jdbc:sqlite:" + Dir + "/"+DBname+".db";  
         conn = new SQLite.JDBCDataSource(connStr).getConnection();  
       } catch (SQLException e) {  
         System.err.println(e.getMessage());  
       }  
     }  
     return conn;  
   }  
   public static void closeConnection()  
   {  
     if(conn !=null)  
     {  
       try {  
         conn.close();  
       } catch (SQLException e) {  
         System.err.println(e.getMessage()); }  
     }  
     conn = null;  
   }  
 }  

4) Now we want to do operations on our database. I would suggest to create a database controller class, which controls all operations on your local database. The code below is just an example which isn't hard to understand.

 package be.cookie.viewcontroller.controller; 

 public class DBController {  
   private static DBController db;  
   public DBController() {  
     super();  
   }  
   public static DBController getInstance()  
   {  
     if(db == null)  
       db = new DBController();  
     return db;  
   }  
   public List getDrivers()  
   {     
     List items = new ArrayList();  
     ResultSet result = this.queryLocalDB("SELECT * FROM DRIVER");  
     try {  
         while (result.next())  
         {          
           String first = result.getString("firstname");  
           String last = result.getString("lastname");  
           String img = result.getString("imgURI");  
           String nat = result.getString("nat");  
           String team = result.getString("team");  
           int wc = result.getInt("wc");  
           int gp = result.getInt("gp");  
           int gpwon = result.getInt("gpwon");  
           items.add(new Driver(first,last,img,nat,team,wc,gp,gpwon));  
         }  
     }   
     catch (SQLException e)   
     {  
        e.printStackTrace();  
     }    
     return items;  
   }  
     public List getCalendarItems()  
     {     
       List items = new ArrayList();  
       ResultSet result = this.queryLocalDB("SELECT * FROM CALENDAR");  
       try {  
           while (result.next())  
           {          
             int code = result.getInt("nbr");  
             String place = result.getString("place");  
             String date = result.getString("date");  
             String flagURI = result.getString("flagURI");  
             items.add(new CalendarItem(code,place,date,flagURI));  
           }  
       }   
       catch (SQLException e)   
       {  
          e.printStackTrace();  
       }    
       return items;  
     }  
   public String insertDrivers(List list)  
   {    
     for(int i = 0; i < list.size(); i++)  
     {  
       Driver x = (Driver)list.get(i);  
       PreparedStatement pStmt;  
       try   
       {  
         pStmt = DBConnectionFactory.getConnection().prepareStatement("INSERT INTO DRIVER VALUES(?,?,?,?,?,?,?,?,?);");  
         pStmt.setString(1, x.getFirstname());  
         pStmt.setString(2, x.getLastname());  
         pStmt.setString(3, x.getImgURI());  
         pStmt.setString(4, x.getNat());  
         pStmt.setString(5, x.getTeam());  
         pStmt.setString(6, x.getDriverURI());  
         pStmt.setInt(7, x.getWc());  
         pStmt.setInt(8, x.getGp());  
         pStmt.setInt(9, x.getGpwon());  
         pStmt.executeUpdate();  
         if(pStmt != null)  
           pStmt.close();  
       }   
       catch (Exception e)   
       {  
         return e.getMessage();  
       }  
     }  
     try   
     {  
       DBConnectionFactory.getConnection().commit();  
     }   
     catch (Exception e)   
     {  
       return e.getMessage();  
     }  
     return "";  
   }  
   public String deleteDriver(String firstname)  
   {  
       PreparedStatement pStmt;  
       try   
       {  
         pStmt = DBConnectionFactory.getConnection().prepareStatement("DELETE FROM DRIVER WHERE firstname = ? ;");  
         pStmt.setString(1, firstname);  
         pStmt.executeUpdate();  
         if(pStmt != null)  
           pStmt.close();  
       }   
       catch (Exception e)   
       {  
         return e.getMessage();  
       }  
     return "";  
   }  
   public String updateDriver(String firstname, Driver x)  
   {  
     PreparedStatement pStmt;  
     try   
     {  
       pStmt = DBConnectionFactory.getConnection().prepareStatement("UPDATE DRIVER SET team = ?, wc = ?, gp = ?, gpwon = ? WHERE firstname = ? ;");  
       pStmt.setString(1, x.getTeam());  
       pStmt.setInt(2, x.getWc());  
       pStmt.setInt(3, x.getGp());  
       pStmt.setInt(4, x.getGpwon());  
       pStmt.setString(5, firstname);  
     }   
     catch (Exception e)   
     {  
       return e.getMessage();  
     }  
     return "";  
   }  
   /**   
    * @param query  
    * @return ResultSet queryResult  
    */  
   private ResultSet queryLocalDB(String qry)  
   {  
     Statement stat = null;  
     ResultSet result = null;  
     try  
     {  
     stat = DBConnectionFactory.getConnection().createStatement();  
     result = stat.executeQuery(qry);  
       if(result.equals(null))  
       {  
         return null;  
       }  
       else  
         return result;  
     }  
     catch(Exception ex)  
     {   
       return null;  
     }   
   }  
 }  

Security

When running the code provided in this blog, people can just locate the .db file and read it by using some program (I use SQLite database browser for Mac). Ideal for debugging but not for production. Securing the .db file can be done in few lines of code! 

Use this to secure your db (you should do this in the LifeCycleListenerImpl)

      AdfmfJavaUtilities.encryptDatabase(conn, "mypassword");  

And this for using the connection (this should be changed in the DBConnectionFactory)

 OLD: conn = new SQLite.JDBCDataSource(connStr).getConnection();  
 NEW: conn = new SQLite.JDBCDataSource(connStr)).getConnection(null,"mypassword");  

But keep in the mind the following! (quotes from the dev guide)

In the preceding example, the first parameter of the getConnection method is the user name, but since SQLite does not support user-based security, this value is ignored.
Note: SQLite does not display any error messages if you open an encrypted database with an incorrect password. Likewise, you are not alerted if you mistakenly open an unencrypted database with a password. Instead, when you attempt to read or modify the data, an SQLException is thrown with the message "Error: file is encrypted or is not a database".

Caution: If you open a database incorrectly (for example, use an invalid password to open an encrypted database), and then encrypt it again, neither the old correct password, the invalid password, nor the new password can unlock the database resulting in the irretrievable loss of data.