In this tutorial, you use Oracle JDeveloper and Oracle MAF to install and develop. a SQLite database, and then develop a MAF application to display values from it

Read more...

In this tutorial, you create a MAF application can configure it to retrieve records from a local database or a remote web service, depending on the status of the device's network

The first image, on the left, is the data retrieved from the web service and the second image, on the right, is the data retrieved from the local SQLite database.

Assumptions:

To complete this tutorial you must have installed JDeveloper 12.1.3 and have installed the MAF Extension. You may deploy to either an Android emulator (SDK version 5.0.x API level 21) or iOS simulator (Xcode 6.1.x). If you decide, you can deploy the application to a connected Android device. The tutorial demonstrates deploying the application to an Android emulator. You launch the emulator using the Android Virtual Device (AVD) Manager, which is part of Android SDK Tools. If you are using an iOS environment, you can use the following tutorial to set up the development environment: Set Up and Configure an iOS Environment. If you are using an Android environment, you can use the following tutorial: Set Up and Configure an Android Environment.

Purpose	Duration	Application
This tutorial shows you how to develop an application using a SQLite database with the Mobile Application Framework. To see the completed application for this tutorial, click the Download button to download a zip of the the solution application, and unzip it to your JDeveloper mywork folder.	2 hours

In this first section, you create an ADF Mobile application and configure it to initialize a local database, populate it with records and provide a connection when it’s time to retrieve records from it.

1. Open JDeveloper 12.1.3.

2. In the Application Navigator, click New Application.

   

3. Select the Mobile Application Framework Application. Then click OK.

   

4. Name it SummitLocalDB then click Finish



5. Modify the LifecycleListenerImpl.java to initialize the database when the application starts.
   
   Expand the ApplicationController > Application Sources > Application nodes and double click the LifecycleListenerImp.java.



6. Scroll down to the start method and add the code inside the method.
   
   

      public void start() {
         try {
             initializeDatabaseFromScript();
         } catch (Exception ex) {
             Utility.ApplicationLogger.severe(ex.getMessage());
             throw new RuntimeException(ex);
         }
         }

   Allow the import oracle.adfmf.util.Utility; to be included in the class.
   
   The initializeDatabaseFromScript method has not been defined yet, so it will be flagged as an error. This is fine, you'll fix it in a bit.



7. Scroll down after the deactivate method and add the code below to read the sql script from the device storage one line at a time to create the database records.
   
   As you add the code, import oracle.adfmf.util.Utility, java.sql.Connection, java.sql.Statement and all the default imports suggested for the other classes.
   
   The code does a few things:
   1: As you add the code, import oracle.adfmf.util.Utility, java.sql.Connection, and the default imports suggested for the other classes
   2: Retrieve the path to the local database file
   3: Check if the db file exists or not
   4: If it doesn't exist then we create it - make sure to turn off autocommit - db file created when opened, if it doesn't exist
   5: Retrieve the db script from storage and store it in memory
   6: Iterate line after line in the script - if line starts with rem or comment, then skip it. Otherwise execute each line
   7: Exception handling
   
   

   
   

   public void deactivate()
   {
   }

       // 1: Read sql script from device storage one line at a time to create database records
           private void initializeDatabaseFromScript() throws Exception {
               InputStream scriptStream = null;
               Connection conn = null;
               try {
                  
       // 2: Retrieve the path to the local database file          
                   Utility.ApplicationLogger.info("Initializing DB...");
                   String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);
                   String dbName = docRoot + "/sm.db";
                  
       // 3: Check if the db file exists or not
                   File dbFile = new File(dbName);
                   if (dbFile.exists())
                       return;
                  
       // 4: If it doesn't exist then we create it - make sure to turn off autocommit - db file created when opened, if it doesn't exist
                   conn = new JDBCDataSource("jdbc:sqlite:" + dbName).getConnection();
                   conn.setAutoCommit(false);
                  
       //  5: Retrieve the db script from storage and store it in memory
                   Utility.ApplicationLogger.info("Reading script");
                   scriptStream =
                       Thread.currentThread().getContextClassLoader().getResourceAsStream("META-INF/initializedb.sql");
                   BufferedReader scriptReader = new BufferedReader(new InputStreamReader(scriptStream));
                   String nextLine;
                   StringBuffer nextStatement = new StringBuffer();
                  
       // 6: Iterate line after line in the script - if line starts with rem or comment, then skip it.  Otherwise execute each line
                   Statement stmt = conn.createStatement();
                   while ((nextLine = scriptReader.readLine()) != null) {
                   if (nextLine.startsWith("REM") || nextLine.startsWith("COMMIT") || nextLine.length() <1)
                       continue;
                   nextStatement.append(nextLine);
                       if (nextLine.endsWith(";")) {
                           Utility.ApplicationLogger.info("Execute statement: " + nextStatement.toString());
                           stmt.execute(nextStatement.toString());
                           nextStatement = new StringBuffer();
                       }
                   }
                  
       // 7: Exception handling
                       }finally {
                           if (conn != null) {
                               conn.commit();
                               conn.close();
                       }
   
           }
   }
   }
         

   
   


