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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
public class DatabaseHelper extends MSQLiteOpenHelper { private static final int DB_VERSION = 1; private static final String DB_NAME = "notemap.db"; public DatabaseHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { createTable(db, Note.class, true); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { dropTable(db, Note.class, true); onCreate(db); } } |
The following is the Note class from the above example:
|
1 2 3 4 5 6 7 8 9 10 |
public class Note { @PrimaryKey Integer id; @NotNull String noteTitle; String noteDestription; double latitude, longitude; String noteSubTitle; } |
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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `Note` ( `id` INTEGER NULL, `noteTitle` TEXT NOT NULL, `noteSubTitle` TEXT NULL, `noteDestription` TEXT NULL, `longitude` REAL NULL, `latitude` REAL NULL, `pinId` INTEGER NULL, PRIMARY KEY (`id`) ); |
Inserting instance of Note into database can be easily done by calling static insert() method:
|
1 2 3 |
SQLiteDatabase database = databaseHelper.getWritableDatabase(); DatabaseHelper.insert(database, note); database.close(); |
or using the helper insert() method on an instance of DatabaseHelper, which gets database instance for you, and closes it afterwards:
|
1 |
databaseHelper.insert(note); |
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:
|
1 2 3 |
List<Note> importList = new ArrayList<Note>(); // populate list ... databaseHelper.insert(Note.class, importList); |
Sure enough, selecting data is just as easy. The selectAll() method returns a List of all objects of specified type:
|
1 |
List<Note> notes = databaseHelper.selectAll(Note.class); |
If you need more control over which data you need, you can use the main select() method, which wraps SQLiteDatabase.select() method:
|
1 |
public static <T> List<T> select(SQLiteDatabase database, Class<T> type, String selection, String [] selectionArgs, String orderBy, String limit) throws InstantiationException |
Obviously there are also methods for deleting, updating and even replacing items in database:
|
1 2 3 |
public int delete(Object item); public int update(Object object); public long replace(Object item); |
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.