Persistence on SQLite database

As you know, SQLite is the database present in Android SDK. In gradle config add:

In the class used as application (in manifest file is the value of attribute android:name of  application tag) it is necessary to initialise the Kripton library.

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 to a table and a DAO.
  • DAO (Data Access Object) interface: is the contract used to access 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.

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 same name converted in lower case with 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:

With annotation @BindTable, “students” will be used as table name associated to 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 be long type too.

Student is the model object of our example database. The field id will become primary key of associated table. It is possible use @BindColumn to customise this behaviour.

 

CollegeStudentDao interface is the DAO interface. @BindDao allow to specify with model object is associated to the DAO.

Usually executed SQL on a database have 4 type of statement:

  • SELECT: retrieve some rows from one or more table. Standard syntax is:

  • UPDATE: allow to update some rows. Standard syntax is:

  • DELETE: delete some rows. Standard syntax is:

  • INSERT: allow to insert rows.

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

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

  • extended mode, that allow to specify 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.

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):

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 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:

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

 

Supported attribute types on SQLite

Kripton’s annotation processor for SQLite supports a wide range of attribute type:

  • String
  • Enum
  • byte, short, long, int, float, double and associated wrapped types
  • Date, Calendar, Locale, Time, TimeZone and URL
  • arrays, byte arrays
  • every bean annotated with @BindType
  • List, Set and Map collections of types mentioned above.

Every field will be converted in a column. Fields with a complex type like collection or arrays will be stored with theirs JSON representation in a BLOB column.

 

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 a paginated result

A query can return a paginated results.

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

Select a single model object

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

Returns a cursor

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

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.

to manage result:

 

Manage result with a cursor listener

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

To manage result:

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

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’

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):

Insert a row using INSERT-SELECT

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

Method returns number of affected rows.

Update a row updating only a column

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

The method updates a row where id=${id}. Method’s parameters value0 and value1 are used to update columns name and surname. Method returns 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

Method returns number of affected rows.

Delete a row using only a field value

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

Delete rows specifying entire JQL

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.

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.

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:

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.

 

Manage data source generation

For database management, the generation of boilerplate-code is controlled by its interface annotated with @BindDataSource annotation. Just an example to show other features:

Let’s talk about attributes:

  • daoSet: allow to define which DAO will compose database. Remember that each DAO is associated to a model class.
  • filename: is the database’s filename. It’s mandatory.
  • version: default value is 1
  • generateLog: allow to generate in DAOs implementation log associated to SQL operation, in this way it is possible to view directly SQL executed.

  • generateAsyncTask: if true, this flag allows to generate an async task that contains boilerplate code to automatically manage database transaction during invocation of its execute method. For a datasource named ‘StudentsDataSource’ the asynctask generated will be named ‘BindStudentsAsyncTask’. Given a datasource name, the asynctask naming rule is: preappend ‘Bind’ and replace ‘DataSource’ with ‘AsyncTask’. Unlike standard async task, for an instance of this class can be reused. An usage example:

SQLite can handle multithread operation with one connection for write operations and many connection for read operations. For this reason, the generated async task is used will open for default a readonly-mode connection (but it is possible to chang). Write operations are typically managed in separate service.

  • generateSchema: in real life application, it is important to version schema DDL and have the capability to trace schema changes. If this attribute is true, in folder <project root>/schemas will stored a file name <database-filename_no_extension>_schema_<version>.sql contains schema definition. For student database version 1, the content of this file will be:

Usually the order used to define table is important in a schema definition. Kripton analyse table dependencies (based on foreign keys) and reorder tables, if it needed to respect table dependencies. One important thing to know is that in Kripton re-order table definition to respect its dependencies.

Indices

A SQLite database can have simple indexes, composed indexes and unique indexes. Kripton supports all type of indices.

A complete model example

Suppose our application need a model objects that represents recipes. In version 1 of the database we have a unique model value named Recipe (and so unique DAO).

 

 

The generated schema will be :

In version 2 of our application we realise that we need a more complex model so we introduce author of recipes and put ingredients in a separate table. We need 3 model objects and 3 DAOs. Because we are lazy, we create a base DAO and a base entity used to contains all methods and attributes shared between DAOs and model values, just to avoid to write duplicate definition in DAOs and entities.

