Android SQLite annotation

• Download AndroidSQLiteORM.zip - 114 KB

Introduction 

With Android, SQLite is the way to make database to storage data. With original way, we must write SQL query string in almost case. It is ok, of course. However, Do you think about the efforts you must spend for coding, fix bug and maintain? I'm sure that, the effort to do it with original way is not small.

Do you know the way of Hibernate framework? I will resolve problem with object-relational mapping (ORM), the same with Hibernate.  

Analytics to compare 

Suppose that, we must create database with table User which contains:

• id: auto increasement key.
• user_name: name of user.

With original way, to create User table, we must override SQLiteOpenHelper and below code is implemented:

public class MySQLiteHelper extends SQLiteOpenHelper {
    public static final String TABLE_USER = "user_tbl";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_USER_NAME = "user_name";
    private static final String DATABASE_NAME = "my_db.db";
    private static final int DATABASE_VERSION = 1;
    // Database creation sql statement
    private static final String DATABASE_CREATE = "create table " + TABLE_USER
            + "(" + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_USER_NAME + " text not null);";
    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    } 
}  

And if you want to insert a User, we must code the same as below:

public long insertUser(User user) {
    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_USER_NAME, user.userName);
    long insertId = database
            .insert(MySQLiteHelper.TABLE_USER, null, values);
    return insertId;
} 

With above way, we can write code to do any requirement: get, delete, update...Everything seems ok!

But, what happens if you must create 20 tables, each table has 20 fields

• To create 20 tables, you must write 20 sqls.
• To insert a Model object to database, you must write code many code to insert, get,....
• The code structure will be difficult to control.

With my experience, all developers always has a lot difficulty to control code like that! And after finishing all for a project, it will have nothing to keep. If there is new requirement, we must re-code for all.

How to resolve it with the same way with Hibernate?  Please refer this link. I think this is a good tutorial for Java annotation. 

SQLITE ORM   

* ORM: object-relational mapping 

Base on the knowleage of Java annotation, define Table and Column likes that

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
    /** Table name. */
    String name();
} 
 
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
    boolean isPrimaryKey() default false;
    boolean isAutoincrement() default false;
    /** Column type. */
    String type() default "TEXT";
    /** Column name. */
    String name();
} 

And define a UserItem as below:

@Table(name = "user_tbl")
public class UserItem {
    @Column(name = "userName", type = "TEXT")
    public String userName;
 
    public UserItem() {
    }
} 

Above class is the mapping file, it means:

• UserItem is mapped with user_table.
• Property userName is mapped with userName column of user_table.

To get the table name of Model which has annotation Table in class definition, we have function:

public static String getTableName(Class<?> tClass) {
    Table annotationTable = tClass.getAnnotation(Table.class);
    String table = tClass.getSimpleName();
    if (annotationTable != null) {
        if (!annotationTable.name().equals("")) {
            table = annotationTable.name();
        }
    }
    return table;
}

And to get the column list of Model which has annotation Column in its properties, we have function:

public String[] getColumns() {
    boolean isHaveAnyKey = false;
    List<String> columnsList = new ArrayList<String>();
    for (Field field : tClass.getDeclaredFields()) {
        Column fieldEntityAnnotation = field.getAnnotation(Column.class);
        if (fieldEntityAnnotation != null) {
            String columnName = getColumnName(field);
            if (columnName != null)
                columnsList.add(columnName);
            if (fieldEntityAnnotation.isPrimaryKey()) {
                isHaveAnyKey = true;
            }
        }
    }
    if (!isHaveAnyKey) {
        columnsList.add("_id");
    }
    String[] columnsArray = new String[columnsList.size()];
    return columnsList.toArray(columnsArray);
}

Base on 2 util methods above, it is easy to make the function to create table by Android sqlite:

public void createTable(SQLiteDatabase db) {
if (getTableName(tClass) == null) {
        return;
    }
    StringBuffer sql = new StringBuffer("CREATE TABLE ");
    sql.append(getTableName(tClass));
    sql.append(" (_id INTEGER PRIMARY KEY AUTOINCREMENT");
    for (Field field : tClass.getDeclaredFields()) {
        Column fieldEntityAnnotation = field.getAnnotation(Column.class);
        if (fieldEntityAnnotation != null) {
            sql.append(", ");
            sql.append(fieldEntityAnnotation.name());
            sql.append(" ");
            sql.append(fieldEntityAnnotation.type());
        }
    }
    sql.append(");");
    db.execSQL(sql.toString());
} 

What is the advantage when using above code? Your working is mapping Model file only, and beside that, it is nothing.

How to write insert method?

To fill value of Model to a ContentValues, we have

private ContentValues getFilledContentValues(Object object)
        throws IllegalAccessException {
    ContentValues contentValues = new ContentValues();
    for (Field field : object.getClass().getDeclaredFields()) {
        Column fieldEntityAnnotation = field.getAnnotation(Column.class);
        if (fieldEntityAnnotation != null) {
            if (!fieldEntityAnnotation.isAutoincrement()) {
                putInContentValues(contentValues, field, object);
            }
        }
    }
    return contentValues;
} 
public void putInContentValues(ContentValues contentValues, Field field,
        Object object) throws IllegalAccessException {
    if (!field.isAccessible())
        field.setAccessible(true); // for private variables
    Object fieldValue = field.get(object);
    String key = getColumnName(field);
    if (fieldValue instanceof Long) {
        contentValues.put(key, Long.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof String) {
        contentValues.put(key, fieldValue.toString());
    } else if (fieldValue instanceof Integer) {
        contentValues.put(key, Integer.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof Float) {
        contentValues.put(key, Float.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof Byte) {
        contentValues.put(key, Byte.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof Short) {
        contentValues.put(key, Short.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof Boolean) {
        contentValues.put(key, Boolean.parseBoolean(fieldValue.toString()));
    } else if (fieldValue instanceof Double) {
        contentValues.put(key, Double.valueOf(fieldValue.toString()));
    } else if (fieldValue instanceof Byte[] || fieldValue instanceof byte[]) {
        try {
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            ObjectOutputStream objectOutputStream = new ObjectOutputStream(
                    outputStream);
            objectOutputStream.writeObject(fieldValue);
            contentValues.put(key, outputStream.toByteArray());
            objectOutputStream.flush();
            objectOutputStream.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
        }
    }
}  

And insert method will be implemented

public long insert(T item) {
    if (item != null) {
        try {
            ContentValues value = getFilledContentValues(item);
            long id = mDB.insert(getTableName(tClass), null, value);
            return id;
        } catch (IllegalAccessException e) {
        }
    }
    return -1;
} 

mDB: the SQLiteDatabase object. What is the advantage of this inserting way? You need to unit test base method only, and after that, there is nothing to do.

Summary

With above sample, you can see Java Annotation will help us to coding SQLite following ORM. About the detail, you can refer to Source code I attached in article. I put here the class diagram for easy in understanding.

History 

• 2013-08-29: First created.