Android Database Library

An ORM library for Android that provides handy functionalities for CRUD operations of POJOs in an object-oriented way.

Features

  • POJO table/column/index mappings.
  • Object-oriented API for query construction.
  • Auto-increment, indexing and many other SQLite features.
  • Composite column indexing.
  • Complex query construction (e.g. DISTINCT, MIN/MAX, JOIN, GROUP BY, etc).
  • Automatic data type conversion.

Installation

Put android.lib.database-1.0.jar to your project's libs folder.

Quick Start

First we have to define a POJO class just like any other ORM libraries. For example, we have a map application that contains tables country and city.

Define POJO class

We create 2 classes for table mapping:

@Table
public class Country {
    @Column(autoIncrement = true, nullable = false, primaryKey = true)
    private int id;

    @Column(nullable = false)
    @Index(unique = true)
    private String name;

    @Column
    private String description;

    // Empty constructor for INSERT statements.
    public Country() {
    }

    // Set id through the constructor for SELECT statements.
    public Country(int id) {
        this.id = id;
    }

    // We define only the getter, not setter, for id because id is an auto-increment column,
    // we don't set its value for INSERT rows. For SELECT rows, we set it through the constructor.
    public int getId() {
        return id;
    }

    // Getters and setters for name and description
    ...
}

Likewise for city:

@Table
public class City {
    @Column(autoIncrement = true, nullable = false, primaryKey = true)
    private int id;

    @Column(nullable = false)
    private String name;
}

Here, @Table annotates a class is a POJO for table mapping by the library. By default the table name is the same as the class name. You can define custom table name, countries, like this:

@Table("countries")
public class Country {
    ...
}

@Column annotates the field-column mapping. In Country class, we have defined Country.id, Country.name and Country.description columns. There are 4 attributes supported by @Column:

  • value: value is the default attribute. When omitted, the mapped column name is the same as the field name. Otherwise, it will use the specified value as the column name.
  • autoIncrement: Set to true if it is an auto-increment column. Default is false.
  • nullable: Set to false if the column cannot be null. Default is true.
  • primaryKey: Set to true if it is the primary key column. Default is false.

@Index annotates table indexing. It has 2 attributes:

  • value: The default attribute. When omitted, the mapped index name is the same as the field name. Otherwise, it will use the specified value as the index name.
  • unique: Set to true if the indexed column is unique. Default is false.

@CompositeIndex annotates multiple fields indexing:

  • value: Defines the name of the index. All fields annotated with the same @CompositeIndex name will be grouped to form a composite index.
  • order: Defines the ordering of the column in the composite index.

@UniqueCompositeIndex: Same as @CompositeIndex, but this one is for unique composite indexing.

@UseConverter specifies non-standard SQLite data type conversion. For example, a java.util.Date field can be converted to a long before storing into the database and vice versa. This annotation specifies which TypeConverter to use for this purpose. You can create your custom converter by implementing TypeConverter interface. This library includes a DateConverter as an example.

Object mapping

This library includes a ready-to-use mapper, RowMapper<T>, for object mapping. We can extend this class like this:

public class CountryRowMapper extends RowMapper {
    ...
}

Typically we don't call its methods directly because this library handles all of these things for us. We will show you how later.

Bonus: JSON mapping

This library includes a JSON object mapper, JSONRowMapper<T>, to minimize the tedious work of writing wiring code. The only requirement is that the JSON object's field name has a 1-to-1 mapping to your column name.

Get a database connection

When using SQLite in Android, typically we use SQLiteOpenHelper to get a SQLiteDatabase instance. This library includes a DatabaseOpenHelper that does what a SQLiteOpenHelper does, and handles any necessary table creation tasks. However, in most cases we will be using the Database class which does all these stuff for us.

We can get a database connection like this:

Database database = Database.newInstance(context, Country.class, City.class);

The above line will open a SQLite database, or create one if it does not exist. If the database is new, it will also create the tables we specify, Country and City, define all the columns and create any indexes we annotated.

Query construction

You can build a simple (or complicated) SELECT statements like this:

// SELECT * FROM Country WHERE name = 'Spain' ORDER BY name
Query query = Query.select().columns().from(Country.class).where(Predicate.equalTo("name", "Spain")).orderBy("name").build();

List countries = database.execute(query, new CountryRowMapper());

For simple SELECT by ID statements, you can call database.selectById(Country.class, "id", 1) without using the Query object.

For INSERT statements, you can simply call database.insert(aCountry). Alternatively, you can construct a customized INSERT object by Query.insert() and call database.execute(Query) to execute raw SQL statements.

For DELETE by ID statements, you can call database.deleteById(Country.class, "id", 1). Or, similar to INSERT statements, you can construct a customized DELETE object by Query.delete().

Predicates

This library provides complex predicate construction, including:

  • =
  • <>
  • <
  • <=
  • >
  • >=
  • LIKE
  • IS NULL
  • IS NOT NULL
  • IN
  • BETWEEN

Transaction

Call database.beginTransaction() to declare the begining of a transaction. When you finish, call database.commit(). Or if it fails, call database.rollback() within the catch clause.

Transactions can be nested, just like a normal SQLite database you use in Android.

Typical use of transaction:

try {
    database.beginTransaction();

    // Perform INSERT/DELETE/etc that modifies the database.
    ...

    database.commit();
} catch (SQLException e) {
    database.rollback();
} finally {
    database.close();
}

Close the database

Just call database.close() to close your SQLite database. Uncommitted transaction, if any, will be rolled back.

Built With

Share this project:

Updates