8. Save your work.

9. Next, confirm the the Lifecycle event listener class (LifecycleListenerImpl.java) is registered with the application. If create your own lifecycle listener, rather than using the default one, you must register it.
   
   In the Application Resources pane, expand the Descriptors > ADF META-INF nodes and double click the maf-application.xml file.
   
   In the Application tab, you should see Lifecycle Event Listener property. By default the value is set to application.LifecycleListenerImpl, the file you just modified. If you did not use the default lifecycle listener file, you would need to set this value to the file you used.
   
   Ensure the value is set to application.LifecycleListenerImpl.

In this section, now that you have the code to initialize the database, add the file containing the sql code to create the database records.

1. In the ApplicationController project, right click the META-INF directory (which is where we told the initialize method to look for the sql code) and select New > From Gallery



2. In the Gallery, select the General category and then the File item.
   
   Name the file initializedb.sql and click OK.





3. Add the code below to initialize the database and add records.
   
   Save all your work.
   

   
   PRAGMA foreign_keys = ON;
   PRAGMA auto_vacuum = FULL;
   CREATE TABLE REGIONS (REGION_ID NUMBER PRIMARY KEY, REGION_NAME VARCHAR2(25));
   CREATE TABLE COUNTRIES (COUNTRY_ID CHAR(2) PRIMARY KEY, COUNTRY_NAME VARCHAR2(40), REGION_ID NUMBER, FOREIGN KEY (REGION_ID) REFERENCES REGIONS(REGION_ID) );
   INSERT INTO REGIONS (REGION_ID,REGION_NAME) VALUES (1,'Europe');
   INSERT INTO REGIONS (REGION_ID,REGION_NAME) VALUES (2,'Americas');
   INSERT INTO REGIONS (REGION_ID,REGION_NAME) VALUES (3,'Asia');
   INSERT INTO REGIONS (REGION_ID,REGION_NAME) VALUES (4,'Middle East and Africa');
   INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('AD','ADFa', 1);
   INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('DU','United States of Duke', 2);
   INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('OR','OracleLand', 3);
   INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('JA','Javatopia', 4);






4. Next steps add a class that will allow you establish a connection to the database. You’ll reference methods in it when you create the class to determine where to retrieve the customer data.
   
   Right click the ApplicationController and select New > From Gallery the context menu. In the gallery, create a new Java class. Name it ConnectionFactory and change the package to database.
   
   Then click OK.






5. Replace all the code in the class with the code below and save your work.
   
   This code establishes the connection to the database.
   
   

   
   package database;
   

   import java.sql.Connection;
   import java.sql.SQLException;

   import oracle.adfmf.framework.api.AdfmfJavaUtilities;

   // Established the connection to the database
   public class ConnectionFactory {
   public ConnectionFactory() {
   super();
   }

   protected static Connection conn = null;

   public static Connection getConnection() throws Exception {
   if (conn == null) {
   try {
   String Dir = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);
   String connStr = "jdbc:sqlite:" + Dir + "/sm.db";
   conn = new SQLite.JDBCDataSource(connStr).getConnection();
   } catch (SQLException e) {
   System.err.println(e.getMessage());
   }
   }

   return conn;
   }

   public static void closeConnection() {
   try {
   if (conn != null) {
   conn.close();
   conn = null;
   }
   } catch (Exception ex) {
   throw new RuntimeException(ex);
   }
   }
   }
   

   
   

   
   


