MSQLite library

MSQLite is my first library for Android. It’s a wrapper for Android’s SQLiteOpenHelper that extends it by adding methods for easier data manipulation.

Source code is available on GitHub: https://github.com/mick88/MSQLite

The aim of this project is to make it easier for beginner developers on android to use database, but at the same time give them direct control on how data is stored. By default table and column take over names from Class/field, but table and field names can be customized by using @TableName() or @ColumnName() annotation. This is very useful if you are upgrading your current database solution to MSQLite, and your database fields differ from class field names. Another useful feature is excluding select fields from database by adding transient keyword to them.

It uses generics and reflection features of java languages to interpret classes as database tables. This way users can easily insert data to database without worrying about SQL syntax. It also uses annotations for adding metadata to database tables/columns, like Primary Keys, names or data types.

The main class MSQLiteOpenHelper extends SQLiteOpenHelper, so upgrading your database helper with new methods is as easy as changing which class you extend. This gives you access to a number of useful methods

Currently supported field types are:

  • Primitives: int, double, float, long, short, char, byte
  • Wrappers for primitives: Integer, Double, Float etc…
  • String
  • Serializable

 Examples:

Database helper can now look as clean as this:

 

The following is the Note class from the above example:

This is an example of plain Java class that can be converted into database table using createTable() method. There is no need to extend any class or implement any interface. The @PrimaryKey annotation points to field that will be used as a primary key in the table. It is not required, and since SQLite supports multiple PrimaryKeys, multiple fieds can be defined as such. Similarily @NotNull specifies that field may not be null.

The createTable() method effectively creates and executes the following SQL statement:

 

Inserting instance of Note into database can be easily done by calling static insert() method:

or using the helper insert() method on an instance of DatabaseHelper, which gets database instance for you, and closes it afterwards:

TIP: If your PrimaryKey is an integer and is null, after insertion it will take value of row id after insertion.

Inserting a batch of objects is just as simple, you just have to specify Type of the objects in the List:

Sure enough, selecting data is just as easy. The selectAll() method returns a List of all objects of specified type:

If you need more control over which data you need, you can use the main select() method, which wraps SQLiteDatabase.select() method:

Obviously there are also methods for deleting, updating and even replacing items in database:

 Downsides?

  • Currently MSQLite doesn’t support relational databases, so non-supported fields must be declared as transient or implement Serializable.
  • Performance of this solution hasn’t been measured, but it’s expected to execute slower than code tailored for your specific needs.

Comments are closed.