Thursday, January 24, 2013

ADF Mobile - Access Device Native SQLite Database to Store Data

Today I learnt on how to access device native SQLite database to store data and perform CRUD operations. This is my first article on ADF Mobile, In this article I will show on how to read data for display, create and save new record to the database.

You can find more examples in Oracle ADF Mobile Samples, by looking into the samples and Debugging ADF Mobile Apps on Android. I was able to use SQLite database to store the data.

Download the sample workspace from here and application screen looks like below when it deployed to Android Emulator. Displaying the Departments List is fetched from SQLite, click on the Add button to create new department record.


Enter the details in the department form and click on Save button. The Department information will saved to the database and moved to Dept List screen.


Department list will get updated by newly added department record.


Implementation Steps

Create an ADF Mobile Application, the application consists of two projects. Application Controller project of Application LifeCycle, Listeners, Device Features DataControl and ViewController project contains mobile features content like AMX Files, Task Flows and DataControl.

In Application Controller project. Create a DBConnectionFactory.java class and copy the below code, this class contains code for connecting to SQLlite DB. Connecting to the SQLite database is somewhat different from opening a connection to an Oracle database, here use the java.sql.Connection object associated with your application to connect to the SQLite database.
public class DBConnectionFactory {
    protected static Connection conn = null;

    public DBConnectionFactory() {
        super();
    }

