MAF 2.0: Using Local Database

When you are building a MAF application, you may decide to use Web Services (SOAP / REST) or local Database to retrieve or persist your data. If you decide to use local Database, the SQLite is the default Database of MAF. SQLite is designed for use as an embedded database system, one typically used by a single user and often linked directly into the application. It is ACID-compliant, lightweight and portable.

In this post I will create a CRUD of employees in Oracle MAF 2.0 using SQLite Database. Download the sample application: MAFDBApp.zip.

This article was published on OTN LA in brazilian portuguese, and you can read it here: Oracle Mobile Application Framework 2.0: Usando Banco de Dados Local.

Create a Mobile Application Framework Application, and name it as MAFDBApp.

maf-2-0-using-local-database1

In the maf-feature.xml file, create a new feature and call it as employees.

maf-2-0-using-local-database2

Click the Content tab, and then click green plus button near File field, to create the EmployeesTF Task Flow.

maf-2-0-using-local-database3

Create initializedb.sql file.
This SQL script initializes the database when the application starts.
Go to Projects panel > ApplicationController project, right-click META-INF folder and choose New > From Gallery.
In the New Gallery dialog, choose General > File.

maf-2-0-using-local-database4

Copy the following code in the file.

PRAGMA auto_vacuum = FULL;
CREATE TABLE EMPLOYEES (EMPLOYEE_ID NUMBER(6) PRIMARY KEY, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25) NOT NULL, EMAIL VARCHAR2(25) NOT NULL);
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (100,'David','King','steven@king.net');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (101,'Neena','Kochhar','neena@kochhar.net');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (102,'Lex','De Haan','lex@dehaan.net');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (103,'Alexander','Hunold','alexander@hunold.net');
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (104,'Bruce','Ernst','bruce@ernst.net');

Open LifeCycleListenerImpl.java file.
Replace the start() method with the following code:

public void start() {
  try {
    initializeDatabaseFromScript();
  } catch (Exception e) {
    Trace.log(Utility.ApplicationLogger, Level.SEVERE, this.getClass(), "start", e);
  }
}

Add initializeDatabaseFromScript() method.
This method creates the database and executes the SQL statements.

private void initializeDatabaseFromScript() throws Exception {
  InputStream scriptStream = null;
  Connection conn = null;

  try {
    // ApplicationDirectory returns the private read-write sandbox area
    // of the mobile device's file system that this application can access.
    // This is where the database is created
    String docRoot = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);
    String dbName = docRoot + "/sample.db";

    // Verify whether or not the database exists.
    // If it does, then it has already been initialized
    // and no furher actions are required
    File dbFile = new File(dbName);
    if (dbFile.exists())
      return;

    // If the database does not exist, a new database is automatically
    // created when the SQLite JDBC connection is created
    conn = new SQLite.JDBCDataSource("jdbc:sqlite:" + docRoot + "/sample.db").getConnection();

    // To improve performance, the statements are executed
    // one at a time in the context of a single transaction
    conn.setAutoCommit(false);

    // Since the SQL script has been packaged as a resource within
    // the application, the getResourceAsStream method is used
    scriptStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("META-INF/initializedb.sql");
    BufferedReader scriptReader = new BufferedReader(new InputStreamReader(scriptStream));
    String nextLine;
    StringBuffer nextStatement = new StringBuffer();

    // The while loop iterates over all the lines in the SQL script,
    // assembling them into valid SQL statements and executing them as
    // a terminating semicolon is encountered
    Statement stmt = conn.createStatement();
    while ((nextLine = scriptReader.readLine()) != null) {
      // Skipping blank lines, comments, and COMMIT statements
      if (nextLine.startsWith("REM") || nextLine.startsWith("COMMIT") || nextLine.length() < 1) {
        continue;
      }

      nextStatement.append(nextLine);

      if (nextLine.endsWith(";")) {
        stmt.execute(nextStatement.toString());
        nextStatement = new StringBuffer();
      }
    }

    conn.commit();

  } finally {
    if (conn != null) {
      conn.close();
    }
  }
}

