Share
Don't fear SQL (360AnDev 2017)
Don’t fear SQL: A better way to store and handle data with SQLBrite && SQLDelight
For many people, SQL can be intimidating when writing mobile apps. To avoid its complexity, various libraries started to implement techniques such as Object-Relational Mapping. They ease basic operations on complex objects, but come with a set of downsides such as decreased performance and the learning curve of a new library.
Square’s libraries SQLBrite and SQLDelight improve data manipulation by embracing all the powerful capabilities of SQLite while removing common frictions like runtime crashes, boilerplate code, and type-unsafe APIs.
In this talk I’ll present the reactive mindset behind SQLBrite and the code-generation capabilities of SQLDelight. When combined, these two libraries will help you architect and code safer and faster with queries autocompletion, code reuse, and much more.
Video: https://academy.realm.io/posts/360-andev-2017-leandro-favarin-sqlbrite-sqdelight/
Leandro Favarin
Transcript
119 slides
-
Don’t fear SQL A better way to store and handle
data with SQLBrite & SQLDelight Leandro Favarin -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
SQLite in Android – pros • Default choice for persisting
relational data -
SQLite in Android – pros • Default choice for persisting
relational data • Powerful (cover incredibly amount of use cases) -
SQLite in Android – pros • Default choice for persisting
relational data • Powerful (cover incredibly amount of use cases) • Efficient -
SQLite in Android – pros • Default choice for persisting
relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast -
SQLite in Android – pros • Default choice for persisting
relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable -
SQLite in Android – pros • Default choice for persisting
relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable • Cross-platform -
SQLite in Android – cons • It’s hard, because: •
Cursors • Verboseness • No type-safety Implemented as an interface on top of a native SQLite bundle -
SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; // Filter results WHERE "title" = 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " 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 ); -
SQLite in Android – example // Filter results WHERE "title"
= 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " DESC"; -
SQLite in Android – example // Filter results WHERE "title"
= 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " DESC"; -
SQLite in Android – example // Filter results WHERE "title"
= 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " DESC"; -
SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; // Filter results WHERE "title" = 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " 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 ); -
SQLite in Android – example SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { FeedEntry._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; // Filter results WHERE "title" = 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " 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<BdItem> result = new ArrayList <>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
SQLite in Android – example List<BdItem> result = new ArrayList
<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
SQLite in Android – example List<BdItem> result = new ArrayList
<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
SQLite in Android – example List<BdItem> result = new ArrayList
<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
SQLite in Android – example List<BdItem> result = new ArrayList
<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
SQLite in Android – example List<BdItem> result = new ArrayList
<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID)); String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE)); String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE)); DbItem item = new DbItem(itemId, title, subtitle); result.add(item); } cursor.close(); -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
What is ORM? Technique for converting data between incompatible type
systems in OO languages … basically: Converting between complex data types to “primitives" Wraps a SQL source Examples: greenDAO, OrmLite, Cupboard, Core Data (iOS) -
ORM – cons • New APIs to be learned •
Hide many powerful SQL capabilities -
ORM – cons • New APIs to be learned •
Hide many powerful SQL capabilities • Decreased performance -
ORM – cons • New APIs to be learned •
Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers -
ORM – cons • New APIs to be learned •
Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Objects are usually mutable -
ORM – cons • New APIs to be learned •
Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Objects are usually mutable • Lock-in effect -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
SQLDelight • Empower SQL(ite) by embracing it • Type-safe APIs
to read & write the rows of your tables • Organizes your SQL statements • Easy to access from Java -
SQLDelight – how it works • Generates code from SQL
`CREATE TABLE` statements • Create special files containing all SQL-related content • Extension: .sq • Runs on compile-time -
SQLDelight – build.gradle buildscript { repositories { mavenCentral() } dependencies
{ classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1' } } apply plugin: 'com.squareup.sqldelight' -
SQLDelight – IntelliJ plugin Provides language-level features for .sq files,
including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits • Right click to copy as valid SQLite • Compiler errors in IDE click through to file -
SQLDelight – keep in mind • The file structure of
your project is crucial for this to work • .sq files must be in a folder matching the directory structure -
SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java
... - sqldelight/ - com/example/project/models/ - Book.sq ... -
SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); -
SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); selectAll: SELECT * FROM book; -
SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, num_chapters) VALUES (?, ?, ?); -
SQLDelight – … generates: public interface BookModel { String TABLE_NAME
= "book"; String _ID = "_id"; String TITLE = "title"; String AUTHOR = "author"; String NUM_CHAPTERS = "num_chapters"; String CREATE_TABLE = "" + "CREATE TABLE book (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " title TEXT NOT NULL,\n" + " author TEXT NOT NULL,\n" + " num_chapters INTEGER\n" + ")"; long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters(); ... } -
SQLDelight – … generates: long _id(); @NonNull String title(); @NonNull
String author(); @Nullable Long num_chapters(); -
SQLDelight – … generates: public interface BookModel { String TABLE_NAME
= "book"; String _ID = "_id"; String TITLE = "title"; String AUTHOR = "author"; String NUM_CHAPTERS = "num_chapters"; String CREATE_TABLE = "" + "CREATE TABLE book (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " title TEXT NOT NULL,\n" + " author TEXT NOT NULL,\n" + " num_chapters INTEGER\n" + ")"; long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters(); ... } -
SQLDelight – … generates: class Mapper<T extends BookModel> implements RowMapper<T>
{ @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } } class Mapper<T extends BookModel> implements RowMapper<T> { @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } } -
SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel
{ public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); } -
SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel
{ public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); public static final Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper(); } -
SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =
Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } } -
SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =
Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { List<Book> result = new ArrayList <>(c.getCount()); while (c.moveToNext()) { result.add(Book.ROW_MAPPER.map(c)); } return result; } } -
SQLDelight – projections, joins Each SELECT statement will have: •
an interface • mapper • method to create a new instance of the mapper -
SQLDelight – types • Identical to SQLite column definitions •
Plus extra column constraint for Java type -
SQLDelight – types CREATE TABLE some_types ( some_long INTEGER, --
Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float -- Stored as REAL, retrieved as Float ); -
SQLDelight – booleans CREATE TABLE some_types ( some_long INTEGER, --
Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float, -- Stored as REAL, retrieved as Float some_boolean INTEGER AS Boolean DEFAULT 0 -- Stored as INTEGER, retrieved as Boolean ); -
SQLDelight – custom classes import org.threeten.bp.ZonedDateTime; CREATE TABLE book (
_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, publishedAt TEXT AS ZonedDateTime ); -
SQLDelight – custom classes class ZonedDateTimeColumnAdapter implements ColumnAdapter<ZonedDateTime, String> {
static final DateTimeFormatter FORMATTER = DateTimeFormatter.ISO_OFFSET_DATE_TIME; @NonNull @Override public ZonedDateTime decode(String databaseValue) { return FORMATTER.parse(databaseValue, ZonedDateTime.FROM); } @Override public String encode(@NonNull ZonedDateTime value) { return FORMATTER.format(value); } } class ZonedDateTimeColumnAdapter implements ColumnAdapter<ZonedDateTime, String> { static final DateTimeFormatter FORMATTER = DateTimeFormatter.ISO_OFFSET_DATE_TIME; @NonNull @Override public ZonedDateTime decode(String databaseValue) { return FORMATTER.parse(databaseValue, ZonedDateTime.FROM); } @Override public String encode(@NonNull ZonedDateTime value) { return FORMATTER.format(value); } } -
SQLDelight – views • Same treatment in generated code as
tables with their own model interface -
SQLDelight – Kotlin • Mostly works! • .. just have
to redundantly declare getters -
SQLDelight – Kotlin CREATE TABLE book ( _id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, edition INTEGER AS Integer NOT NULL ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, edition) VALUES (?, ?, ?); -
SQLDelight – Kotlin data class Book( val _id: Long, val
title: String, val author: String?, val edition: Int ) : BookModel { companion object { val FACTORY = BookModel.Factory( ::Book) val SELECT_ALL_MAPPER = FACTORY.selectAllMapper() } override fun _id() = _id override fun title() = title override fun author() = author override fun edition() = edition } data class Book( val _id: Long, val title: String, val author: String?, val edition: Int ) : BookModel { companion object { val FACTORY = BookModel.Factory( ::Book) val SELECT_ALL_MAPPER = FACTORY.selectAllMapper() } override fun _id() = _id override fun title() = title override fun author() = author override fun edition() = edition } -
SQLDelight – migrations • Least supported part of SQLDelight •
Library has potential to execute migrations for you • Likely to be implemented using specific files .sqm (issue #89) • Today’s solution: write strings in your SQLiteOpenHelper … or • Create a migration .sq with unused `CREATE TABLE` statement • Write your migration like any SQL statement • Cons: Unused generated code -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive
stream semantics to SQL operations -
SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive
stream semantics to SQL operations • Updates to the subscribed table(s) will trigger notifications -
SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->
Timber.tag("Database").v(message)) .build(); -
SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->
Timber.tag("Database").v(message)) .queryTransformer() .build(); -
SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->
Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io()); -
SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->
Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io()); db.setLoggingEnabled(boolean); -
SQLBrite – content resolvers SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message
-> Timber.tag("Database").v(message)) .build(); BriteContentResolver db = sqlBrite.wrapContentProvider( contentResolver, Schedulers.io()); db.setLoggingEnabled(boolean); -
SQLBrite – powerful logging QUERY (0ms) tables: [book] sql: SELECT
* FROM book WHERE title = ?1 AND author = ?2 args: ["1984","George Orwell"] EXECUTE SQLiteProgram: INSERT INTO book(title, author) VALUES(?, ?) -
SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books
= db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> { Cursor run = query.run(); // parse data }); } -
SQLBrite – BookRepo.java BriteDatabase db = // ...; QueryObservable books
= db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */); -
SQLBrite – BookRepo.java BriteDatabase db = // ...; QueryObservable books
= db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */); db.insert("book", Book.create("1984", "George Orwell")); db.delete("book", "title = ?", "The Great Gatsby"); db.update("book", ...); BriteDatabase db = // ...; QueryObservable books = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */); db.insert("book", Book.create("1984", "George Orwell")); db.delete("book", "title = ?", "The Great Gatsby"); db.update("book", ...); Will emit updates to subscribers -
SQLBrite – transactions • Can be used to group changes
and be notified only once • transaction.markSuccessful() then triggers emission -
SQLBrite – Kotlin • Yes! • Kotlin extensions methods are
also provided -
SQLBrite – Kotlin module Provides mapTo* extension functions: • Observable<Query>.mapToOne()
• Observable<Query>.mapToOneOrDefault() • Observable<Query>.mapToOptional() • Observable<Query>.mapToList() -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
SQLDelight & SQLBrite CREATE TABLE book ( _id INTEGER NOT
NULL PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT, publishedAt TEXT AS ZonedDateTime ); selectByPublishedAt: SELECT * FROM book WHERE publishedAt = ?; insertRow: INSERT INTO book(title, author, publishedAt) VALUES (?, ?, ?); -
SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel
{ static Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new, new ZonedDateTimeColumnAdapter()); static Mapper<Book> ROW_MAPPER = FACTORY.selectByPublishedAtMapper(); } -
SQLDelight & SQLBrite Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement
query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); } Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); } Observable<Book> getBookByPublishedDate(BriteDatabase db, ZonedDateTime publishedAt) { SqlDelightStatement query = Book.FACTORY.selectByPublishedAt(publishedAt); return db.createQuery(query.tables, query.statement, query.args) .mapToOne(Book.ROW_MAPPER ::map); } -
Traditional Android APIs Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase
db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; } -
Traditional Android APIs Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase
db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; } Book getBookByPublishedDate(SQLiteOpenHelper openHelper, ZonedDateTime publishedAt) { SQLiteDatabase db = openHelper.getReadableDatabase(); String[] projection = { Book.TITLE, Book.AUTHOR, Book.PUBLISHEDAT }; String selection = Book.PUBLISHEDAT + " = ?"; String[] selectionArgs = {FORMATTER.format(publishedAt)}; Cursor cursor = db.query( Book.TABLE_NAME, projection, selection, selectionArgs, null, null, null); Book book = null; if (cursor.moveToNext()) { String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE)); String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR)); String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT)); ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr, ZonedDateTime.FROM); book = Book.create(title, author, parsed); } cursor.close(); return book; } -
SQLDelight & SQLBrite public class BookRepository { private final BriteDatabase
db; private final Book.InsertRow insertRow; public BookRepository(BriteDatabase db) { this.db = db; insertRow = new Book.InsertRow(db.getWritableDatabase()); } public void insert(Book book) { insertRow.bind(book.title(), book.author()); db.executeInsert(insertRow.table, insertRow.program); } } public class BookRepository { private final BriteDatabase db; private final Book.InsertRow insertRow; public BookRepository(BriteDatabase db) { this.db = db; insertRow = new Book.InsertRow(db.getWritableDatabase()); } public void insert(Book book) { insertRow.bind(book.title(), book.author()); db.executeInsert(insertRow.table, insertRow.program); } } public class BookRepository { private final BriteDatabase db; private final Book.InsertRow insertRow; public BookRepository(BriteDatabase db) { this.db = db; insertRow = new Book.InsertRow(db.getWritableDatabase()); } public void insert(Book book) { insertRow.bind(book.title(), book.author()); db.executeInsert(insertRow.table, insertRow.program); } } -
SQLDelight & SQLBrite • SQLDelight and SQLBrite are highly optimized
• Lightweight (~160 methods + generated code) -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
Room • Released this year at Google I/O • Database
part of Architecture Components • Aims for the same goal as to SQLDelight & SQLBrite -
Room • Avoids boilerplate code • Compile-time checked SQL queries
• Allows asynchronous queries Aims for the same goal as to SQLDelight & SQLBrite: More information: Data Persistence In Android: There’s Room For Improvement Florina Muntenescu -
Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite
5. SQLDelight & SQLBrite 6. Room 7. Review -
Review • Type-safe marshalling & unmarshalling • Compile-time SQL validation
• SQL autocompletion • Incentivizes immutability • All SQLite feature-set at your disposal • Migration process is not ideal SQLDelight -
Review • Type-safe marshalling & unmarshalling • Compile-time SQL validation
• SQL autocompletion • Incentivizes immutability • All SQLite feature-set at your disposal • Migration process is not ideal SQLDelight • Content providers support • Real-time UI updates • Agnostic to object mapper or SQL source SQLBrite