Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

877215a85ea128b67b4334142a6df260?s=47 Eric Fung
October 23, 2017

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

877215a85ea128b67b4334142a6df260?s=128

Eric Fung

October 23, 2017
Tweet

More Decks by Eric Fung

Other Decks in Programming

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()); }
  51. None
  52. None
  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