Create DBConnectionFactory class.
This class manages the database connections.
Go to Projects panel, right-click ApplicationController project and choose New > Java Class.

maf-2-0-using-local-database5

Replace all the file content with the following code:

package br.com.waslleysouza.application;

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

import oracle.adfmf.framework.api.AdfmfJavaUtilities;
import oracle.adfmf.util.Utility;

public class DBConnectionFactory {
  private static Connection conn = null;

  public DBConnectionFactory() {
    super();
  }

  public static Connection getConnection() throws Exception {
    if (conn == null) {
      try {
        String dirRoot = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);
        String connStr = "jdbc:sqlite:" + dirRoot + "/sample.db";
        conn = new SQLite.JDBCDataSource(connStr).getConnection();
            
      } catch (SQLException e) {
        Utility.ApplicationLogger.severe(e.getMessage());
      }
    }

    return conn;
  }

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

Go to Projects panel, double-click ViewController project and choose Dependencies.
Add the ApplicationController project as dependency.

maf-2-0-using-local-database6

Create Employee Entity.
Go to Projects panel, right-click ViewController project and choose New > Java Class.
In this application, Employee class must implement the Cloneable class.

maf-2-0-using-local-database7

Replace all the file content with the following code:

package br.com.waslleysouza.mobile;

public class Employee implements Cloneable {
  private Integer employeeId;
  private String firstName;
  private String lastName;
  private String email;

  public Employee() {
    super();
  }

  protected Object clone() throws CloneNotSupportedException {
    return super.clone();
  }
}

Generate the accessors.
Mark this option: “Notify listeners when property changes”.

maf-2-0-using-local-database8

Create DBAdapter class.
This class contains codes to retrieve / persist data in the Database.
Go to Projects panel, right-click ViewController project and choose New > Java Class.

maf-2-0-using-local-database9

Replace all the file content with the following code:

package br.com.waslleysouza.mobile;

import br.com.waslleysouza.application.DBConnectionFactory;

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

import oracle.adfmf.util.Utility;

public class DBAdapter {
  public DBAdapter() {
    super();
  }

  protected boolean executeUpdate(String query) {
    boolean success = false;

    try {
      Connection conn = DBConnectionFactory.getConnection();
      Statement stmt = conn.createStatement();
      int rowCount = stmt.executeUpdate(query);

      if (rowCount > 0) {
        success = true;
        Utility.ApplicationLogger.severe("RowCount=" + rowCount + ", Query=" + query);
      }

    } catch (Exception e) {
      Utility.ApplicationLogger.severe(e.getMessage());
      e.printStackTrace();
      throw new RuntimeException(e);

    } finally {
      DBConnectionFactory.closeConnection();
    }

    return success;
  }

  protected ResultSet executeQuery(String query) {
    ResultSet result = null;

    try {
      Connection conn = DBConnectionFactory.getConnection();
      Statement stmt = conn.createStatement();
      result = stmt.executeQuery(query);

    } catch (Exception ex) {
      Utility.ApplicationLogger.severe(ex.getMessage());
      ex.printStackTrace();
      throw new RuntimeException(ex);

    } finally {
      DBConnectionFactory.closeConnection();
    }

    return result;
  }
}

Create EmployeeAdapter class.
This class contains codes to retrieve / persist employees to Database.
Go to Projects panel, right-click ViewController project and choose New > Java Class.

maf-2-0-using-local-database10

Replace all the file content with the following code:

package br.com.waslleysouza.mobile;

import java.sql.ResultSet;

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

import oracle.adfmf.util.Utility;

public class EmployeeAdapter extends DBAdapter {
  public EmployeeAdapter() {
    super();
  }

