Accessing the SQLite Database

AMPA provides you with a convenient and comprehensive API to access the on-device SQLite database within your mobile application. Using this API, the database connection management is handled for you, and there is no need to write low-level JDBC statements.
As explained in the section Understanding the AMPA Runtime Persistence Architecture, all interaction with the local database is delegated to the DBPersistenceManager class. So, all the methods you need to access the database are present in this class.

To get an instance of the DBPersistenceManager class, you can simply use the default constructor:

DBPersistenceManager pm = new DBPersistencemanager();

In the context of an entity CRUD service class, you can also call getLocalPersistenceManager(), this will return an instance of DBPersistenceManager, or a subclass if you registered a custom local persistence manager in the Runtime Options panel of the AMPA wizards.

You can access the database in two ways:

• Using the entity-based API
• Using SQL

##Using the Entity-Based API With the entity based API you can use the following methods to retrieve an entity or a list of entities: ###findAll This overloaded method takes a class or class name as argument and returns a list of all entities:

List<Department> departments = pm.findAll(Department.class);

###find This overloaded method returns a filtered list of entities. There are various ways to specify your filter conditions. We will explain them using some code samples:

List<Employee> emps = pm.find(Employee.class,"king");

This is a "quick search" method, it will return all employees where at least one of the String attributes (firstName, lastName, emailAddress, etc) starts with "king" (case insensitive). This translates to use of the SQL LIKE operator where the search value is suffixed with %.

List<String> searchAttrs = new ArrayList<String>();
searchAttrs.add("firstName");
searchAttrs.add("lastName");
List<Employee> emps = pm.find(Employee.class,"king",searchAttrs);

This is also a "quick search" method which allows you to specify the search attributes, it will return all employees where at least one of the attributes passed in as list in the 3rd argument starts with "king" (case insensitive).

Map<String,String> searchAttrs = new HashMap<String,String>();
searchAttrs.put("firstName","Steven");
searchAttrs.put("lastName","King");
List<Employee> emps = pm.find(Employee.class,searchAttrs);

This is a search method which allows you to specify separate values for each attribute you want to search on, it will return all employees where the firstName equals "Steven" and the lastName equals "King". The query is case sensitive.

###findByKey This method returns one entity based on its primary key:

Department dep = (Department) pm.findByKey(Department.class, new Object[]{10});

This will first check the entity cache, and the department with id 10 does not exist in the cache, it will query the database. If you do not want to check the cache, you add a third boolean argument checkEntityCache:

Department dep = (Department) pm.findByKey(Department.class, new Object[]{10}, false);

###insertEntity Use this method to insert a row for a new entity instance:

pm.insertEntity(department, true);

If the second doCommit argument is true, the row will be auto-committed. If you set it to false, you need to call pm.commit() later on. If the primary key value of the entity is not yet set and in the Runtime Options panel you have checked the Generate Primary Key checkbox, then AMPA will set the primary key attribute for you.

###updateEntity Use this method to update a row that corresponds to an existing entity instance:

pm.updateEntity(department, true);

If the second doCommit argument is true, the row will be auto-committed. If you set it to false, you need to call pm.commit() later on.

###removeEntity Use this method to delete a row that corresponds to an existing entity instance:

pm.removeEntity(department, true);

If the second doCommit argument is true, the row will be auto-committed. If you set it to false, you need to call pm.commit() later on.

###deleteAllRows Use this method to delete all rows in the table that maps to the entity class passed in:

pm.deleteAllRows(Department.class);

##Using SQL

If the above entity-based API does not provide an option to execute the SQL statement you need, you can specify custom SQL statements. We distinguish between SQL SELECT and SQL DML statements

###Using SQL SELECT Statements If the result of your SELECT statement needs to be converted to an entity or entity list, you should use code like this:

DBPersistenceManager pm = new DBPersistenceManager();
ClassMappingDescriptor descriptor = ClassMappingDescriptor.getInstance(Dealer.class);
StringBuffer sql = pm.getSqlSelectFromPart(descriptor);
sql.append(" WHERE SALES_ACCOUNT_ID not in (SELECT DEALER_ID FROM PRIORITY_ASSIGNMENT WHERE PRIORITY_ID=" +
             priority.getId() + ")");
sql = pm.constructOrderByClause(sql, descriptor);
ResultSet set = pm.executeSqlSelect(sql.toString(), null);
List<Dealer> dealerList = pm.createEntitiesFromResultSet(set, descriptor.getAttributeMappingsDirect());

Since the result must be converted to an entity list, the SELECT clause should include all the columns, and the FROM clause should use the table name that corresponds to the entity. By using getSqlSelectFromPart convenience method, the SELECT and FROM clause will be automatically created for you using the entity class descriptor from persistence-mapping.xml. You can then append your custom WHERE clause to the SQL statement. You can also append your custom ORDER BY clause, or, if you just want to use the default order by as registered with the entity class descriptor you can use the convenience method constructOrderByClause as in the example above. Once you have constructed your SQL SELECT statement, you can execute it using method executeSqlSelect which returns a JDBC RowSet object. You then convert the JDBC RowSet to an entity list using method createEntitiesFromResultSet.

For all SQL SELECT results that cannot be converted to an entity list, you can define the whole SQL statement, and also process the JDBC RowSet. Here is an example of an aggregate query:

DBPersistenceManager pm = new DBPersistenceManager();
String sql = "SELECT AVG(SALARY) FROM EMPLOYEE";
ResultSet set = pm.executeSqlSelect(sql, null);
try {
  set.first();
  int averageSalary = set.getInt(1);
} 
catch (SQLException e) {
  sLog.severe("Error executing SQL statement: "+e.getLocalizedMessage());
}

###Using SQL DML Statements For single-row insert, update or delete you will typically use the entity-based API as described above. However, if you want to insert, update or delete multiple rows at once, you can use the executeSqlDml method. Here is an example where we increase the salary of all clerks with 10%:

DBPersistenceManager pm = new DBPersistenceManager();
String sql = "UPDATE EMPLOYEE SET SALARY = SALARY * 1.1 WHERE JOB_ID='CLERK'";
pm.executeSqlDml(sql, null, true);

If the third doCommit argument is true, the batch update will be auto-committed. If you set it to false, you need to call pm.commit() later on.