Vertabelo Mobile ORM - DAO Guide for Android

1. Introduction

This document presents the rules of using generated code, which maps a database structure to Java classes of Android application. Based on the defined physical database schema, there are three files generated for each database table:

  • A class which maps a single record from a table. It is a simple Java POJO containing properties corresponding to the fields of the table together with getter and setter methods for them
  • Data Access Object (DAO) interface, which defines a set of operations that are used to create, read, update, and delete objects in/from a database
  • A class implementing the methods defined in the DAO interface

2. How to start

2.1. Creating a DAO object

To create a DAO object in your application, we need an implemetation of pl.epoint.mobiorm.android.runtime.util.SQLiteDataSource interface which is wrapper for android.database.sqlite.SQLiteDatabase object.

public interface SQLiteDataSource {
    SQLiteDatabase getSQLiteDatabase();
}
                

Creation of SQLiteDataSource may vary depending on chosen implementation. We provide you with our implementation: pl.epoint.mobiorm.android.runtime.util.SimpleSQLiteDataSource.

public class SimpleSQLiteDataSource implements SQLiteDataSource {

    private SQLiteDatabase database;

    public SimpleSQLiteDataSource(SQLiteDatabase database) {
        this.database = database;
    }

    @Override
    public SQLiteDatabase getSQLiteDatabase() {
        return database;
    }
}
                

To create a DAO object, you can use the following syntax:

SQLiteDatabase db = ...;
SQLiteDataSource dataSource = new SimpleSQLiteDataSource(db);
BookDAO bookDao = new BookDAOImpl(dataSource);
                

2.2. Simple SQLite Open Helper

SimpleSQLiteOpenHelper is a class which helps creating SQLite database. To use it you have to put creation and migration scripts in res/raw/ directory of your Android application and create a subclass implementing configure() method where you specify which SQL scripts should perform at creation and migration time. In the simpliest case, such a subclass can look as following:

public class DemoSQLiteOpenHelper extends SimpleSQLiteOpenHelper {}

    protected static final String DB_NAME = "library";
    protected static final Integer VERSION = 1;

    public DatabaseOpenHelper(Context context) {
        super(context, DB_NAME, VERSION);
    }

    @Override
    public void configure() {
        this.addCreationScript(R.raw.create);
    }
}
                

where R.raw.create is the resource id of the creation script. As SimpleSQLiteOpenHelper implements SQLiteDataSource, you can use DemoSQLiteOpenHelper to create DAOs too.

DemoSQLiteOpenHelper helper = new DemoSQLiteOpenHelper(context);
BookDAO bookDao = new BookDAOImpl(helper);
                

2.3. DAO Provider

DAOProvider is an utility class which helps getting access to DAO objects. It's generated in addition to table-specific classes and interfaces. Usage of DAOProvider can reduce number of created instances and simplify a code. Here is an example:

DemoSQLiteOpenHelper helper = new DemoSQLiteOpenHelper(context);
DAOProvider provider = new DAOProvider(helper);
BookDAO bookDAO = provider.getBookDAO();
                 

3. Operations on data

Vertabelo Mobile ORM enables you to create, retrieve, update and delete data from database. For any of these operations, DAO that corresponds to the table is required. For example, to delete from the table book, you need to have the BookDAO instance.

The code samples use table book, which has the following fields:

  • id - primary key
  • title - title
  • isbn - ISBN, International Standard Book Number
  • publication_year - year of publication
  • number_of_pages - number of pages
  • genre_id - id of book genre
  • cover_image - cover image
  • description - description of the book

3.1. Adding records

3.1.1. A single record

The samples begin with adding a single record.

Create a POJO object.

Book book = new Book();
                    

Fill it with data.

book.setTitle("Foo bar");
book.setIsbn("123-" + System.currentTimeMillis());
book.setGenreId(1);
book.setPublicationYear(2000);
book.setNumberOfPages(200);
                    

You should leave the id field empty, as it is going to be set with a value from a sequence when the object is saved in the database.

Create a DAO object, using the data source and DBAdapter, as in Section 1.0, Introduction.

BookDAO bookDao = new BookDAOImpl(dataSource);
                    

Add a record to the database.

bookDao.insert(book);
                    

Now calling book.getId() should give you the id of the newly added record.

long id = book.getId();
                    

3.1.2. Adding multiple rows at the same time

The insert method makes it possible to insert multiple rows of data at the same time. For example, assume that in one part of the application someone creates a list of books, as follows:

List<Book> books = new ArrayList<Book>();
for(int i = 1; i < 11; i++) {
   Book book = new Book();
   book.setAuthorId(1); 
   book.setTitle("Title " + i);
   book.setIsbn("123-"  + i + " " + System.currentTimeMillis());
   book.setGenreId(1);
   book.setNumberOfPager(i*100);
   book.setPublicationYear(2000 + i)
   books.add(book);
}
                    

If, in another part of the application, you want to add all these books to the database, there is no need to iterate over the list, adding books one at the time. You can do the following instead:

BookDAO bookDao = new BookDAOImpl(dataSource);
bookDao.insert(books);
                    

3.2. Retrieving records

You have inserted a couple of records, now it is time to retrieve them from the database.

3.2.1. An entire table

Database rows can be retrieved in many ways. To start with, let's read contents of an entire table:

BookDAO bookDao = new BookDAOImpl(dataSource);
List<Book> all = bookDao.getBookList();
                    

The order in which the records appear is determined by the database. In other words, you cannot rely on them being sorted in any particular way. If you want, you can force a particular order:

BookDAO bookDao = new BookDAOImpl(dataSource);
List<Book> sorted = bookDao.getBookList(BookDAO.ID, OrderByDirection.DESC);
                    

The list has been sorted in a descending order by id (parameter OrderByDirection.DESC indicates a descending sort).

3.2.2. A single record

3.2.2.1. Using a primary key

You can retrieve a single record if you know its id:

BookDAO bookDao = new BookDAOImpl(dataSource);

Integer currentBookId = 1;
Book book = bookDao.getById(currentBookId);
                    
3.2.2.2. Using an alternative key

The book table has another unique identifier, ISBN, so you can use it to retrieve a record:

BookDAO bookDao = new BookDAOImpl(dataSource);
String isbn = "Sample-ISBN-" + System.currentTimeMillis();

Book book = new Book();
book.setAuthorId(1);
book.setTitle("Title");
book.setIsbn(isbn);
book.setGenreId(1);
book.setPublicationYear(2002);
book.setNumberOfPages(300);
bookDao.insert(book);

Book bookFromDb = bookDao.getByIsbn(isbn);
                    
3.2.2.3. Using a foreign key

Generated DAO classes contain information about the relationships between tables. This means that you can retrieve a record using a foreign key.

The code below shows how to retrieve an author having a book. The foreign key, author_id, references the author object.

BookDAO bookDao = new BookDAOImpl(dataSource);

Integer currentBookId = 1;
Book book = bookDao.getBook(currentBookId);

GenreDAO genreDao = new GenreDAOImpl(dataSource);
Genre genre = genreDao.getDao(book);
                    

3.2.3. Query language

DAO methods are not the only way of retrieving records. Query language can be used for more complicated queries. The query language allows you to create queries, just as SQL does.

The query language is a set of Java classes that represent SQL and SQL queries in Java. You can find a description of the query language in the Query Language Guide.

In the following example, you will retrieve a subset of records with an id less than 10:

BookDAO bookDao = new BookDAOImpl(dataSource);

List<Book> books = bookDao.getBookList(BookDAO.ID.lt(10));
                    

3.3. Updating records

You have just practiced inserting and retrieving records. Now you will update records in a database using DAO. In the following example, you will modify a single record in a table:

BookDAO bookDao = new BookDAOImpl(dataSource);

Integer currentBookId = 1;
Book book = bookDao.getByPK(currentBookId);
book.setTitle("Updated title " + System.currentTimeMillis());
bookDao.update(book);
                  

Note that if you would like to modify multiple (thousands, millions) records in a one query, it is much better to use the query language, rather than using DAO's update method several times to update several different rows.

3.4. Deleting records

With the passing of time, some records become obsolete.

3.4.1. A single record

Here is an example of removing a record using its corresponding DAO object:

BookDAO bookDao = new BookDAOImpl(dataSource);

Integer currentBookId = 1;
Book book = bookDao.getBook(currentBookId);
        
bookDao.delete(book);
                    

3.4.2. Multiple records in one go

Just as you have seen in the example for retrieving database rows, it is possible to remove several records in one go. In the following example, you will delete all books with an identifier smaller than 20:

BookDAO bookDao = new BookDAOImpl(dataSource);
bookDao.delete(BookDAO.ID.lt(20));
                    

Or, as in this example, you can delete all books which have the word 'sample' in their description:

BookDAO bookDao = new BookDAOImpl(dataSource);
bookDao.delete(BookDAO.ISBN.like("sample"));
                    

You can also remove multiple chosen records if you have the corresponding POJO objects:

BookDAO bookDao = new BookDAOImpl(dataSource);

List<Book> books = bookDao.getBookList(BookDAO.ID.lt(10));

bookDao.delete(books);
                    

Just as it was in the case of an update, you can use the query language to remove records. Its description can be found in Query Language Guide.

4. Support for BLOB fields

Fields of type BLOB are nowhere to be found in POJO objects. They can be accessed using a DAO object. Note that in order to work correctly, they cannot be marked as NOT NULL columns. In opposite case inserting row through DAO would break SQL constraint because POJO objects don't contain any BLOB fields.

Example:

BookDAO bookDao = new BookDAOImpl(dataSource);
Book book = bookDao.getBook(1);
byte[] image = bookDAO.getCoverImage(book);
                

5. Summary

You've seen an overview of DAO methods. DAOs provide access to database tables. They give methods to perform basic operations on data: create, retrieve, update, and delete (CRUD). In addition, DAO methods give access to BLOB columns in tables.