6. When you’re finished your ApplicationController project should look like the following image, including the updated LifecycleListener.java, and the new ConnectionFactory.java and initializedb.sql files.

In this section you create two classes: One will be used to define the data structure that will be used on the page (CountryBO.java) and the other will determine the network status and retrieve records from the web service or the local database (CountryDC.java).

1. In the first section create a class that will define, and provide all the accessors for all the country attributes. These classes will be used with the UI, so create them in the ViewController project.
   
   Create a class to define the data structure used in the page.  Select the ViewController project an invoke the New Gallery and select the Java class from the General category. Then select the Java Class item and click OK.



2. Name the class CountryBO and set the Package property to mobile.



3. Next, copy the code below and add it to the class. Then save your work.
   
   JDeveloper can generate nearly everything in this class. Once the attributes have been created, there is a contextual menu option to generate getters and setters for them (Generate accessors), and implement property change support as well.
   
   

   
   package mobile;

   import oracle.adfmf.java.beans.PropertyChangeListener;
   import oracle.adfmf.java.beans.PropertyChangeSupport;

   // Defines data structure for the return of the data from the local db and web service
   // Supports updating records when their value changes with propertyChangeSupport (during Generate Accessors)

   public class CountryBO implements Comparable{
   
   private String id;
   private String country;
   private Integer regionId;
   private transient PropertyChangeSupport propertyChangeSupport = new PropertyChangeSupport(this);
   

   public CountryBO() {
   super();
   }

   public boolean equals(Object object) {
   if (this == object) {
   return true;
   }
   if (!(object instanceof CountryBO)) {
   return false;
   }
   final CountryBO other = (CountryBO)object;
   if (!(id == null ? other.id == null : id.equals(other.id))) {
   return false;
   }
   return true;
   }

   public int hashCode() {
   final int PRIME = 37;
   int result = 1;
   result = PRIME * result + ((id == null) ? 0 : id.hashCode());
   return result;
   }

   public int compareTo(Object o) {
   if (this.equals(o)){
   return 0;
   }
   else if (o instanceof CountryBO){
   return this.country.compareTo(((CountryBO)o).getCountry());
   }
   else{
   throw new ClassCastException("CountryBO expected.");
   }
   }

   public void setId(String countryId) {
   String oldCountryId = this.id;
   this.id = countryId;
   propertyChangeSupport.firePropertyChange("countryId", oldCountryId, countryId);
   }

   public void addPropertyChangeListener(PropertyChangeListener l) {
   propertyChangeSupport.addPropertyChangeListener(l);
   }

   public void removePropertyChangeListener(PropertyChangeListener l) {
   propertyChangeSupport.removePropertyChangeListener(l);
   }

   public String getId() {
   return id;
   }

   public void setCountry(String countryName) {
   String oldCountryName = this.country;
   this.country = countryName;
   propertyChangeSupport.firePropertyChange("countryName", oldCountryName, countryName);
   }

   public String getCountry() {
   return country;
   }

   public void setRegionId(Integer regionId) {
   Integer oldRegionId = this.regionId;
   this.regionId = regionId;
   propertyChangeSupport.firePropertyChange("regionId", oldRegionId, regionId);
   }

   public Integer getRegionId() {
   return regionId;
   }
   }
   

   
   

    



4. In the next few steps create a class that will check the network status of the device and depending if it’s available, either retrieve records from the web service or the local database.  As you add code, make sure to include all the import statements as prompted.
   
   Select the mobile package in the ViewController and invoke the New Gallery, create another class. Name it CountryDC and make sure it’s package name is mobile.
   
   
   
   