  public List findAllEmployees() {
    List employees = new ArrayList();

    try {
      ResultSet result = executeQuery("SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES;");

      while (result.next()) {
        Employee employee = new Employee();
        employee.setEmployeeId(new Integer(result.getInt("EMPLOYEE_ID")));
        employee.setFirstName(result.getString("FIRST_NAME"));
        employee.setLastName(result.getString("LAST_NAME"));
        employee.setEmail(result.getString("EMAIL"));
        employees.add(employee);
        Utility.ApplicationLogger.severe("Employee: " + employee.getEmployeeId() + "," +
                                         employee.getFirstName() + "," + employee.getLastName() + "," +
                                         employee.getEmail());
      }

    } catch (Exception ex) {
      Utility.ApplicationLogger.severe(ex.getMessage());
      ex.printStackTrace();
      throw new RuntimeException(ex);
    }

    return employees;
  }

  public boolean insertEmployee(Employee employee) {
    return executeUpdate("INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES (" +
                         employee.getEmployeeId() + ",'" + employee.getFirstName() + "','" +
                         employee.getLastName() + "','" + employee.getEmail() + "')");
  }

  public boolean deleteEmployee(Integer id) {
    return executeUpdate("DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID=" + id);
  }

  public boolean updateEmployee(Employee employee) {
    return executeUpdate("UPDATE EMPLOYEES SET FIRST_NAME='" + employee.getFirstName() + "', LAST_NAME='" +
                         employee.getLastName() + "', EMAIL='" + employee.getEmail() + "' WHERE EMPLOYEE_ID=" +
                         employee.getEmployeeId());
  }
}

Create EmployeeDC class.
This class uses the methods of EmployeeAdapter class.
Go to Projects panel, right-click ViewController project and choose New > Java Class.

maf-2-0-using-local-database11

Replace all the file content with the following code:

package br.com.waslleysouza.mobile;

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

public class EmployeeDC {
  private Employee employee;
  private List employees = new ArrayList();
  private EmployeeAdapter adapter = new EmployeeAdapter();

  public EmployeeDC() {
    super();
    findAll();
  }

  public void setEmployee(Employee employee) {
    this.employee = employee;
  }

  public Employee getEmployee() {
    return employee;
  }

  public Employee[] getEmployees() {
    return (Employee[]) employees.toArray(new Employee[employees.size()]);
  }

  public void findAll() {
    employees = adapter.findAllEmployees();
  }

  public void delete(Integer employeeId) {
    boolean success = adapter.deleteEmployee(employeeId);
    if (success) {
      employees.remove(employee);
    }
  }

  public void insert(Employee employee) {
    boolean success = adapter.insertEmployee(employee);
    if (success) {
      employees.add(employee);
    }
  }

  public void update(Employee employee) {
    boolean success = adapter.updateEmployee(employee);
    if (success) {
      int index = employees.indexOf(employee);
      employees.set(index, employee);
    }
  }

  public void prepareEmployeeToAdd() {
    employee = new Employee();
  }

  public void prepareEmployeeToEdit(Employee employee) throws CloneNotSupportedException {
    this.employee = (Employee) employee.clone();
  }
}

Create EmployeeDC Data Control.
Go to Projects panel, right-click EmployeesPojoDC.java file and choose Create Data Control.
In the Create Bean Data Control dialog, click Next and Finish.

maf-2-0-using-local-database12

maf-2-0-using-local-database13

Open EmployeesTF.xml file.
Go to Components panel, drag three View and a Wildcard Control Flow Rule components and drop inside the EmployeesTF.
Connect them using the Control Flow Case component.

maf-2-0-using-local-database14

Double-click all View components and click OK to create the AMX Pages.

maf-2-0-using-local-database15

maf-2-0-using-local-database16

maf-2-0-using-local-database17

Open employeeList page.
Go to Data Control panel, drag the employees collection and drop inside the page as MAF List View.
In the ListView Gallery, click Ok twice.

maf-2-0-using-local-database18

maf-2-0-using-local-database19

