Most open source databases are based on SQLite, such as SQLBrite, OrmLite, DBFlow, GreenDao, etc. There is also a Realm framework that is not based on SQLite and builds its own unique database storage engine. So what’s the difference between all these frameworks, and which one is more suitable for you?

Let’s take a look at the framework developed based on SQLite, which is divided into two main development paths, one is Rx or object mapping (OM) path, one is object relational mapping (ORM) path.

Rx/ object mapping route

Sqlbrite and SQLDelight, both of which are from Square.

SqlBrite and SqlDelight are Object maps (OM, Object Mappers) rather than Object Relational maps (ORM, Object/Relational Mappers).

ORM is actually not a good framework. ORM implementations on many platforms have performance and memory issues. We won’t write orMs either. – JakeWharton

The above sentence is a good illustration of the starting point of the two frameworks, so it’s easy to understand.

SqlBrite

SqlBrite is a wrapper around SQLiteOpenHelper for Android, introducing reactive semantics (Rx) for SQL operations (for use in RxJava)

Basic usage

  • Create a SqlBrite object that is the entry point to the library:SqlBrite sqlBrite = SqlBrite.create();
  • Provide an SQLiteOpenHelper instance and a Scheduler instance to create a BriteDatabase object:BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());, the Scheduler is the thread that specifies the operation to execute the query. Since database queries are not recommended to be performed in UI threads, schedulers.io () is typically specified.
  • BriteDatabase. CreateQuery method and SQLiteDatabase. RawQuery method, compared with more than a table (s) parameter table, used to monitor data changes. When we subscribe to the Observable returned by subscribe, we execute the required query immediately.