the generated schema is:

 

Generation of content provider

Another feature of Kripton is the capability to generate SQLite database based content provider. Content provider generation is based on the use of three annotation: @BindContentProvider: used on same interface that define datasource

@BindContentPath: used on DAOs to be exposed by content provider

@BindContentEntry: used on DAO’s methods to be exposed by content provider.

Note that not all type of SQL can be used on content provider. If invalid method is annotated with @BindContentEntry an error will occur during compile time.

To show how to generate them just we resume previous example of recipes model and create version 3 to support content provider generation. The changes are on dataSource and DAOs interfaces. For brevity we report only these interfaces.

The generated content provider is called BindRecipesV3ContentProvider. To enable it in the application, just declare it on manifest file:

Like other generated classes, content provider is fully documented and it’s possible to access to javadoc and generate code to view what is generated. What follows is the javadoc associated to generated content provider.

Kripton for the moment, does not help in content provider’s client generation.

Select/Update/Insert operation done with DAO specify exactly which fields are projected or updated or inserted. In content provider every query can used specifying a subset of column too. Kripton support this, using the specified projected column in DAO method as max allowed set of fields.

Select operation exposed by content provider contains in its signature a dynamic where and parameters. Kripton allow the use of these dynamic parts only if in DAO method exists parameters annotated with @BindSqlDynamicWhere and @BindSqlDynamicWhereParams.

 

Database schema updates

The database schema update is not a trivial task. Kripton try to help developer in this task by providing helper classes. In the Recipe example shown above, the schema version 1 and version 2 are quite different. To allow the schema update is necessary to provide a SQL instruction to migrate schema (with step 1) and eventually preserve data. In the Application.onCreate:

 

It is possible to define an update task directly by code or in a file.

 

Test database schema updates

Kripton contains also some utility helper to test database schema update directly on device or on junit test (with Roboletric integration). Just for example,given two version of same schema.

 

Available annotations in short

A summary table for available annotations

Annotation Applied to Description
@BindTable Class Allows to customize table generation associated to class.
@BindColumn Field Allows to customize field persistence as table’s column.
@BindDao Interface Define a DAO interface. Given an interface <dummy>, it allow to generate a DAO implementation <dummy>Impl.
@BindDataSource Interface Define a data source associated to a SQLite database. Given an interface <dummy>DataSource, it will generate:

–          for each model class managed by datasource it will generate a class <bean’s name>Table

–          generate an AsyncTask named <dummy>AsyncTask (OPTIONAL)

–          generate <dummy>DaoFactory interface that expose DAO set of data source

–          generate cursor wrapper classes for DAO’s method which return Cursors (OPTIONAL)

–          generate Bind<dummy>DataSource, data source implementation class.

 

@BindSqlInsert Method Binds a DAO interface’s method to an INSERT SQL statement
@BindSqlSelect Method Binds a DAO interface’s method to a SELECT SQL statement
@BindSqlUpdate Method Binds a DAO interface’s method to an UPDATE SQL statement
@BindSqlDelete Method Binds a DAO interface’s method to a DELETE SQL statement
@BindContentProvider Interface Used to generated a content provider starting from data source declaration. Given an interface <dummy>DataSource, it will generate <dummy>ContentProvider class.
@BindContentProviderPath Interface Allow to specify path in a content provider path for a DAO
@BindContentProviderEntry Method Allows to use a specific DAO method in the generated content provider implementation
@BindSqlParam Method’s parameter Allows to customize how parameter is named in SQL
@BindSqlDynamicOrderBy Method’s parameter Allows to use a DAO’s method parameter as dynamic part of order by clauses in SQL associated to method
@BindSqlPageSize Method’s parameter Marks a parameter as pageSize for SQL results
@BindSqlDynamicWhere Method’s parameter Allows to use a DAO’s method parameter as dynamic part of where clauses in SQL associated to method
@BindSqlDynamicWhereParams Method’s parameter Allow to use a DAO’s method parameter as dynamic parameters of where clauses in SQL associated to method

 

 

%d bloggers like this:

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close