    /**
     * Connect to the SQLite database
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        if (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 Dir = AdfmfJavaUtilities.getDirectoryPathRoot(AdfmfJavaUtilities.ApplicationDirectory);
                String connStr = "jdbc:sqlite:" + Dir + "/DEMO.db";

                // If the database does not exist, a new database is automatically
                // created when the SQLite JDBC connection is created
                conn = new SQLite.JDBCDataSource(connStr).getConnection();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
}
Note:- demo.sql file should be put in .adf/META-INF/ folder. You can get the sql file by downloading the MobileDemo application.

Open LifeCycleListenerImpl.java file and add the below method code.
/**
* This method will read the sql file and
* commit the sql statements to the SQLite DB
*/
private void InitDB() {
	try {
		// Since the SQL script has been packaged as a resource within
		// the application, the getResourceAsStream method is used
		ClassLoader cl = Thread.currentThread().getContextClassLoader();
		InputStream is = cl.getResourceAsStream(".adf/META-INF/demo.sql");
		if (is == null) {
			System.err.println("Could not look up : .adf/META-INF/demo.sql");
			return;
		}

		BufferedReader bReader = new BufferedReader(new InputStreamReader(is));
		List stmts = new ArrayList();
		String strstmt = "";
		String ln = bReader.readLine();

		// 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
		while (ln != null) {
			// Skipping blank lines, comments, and COMMIT statements
			if (ln.startsWith("REM") || ln.startsWith("COMMIT")) {
				ln = bReader.readLine();
				continue;
			}
			strstmt = strstmt + ln;
			if (strstmt.endsWith(";")) {
				stmts.add(strstmt);
				strstmt = "";
				ln = bReader.readLine();
				continue;
			}
			ln = bReader.readLine();
		}

		// To improve performance, the statements are executed
		// one at a time in the context of a single transaction
		DBConnectionFactory.getConnection().setAutoCommit(false);
		for (int i = 0; i < stmts.size(); i++) {
			Statement pStmt = DBConnectionFactory.getConnection().createStatement();
			pStmt.executeUpdate((String)stmts.get(i));
		}
		DBConnectionFactory.getConnection().commit();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
In Start() method LifeCycleListenerImpl.java file, add the below code.
try {
	//Getting the connection to the database
	Statement stat = DBConnectionFactory.getConnection().createStatement();
	ResultSet rs = stat.executeQuery("SELECT * FROM DEPARTMENTS;");
  } catch (SQLException e) {
	// if the error message is "out of memory",
	// it probably means no database file is found
	InitDB();
  } catch (Exception e) {
	e.printStackTrace();
}
Expand the ViewController project. Locate and expand the Application Sources folder, create a Department.java file and add the below code.
public class Department {
    protected int deptId;
    protected String deptName;
    protected int mgrId;
    protected int locId;
    private transient PropertyChangeSupport propertyChangeSupport = new PropertyChangeSupport(this);

    public Department() {
        super();
    }

    public Department(int deptId, String deptName, int mgrId, int locId) {
        setDeptId(deptId);
        setDeptName(deptName);
        setMgrId(mgrId);
        setLocId(locId);
    }

    Department(Department newDept) {
        setDeptId(newDept.getDeptId());
        setDeptName(newDept.getDeptName());
        setMgrId(newDept.getMgrId());
        setLocId(newDept.getLocId());
    }

    public String getKey() {
        Integer i = new Integer(deptId);
        return i.toString();
    }

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

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


    public void setDeptId(int deptId) {
        int oldDeptId = this.deptId;
        this.deptId = deptId;
        propertyChangeSupport.firePropertyChange("deptId", oldDeptId, deptId);
    }

    public int getDeptId() {
        return deptId;
    }

    public void setDeptName(String deptName) {
        String oldDeptName = this.deptName;
        this.deptName = deptName;
        propertyChangeSupport.firePropertyChange("deptName", oldDeptName, deptName);
    }

    public String getDeptName() {
        return deptName;
    }

    public void setMgrId(int mgrId) {
        int oldMgrId = this.mgrId;
        this.mgrId = mgrId;
        propertyChangeSupport.firePropertyChange("mgrId", oldMgrId, mgrId);
    }

    public int getMgrId() {
        return mgrId;
    }

    public void setLocId(int locId) {
        int oldLocId = this.locId;
        this.locId = locId;
        propertyChangeSupport.firePropertyChange("locId", oldLocId, locId);
    }

    public int getLocId() {
        return locId;
    }
}
Create DepartmentList.java file and add the below code. Create DataControl based on DepartmentList.java file.
public class DepartmentList {
    private static List s_departments = null;
    private Department editDepartment = new Department();

    private transient ProviderChangeSupport providerChangeSupport = new ProviderChangeSupport(this);

    public void addProviderChangeListener(ProviderChangeListener l) {
        providerChangeSupport.addProviderChangeListener(l);
    }

    public void removeProviderChangeListener(ProviderChangeListener l) {
        providerChangeSupport.removeProviderChangeListener(l);
    }

    public DepartmentList() {
        if (s_departments == null) {
            s_departments = new ArrayList();
            Execute();
        }
    }

    /**
     * Method will query the database and populate
     * the result list to s_departments
     */
    public void Execute() {
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "Execute",
                  "Coming Inside Execute Method");
        try {
            Connection conn = DBConnectionFactory.getConnection();
            s_departments.clear();
            conn.setAutoCommit(false);
            PreparedStatement stat = conn.prepareStatement("SELECT * from DEPARTMENTS ORDER BY DEPARTMENT_NAME");
            ResultSet rs = stat.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("DEPARTMENT_ID");
                String deptName = rs.getString("DEPARTMENT_NAME");
                int mgrId = rs.getInt("MANAGER_ID");
                int locId = rs.getInt("LOCATION_ID");
                Department d = new Department(id, deptName, mgrId, locId);
                s_departments.add(d);
            }
            rs.close();
            Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "Execute",
                      "Exiting from Execute Method");
            //  providerChangeSupport.fireProviderRefresh("departments");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }

    /**
     * Method will get count of departments in database
     * @return
     */
    public int getDepartmentCount() {
        return s_departments.size();
    }

    /**
     * Method will get the list of departments
     * @return
     */
    public Department[] getDepartments() {
        Department d[] = null;
        d = (Department[])s_departments.toArray(new Department[s_departments.size()]);
        return d;
    }

    /**
     * Method will create new deptartment object instance
     */
    public void AddDepartment() {
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "AddDepartment",
                  "Coming inside AddDepartment");
        //Incrementing the intial deptId by 10
        int deptId = getDepartmentCount() + 10;
        setEditDepartment(new Department(deptId, "", 0, 0));
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "AddDepartment",
                  "Exiting AddDepartment");
    }