Observable users = db.createQuery("users", "SELECT * FROM users"); users.subscribe(new Action1() { @Override public void call(Query query) { Cursor cursor = query.run(); // TODO parse data... }});Copy the code

advantages

Using Rxjava to manipulate Sql provides some ease of use improvements while maintaining performance and complex extensibility.

disadvantages

You also need to write Sql statements, which is still complicated.

SqlDelight

SqlDelight generates JAVA model code from SQL statements. In this way, all SQL statements are located in the same location. By viewing SQL statements, you can clearly understand the functions to be implemented and the structure of the database. It is also easy to manage and access Java classes.

Basic usage

Need to put the SQL statements to the corresponding sq file, the default directory for and the main directory of the Java code, at the same level as the SRC/main/sqldelight/com/example/HockeyPlayer sq, Where com/example/ is the package name of the corresponding Java object. The usual first statement in the.sq file is the statement that creates the table:

CREATE TABLE hockey_player ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, number INTEGER NOT NULL, name TEXT NOT NULL ); Other statements are referenced by identifiers. The generated Java object contains a constant reference to this statement for the identifier. select_by_name: SELECT * FROM hockey_player WHERE name = ? ;Copy the code

The SQL statement above generates a HockeyPlayerModel Java interface. The interface has two nested classes that map Cursor to Java objects and convert Java objects to ContentValues for insertion into the database

advantages

  • All SQL statements are stored in. Sq files for easy management
  • Free to use plain SQLite while helping you deal with stylized code

disadvantages

You need to write native SQL

conclusion

SqlBrite facilitates Sql operations in RxJava, and adds an additional mechanism for notifying database table data updates. When you operate on a table, other subscribers can be notified when data changes. You can then manipulate the data in RxJava fashion. It’s just a lightweight wrapper around SQLiteOpenHelper that doesn’t care how your objects are implemented or your database. SqlBrite also does not support object mapping and type-safe queries. SqlBrite is not an ORM framework, nor is it a type-safe query framework. There is no provision for object serialization like in Gson, nor for database migration.

SQL Delight does this by generating JAVA model code from SQL statements. The advantage of this is that all SQL statements are in the same location. SQL Delight adds code completion for compile-time validation of SQL statements, table names, and column names. Make writing SQL statements faster. At compile time, Java model interfaces and Builders are generated from SQL statements to convert data rows to Java objects. SqlDelight doesn’t do heavy functionality (lazy data loading, caching, cascading delete, etc.) that is common in ORM frameworks.

Object relational mapping (ORM) routes

This part of the framework has done a lot of work in terms of ease of use and performance, usability is basically up to the extreme, performance is close to native Sql, here are a few representative projects.

OrmLite

OrmLite – Lightweight Object Relational Mapping (ORM) Java Package

The basic principle of

  • Annotate fields such as databases, tables, etc
  • The runtime uses reflection to fetch the corresponding fields and concatenate the SQL to execute

advantages

In terms of ease of use compared to native SQL

disadvantages

There is a performance penalty

greenDAO

greenDAO is an open source Android ORM making development for SQLite databases fun again.

Basic principles and characteristics

  • Annotate fields
  • Generate native SQL at compile time
  • GreenDAO supports direct storage of protocol buffer(Protobuf) data. If you use the Protobuf protocol to interact with the server, no mapping is required.
  • Data encryption

parsing


DaoMaster holds the SQliteDateBase object and manipulates the DAO classes. It provides static methods for creating and deleting tables, and its internal classes OpenHelper and DevOpenHelper implement the framework for SQLiteOpenHelper and creating databases.

In addition to creating and deleting tables, DaoMaster also has two internal classes, namely OpenHelper and DevOpenHelper. DevOpenHelper inherits from OpenHelper. OpenHelper inherits SQLiteOpenHelper and overwrites onCreate() with createAllTables(db,false); CreateTable (db, ifNotExists); createTable(db, ifNotExists);

/** Creates the underlying database table. */ public static void createTable(SQLiteDatabase db, boolean ifNotExists) { String constraint = ifNotExists? "IF NOT EXISTS ": ""; db.execSQL("CREATE TABLE " + constraint + "\\"NOTE\\" (" + // "\\"_id\\" INTEGER PRIMARY KEY AUTOINCREMENT ," + // 0: id "\\"TEXT\\" TEXT NOT NULL ," + // 1: text "\\"COMMENT\\" TEXT," + // 2: comment "\\"DATE\\" INTEGER);" ); / / 3: date } /** Drops the underlying database table. */ public static void dropTable(SQLiteDatabase db, boolean ifExists) { String sql = "DROP TABLE " + (ifExists ? "IF EXISTS " : "") + "\\"NOTE\\""; db.execSQL(sql); }Copy the code

Some of greenDAO’s add, delete, change and check methods are encapsulated in the Android native operation methods, and the final execution of chained query also calls the Android native query operation.

public List list() {
    checkThread();
    Cursor cursor = dao.getDatabase().rawQuery(sql, parameters);
    return daoAccess.loadAllAndCloseCursor(cursor);
}Copy the code

There are also methods that are implemented based on SQLiteStatement, which is faster than the native execSQL method, and finally executes with transactions enabled, which improves performance. Here is the final implementation of inserting data:

private long executeInsert(T entity, SQLiteStatement stmt) {
        long rowId;
    if (db.isDbLockedByCurrentThread()) {
        synchronized (stmt) {
            bindValues(stmt, entity);
            rowId = stmt.executeInsert();
        }
    } else {
        // Do TX to acquire a connection before locking the stmt to avoid deadlocks
        db.beginTransaction();
        try {
            synchronized (stmt) {
                bindValues(stmt, entity);
                rowId = stmt.executeInsert();
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }
    updateKeyAfterInsertAndAttach(entity, rowId, true);
    return rowId;
}Copy the code

It can be seen that the bindValues method is executed first, which is an abstract method and needs to be implemented by the business side in the DAO file. Trace to the NoteDao file and check the code of the method as follows:

@Override protected void bindValues(SQLiteStatement stmt, Note entity) { stmt.clearBindings(); Long id = entity.getId(); if (id ! = null) { stmt.bindLong(1, id); } stmt.bindString(2, entity.gettext ());} stmt.bindString(2, entity.gettext ()); String comment = entity.getComment(); if (comment ! = null) { stmt.bindString(3, comment); } java.util.Date date = entity.getDate(); if (date ! = null) { stmt.bindLong(4, date.getTime()); }}Copy the code

This wraps all the data needed for SQLiteStatement, and then executes the stmt.executeInsert() method to complete the database insert. GreenDAO uses SQLiteStatement to complete the data insertion process, avoiding the low execution efficiency caused by other frameworks using reflection to assemble SQL statements.

Other optimizations:

  • Avoid assembling SQL statements with annotations and reflections
  • The final execution starts the transaction
  • Supports asynchronous query and callback
  • Query cache mechanism, using WeakReference, the first query will add data to the collection of SparseArray>

advantages

It strikes a good balance between ease of use and performance

disadvantages

It costs

conclusion

ORM type frameworks are much better than native Sql in terms of ease of use, and GreenDao is even better than native Sql in some aspects of performance, making it suitable for most projects.

Realm

Instead of building on SQLite, the Realm framework builds its own unique database storage engine, which has its own unique advantages in some areas.

GreenDao vs Realm

Basic usage

Add:

Realm realm=Realm.getDefaultInstance();
realm.beginTransaction();
User user = realm.createObject(User.class); // Create a new object
user.setName("John");
user.setEmail("[email protected]");
realm.commitTransaction();
Copy the code

Delete:

Realm mRealm=Realm.getDefaultInstance(); final RealmResults dogs= mRealm.where(Dog.class).findAll(); mRealm.executeTransaction(new Realm.Transaction() { @Override public void execute(Realm realm) { Dog dog=dogs.get(5); dog.deleteFromRealm(); / / remove the first data dogs. DeleteFirstFromRealm (); // Delete the last data.dogs.deletElastFromrealm (); // Delete data dog.deleteFromrealm (1); // Delete data dog.deleteFromrealm (1); // Delete all data dog.deleteAllFromrealm (); }});Copy the code

advantages

  • Easy to use
  • fast
  • cross-platform
  • visualization

disadvantages

  • Significantly increase the size of the installation package by about 4 or 5 megabytes
  • Data type restrictions, must inherit from RealmObject, does not support inner classes, modified some types, does not support key growth.
  • Thread restrictions. If the CURRENT Realm object is acquired in the UI thread and used in an asynchronous thread, an exception will be thrown.

conclusion

Sqlite-based databases are recommended if the volume of data is not up to SQLite’s performance bottleneck, or if you don’t like writing native Sql statements, sacrifice a bit of performance to accommodate fast business iterations. The existence of these frameworks at the same time has its reasons. Performance has advantages, compatibility and stability can not be guaranteed, and the ease of use is good, so it is inevitable that too much customization can not be done. For general business, GreenDao is a good solution.