Query definition

Kripton approach to persistence on SQLite is based on DAO pattern. In this pattern there are three important entities:

• Model object or Value Object: is the representation in Java domain of the table’s row of database domain. Sometimes it also called DTO: Data Transfer Object. In Kripton, a model object is a @BindType annotated POJO with a no-args constructor with a type long field named id or field marked as primary key. Every class of model is associated with a table and a DAO.
• DAO (Data Access Object) interface: is the contract used to access the database. In Kripton context, a DAO interface has to be decorated with @BindDao annotation and every method’s must have annotated with @BindSqlInsert, @BindSqlUpdate, @BindSqlSelect or @BindSqlDelete.
• DAO concrete class: the implementation of the DAO interface.

A developer has to define the model objects, DAOs interfaces and data source interface associated to a data source (SQLite database). Kripton will generate DAO concrete class. and data source implementation. The DAO implementation generated by Kripton has the same name of DAO interface with suffix Impl (example: StudentDAO -> StudentDAOImpl). Each model object is associated to a table with the same name converted in lower case with an underline format (example: RecipeIngredient -> recipe_ingredient). It is possible to change this behaviour with @BindTable. For example, suppose we have a model class named CollegeStudent defined as:

@BindType
@BindTable(name=“students”)
public class CollegeStudent {
  public String firstName;
  public String surname;
  public long id;
}

With annotation @BindTable, students will be used as table name associated with model object CollegeStudent. Kripton has support to foreign key and indices, but there are some constraints:

• every table must have a type long primary key. Its default name is id.
• foreign key between tables must belong type too. Student is the model object of our example database. The field id will become the primary key of the associated table. It is possible to use @BindColumn to customize this behaviour.

@BindDao(CollegeStudent.class)
public interface CollegeStudentDao {

  // retrieve all student from table student
  @BindSqlSelect
  ArrayList<CollegeStudent> getAllStudents();

  // select student by id
  @BindSqlSelect(where="id=${uid}")
  Student getStudent(int uid);