    /**
     * Method will commit the details of newly created department object
     * @return
     */
    public boolean AddDepartmentToDB() {
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "AddDepartmentToDB",
                  "Coming inside AddDepartmentToDB");
        boolean result = false;
        try {
            Connection conn = DBConnectionFactory.getConnection();
            conn.setAutoCommit(false);
            String insertSQL =
                "Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (?,?,?,?)";
            PreparedStatement pStmt = conn.prepareStatement(insertSQL);
            pStmt.setInt(1, editDepartment.getDeptId());
            pStmt.setString(2, editDepartment.getDeptName());
            pStmt.setInt(3, editDepartment.getMgrId());
            pStmt.setInt(4, editDepartment.getLocId());
            pStmt.execute();
            conn.commit();
            result = true;
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "AddDepartment",
                  "Exiting AddDepartmentToDB");
        return result;
    }

    /**
     * Method will call the AddDepartmentToDB
     * Call PropertyChangeSupport listener to push data changes to the UI
     */
    public void saveDepartment() {
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "saveDepartment",
                  "Coming inside saveDepartment");
        if (AddDepartmentToDB()) {
            Department newDepartment = new Department(editDepartment);
            s_departments.add(0, newDepartment);
            providerChangeSupport.fireProviderCreate("departments", newDepartment.getKey(), newDepartment);
        }
        Trace.log(Utility.ApplicationLogger, Level.INFO, DepartmentList.class, "saveDepartment",
                  "Exiting saveDepartment");
    }

    public void setEditDepartment(Department editDepartment) {
        this.editDepartment = editDepartment;
    }

    public Department getEditDepartment() {
        return editDepartment;
    }
}
Note:- Import Error might be showing in DepartmentList java, in viewController project properties->Dependencies add the ApplicationContoller.jpr

In ViewController project. Locate and expand the Application Sources folder, then expand the META-INF folder. You will see the adfmf-feature.xml file, click on the adfmf-feature.xml file to launch the Feature editor. Add a new feature by clicking the green plus sign on the Features table near top of the editor this will launch the new Create ADF Mobile Feature dialog, modify the values as shown below.


In the Features table, select the newly created feature Departments. Under the Features table, click the Content tab, and locate the Content table. Notice that the content item Departments.1 is created by default. Next add a new file by clicking the green plus sign and select taskflow option, this will launch the new Create ADF Mobile Task Flow dialog, modify the value as shown below.


Click on the DepartmentsTaskflow.xml to open the file in taskflow editor and follow the below steps.
1) Create views and name them as deptList and editDept respectively.
2) Draw the control flow case from deptList to editDept and Outcome as "add", Behavior->Transition as "flipRight".
3) Draw the control flow case from editDept to deptList and Outcome as "list", Behavior->Transition as "slideRight".

Double click on deptList view will launch Create ADF Mobile AMX Page dialog, in page facets select Header and Secondary Action. Open the deptList.amx page and go to page bindings, in Bindings section click on create control binding and select methodAction item. In create action binding wizard select DepartmentList->AddDepartment


Go to source tab and follow the below steps:
1) In Header facet, amx:outputText set the value as "Dept List"
2) In Seconday Action facet, for amx:commandButton modify the values
    text: Add, action: add, actionListener: #{bindings.AddDepartment.execute}
3) From DC palette drag and drop departments->ADF Mobile List View and select the default options

Double click on editDept view will launch Create ADF Mobile AMX Page dialog, in page facets select Header, Primary Action and Secondary Action. Open the deptList.amx page and go to page bindings, in Bindings section click on create control binding and select methodAction item. In create action binding wizard select DepartmentList->saveDepartment


Go to source tab and follow the below steps:
1) In Header facet, amx:outputText set the value as "Add Dept"
2) In Primary Action facet, for amx:commandButton modify the values
    text:Cancel, action:_back
3) In Seconday Action facet, for amx:commandButton modify the values
     text: Save, action: _back, actionListener: #{bindings.saveDepartment.execute}
4)  From DC palette drag and drop editDepartments->Form as ADF Mobile form, In Edit Form fields delete "key" value binding

Preview of the deptList.amx and editDept.amx will looks like below.


Now last configuration, in Application Resources palette expand Descriptor->ADF META-INF. You will see the adfmf-application.xml, click on the adfmf-application.xml file to launch the adfmf-application editor. In Application section for Lifecycle Event Listener set as "application.LifeCycleListenerImpl".

