SQL on Android: Rise and Shine (Toronto Android Developers Meetup)

Let’s take a stroll through the landscape of databases on Android, and see where we’re headed. We’ll go through examples from a few implementations (Android's own API, ORMLite), then look at the trend of letting SQL emerge from the shadows (SQLDelight, Room).

Video: https://www.youtube.com/watch?v=vbHDoiMBbmA

Transcript

1. SQL on Android: Rise and Shine Eric Fung @gnufmuffin Toronto Android Developers Meetup 2017-10-23
2. Overview ! android.database.sqlite ! ORMLite ! SQLDelight ! Room
3. android.database.sqlite1 1 developer.android.com/reference/android/database/sqlite/package-summary.html
4. android.database.sqlite — Part of Android since API 1 — Very stable — CRUD API is in SQLiteDatabase class
5. public interface BaseColumns { public static final String _ID = "_id"; } public final class FeedReaderContract { private FeedReaderContract() {} public static class FeedEntry implements BaseColumns { public static final String TABLE_NAME = "entry"; public static final String COLUMN_NAME_TITLE = "title"; public static final String COLUMN_NAME_SUBTITLE = "subtitle"; } }
6. public class FeedReaderDbHelper extends SQLiteOpenHelper { private static final String SQL_CREATE_ENTRIES = "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" + FeedEntry._ID + " INTEGER PRIMARY KEY," + FeedEntry.COLUMN_NAME_TITLE + " TEXT," + FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)"; private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME; public FeedReaderDbHelper(Context context) {…} } public void onCreate(SQLiteDatabase db) { db.execSQL(SQL_CREATE_ENTRIES); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(SQL_DELETE_ENTRIES); onCreate(db); } }
7. public class FeedReaderDbHelper extends SQLiteOpenHelper { private static final String SQL_CREATE_ENTRIES = "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" + FeedEntry._ID + " INTEGER PRIMARY KEY," + FeedEntry.COLUMN_NAME_TITLE + " TEXT," + FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)"; private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME; public FeedReaderDbHelper(Context context) {…} } public void onCreate(SQLiteDatabase db) { db.execSQL(SQL_CREATE_ENTRIES); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL(SQL_DELETE_ENTRIES); onCreate(db); } }
8. import android.content.ContentValues; SQLiteDatabase db = mDbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(FeedEntry.COLUMN_NAME_TITLE, title); values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle); long newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);
9. import android.content.ContentValues; SQLiteDatabase db = mDbHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(FeedEntry.COLUMN_NAME_TITLE, title); values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle); long newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);
10. SQLiteDatabase db = mDbHelper.getReadableDatabase(); String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?" + " AND " + FeedEntry.COLUMN_NAME_SUBTITLE + " LIKE ?"; String[] selectionArgs = { "Breaking News", "%Canada%" }; String sortOrder = FeedEntry.COLUMN_NAME_TITLE + " DESC"; Cursor cursor = db.query( FeedEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder );
11. SQLiteDatabase db = mDbHelper.getReadableDatabase(); String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?" + " AND " + FeedEntry.COLUMN_NAME_SUBTITLE + " LIKE ?"; // "title = ? AND subtitle LIKE ?" String[] selectionArgs = { "Breaking News", "%Canada%" }; String sortOrder = FeedEntry.COLUMN_NAME_TITLE + " DESC"; Cursor cursor = db.query( FeedEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder );
12. SQLiteDatabase db = mDbHelper.getReadableDatabase(); String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?" + " AND " + FeedEntry.COLUMN_NAME_SUBTITLE + " LIKE ?"; String[] selectionArgs = { "Breaking News", "%Canada%" }; String sortOrder = FeedEntry.COLUMN_NAME_TITLE + " DESC"; Cursor cursor = db.query( FeedEntry.TABLE_NAME, // The table to query projection, // The columns to return selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order );
13. List<FeedEntryModel> entries = new ArrayList<>(); while (cursor.moveToNext()) { FeedEntryModel e = new FeedEntryModel() e.id = cursor.getLong( cursor.getColumnIndexOrThrow(FeedEntry._ID)); e.title = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); e.subtitle = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); entries.add(e); } cursor.close();
14. List<FeedEntryModel> entries = new ArrayList<>(); while (cursor.moveToNext()) { FeedEntryModel e = new FeedEntryModel() e.id = cursor.getLong( cursor.getColumnIndexOrThrow(FeedEntry._ID)); e.title = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); e.subtitle = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); entries.add(e); } cursor.close();
15. List<FeedEntryModel> entries = new ArrayList<>(); while (cursor.moveToNext()) { FeedEntryModel e = new FeedEntryModel() e.id = cursor.getLong( cursor.getColumnIndexOrThrow(FeedEntry._ID)); e.title = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); e.subtitle = cursor.getString( cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); entries.add(e); } cursor.close();
16. String rawQuery = "SELECT * FROM " + FeedEntry.TABLE_NAME + " INNER JOIN " + Author.TABLE_NAME + " ON " + Author._ID + " = " + FeedEntry.AUTHOR_ID + " WHERE " + Author._ID + " = ?"; String args = { authorId }; Cursor c = db.rawQuery(rawQuery, args);
17. String rawQuery = "SELECT * FROM " + FeedEntry.TABLE_NAME + " INNER JOIN " + Author.TABLE_NAME + " ON " + Author._ID + " = " + FeedEntry.AUTHOR_ID + " WHERE " + Author._ID + " = ?"; String args = { authorId }; Cursor c = db.rawQuery(rawQuery, args); Really!?
18. android.database.sqlite — ! No dependencies — ! Full access to SQL — " Lots of boilerplate — # No compile-time checks — $ No type safety
19. What about OOP? — Object Relational Mapping (ORM) — Object-oriented abstraction over SQL — Many Android implementations — ORMLite — greenDAO — DBFlow — Requery
20. ORMLite 2 2 ormlite.com
21. ORMLite — 2010-2016 — General Java 㲗 SQL library — Based on model annotations — Uses Data Access Object (DAO) pattern
22. @DatabaseTable(tableName = "feed_entry") public class FeedEntry { @DatabaseField(generatedId = true) private Long id; @DatabaseField private String title; @DatabaseField private String subtitle; public FeedEntry() {} public FeedEntry(String title, String subtitle) { this.title = title; this.subtitle = subtitle; } public Long getId() { return id; } public void setTitle(String title) { this.title = title; } … }
23. @DatabaseTable(tableName = "feed_entry") public class FeedEntry { @DatabaseField(generatedId = true) private Long id; @DatabaseField private String title; @DatabaseField private String subtitle; public FeedEntry() {} public FeedEntry(String title, String subtitle) { this.title = title; this.subtitle = subtitle; } public Long getId() { return id; } public void setTitle(String title) { this.title = title; } … }
24. @DatabaseTable(tableName = "feed_entry") public class FeedEntry { @DatabaseField(generatedId = true) private Long id; @DatabaseField(canBeNull = false) private String title; @DatabaseField(columnName = "sub_title") private String subtitle; public FeedEntry() {} public FeedEntry(String title, String subtitle) { this.title = title; this.subtitle = subtitle; } public Long getId() { return id; } public void setTitle(String title) { this.title = title; } … }
25. public class FeedReaderOrmHelper extends OrmLiteSqliteOpenHelper { public void onCreate(SQLiteDatabase db, ConnectionSource cs) { TableUtils.createTable(connectionSource, FeedEntry.class); } public void onUpgrade(SQLiteDatabase db, ConnectionSource cs, int old, int new) { } }
26. Dao<FeedEntry, Long> dao = dbHelper.getDao(FeedEntry.class); FeedEntry entry = new FeedEntry(); entry.setTitle("First look at the Google Pixel 2"); entry.setSubtitle("A Pragmatic Design"); dao.create(entry);
27. Dao<FeedEntry, Long> dao = dbHelper.getDao(FeedEntry.class); FeedEntry entry = new FeedEntry(); entry.setTitle("First look at the Google Pixel 2"); entry.setSubtitle("A Pragmatic Design"); dao.create(entry);
28. Dao<FeedEntry, Long> dao = dbHelper.getDao(FeedEntry.class); FeedEntry entry = new FeedEntry(); entry.setTitle("First look at the Google Pixel 2"); entry.setSubtitle("A Pragmatic Design"); dao.create(entry);
29. Dao<FeedEntry, Long> dao = dbHelper.getDao(FeedEntry.class); FeedEntry entry = new FeedEntry(); entry.setTitle("First look at the Google Pixel 2"); entry.setSubtitle("A Pragmatic Design"); dao.create(entry); entry.setSubtitle("More of the same"); dao.update(entry);
30. Dao<FeedEntry, Long> dao = dbHelper.getDao(FeedEntry.class) FeedEntry entry = new FeedEntry(); entry.setTitle("First look at the Google Pixel 2"); entry.setSubtitle("A Pragmatic Design"); dao.create(entry); entry.setSubtitle("More of the same"); dao.update(entry); dao.delete(entry);
31. QueryBuilder<FeedEntry, Long> qb = dao.queryBuilder(); qb.where().like(FeedEntry.TITLE_NAME, "breaking"); PreparedQuery<FeedEntry> preparedQuery = qb.prepare(); List<FeedEntry> entries = dao.query(preparedQuery);
32. QueryBuilder<FeedEntry, Long> qb = dao.queryBuilder(); Where where = qb.where() where.like(FeedEntry.TITLE_NAME, "breaking"); where.and() where.like(FeedEntry.SUBTITLE_NAME, "noteworthy"); qb.groupBy(FeedEntry.DATE_NAME); PreparedQuery<FeedEntry> preparedQuery = qb.prepare(); List<FeedEntry> entries = dao.query(preparedQuery);
33. class Author { … @ForeignCollectionField ForeignCollection<FeedEntry> feedEntries; … } class FeedEntry { … @DatabaseField(foreign = true) Author author; … }
34. ORMLite — ! Type safety, compile-time checking — ! Much less boilerplate — " Learning curve — # Incomplete SQL support — $ Performance (?)
35. What about OOP without SQL? — NoSQL, e.g. Firebase — Object database, e.g. Realm
36. How about SQL done better? Instead of generating SQL from Java… Java ➡ SQL
37. How about SQL done better? Instead of generating SQL from Java, why not generate Java from SQL? Java ⬅ SQL
38. SQLDelight3 3 github.com/square/sqldelight
39. SQLDelight — 2016 - present — IntelliJ plugin — SQL with “superpowers” — Generates code for you to use — Specific design goals
40. -- article.sq CREATE TABLE article ( _id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE, title TEXT NOT NULL, author_email TEXT NOT NULL, tags TEXT );
41. // ArticleModel.java public interface ArticleModel { String TABLE_NAME = "article"; String _ID = "_id"; String TITLE = "title"; String AUTHOR_EMAIL = "author_email"; String TAGS = "tags"; String CREATE_TABLE = "" + "CREATE TABLE article (\n" + " _id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,\n" + " title TEXT NOT NULL,\n" + " author_email TEXT NOT NULL,\n" + " tags TEXT\n" + ")"; long _id(); @NonNull String title(); @NonNull String author_email(); @Nullable String tags(); …
42. // ArticleModel.java public interface ArticleModel { String TABLE_NAME = "article"; String _ID = "_id"; String TITLE = "title"; String AUTHOR_EMAIL = "author_email"; String TAGS = "tags"; String CREATE_TABLE = "" + "CREATE TABLE article (\n" + " _id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,\n" + " title TEXT NOT NULL,\n" + " author_email TEXT NOT NULL,\n" + " tags TEXT\n" + ")"; long _id(); @NonNull String title(); @NonNull String author_email(); @Nullable String tags(); …
43. public class MyAppOpenHelper extends SQLiteOpenHelper { … public void onCreate(SQLiteDatabase db) { db.execSQL(ArticleModel.CREATE_TABLE); } }
44. // ArticleModel.java public interface ArticleModel { String TABLE_NAME = "article"; String _ID = "_id"; String TITLE = "title"; String AUTHOR_EMAIL = "author_email"; String TAGS = "tags"; String CREATE_TABLE = "" + "CREATE TABLE article (\n" + " _id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE,\n" + " title TEXT NOT NULL,\n" + " author_email TEXT NOT NULL,\n" + " tags TEXT\n" + ")"; long _id(); @NonNull String title(); @NonNull String author_email(); @Nullable String tags(); …
45. @AutoValue public abstract class Article implements ArticleModel { public static final Factory<Article> FACTORY = new Factory<>(AutoValue_Article::new); … }
46. -- article.sq CREATE TABLE article ( _id INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE, title TEXT NOT NULL, author_email TEXT NOT NULL, tags TEXT ); select_by_id: SELECT * FROM article WHERE _id = ?;
47. // ArticleModel.java String SELECT_BY_ID = "" + "SELECT *\n" + "FROM article\n" + "WHERE _id = ?"; final class Mapper<T extends ArticleModel> implements RowMapper<T> { … @Override public T map(@NonNull Cursor cursor) { return articleModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3) ); } }
48. SQLiteDatabase db; public Article getById(long id) { Cursor cursor = db.rawQuery(Article.SELECT_BY_ID, new String[]{String.valueOf(id)}); boolean notEmpty = cursor.moveToFirst(); return notEmpty ? Article.MAPPER.map(cursor) : null; }
49. SQLiteDatabase db; public Article getById(long id) { Cursor cursor = db.rawQuery(Article.SELECT_BY_ID, new String[]{String.valueOf(id)}); boolean notEmpty = cursor.moveToFirst(); return notEmpty ? Article.MAPPER.map(cursor) : null; }
50. SQLiteDatabase db; public void saveArticle(@NonNull Article article) { … db.insert(Article.TABLE_NAME, null, Article.FACTORY.marshal(article).asContentValues()); }
53. SQLDelight Other good stu f f — Projections — Compiled statements — Custom Java type adapters — Views
54. SQLDelight — ! Type safety, compile-time checking — ! Full access to SQL — ! Minimal boilerplate — " Learning curve — # No migration support (yet) — ✨ Work in progress (0.6.1)
55. Room 4 4 developer.android.com/topic/libraries/architecture/room.html
56. Room — Mid 2017-present — Like Retrofit for database queries — Based on annotations — Uses Data Access Object (DAO) pattern
57. @Entity(tableName = "article") public class Article { public @PrimaryKey long _id; public String title; @ColumnInfo(name="authorEmail") public String author_email; public String tags; }
58. @Dao public interface ArticleDao { @Query("SELECT * FROM article WHERE _id = :id") Article loadById(int id); @Query("SELECT * FROM article WHERE authorEmail = :email and tags = :tag") Article loadByEmailAndTags(String email, String tag); @Insert(onConflict = IGNORE) void insert(Article article); @Delete void delete(Article article); }
59. @Override public Article loadById(int id) { final String _sql = "SELECT * FROM article WHERE _id = ?"; final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1); int _argIndex = 1; _statement.bindLong(_argIndex, id); final Cursor _cursor = __db.query(_statement); try { final int _cursorIndexOfId = _cursor.getColumnIndexOrThrow("_id"); final int _cursorIndexOfTitle = _cursor.getColumnIndexOrThrow("title"); final int _cursorIndexOfAuthorEmail = _cursor.getColumnIndexOrThrow("authorEmail"); final int _cursorIndexOfTags = _cursor.getColumnIndexOrThrow("tags"); final Article _result; if(_cursor.moveToFirst()) { _result = new Article(); _result._id = _cursor.getLong(_cursorIndexOfId); _result.title = _cursor.getString(_cursorIndexOfTitle); _result.author_email = _cursor.getString(_cursorIndexOfAuthorEmail); _result.tags = _cursor.getString(_cursorIndexOfTags); } else { _result = null; } return _result; } finally { _cursor.close(); _statement.release(); } }
60. @Override public Article loadById(int id) { final String _sql = "SELECT * FROM article WHERE _id = ?"; final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1); int _argIndex = 1; _statement.bindLong(_argIndex, id); final Cursor _cursor = __db.query(_statement); try { final int _cursorIndexOfId = _cursor.getColumnIndexOrThrow("_id"); final int _cursorIndexOfTitle = _cursor.getColumnIndexOrThrow("title"); final int _cursorIndexOfAuthorEmail = _cursor.getColumnIndexOrThrow("authorEmail"); final int _cursorIndexOfTags = _cursor.getColumnIndexOrThrow("tags"); final Article _result; if(_cursor.moveToFirst()) { _result = new Article(); _result._id = _cursor.getLong(_cursorIndexOfId); _result.title = _cursor.getString(_cursorIndexOfTitle); _result.author_email = _cursor.getString(_cursorIndexOfAuthorEmail); _result.tags = _cursor.getString(_cursorIndexOfTags); } else { _result = null; } return _result; } finally { _cursor.close(); _statement.release(); } }
61. @Override public Article loadById(int id) { final String _sql = "SELECT * FROM article WHERE _id = ?"; final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1); int _argIndex = 1; _statement.bindLong(_argIndex, id); final Cursor _cursor = __db.query(_statement); try { final int _cursorIndexOfId = _cursor.getColumnIndexOrThrow("_id"); final int _cursorIndexOfTitle = _cursor.getColumnIndexOrThrow("title"); final int _cursorIndexOfAuthorEmail = _cursor.getColumnIndexOrThrow("authorEmail"); final int _cursorIndexOfTags = _cursor.getColumnIndexOrThrow("tags"); final Article _result; if(_cursor.moveToFirst()) { _result = new Article(); _result._id = _cursor.getLong(_cursorIndexOfId); _result.title = _cursor.getString(_cursorIndexOfTitle); _result.author_email = _cursor.getString(_cursorIndexOfAuthorEmail); _result.tags = _cursor.getString(_cursorIndexOfTags); } else { _result = null; } return _result; } finally { _cursor.close(); _statement.release(); } }
62. @Database(entities = {Article.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract ArticleDao articleModel(); }
63. @Database(entities = {Article.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract ArticleDao articleModel(); } AppDatabase db = Room.databaseBuilder(getApplicationContext(), AppDatabase.class, "database-name").build();
64. @Database(entities = {Article.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract ArticleDao articleModel(); } AppDatabase db = Room.databaseBuilder(getApplicationContext(), AppDatabase.class, "database-name").build(); Article result = db.articleModel().loadById(123);
65. @Database(entities = {Article.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract ArticleDao articleModel(); } AppDatabase db = Room.databaseBuilder(getApplicationContext(), AppDatabase.class, "database-name").build(); Article article = new Article() article.title = "Wait, there's more!" db.articleModel().insert(article);
66. Room Other good stu f f — Prevent blocking UI — Reactive results: RxJava or LiveData — Migration and testing support — In-memory implementation
67. Room — ! Type safety, compile-time checking — ! Testing and migration support — " Works with Arch. Components — # Learning curve — ✨ Brand new (1.0.0-rc1)
68. Parting Thoughts — SQL is here to stay — Developer's Dilemma — Be aware — Lock-in inertia
69. End Let's have some questions! — Email efung@acm.org — Blog code.gnufmuffin.com — Code github.com/efung — Social @gnufmuffin — Slides speakerdeck.com/efung