  // update students info by id
  @BindSqlUpdate(where=“id=${student.id}")
  void updateStudent(CollegeStudent student);

  // delete student row by id
  @BindSqlDelete(where=“id=${id}")
  void deleteStudent(long id);

  // insert student
  @BindSqlInsert(jql="INSERT INTO CollegeStudent (firstName) VALUES (${student.firstName})")
  void insertStudent(Student student);
}

@BindDataSource(fileName = "students.db", daoSet = {CollegeStudentDao.class})
public interface CollegeStudentsDataSource {
}

CollegeStudentDao interface is the DAO interface. @BindDao allows specifying the model object is associated to the DAO. Usually executed SQL on a database have four type of statement:

• SELECT: retrieve some rows from one or more table. Standard syntax is: SELECT <projected columns> FROM <table> WHERE <where condition>
• UPDATE: allow to update some rows. Standard syntax is: UPDATE <table> SET <field>=<value> WHERE <where condition>
• DELETE: delete some rows. Standard syntax is: DELETE FROM <table> WHERE <where condition>
• INSERT: allow to insert rows. INSERT INTO <table> (<fields>) VALUES (<values>)

For each SQL statement, Kripton has two modes to define associated SQL to a method:

• compact mode, that allows specifying only where condition (excluding INSERT SQL). An example:

// SELECT name, id FROM student WHERE id=${uid}
@BindSqlSelect(where="id=${uid}")
Student getStudent(int uid);

• extended mode, that allows specifying entire SQL. In which tables and columns are defined by associated java classes and fields. This is the reason because this particular SQL is called JQL (Java Query Language). Kripton will convert class and field names in associated table and column name.

//SQL: SELECT first_name, id FROM students WHERE id=${uid}
@BindSqlSelect(jql=" SELECT firstName, id FROM CollegeStudent WHERE id=${uid}")
CollegeStudent getStudent(int uid);

Kripton performs a syntax validation of JQL at compile time. A method’s parameter can be used like query’s parameter, using placeholder with the syntax ${<parameter name>}. For CollegeStudentDao interface, Kripton will generate the implementation named BindCollegStudentDao. This implementation contains generated documentation too. Just for clarification, the generated classes will be (implementation detail will be omitted):

/**
 * <p>
 * DAO implementation for entity <code>CollegeStudent</code>, based on interface <code>CollegeStudentDao</code>
 * </p>
 *
 * <p><strong>This class is generated by Kripton Annotation Processor (2.0.1)</strong></p>
 *
 *  @see CollegeStudent
 *  @see CollegeStudentDao
 *  @see com.abubusoft.kripton.examplea0.model.CollegeStudentTable
 */
public class CollegeStudentDaoImpl extends AbstractDao implements CollegeStudentDao {
  public CollegeStudentDaoImpl(BindCollegeStudentsDataSource dataSet) {
    super(dataSet);
  }

  /**
   * <h2>Select SQL:</h2>
   *
   * <pre>SELECT first_name, surname, id FROM students</pre>
   *
   * <h2>Projected columns:</h2>
   * <dl>
   * 	<dt>first_name</dt><dd>is associated to bean's property <strong>firstName</strong></dd>
   * 	<dt>surname</dt><dd>is associated to bean's property <strong>surname</strong></dd>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * </dl>
   *
   * @return collection of bean or empty collection.
   */
  @Override
  public List<CollegeStudent> getAllStudents() {
    // omitted for simplicity
    ..
  }

  /**
   * <h2>Select SQL:</h2>
   *
   * <pre>SELECT first_name, surname, id FROM students WHERE id=${uid}</pre>
   *
   * <h2>Projected columns:</h2>
   * <dl>
   * 	<dt>first_name</dt><dd>is associated to bean's property <strong>firstName</strong></dd>
   * 	<dt>surname</dt><dd>is associated to bean's property <strong>surname</strong></dd>
   * 	<dt>id</dt><dd>is associated to bean's property <strong>id</strong></dd>
   * </dl>
   *
   * <h2>Query's parameters:</h2>
   * <dl>
   * 	<dt>${uid}</dt><dd>is binded to method's parameter <strong>uid</strong></dd>
   * </dl>
   *
   * @param uid
   * 	is binded to <code>${uid}</code>
   * @return selected bean or <code>null</code>.
   */
  @Override
  public CollegeStudent getStudent(int uid) {
    // omitted for simplicity
    ..
  }

  /**
   * <h2>SQL update:</h2>
   * <pre>UPDATE students SET first_name=:firstName, surname=:surname WHERE id=${student.id}</pre>
   *
   * <h2>Updated columns:</h2>
   * <dl>
   * 	<dt>first_name</dt><dd>is mapped to <strong>${student.firstName}</strong></dd>
   * 	<dt>surname</dt><dd>is mapped to <strong>${student.surname}</strong></dd>
   * </dl>
   *
   * <h2>Parameters used in where conditions:</h2>
   * <dl>
   * 	<dt>${student.id}</dt><dd>is mapped to method's parameter <strong>student.id</strong></dd>
   * </dl>
   *
   * @param student
   * 	is used as ${student}
   */
  @Override
  public void update(CollegeStudent student) {
    // omitted for simplicity
    ..
  }

  /**
   * <h2>SQL delete:</h2>
   * <pre>DELETE FROM students WHERE id=${bean.id}</pre>
   *
   * <h2>Parameters used in where conditions:</h2>
   * <dl>
   * 	<dt>${bean.id}</dt><dd>is mapped to method's parameter <strong>bean.id</strong></dd>
   * </dl>
   *
   * @param bean
   * 	is used as ${bean}
   *
   * @return number of deleted records
   */
  @Override
  public int delete(CollegeStudent bean) {
    // omitted for simplicity
    ..
  }

  /**
   * <p>SQL insert:</p>
   * <pre>INSERT INTO students (first_name, surname) VALUES (${bean.firstName}, ${bean.surname})</pre>
   *
   * <p><code>bean.id</code> is automatically updated because it is the primary key</p>
   *
   * <p><strong>Inserted columns:</strong></p>
   * <dl>
   * 	<dt>first_name</dt><dd>is mapped to <strong>${bean.firstName}</strong></dd>
   * 	<dt>surname</dt><dd>is mapped to <strong>${bean.surname}</strong></dd>
   * </dl>
   *
   * @param bean
   * 	is mapped to parameter <strong>bean</strong>
   *
   * @return <strong>id</strong> of inserted record
   */
  @Override
  public int insert(CollegeStudent bean) {
    // omitted for simplicity
    ..
  }
}

As you can see, the code is well documented, contains null values management and allows SQL commands log to logcat. Last but not least, the data source interface: it represents the database. In @BindDataSource annotation can be specified the database attributes like file name, version and DAO set that will be used to generate the database. Moreover, it is possible to enable:

• log generation
• generation of async task class with database support
• generation of schema definition on a file
• generation of cursor helper classes.

The generate classes from CollegeStudentDataSource will be BindCollegeStudentDataSource. You can work with it manually opening database, do operation and closing it:

// get datasource
BindCollegeStudentsDataSource dataSource=BindCollegeStudentsDataSource.instance();

// open database
dataSource.openWritableDatabase();
// do operation with dao
CollegeStudentDaoImpl dao = dataSource.getCollegeStudentDao();
dao.insertStudent(new Student());
// close database
dataSource.close();

Or working in the managed transaction, that manages automatically the open/close operation on the database.

// get datasource
BindCollegeStudentsDataSource dataSource=BindCollegeStudentsDataSource.instance();
dataSource.execute(new Transaction() {
  @Override
  public void onError(Throwable e) { // manage error }

  @Override
  public boolean onExecute(BindCollegeStudentsDaoFactory daoFactory) throws Throwable {
    daoFactory.getCollegeStudentDao().insertStudent(new Student());
    // true means transaction commit
    return true;
  }
});

SELECT SQL

To bind a DAO interface method to a SELECT SQL statement is necessary to decorate it with @BindSqlSelect. When @BindSqlSelect is used there are many feature and options that can be useful to mention. We use the interface StudentDao as case study.

Select a List or a concrete implementation of a collection of model objects

Method returns a List or a concrete implementation of a collection of model objects. The collection can be an interface (like List or Set) or concrete class (like ArrayList or HashSet):

// returns result as List
// SQL: select * from students
@BindSqlSelect
List<CollegeStudent> getAllStudents();

// returns result as HashSet
// SQL: select * from students
@BindSqlSelect
HashSet<CollegeStudent> getAllStudentsAsSet();

Returns a paginated result

A query can return a paginated results.

@BindSqlSelect(pageSize = 20)
PaginatedResult<CollegeStudent> getAllStudentsPaginated();

// to get paginated result (database must be already opened)
PaginatedResult<CollegeStudent> res = daoFactory.getStudentDao().getAllStudentsPaginated();

// iterate over pages
while(res.hasNext()) {
  // retrieve partial list of result
  List<CollegeStudent> pageElements = res.list();
}

The page size can be defined statically with @BindSqlSelect pageSize attribute or dynamically with a method’s parameter decorated with @BindSqlPageSize.

@BindSqlSelect
PaginatedResult<CollegeStudent> getAllStudentsPaginated(@BindSqlPageSize int pageSize);

Select a single model object

A DAO’s method can be configured to return a single bean

// SQL: select * from students where id=${id}
@BindSqlSelect(where = “id=${id}”)
CollegeStudent getStudentById(long id);

Returns a cursor

In some case it may necessary to work with cursor. Kripton, obviously supports this case.

@BindSqlSelect
Cursor getAllStudentsAsCursor();

Manage result with a bean listener

Instead, to create a bean for each row, a bean listener allows to reuse same bean instance for all item list.

@BindSqlSelect
void getAllStudentWithBeanListener(OnReadBeanListener<CollegeStudent> listener);

to manage result:

collegeStudentDao.getAllStudentWithBeanListener(new OnReadBeanListener<Student>() {
  @Override
  public void onRead(Student bean, int row, int rowCount) {
    // invoked for each row
  }
});

Manage result with a cursor listener

To manage query result with a cursor iterator, just use an OnReadCursorListener parameter. When this kind of parameter is present, Kripton generates, inside the method, an iterator that launches listener’s method for each row.

@BindSqlSelect
void getAllStudentWithCursorListener(OnReadCursorListener listener);

To manage result:

collegeStudentDao.getAllStudentWithCursorListener(new OnReadCursorListener() {
  @Override
  public void onRead(Cursor cursor) {
    // invoked for each row 
  }
});

INSERT SQL

To bind a DAO interface method to an INSERT SQL statement is necessary to decorate it with @BindSqlInsert.

Insert a new row using a POJO

// insert in Student’s table a bean, bean’s id is automatically updated.
// SQL: INSERT INTO students (first_name, surname) VALUES (${bean.firstName}, ${bean.surname})
@BindSqlInsert
int insertBean(CollegeStudent bean);

The property bean.id will be automatically filled with new row’s id. The method returns the new id value.

Insert a new row defining only a field name

An example to show how to insert a row in students table specifying only field firstName:

// SQL: INSERT INTO students (first_name) VALUES (${firstName})
@BindSqlInsert
int insertRaw(String firstName);

The method returns the new id value. The method’s parameter has the same name of CollegeStudent, so it automatically binded to it.

Insert a new row specifying entire JQL

An example to show how to insert in students table specifying entire JQL (SQL use class and field names):

// will converts in SQL (that works with tables and columns).
@BindSqlInsert(jql="INSERT INTO students (first_name) VALUES (${student.firstName})")
void insertStudentJQL(CollegeStudent student);

Insert a row using INSERT-SELECT

// INSERT OR REPLACE INTO student SELECT * FROM student WHERE name=${name}
@BindSqlInsert(jql="INSERT OR REPLACE INTO student SELECT * FROM student WHERE name=${name}")
void insertStudentA(String name);

The method use an INSERT-SELECT SQL to insert rows. This type of query must have void return type, otherwise an exception will be throws during compile time.

UPDATE SQL

To bind a DAO interface method to an UPDATE SQL statement is necessary to decorate it with @BindSqlUpdate.

Update a row using a POJO

// update student’s table row with id=bean.id.
// SQL: UPDATE students SET first_name=:firstName WHERE id=${bean.id}
@BindSqlUpdate(where="id=${bean.id}")
int update(CollegeStudent bean);

Method returns the number of affected rows.

Update a row updating only a column
// update table row specifing only field name where id=${id}
// SQL: UPDATE OR ABORT students SET first_name=:firstName WHERE id=${id}
@BindSqlUpdate(conflictAlgorithm = ConflictAlgorithmType.ABORT ,where="id=${id}")
int update(String firstName, long id);

Parameter name is used to update column name, parameter id is used in where condition. Method returns number of affected rows.

Update rows specifying the JQL

// update using JQL
@BindSqlUpdate(jql="UPDATE OR ABORT CollegeStudent SET firstName=${firstName}, surname=${surname} WHERE id=${id}")
int update(@BindSqlParam("firstName") String value0, @BindSqlParam("surname") String value1, long id);

The method updates a row where id=${id}. Method’s parameters value0 and value1 are used to update columns name and surname. The method returns the number of affected rows.

DELETE SQL

To bind a DAO interface method to a DELETE SQL statement is necessary to decorate it with @BindSqlDelete.

Delete a row using a POJO
// delete CollegeStudent’s table row with id=bean.id.
// SQL: UPDATE students SET first_name=:firstName WHERE id=${bean.id}
@BindSqlDelete(where="id=${bean.id}")
int delete(CollegeStudent bean);

Method returns the number of affected rows.

Delete a row using only a field value

// delete table row where id=${id}
// SQL: DELETE FROM student WHERE id=${id}
@BindSqlUpdate(where="id=${id}")
int delete(long id);

Parameter id is used in where condition. Method returns the number of affected rows.

Delete rows specifying entire JQL

// specify entire JQL
@BindSqlDelete(jql="DELETE FROM CollegeStudent WHERE id=${id}")
int delete(long id);

The method deletes a row where id=${id}. Method returns number of affected rows.

Query parameters

All JQL commands ca use method’s parameters as SQL parameters. Method’s parameter used as SQL parameter can be one of the supported Kripton type (like long or String) or model object’s type.

// select a list of student with compact JQL query
@BindSqlSelect(where=”where id=${bean.id}”)
List<CollegeStudent> getStudents(CollegeStudent bean);

// select a list of students with extended JQL
@BindSqlSelect(jql=”select * from CollegeStudent where firstName like ${firstName} || ‘%’”)
List<CollegeStudent> getStudents(String name);

In UPDATE statements, parameters passed to the SQL can be column value to update or parameters used in where conditions. Kripton can easily understand how to use them.

// param id will be used in where condition. Param Name will be used 
// value for updated column ‘name’. The resulting query, as you can 
// see in generated javadoc will be:
// UPDATE students SET name=:name WHERE id=${id}
@BindSqlUpdate(where="id=${id}")
long update(String name, long id);

Dynamic parts of queries

In compact query, it is also possible to include dynamic parts of SQL. Unlike SQL defined as JQL in Kripton annotations (@BindSqlSelect, etc), dynamic SQL can not obviously checked at compile time. It is possible to add dynamic where and order (only for select statement). Some examples:

// equivalent SQL: 
// SELECT id, first_name FROM students WHERE id=${id} AND #{DYNAMIC_WHERE} ORDER BY first_name,  #{DYNAMIC_ORDER_BY}
@BindSqlSelect(where="id=${id}", orderBy = "firstName")
List<CollegeStudent> selectStudentsDynamic(long id, @BindSqlDynamicWhere String dynamicWhere, @BindSqlDynamicOrderBy String orderBy);

// equivalent SQL: 
// SELECT id, first_name FROM students WHERE #{DYNAMIC_WHERE} ORDER BY #{DYNAMIC_ORDER_BY}
@BindSqlSelect(where="id=${id}", orderBy = "firstName")
List<CollegeStudent> selectStudentsDynamic2(long id, @BindSqlDynamicWhere String dynamicWhere, @BindSqlDynamicWhereParams String[] dynParams, @BindSqlDynamicOrderBy String orderBy);

Dynamic where and order is nullable. The null-case is managed by removing useless pieces of SQL. It is also possible to define dynamic SQL parameters simply decorating a method’s parameter with @BindSqlDynamicWhereParams. @BindSqlDynamicWhere and @BindSqlDynamicWhereParams are compatible with all SQL type except INSERT. @BindSqlDynamicOrderBy is only compatible with SELECT type. In compact SELECT, it is also possible to specify order statement too with attribute @BindSqlSelect.orderBy or with a method’s parameter decorated with @BindDynamicOrderBy.

// use attribute orderBy of annotation @BindSqlSelect
@BindSqlSelect(where = “id=${id}”, orderBy=”firstName”)
CollegeStudent getStudentWithOrder(long id);

// use method’s parameter
@BindSqlSelect(where = “id=${id}”)
CollegeStudent getStudentWithDynamic(long id, @BindSqlDynamicOrderBy String order);