Make sure the below Libraries and Classpath present in the ViewController project.


In the Application menu, select Deploy - New Deployment Profile to start the Create Deployment Profile dialog box. In the Profile Type drop-down list, ensure ADF Mobile for Android/IOS is selected and then click OK.


In the ADF Mobile for Android Deployment Profile Properties dialog box, Select the Android Options node and confirm your values look like the ones below.


In the Application menu, select Deploy - New Deployment deployment profile. In the subsequent dialog box, select Deploy application to device/emulator/package, and click Finish.


Watch the Deployment Log window for any errors.

28 comments:

  1. I am facing this error.please help me to resolve it

    Deployment failed due to one or more errors returned by 'C:\Program Files\Android\android-sdk\platform-tools\adb'. The following is a summary of the returned error(s):
    Failure [INSTALL_FAILED_CONTAINER_ERROR]

    ReplyDelete
    Replies
    1. Hi Pradip,

      I never faced this issue, you can try recreating the AVD and provide more space for SD card. I will try to get the correct solution for this ASAP.

      - Deepak

      Delete
  2. Thanks Deepak,
    I have created new AVD and able to solve this problem.
    Thanks a lot!!!!!

    ReplyDelete
  3. Hi Deepak,

    I want to create one application which will access and store data through web service from remote location in oracle adf mobile.

    It would be better if any video tutorial or any resource is available to implement this demo.

    Thanks in advance!!!

    ReplyDelete
    Replies
    1. Pradip,

      Following link explains you how to access web service from remote location -https://blogs.oracle.com/mobile/entry/web_services_example_part_2.

      - Deepak

      Delete
    2. Thanks Deepak, I will go through this link.

      I have implemented your demo of department with sqlite database. It would be better if get any video link or more database connectivity examples as we are developing application for both local and remote database.

      Thanks.

      Delete
    3. Pradip,

      I din't find much sqlite database blogs. You can mail me to deepak.siddappa@gmail.com if you need any help.

      - Deepak

      Delete
    4. Thanks sir. I will be in touch and let you know if I face any difficulty.

      Once again thanks for your valuable support!!!!

      Delete
  4. Hi Deepak,

    In above example when I click on any of the Department name, I want to show details of department on another page. How I can do this?

    I have used set Property Listener to pass parameter from department list to next page but it's not working.

    Please give any idea....

    Thanks,
    Pradip

    ReplyDelete
    Replies
    1. Pradip,

      How are your storing the values in set Property Listener for attribute type "to". Use pageFlowScope variables then you can access the values in next page.

      Also please send me the application/code snippet, I will check.

      - Deepak

      Delete
    2. Deepak,

      I have used pageFlowScope variable but it's working only for title and not for other details on list below. I have send snippet to your mail, please check it.

      Delete
  5. To generate Release mode of your sample application,I followed the steps in the link(Shay Shmeltzer's Weblog) :
    https://blogs.oracle.com/shay/entry/speed_up_adf_mobile_deployment

    I used "C:\Oracle\Middleware\jdk160_24\bin" in command line to use the command of the above link but gives error as :
    Illegal option :
    Try keytool -Help

    will you help ?

    ReplyDelete
    Replies
    1. Hi Mozaharul,

      Don't not copy paste the command mentioned in Shay Shmeltzer's Weblog) :
      https://blogs.oracle.com/shay/entry/speed_up_adf_mobile_deployment

      Instead type the entire command in command line it will work. Even I faced this problem while generating keystroke.

      - Deepak

      Delete
  6. Hi,
    I got the solution. I can add new dept. to the application. Now where to find the generated database in the device (application directory)? I could not find it ! Please me the path.

    ReplyDelete
    Replies
    1. If you're talking about real device /data/data/ is unaccessible.

      Delete
  7. hi,
    I followed the steps as given above but I am getting error: SQLite database failed to load from /CachedGeoposition.db
    and no DEPARTMENTS table is created.

    ReplyDelete
  8. Hi
    I am getting an error while deploying the app to emulator as follows
    Deployment failed due to one or more errors returned by 'D:\Temp\ADF Mobile Install\adt-bundle-windows-x86_64-20130917\sdk\platform-tools\adb'. The following is a summary of the returned error(s):
    Failure [INSTALL_PARSE_FAILED_MANIFEST_MALFORMED]
    Did I missed anything ?

    ReplyDelete
  9. Hi
    I am new to adf mobile i follow the entire steps above records displaying properly but when saving the new record its getting "error save department"..

    ReplyDelete
  10. Hi
    I am unable to run your application.when I deployed and its opening.But when I testing its not able to show/sync data into sqlite.
    can you please help me on this.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Please can you look into the - http://deepakcs.blogspot.in/2014/03/adf-mobile-how-to-add-record-using.html.

      Here the implementation is little changed also, instead of reading the SQl file and populating we can directly copy the SQLite.DB file to AdfmfJavaUtilities.ApplicationDirectory

      Delete
  12. INTERNATIONAL CONCEPT OF WORK FROM HOME
    Work from home theory is fast gaining popularity because of the freedom and flexibility that comes with it. Since one is not bound by fixed working hours, they can schedule their work at the time when they feel most productive and convenient to them. Women & Men benefit a lot from this concept of work since they can balance their home and work perfectly. People mostly find that in this situation, their productivity is higher and stress levels lower. Those who like isolation and a tranquil work environment also tend to prefer this way of working. Today, with the kind of communication networks available, millions of people worldwide are considering this option.

    Women & Men who want to be independent but cannot afford to leave their responsibilities at home aside will benefit a lot from this concept of work. It makes it easier to maintain a healthy balance between home and work. The family doesn't get neglected and you can get your work done too. You can thus effectively juggle home responsibilities with your career. Working from home is definitely a viable option but it also needs a lot of hard work and discipline. You have to make a time schedule for yourself and stick to it. There will be a time frame of course for any job you take up and you have to fulfill that project within that time frame.

    There are many things that can be done working from home. A few of them is listed below that will give you a general idea about the benefits of this concept.

    Baby-sitting
    This is the most common and highly preferred job that Women & Men like doing. Since in today's competitive world both the parents have to work they need a secure place to leave behind their children who will take care of them and parents can also relax without being worried all the time. In this job you don't require any degree or qualifications. You only have to know how to take care of children. Parents are happy to pay handsome salary and you can also earn a lot without putting too much of an effort.

    Nursery
    For those who have a garden or an open space at your disposal and are also interested in gardening can go for this method of earning money. If given proper time and efforts nursery business can flourish very well and you will earn handsomely. But just as all jobs establishing it will be a bit difficult but the end results are outstanding.

    Freelance
    Freelance can be in different wings. Either you can be a freelance reporter or a freelance photographer. You can also do designing or be in the advertising field doing project on your own. Being independent and working independently will depend on your field of work and the availability of its worth in the market. If you like doing jewellery designing you can do that at home totally independently. You can also work on freelancing as a marketing executive working from home. Wanna know more, email us on workfromhome.otr214423@gmail.com and we will send you information on how you can actually work as a marketing freelancer.


    Internet related work
    This is a very vast field and here sky is the limit. All you need is a computer and Internet facility. Whatever field you are into work at home is perfect match in the software field. You can match your time according to your convenience and complete whatever projects you get. To learn more about how to work from home, contact us today on workfromhome.otr214423@gmail.comand our team will get you started on some excellent work from home projects.


    Diet food
    Since now a days Women & Men are more conscious of the food that they eat hence they prefer to have homemade low cal food and if you can start supplying low cal food to various offices then it will be a very good source of income and not too much of efforts. You can hire a few ladies who will help you out and this can be a good business.

    Thus think over this concept and go ahead.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi deepak I am new to ADF mobile .Mai i know how to connect to sqlite3 using jdeveloper is there any username ,password required????

    ReplyDelete
  15. Hi, I followed your tutorial and it works absolutely fine for .sql file, but am using the .sqlite created in sqlite browser. It does not work, I get a NullPointerException. I went through many tutorials and videos, but all show about the .sql file. Please guide me in using the .sqlite file.

    ReplyDelete
  16. Hi,i am unable to add any data binding..When i open data binding the pLus symbol is greyed out

    ReplyDelete