preface

Recently, Rxjava may have been applied to many people’s projects, the last collection of two articles by the author han jiang budiao Google Play Top200 application analysis report and domestic Top500Android application analysis report respectively mentioned the use of Rxjava applications, There are a lot of great companies.







In the open source community, there have been a lot of articles about RxJava, and many companies have been asking interview questions about Retrofit, RxJava, the latest technologies. It’s not too long before Rxjava becomes standard in every application.

Rxjava can be used to perform all kinds of operations until you encounter database operations. Can Rxjava also be used to perform database operations in the form of a responsive, event-based, Observable as an operation sequence? This is the same problem I encountered when I first got into RXJava. Square has provided a lightweight library SqlBrite to meet this requirement. SqlDelight is a lightweight library for Square.

Github address: square/sqlbrite Square/sqlDelight

What are SqlBrite and SqlDelight?

sqlbrite

A lightweight wrapper around SQLiteOpenHelper which introduces reactive stream semantics to SQL operations. Square. Making. IO/sqlbrite / 0….

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

sqldelight

SQLDelight generates Java models from your SQL CREATE TABLE

statements. These models give you a typesafe API to read & write the rows of your tables. It helps you to keep your SQL statements together, organized, and easy to access from Java.

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.

The use of SqlBrite

The following is taken from the official readme.md document

Create a SqlBrite object that is the entry point to the library:

SqlBrite sqlBrite = SqlBrite.create();Copy the code

Provide an SQLiteOpenHelper instance and a Scheduler instance to create a BriteDatabase object:

BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());Copy the code

Scheduler is the thread that specifies the operation to execute the query, and since querying a database is not recommended to be performed in a UI thread, 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

Unlike the traditional rawQuery method, as long as you subscribe to an Observable, the subscribed query gets updates as you insert and delete data. This code demonstrates data notification.

final AtomicInteger queries = new AtomicInteger(); users.subscribe(new Action1() { @Override public void call(Query query) { queries.getAndIncrement(); }}); System.out.println("Queries: " + queries.get()); // Prints 1 db.insert("users", createUser("jw", "Jake Wharton")); db.insert("users", createUser("mattp", "Matt Precious")); db.insert("users", createUser("strong", "Alec Strong")); System.out.println("Queries: " + queries.get()); // Prints 4Copy the code

Sqlbrite uses the name of the table to notify other Observables listening to the table to update the data. This requires you to access the database only through BriteDatabase, not SQLiteOpenHelper. The following code demonstrates that after Subscription is unsubscribed, no data will be received.

final AtomicInteger queries = new AtomicInteger(); Subscription s = users.subscribe(new Action1() { @Override public void call(Query query) { queries.getAndIncrement(); }}); System.out.println("Queries: " + queries.get()); // Prints 1 db.insert("users", createUser("jw", "Jake Wharton")); db.insert("users", createUser("mattp", "Matt Precious")); s.unsubscribe(); db.insert("users", createUser("strong", "Alec Strong")); System.out.println("Queries: " + queries.get()); // Prints 3Copy the code

If a large amount of data is committed, transactions can be used:

final AtomicInteger queries = new AtomicInteger(); users.subscribe(new Action1() { @Override public void call(Query query) { queries.getAndIncrement(); }}); System.out.println("Queries: " + queries.get()); // Prints 1 Transaction transaction = db.newTransaction(); try { db.insert("users", createUser("jw", "Jake Wharton")); db.insert("users", createUser("mattp", "Matt Precious")); db.insert("users", createUser("strong", "Alec Strong")); transaction.markSuccessful(); } finally { transaction.end(); } System.out.println("Queries: " + queries.get()); // Prints 2Copy the code

You can also use try-with-resources on Transaction objects

Since the query is just a common RxJava Observable, various operation functions can be used above. Here, the frequency of notifying subscriber is controlled by the operator:

users.debounce(500, MILLISECONDS).subscribe(new Action1() { @Override public void call(Query query) { // TODO... }});Copy the code

SqlBrite can also encapsulate the ContentResolver for the content Provider’s data query operations.

BriteContentResolver resolver = sqlBrite.wrapContentProvider(contentResolver, Schedulers.io()); Observable query = resolver.createQuery(/*... * /);Copy the code

Of course Rxjava combine, filter and other operators will also play a great role in using SqlBrite.

The use of SqlDelight

The following content is from the official readme. md. Since I have not used SqlDelight so far, some of it is not translated, please directly check the original document. Will be updated after subsequent use

The sample

SQLDelight is used, 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, called Mapper and Marshal:

package com.example; import android.content.ContentValues; import android.database.Cursor; import java.lang.String; public interface HockeyPlayerModel { String TABLE_NAME = "hockey_player"; String _ID = "_id"; String NUMBER = "number"; String NAME = "name"; String CREATE_TABLE = "" + "CREATE TABLE hockey_player (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " number INTEGER NOT NULL,\n" + " name TEXT NOT NULL\n" + ")"; String SELECT_BY_NAME = "" + "SELECT *\n" + "FROM hockey_player\n" + "WHERE name = ?" ; long _id(); long number(); String name(); final class Mapper { private final Creator creator; protected Mapper(Creator creator) { this.creator = creator; } public T map(Cursor cursor) { return creator.create( cursor.getLong(cursor.getColumnIndex(_ID)), cursor.getLong(cursor.getColumnIndex(NUMBER)), cursor.getString(cursor.getColumnIndex(NAME)) ); } public interface Creator { R create(long _id, long number, String name); } } class HockeyPlayerMarshal> { protected ContentValues contentValues = new ContentValues(); public HockeyPlayerMarshal() { } public final ContentValues asContentValues() { return contentValues; } public T _id(long _id) { contentValues.put(_ID, _id); return (T) this; } public T number(long number) { contentValues.put(NUMBER, number); return (T) this; } public T name(String name) { contentValues.put(NAME, name); return (T) this; } } } Read more: [http://blog.chengyunfeng.com/?p=993#ixzz4EqMaUGwO](http://blog.chengyunfeng.com/?p=993#ixzz4EqMaUGwO)Copy the code

AutoValue

If you have used AutoValue, you can see that the HockeyPlayerModel generated above supports AutoValue directly, which greatly reduces the amount of code required to implement this interface:

@AutoValue
public abstract class HockeyPlayer implements HockeyPlayerModel {
  public static final Mapper MAPPER = new Mapper<>(new Mapper.Creator() {
    @Override public HockeyPlayer create(long _id, long number, String name) {
      return new AutoValue_HockeyPlayer(_id, age, number, gender);
    }
  }

  public static final class Marshal extends HockeyPlayerMarshal { }
}Copy the code

With Retrolambda, the code can be further simplified:

@AutoValue
public abstract class HockeyPlayer implements HockeyPlayerModel {
  public static final Mapper MAPPER = new Mapper<>(AutoValue_HockeyPlayer::new);

  public static final class Marshal extends HockeyPlayerMarshal { }
}Copy the code

Use AutoValue to automatically generate the corresponding Java model, and then use the AutoValue generated Java model implementation classes to create a static MAPPER. MAPPER can be used to read data and Marshal can be used to insert data:

public void insert(SqliteDatabase db, long _id, long number, String name) {
  db.insert(HockeyPlayer.TABLE_NAME, null, new HockeyPlayer.Marshal()
    ._id(_id)
    .number(number)
    .name(name)
    .asContentValues());
}

public List alecs(SQLiteDatabase db) {
  List result = new ArrayList<>();
  try (Cursor cursor = db.rawQuery(HockeyPlayer.SELECT_BY_NAME, new String[] { "Alec" })) {
    while (cursor.moveToNext()) {
      result.add(HockeyPlayer.MAPPER.map(cursor));
    }
  }
  return result;
}Copy the code

The data type

SQLDelight’s data column definitions are the same as normal SQLite column definitions, but with additional support for some custom properties specifying Java data types. SQLDelight directly supports Cursor and ContentValues data types required:

CREATE TABLE some_types {
  some_long INTEGER,           -- Stored as INTEGER in db, retrieved as Long
  some_double REAL,            -- Stored as REAL in db, retrieved as Double
  some_string TEXT,            -- Stored as TEXT in db, retrieved as String
  some_blob BLOB,              -- Stored as BLOB in db, retrieved as byte[]
  some_int INTEGER AS Integer, -- Stored as INTEGER in db, retrieved as Integer
  some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short
  some_float REAL AS Float     -- Stored as REAL in db, retrieved as Float
}Copy the code

Booleans

SQLDelight uses the same type as SQLite to support booleans. But when you write an SQL statement, you can specify this field as a Boolean with additional attributes, so that the generated Java object is of type Boolean:

CREATE TABLE hockey_player (
  injured INTEGER AS Boolean DEFAULT 0
)Copy the code

Custom type

If the above basic type does not meet the requirements, you can also customize the type, directly specify the corresponding Java type:

import java.util.Calendar;
CREATE TABLE hockey_player ( 
birth_date INTEGER AS Calendar NOT NULL
)Copy the code

If a custom type is used, create a ColumnAdapter to convert Cursor data to the corresponding type. Convert Java type data to data supported by ContentValues:

public class HockeyPlayer implements HockeyPlayerModel { private static final ColumnAdapter CALENDAR_ADAPTER = new ColumnAdapter<>() { @Override public Calendar map(Cursor cursor, int columnIndex) { Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(cursor.getLong(columnIndex)); return calendar; } @Override public void marshal(ContentValues contentValues, String key, Calendar value) { contentValues.put(key, value.getTimeInMillis()); } } public static final Mapper MAPPER = new Mapper<>(new Mapper.Creator<>() { }, CALENDAR_ADAPTER); public static final class Marshal extends HockeyPlayerMarshal { public Marshal() { super(CALENDAR_ADAPTER); }}}Copy the code

Enumerated type

To facilitate support for enumerated types, SQLDelight includes a ColumnAdapter that stores the enumerated type as TEXT:

import com.example.hockey.HockeyPlayer;

CREATE TABLE hockey_player (
  position TEXT AS HockeyPlayer.Position
)Copy the code
public class HockeyPlayer implements HockeyPlayerModel {
  public enum Position {
    CENTER, LEFT_WING, RIGHT_WING, DEFENSE, GOALIE
  }

  private static final ColumnAdapter POSITION_ADAPTER = EnumColumnAdapter.create(Position.class);

  public static final Factory FACTORY = new Factory<>(new Creator<>() { },
      POSITION_ADAPTER);
}Copy the code

Projection

Go directly to the original document readme.md

Views

Go directly to the original document readme.md

Join operation

Go directly to the original document readme.md

SQL statement parameters

Statements in the.sq file can also support parameters in SqliteDatabase:

select_by_position: SELECT * FROM hockey_player WHERE position = ? ;Copy the code
Cursor centers = db.rawQuery(HockeyPlayer.SELECT_BY_POSITION, new String[] { Center.name() });Copy the code

Intellij (Android Studio) plug-in

The plugin is used to help edit.sq text and supports the following functions:

  • Syntax highlighting
  • Refactor/find identifiers
  • Automatic code completion
  • The Model interface is automatically generated after editing

Install and use

Gradle plug-in:

Dependencies buildscript {repositories {mavenCentral ()} {the classpath 'com. Squareup. Sqldelight: gradle - plugin: 0.4.3'}}  apply plugin: 'com.squareup.sqldelight'Copy the code

Thoughts on SqlBrite and SqlDelight

For those interested, check out Jack Wharton’s answer on Reddit. Here’s the translation.

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.

SqlBrite simply makes it easy to use Sql operations in RxJava, and adds an additional mechanism for notifying database table data updates. It’s just a lightweight wrapper around SQLiteOpenHelper that doesn’t care how your objects are implemented or your database. Also, SqlBrite does not support object mapping and type-safe queries, which are often no more convenient than using SQL statements directly. Although there is a good framework for operating SQL in Java – jOOQ. But using jOOQ on Android is a bit of an overkill!

SQL Delight does this by generating 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 database structure. 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. The framework is still young, but as you can see from the framework’s current capabilities, SqlDelight is not going to become an ORM framework. And it does not do heavy functions (such as lazy loading of data, caching, cascading deletion and other common functions in ORM framework).

SqlDelight most of the code is compile-time, the real run-time code (the code included in your application) is just a dozen lines of code and a few interfaces. It will make your SQL writing much easier, and it will be much easier to migrate to these two libraries, while still enjoying the benefits of responsive queries, type-safe object mapping, and compilation.

These frameworks will not implement the following features that ORM frameworks force you to do:

  • Will not become a less comprehensive database query API in the Java language
  • Mapping foreign keys to a collection of Java objects (relational mapping) is not implemented
  • There will be no references to string-ly typed table names and column names
  • There is no base class that requires your database operation object to inherit from it
  • Database tables are not defined in Java, for example through annotations, or by inheriting a class, etc
  • Tables are not automatically created and migrated
  • There are no thread restrictions on Sql queries and Java objects
  • Instead of returning a mutable object, you modify the value of the object and then call the save function to save the updated value to the database.

SqlBrite is simply a lightweight package for coordinating updates to data and notifying other subscribers when data changes as you work on a table. You can then manipulate the data in RxJava fashion. 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. Some of these features are provided by SQLDelight, which can be used with SqlBrite.

Refer to the link

SQLDelight SQLBrite SQLBrite: A Reactive Database Foundation what Android developers use and why Anyone use SQLBrite and/or SQLDelight? Perfect Android Model Architecture (Part 1)