Android database = Sqlite and OrmLite

Overview

Object Relational Mapping Lite (ORM Lite) provides some simple, lightweight functionality for persisting Java objects to SQL databases while avoiding the complexity and overhead of more standard ORM packages.
You can research OrmLite in this link.

How to use

  • Adding OrmLite to project
    compile 'com.j256.ormlite:ormlite-core:4.48'
    compile 'com.j256.ormlite:ormlite-android:4.48'
  • DatabaseHelper for creating and modifying tables of database
public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
    private static final String TAG = DatabaseHelper.class.getSimpleName();

    private Dao<News, Integer> newsDao = null;

    public DatabaseHelper(Context context) {
        super(context, DatabaseConfig.DATABASE_NAME, null, DatabaseConfig.DATABASE_VERSION);
    }

    @Override
    public void close() {
        super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase database, ConnectionSource connectionSource) {
        try {
            Log.i(TAG, "create database");
            TableUtils.createTable(connectionSource, News.class);
        } catch (SQLException e) {
            Log.e(TAG, "onCreate", e);
            throw new RuntimeException(e);
        } catch (java.sql.SQLException e) {
            Log.e(TAG, "onCreate", e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion,
                          int newVersion) {
        try {
            Log.i(TAG, "upgrade: " + oldVersion + "--" + newVersion);
            if (oldVersion < newVersion) {
                for (int i = oldVersion; i < newVersion; i++) {
                    String methodName = "updateFromDatabaseVersion" + i;
                    Method method = getClass().getDeclaredMethod(methodName, null);
                    method.setAccessible(true);
                    method.invoke(this, null);
                }
            }
        } catch (Exception e) {
            Log.e(TAG, "onUpgrade", e);
        }
    }

    public Dao<News, Integer> getNewsDao() {
        if (null == newsDao) {
            try {
                newsDao = getDao(News.class);
            } catch (java.sql.SQLException e) {
                Log.e(TAG, "getNewsDao", e);
            }
        }
        return newsDao;
    }
}
  • Creating DatabaseManager for managing DatabaseHelper instance
public class DatabaseManager {
    private DatabaseHelper helper;
    private static DatabaseManager instance;

    private static final String TAG = DatabaseManager.class.getSimpleName();

    public static DatabaseManager getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseManager(context);
        }
        return instance;
    }

    private DatabaseManager(Context context) {
        helper = new DatabaseHelper(context);
    }

    public DatabaseHelper getHelper() {
        return helper;
    }
}
  • Creating table file with fields of table
public class News implements Serializable {
    private static final String TAG = News.class.getSimpleName();

    public static class Fields {
        public static final String ID = "id";
        public static final String CONTENT = "content";
        public static final String CONTENT_ID = "content_id";
        public static final String TITLE = "title";
        public static final String PORTRAIT_AVATAR = "portrait_avatar";
        public static final String LANDSCAPE_AVATAR = "landscape_avatar";
        public static final String LIST_ID = "list_id";
        public static final String LIST_NAME = "list_name";
        public static final String LIST_TYPE = "zone";
    }

    @DatabaseField(allowGeneratedIdInsert = true, canBeNull = false, columnName = Fields.ID,
        generatedId = true)
    private long id;

    @DatabaseField(columnName = Fields.CONTENT_ID)
    private long contentId;

    @DatabaseField(columnName = Fields.TITLE)
    private String title;

    @DatabaseField(columnName = Fields.PORTRAIT_AVATAR)
    private String portraitAvatar;

    @DatabaseField(columnName = Fields.LANDSCAPE_AVATAR)
    private String landscapeAvatar;

    @DatabaseField(columnName = Fields.LIST_ID)
    private int listId;

    @DatabaseField(columnName = Fields.LIST_NAME)
    private String listName;

    @DatabaseField(columnName = Fields.LIST_TYPE)
    private String listType;

    @DatabaseField(columnName = Fields.CONTENT)
    private String content;
  • Creating datasource file for CRUD (create, read, update, delete) entities
public class NewsDataSource {
    public static final String TAG = NewsDataSource.class.getSimpleName();
    private Context context;

    public NewsDataSource(Context context) {
        this.context = context;
    }

    public void createNews(News news) {
        try {
            DatabaseHelper helper = DatabaseManager.getInstance(context).getHelper();
            helper.getNewsDao().create(news);
        } catch (Exception e) {
            Log.e(TAG, "createNews", e);
        }
    }

    public News getNewsByContentId(long contentId) {
        try {
            DatabaseHelper helper = DatabaseManager.getInstance(context).getHelper();
            PreparedQuery<News> preparedQuery = helper.getNewsDao().queryBuilder().where().eq(
                News.Fields.CONTENT_ID, contentId).prepare();
            News news = helper.getNewsDao().queryForFirst(preparedQuery);
            return news;
        } catch (Exception e) {
            Log.e(TAG, "getNewsByContentId", e);
            return null;
        }
    }
}
  • Using datasource for managing database
News newsOfDatabase = super.newsDataSource.getNewsByContentId(currentNews.getContentId());

Conclusion

ORMLite is easy to use and provides the following features:

  • Setup your classes by simply adding Java annotations.
  • Powerful abstract Database Access Object (DAO) classes.
  • Flexible QueryBuilder to easily construct simple and complex queries.
  • Handles “compiled” SQL statements for repetitive query tasks.
  • Supports “foreign” objects with the class field being the object but an id stored in the database table.
  • Basic support for database transactions.
  • Auto generates SQL to create and drop database tables.
  • Spring configuration support for DOAs and class configurations.
  • Support for configuring of tables and fields without annotations.

With OrmLite, your sourcecode will be clean and easy to read.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s