In the listItem component, set the property: action="toEmployeeDetail".
In the outputText component, set the property: value="#{row.lastName}, #{row.firstName}".

Remove the commandButton component of the secondary facet.

Go to Data Control panel > EmployeeDC, drag the prepareEmployeeToAdd operation and drop inside the secondary facet as MAF Button.
Set the properties: text="Add" and action="toEditEmployee".
Go to Components panel > Operations, drag the Set Property Listener component and drop inside the commandButton component.
Set the properties: from="#{'add'}" and to="#{pageFlowScope.actionSelected}".

maf-2-0-using-local-database20

Go to Bindings and edit the employees binding.
Add lastName and firstName as Display Attributes.

maf-2-0-using-local-database21

Open employeeDetail page.
Go to Data Control panel, drag the employees collection and drop inside the page as MAF Read-only Form.
In the Edit Form Fields dialog, click Ok.

maf-2-0-using-local-database22

Remove the commandButton component of the secondary facet.

Go to Data Control panel, drag the delete(Integer) operation and drop inside the secondary facet as Method > MAF Button.
In the Edit Action Binding, set the value of employeeId to #{bindings.employeeId.inputValue}.

maf-2-0-using-local-database23

Set the properties: text="Delete" and action="toEmployeeList".

In the commandButton component of the primary facet, set the properties: text="Back" and action="__back".

Go to Data Control panel, drag the prepareEmployeeToEdit(Employee) operation and drop inside the secondary facet as MAF Button.
In the Edit Action Binding, set the value of employee to #{bindings.employeesIterator.currentRow.dataProvider}.

maf-2-0-using-local-database24

Set the properties: text="Edit" and action="toEditEmployee".
Go to Components panel > Operations, drag the Set Property Listener component and drop inside the commandButton component.
Set the properties: from="#{'edit'}" and to="#{pageFlowScope.actionSelected}".

maf-2-0-using-local-database25

Open editEmployee page.
Add the Validation Group component to validate required fields.
Go to Components panel > Operations, drag the Validation Group component and drop inside the page.
Set the property: id="vg1".

Go to Data Control panel, drag the employee Structured Attribute and drop inside the validationGroup component.
In the Edit Form Fields dialog, click Ok.

maf-2-0-using-local-database26

In the commandButton of the primary facet, set the properties: text="Back" and action="__back".

Remove the commandButton component of the secondary facet.

Go to Data Control panel, drag the insert(Employee) operation and drop inside the secondary facet as MAF Button.
In the Edit Action Binding, set the value of employee to #{bindings.employeeIterator.currentRow.dataProvider}.

maf-2-0-using-local-database27

Set the properties: text="Save", action="toEmployeeList" and rendered="#{pageFlowScope.actionSelected == 'add'}".
Go to Components panel > Operations, drag the Validation Behavior component and drop inside the commandButton component.
Set the property: group="vg1".

Go to Data Control panel, drag the update(Employee) operation and drop inside the secondary facet as MAF Button.
In the Edit Action Binding, set the value of employee to #{bindings.employeeIterator.currentRow.dataProvider}.

maf-2-0-using-local-database28

Set the properties: text="Update", action="toEmployeeList" and rendered="#{pageFlowScope.actionSelected == 'edit'}".
Go to Components panel > Operations, drag the Validation Behavior component and drop inside the commandButton component.
Set the property: group="vg1".

For all inputText components, set the properties: required="true" and showRequired="true".

maf-2-0-using-local-database29

Deploy the application and enjoy!

maf-2-0-using-local-database30

maf-2-0-using-local-database31

maf-2-0-using-local-database32

maf-2-0-using-local-database33

Waslley Souza

Author: Waslley Souza

Consultor Oracle com foco em tecnologias Oracle Fusion Middleware e SOA. Certificado Oracle WebCenter Portal, Oracle ADF e Java.

1 thought on “MAF 2.0: Using Local Database”

Leave a Reply

Your email address will not be published. Required fields are marked *