Android Database Rundown

This talk with provide an overview of different ways of using databases on Android. We’ll cover the basics of creating, using, and managing databases on Android. There will be demonstrations of everything from Google sanctioned solutions like “vanilla” SQLite, Room from Architecture Components, and the Firebase Realtime Database, to third party database solutions and ORMs including Realm, sqldelight, sqlbrite, ORMLite, and more.

Presented at GDG Omaha: https://www.meetup.com/GDGOmaha/events/244166212/

Transcript

1. Android Database Rundown @BenWicks
2. Overview - Why - Past Talks - NoSQL, SQLite, and ORMs… oh my… a Definition of Terms - Top 10 ORMs Comparison - Debugging Tools
3. Why
4. Why
5. June 2016 Past Talks
6. August 2017 Past Talks
7. NoSQL Databases
8. Definition of Terms: SQL vs. ORM Raw SQLite communicates directly with a database. ORMs abstract database access into your preferred language.
9. ORM Graphic
10. createStatement = database.compileStatement( "CREATE TABLE IF NOT EXISTS `" + LibraryTable.NAME + " (" + "`id` INTEGER PRIMARY KEY AUTOINCREMENT, " + `" + LibraryTable.Cols.ADDRESS + "` TEXT, `" + LibraryTable.Cols.NAME + "` TEXT)");
11. insertStatement = database.compileStatement( "INSERT INTO " + LibraryTable.NAME + " (" + LibraryTable.Cols.ADDRESS + ", " + LibraryTable.Cols.NAME + ") VALUES (?, ?)" );
12. public void save(Library c) { insertStatement.clearBindings(); insertStatement.bindString(1, c.getAddress()); insertStatement.bindString(2, c.getName()); insertStatement.executeInsert(); }
13. public void write(List<Library> libraries) { libraryDao.beginTransaction(); for (Library library: libraries) { libraryDao.save(library); } libraryDao.endTransaction(); }
14. public void write(List<Library> libraries) { realm.beginTransaction(); realm.copyToRealm(libraries); realm.commitTransaction(); }
15. SQLite Advantages & Disadvantages Encourages separation of database and app logic. Faster and more expressive than ORM. Not in your “native” programming language. More logic to maintain. No compile-time checks.
16. ORM Advantages vs. Disadvantages Lots of abstractions: DB design, prepared statements, switching/upgrading databases, etc. Less boilerplate and no “reinventing the wheel”. Great for simple CRUD operations. Quick to get started. Define a class to represent a DB entity and use it throughout the app. The ORM will convert DB row ↔ object for you. Less logic to maintain. You get compile-time checks. Slower than SQL. More memory usage than SQL. Have to learn quirky bugs for the specific ORM.
17. Top Options Comparison
18. Top Options Comparison Honorable mentions ObjectBox, Squidb, Cupboard, Squeaky, StorIO, Paper, AndrORM, OHibernate, MemoryORM, ORMDroid
19. Top Options Comparison Results
20. Library write read update delete SQLite 50 436 63 80 Room 131 699 170 109 GreenDAO 81 1238 117 97 Realm 151 29 1079 723 ActiveAndroid 3123 930 2293 2423 DBFlow 97 757 459 186 SugarORM 245 842 252 152 Requery 87 1501 147 129 ORMLite 151 666 122 105 Sprinkles 5766 1050 6364 605 Freezer 248 5430 240 4797 Top Options Comparison Results Cont.
21. Top Options Comparison Results
22. Top Options Comparison Results Cont. Library write read update delete SQLite 386 2155 192 284 Room 562 3201 717 403 GreenDAO 357 5552 455 274 Realm 698 688 19666 9180 ActiveAndroid 14671 4165 15958 13023 DBFlow 360 3534 3124 1044 SugarORM 1402 4129 1467 1003 Requery 461 8057 861 802 ORMLite 445 3836 857 811 Sprinkles 25978 4334 65579 2428 Freezer 1337 78982 2221 22104
23. Top Options Comparison Results
24. Top Options Comparison Results Cont. Library write read update delete SQLite 1146 2313 213 318 Room 1330 3532 790 507 GreenDAO 598 5905 504 315 Realm 1522 210 21129 10006 ActiveAndroid 17213 4653 19303 14642 DBFlow 1129 4653 5204 1268 Sugar 2204 4397 1702 1197 Requery 1368 8002 886 763 ORMLite 1563 3426 724 728 Sprinkles 27774 4526 37705 2519 Freezer 3255 134942 1887 29515
25. SQLite write read update delete 1146 2313 213 318 *Balanced Benchmark
26. Room @Entity( foreignKeys = {@ForeignKey( entity = Library.class, parentColumns = "id", childColumns = "library_id")}, indices = {@Index("library_id")} ) public class Book { @PrimaryKey(autoGenerate = true) public long id; private String author; private String title; private int pagesCount; private int bookId; @ColumnInfo(name="library_id") private long libraryId; … } write read update delete 1330 3532 790 507
27. Room @Dao public interface BookDao { @Query("select * from Book where id = :id") Book loadBookById(int id); @Query("SELECT * FROM Book") List<Book> findAll(); @Query("SELECT * FROM Book LIMIT :limit") List<Book> findAll(int limit); @Insert(onConflict = IGNORE) void insert(Book book); ... write read update delete 1330 3532 790 507 ... @Update(onConflict = REPLACE) void update(Book book); @Query("DELETE FROM Book") void deleteAll(); @Delete void delete(Book book); };
28. Room @Dao public interface RxBookDao { @Query("SELECT * from Book where id = :id LIMIT 1") public Flowable<Book> loadBookById(int id); } write read update delete 1330 3532 790 507
29. write read update delete 598 5905 504 315 @Entity public class Book { @Id private Long id; private String author; private String title; private int pagesCount; private int bookId; private long libraryId; @ToOne(joinProperty = "libraryId") private Library library; public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { libraryDao.insertInTx(libraries); bookDao.insertInTx(books); personDao.insertInTx(persons); }
30. write read update delete 1522 210 21129 10006 class Book extends RealmObject { private String author; private String title; private int pagesCount; private int bookId; private Library library; public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { realm.beginTransaction(); realm.copyToRealm(libraries); realm.copyToRealm(books); realm.copyToRealm(persons); realm.commitTransaction(); }
31. Active Android @Table(name = "Book") public class Book extends Model { @Column(name = "author") private String author; @Column(name = "title") private String title; @Column(name = "pagesCount") private int pagesCount; @Column(name = "bookId") private int bookId; @Column(name = "library") private Library library; write read update delete 17213 4653 19303 14642 public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { ActiveAndroid.beginTransaction(); try { for (Library lib : libraries){ lib.save(); } for (Book book : books){ book.save(); } for (Person person : persons){ person.save(); } ActiveAndroid.setTransactionSuccessful(); } finally{ ActiveAndroid.endTransaction(); } }
32. write read update delete 1129 4653 5204 1268 @Table(database = LibrariesDB.class) public class Book extends BaseModel{ @Column @PrimaryKey(autoincrement = true) long id; @Column String author; @Column String title; @Column int pagesCount; @Column int bookId; @Column @ForeignKey Library library; public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { FlowManager.getDatabase(LibrariesDB.class) .executeTransaction(new ITransaction() { @Override public void execute( DatabaseWrapper databaseWrapper) { for (Library library: libraries) { library.save(); } for (Book book: books) { book.save(); } for (Person person: persons) { person.save(); } } }); }
33. Sugar ORM public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { Library.saveInTx(libraries); Book.saveInTx(books); Person.saveInTx(persons); } class Book extends SugarRecord { String author; String title; int pagesCount; int bookId; Library library; } write read update delete 2204 4397 1702 1197
34. @Entity public class AbstractBook { @Key @Generated int id; String author; String title; int pagesCount; int bookId; @ManyToOne Library library; } write read update delete 1368 8002 886 763 public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { dataStore.insert(libraries); dataStore.insert(books); dataStore.insert(persons); }
35. ORMLite @DatabaseTable(tableName = "books") public class Book { @DatabaseField(generatedId = true) int id; @DatabaseField String author; @DatabaseField String title; @DatabaseField int pagesCount; @DatabaseField int bookId; @DatabaseField(foreign = true) Library library; write read update delete 1563 3426 724 728 public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { try { libraryDAO.callBatchTasks( new Callable<Object>() { @Override public Object call() throws Exception { for (Library library: libraries) { libraryDAO.create(library); } return null; } }); } catch (SQLException e) { e.printStackTrace(); } // Repeat for books and people! }
36. Sprinkles @Table("Book") public class Book extends Model { @AutoIncrementPrimaryKey @Column("_id") private long id; @Column("author") private String author; @Column("title") private String title; @Column("pagesCount") private int pagesCount; @Column("bookID") private int bookId; @ForeignKey("Library(_id)") @Column("library_id") private long libId; } write read update delete 27774 4526 37705 2519 public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { Transaction t = new Transaction(); try { for (Library lib : libraries) { lib.save(t); } for (Book book : books) { book.save(t); } for (Person person : persons) { person.save(t); } t.setSuccessful(true); } finally { t.finish(); } }
37. Freezer public void writeComplex( List<Library> libraries, List<Book> books, List<Person> persons ) { libraryEntityManager.add(libraries); bookEntityManager.add(books); personEntityManager.add(persons); } @Model public class Book { @Id long id; String author; String title; int pagesCount; int bookId; Library library; } write read update delete 3255 134942 1887 29515
38. Others
39. Debugging Tools
40. Debugging Tools
41. ? Questions
42. Sources http://greenrobot.org/android/sqlite-orms-nosql-what-android-developers-use-and-why/ http://stevenhorvatin.com/2016/09/22/database-query-orm-vs-sql/ https://github.com/AlexeyZatsepin/Android-ORM-benchmark http://kpgalligan.tumblr.com/post/133281929963/my-talk-at-droidcon-uk