SQL on Android: Rise and Shine Eric Fung @gnufmuffin Toronto Android Developers Meetup 2017-10-23 

Overview ! android.database.sqlite ! ORMLite ! SQLDelight ! Room 

android.database.sqlite1 1 developer.android.com/reference/android/database/sqlite/package-summary.html 

android.database.sqlite — Part of Android since API 1 — Very stable — CRUD API is in SQLiteDatabase class 

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"; } } 

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); } } 

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); } } 

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); 

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); 

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 ); 

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 ); 

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 ); 

List 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(); 

List 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(); 

List 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(); 

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); 

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!? 

android.database.sqlite — ! No dependencies — ! Full access to SQL — " Lots of boilerplate — # No compile-time checks — $ No type safety 

What about OOP? — Object Relational Mapping (ORM) — Object-oriented abstraction over SQL — Many Android implementations — ORMLite — greenDAO — DBFlow — Requery 

ORMLite 2 2 ormlite.com 

ORMLite — 2010-2016 — General Java 㲗 SQL library — Based on model annotations — Uses Data Access Object (DAO) pattern 

@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; } … } 

@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; } … } 

@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; } … } 

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) { } } 

Dao 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); 

Dao 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); 

Dao 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); 

Dao 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 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); 

QueryBuilder qb = dao.queryBuilder(); qb.where().like(FeedEntry.TITLE_NAME, "breaking"); PreparedQuery preparedQuery = qb.prepare(); List entries = dao.query(preparedQuery); 

QueryBuilder 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 preparedQuery = qb.prepare(); List entries = dao.query(preparedQuery); 

class Author { … @ForeignCollectionField ForeignCollection feedEntries; … } class FeedEntry { … @DatabaseField(foreign = true) Author author; … } 

ORMLite — ! Type safety, compile-time checking — ! Much less boilerplate — " Learning curve — # Incomplete SQL support — $ Performance (?) 

What about OOP without SQL? — NoSQL, e.g. Firebase — Object database, e.g. Realm 

How about SQL done better? Instead of generating SQL from Java… Java ➡ SQL 

How about SQL done better? Instead of generating SQL from Java, why not generate Java from SQL? Java ⬅ SQL 

SQLDelight3 3 github.com/square/sqldelight 

SQLDelight — 2016 - present — IntelliJ plugin — SQL with “superpowers” — Generates code for you to use — Specific design goals 

-- 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 ); 

// 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(); … 

// 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(); … 

public class MyAppOpenHelper extends SQLiteOpenHelper { … public void onCreate(SQLiteDatabase db) { db.execSQL(ArticleModel.CREATE_TABLE); } } 

// 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(); … 

@AutoValue public abstract class Article implements ArticleModel { public static final Factory FACTORY = new Factory<>(AutoValue_Article::new); … } 

-- 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 = ?; 

// ArticleModel.java String SELECT_BY_ID = "" + "SELECT *\n" + "FROM article\n" + "WHERE _id = ?"; final class Mapper implements RowMapper { … @Override public T map(@NonNull Cursor cursor) { return articleModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3) ); } } 

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; } 

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; } 

SQLiteDatabase db; public void saveArticle(@NonNull Article article) { … db.insert(Article.TABLE_NAME, null, Article.FACTORY.marshal(article).asContentValues()); } 

No content

No content

SQLDelight Other good stu f f — Projections — Compiled statements — Custom Java type adapters — Views 

SQLDelight — ! Type safety, compile-time checking — ! Full access to SQL — ! Minimal boilerplate — " Learning curve — # No migration support (yet) — ✨ Work in progress (0.6.1) 

Room 4 4 developer.android.com/topic/libraries/architecture/room.html 

Room — Mid 2017-present — Like Retrofit for database queries — Based on annotations — Uses Data Access Object (DAO) pattern 

@Entity(tableName = "article") public class Article { public @PrimaryKey long _id; public String title; @ColumnInfo(name="authorEmail") public String author_email; public String tags; } 

@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); } 

@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(); } } 

@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(); } } 

@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(); } } 

@Database(entities = {Article.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract ArticleDao articleModel(); } 

@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(); 

@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); 

@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); 

Room Other good stu f f — Prevent blocking UI — Reactive results: RxJava or LiveData — Migration and testing support — In-memory implementation 

Room — ! Type safety, compile-time checking — ! Testing and migration support — " Works with Arch. Components — # Learning curve — ✨ Brand new (1.0.0-rc1) 

Parting Thoughts — SQL is here to stay — Developer's Dilemma — Be aware — Lock-in inertia 

End Let's have some questions! — Email [email protected] — Blog code.gnufmuffin.com — Code github.com/efung — Social @gnufmuffin — Slides speakerdeck.com/efung