5. Copy and replace the code from below into your CountryDC.java file.
   
   A few things happen in the code to point out.
   - Make sure to take the time to accept the import statements that should be added.
   1: Code to determine if the network is available by checking the deviceScope.hardware.networkStatus property.
   2: Check to see if the value of the deviceScope is NOT_REACHABLE. Then, either call the method to retrieve records from the web service or from the local database.
   3: Code to get the records from the local database.
   4: Finally, code to retrieve records using the web service.
   
   

   
   

   package mobile;

   import java.sql.Connection;

   import java.util.ArrayList;
   import java.util.List;

   import oracle.adfmf.framework.api.AdfmfJavaUtilities;
   import mobile.CountryBO;
   import database.ConnectionFactory;
   

   import java.sql.ResultSet;
   import java.sql.Statement;

   import java.util.Arrays;
   import java.util.Collections;

   import oracle.adfmf.framework.api.AdfmfContainerUtilities;
   import oracle.adfmf.framework.api.GenericTypeBeanSerializationHelper;
   import oracle.adfmf.framework.exception.AdfInvocationException;
   import oracle.adfmf.util.GenericType;
   import oracle.adfmf.util.Utility;

   public class CountryDC {
   
   private static final String NOT_REACHABLE = "NotReachable"; // Indiates no network connectivity
   
   public CountryDC() {
   super();
   }
   
   // 1: Determine if the network is available by checking deviceScope.hardware.networkStatus property
   public CountryBO[] getCountries() {
   CountryBO[] countries = null;
   
   String networkStatus =
   (String)AdfmfJavaUtilities.evaluateELExpression("#{deviceScope.hardware.networkStatus}");

   // 2: If the value of the deviceScope is NOT_REACHABLE, then call getCountriesFromDB else call getCountiresFromWS
   if (networkStatus.equals(NOT_REACHABLE)) {
   countries = getCountriesFromDB();
   } else {
   countries = getCountriesFromWS();
   }
   return countries;
   
   }
   
   // 3: Use JDBC to get records from local database
   private CountryBO[] getCountriesFromDB() {
   Connection conn = null;
   List returnValue = new ArrayList();

   try {
   conn = ConnectionFactory.getConnection();

   Statement stmt = conn.createStatement();
   ResultSet result = stmt.executeQuery("SELECT COUNTRY_ID, COUNTRY_NAME, REGION_ID FROM COUNTRIES;");
   while (result.next()) {
   CountryBO country = new CountryBO();
   Utility.ApplicationLogger.severe("Country: " + result.getString("COUNTRY_ID"));
   country.setId(result.getString("COUNTRY_ID"));
   country.setCountry(result.getString("COUNTRY_NAME"));
   country.setRegionId(new Integer(result.getInt("REGION_ID")));
   returnValue.add(country);
   }

   } catch (Exception ex) {
   Utility.ApplicationLogger.severe(ex.getMessage());
   ex.printStackTrace();
   throw new RuntimeException(ex);
   }
   Collections.sort(returnValue);
   return (CountryBO[])returnValue.toArray(new CountryBO[returnValue.size()]);
   }
   
   // 4: Retrieve record from web service
   private CountryBO[] getCountriesFromWS() {
   try {
   GenericType genericReturnValue =
   (GenericType)AdfmfJavaUtilities.invokeDataControlMethod("SM_WS", null, "findCountry", new ArrayList(),
   new ArrayList(), new ArrayList());
   CountryBO[] returnValue =
   (CountryBO[])GenericTypeBeanSerializationHelper.fromGenericType(CountryBO[].class, genericReturnValue, "result");

   Arrays.sort(returnValue);
   return returnValue;

   } catch (AdfInvocationException aie) {
   if (AdfInvocationException.CATEGORY_WEBSERVICE.compareTo(aie.getErrorCategory()) == 0) {
   throw new RuntimeException("Error with the server. Please try later", aie);
   } else {
   throw new RuntimeException(aie);

   }
   }
   }
   }
   

   
   




6. 
   Save all your work.
   
   The complete class should look like the image below. The databaseConnectionFactory import will have a wavy red line below it, signifying that the class can not be found. The class that is needed was created in the ApplicationController project.
   
   

7. To resolve this issue, double click the ViewController project to invoke it's properties. Select the Dependencies node and then click the green plus sign next to the Dependencies Projects and Archives label.
   
   Expand the ApplicationController.jpr and select the Build Output check box.
   
   Then click OK and save your work.





8. Back in the CountryDC.java class, the ConnectionFactory error is resolved.



 

Expand all | Hide all