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

Don't fear SQL (360AnDev 2017)

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

July 14, 2017
Tweet

More Decks by Leandro Favarin

Other Decks in Programming

Transcript

  1. Don’t fear SQL A better way to store and handle

    data with SQLBrite & SQLDelight Leandro Favarin
  2. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  3. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  4. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful (cover incredibly amount of use cases)
  5. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful (cover incredibly amount of use cases) • Efficient
  6. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast
  7. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable
  8. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful (cover incredibly amount of use cases) • Efficient • Fast • Stable • Cross-platform
  9. SQLite in Android – cons • It’s hard, because: •

    Cursors • Verboseness • No type-safety Implemented as an interface on top of a native SQLite bundle
  10. 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
 );
  11. 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";
  12. 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";
  13. 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";
  14. 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
 );
  15. 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();
  16. 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();
  17. 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();
  18. 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();
  19. 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();
  20. 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();
  21. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  22. 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)
  23. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities
  24. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance
  25. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers
  26. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Objects are usually mutable
  27. 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
  28. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  29. 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
  30. 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
  31. SQLDelight – build.gradle buildscript {
 repositories {
 mavenCentral()
 }
 dependencies

    {
 classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
 }
 }
 
 apply plugin: 'com.squareup.sqldelight'
  32. 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
  33. 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
  34. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java

    ... - sqldelight/ - com/example/project/models/ - Book.sq ...
  35. SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER );
  36. 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;
  37. 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 (?, ?, ?);
  38. 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(); ... }
  39. SQLDelight – … generates: long _id(); @NonNull String title(); @NonNull

    String author(); @Nullable Long num_chapters();
  40. 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(); ... }
  41. 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) ); } }
  42. SQLDelight – Book.java @AutoValue public abstract class Book implements BookModel

    { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); }
  43. 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(); }
  44. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =

    Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } }
  45. 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; } }
  46. SQLDelight – projections, joins Each SELECT statement will have: •

    an interface • mapper • method to create a new instance of the mapper
  47. 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 );
  48. 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 );
  49. 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 );
  50. 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); } }
  51. SQLDelight – views • Same treatment in generated code as

    tables with their own model interface
  52. 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 (?, ?, ?);
  53. 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 }
  54. 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
  55. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  56. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations • Updates to the subscribed table(s) will trigger notifications
  57. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder()
 .logger(message ->

    Timber.tag("Database").v(message))
 .queryTransformer()
 .build();
  58. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->

    Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
  59. 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);
  60. 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);
  61. 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(?, ?)
  62. 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 }); }
  63. SQLBrite – BookRepo.java BriteDatabase db = // ...; QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> /* ... */);
  64. 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
  65. SQLBrite – transactions • Can be used to group changes

    and be notified only once • transaction.markSuccessful() then triggers emission
  66. SQLBrite – Kotlin module Provides mapTo* extension functions: • Observable<Query>.mapToOne()

    • Observable<Query>.mapToOneOrDefault() • Observable<Query>.mapToOptional() • Observable<Query>.mapToList()
  67. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  68. 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 (?, ?, ?);
  69. 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(); }
  70. 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); }
  71. 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; }
  72. 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; }
  73. 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);
 }
 }
  74. SQLDelight & SQLBrite • SQLDelight and SQLBrite are highly optimized

    • Lightweight (~160 methods + generated code)
  75. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  76. Room • Released this year at Google I/O • Database

    part of Architecture Components • Aims for the same goal as to SQLDelight & SQLBrite
  77. 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
  78. Outline 1. SQLite 2. ORM libraries 3. SQLDelight 4. SQLBrite

    5. SQLDelight & SQLBrite 6. Room 7. Review
  79. 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
  80. 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