DAO Guide for iOS

1. Introduction

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

  1. A class which maps a single record from a table, containing properties corresponding to the fields of the table and some overriden NSObject methods for your convenience.
  2. A Data Access Object (DAO) class, which defines a set of operations that are used to create, read, update, and delete objects from a database.
  3. Default row handler for a table, which converts data from its database representation to the object one.

2. Creating a DAO object

To create a DAO object in your application, you need an object of class conforming to MOSQLiteDataSource protocol.

@protocol MOSQLiteDataSource <NSObject>
- (sqlite3 *)getConnection;
@end
            

You have to provide a class conforming to this protocol. It may be your own one, but you can help yourself with MOSQLiteOpenHelper class provided by us. To use it correctly, you have to create a subclass inheriting from it and implementing configure method, with database version and create/migration scripts. To learn more, check MOSQLiteOpenHelper source code, where its usage is explained in detail.

DAO object may be created either by init or factory method. Look at example for DAO class generated from table book.

id <MOSQLiteDataSource> dataSource = ...;

// init
BookDAO *daoInit = [[BookDAO alloc] initWithDataSource:dataSource];

// factory
BookDAO *daoFactory = [BookDAO daoWithDataSource:dataSource];
            

3. Operations on data

This section describes the operations on data: creating, retrieving, updating, and deleting. For any of these operations, DAO corresponding to the table is required. For example, to delete from the table book you need to have the BookDAO instance.

For the purposes of this DAO Guide, we're going to use a table book.

3.1. Adding records

3.1.1. Adding single record

Create a Book object.

Book *book = [Book book]; //alternatively [[Book alloc] init]
              

Fill it with data.

book.columnTitle = @"A Midsummer Night's Dream";
book.columnIsbn = @"978-0671722791";
book.columnPublicationYear = @1600;
book.columnNumberOfPages = @256;
book.columnGenreId = @1; // we asssume that a genre with the given id already exists
              

Hint

Default prefix for properties in generated classes is column, but you can provide your own property prefix. Note that the property prefix may be an empty string - classes will be still generated, though you have to be careful with Objective-C reserved words.

You can leave the columnId field empty. As it is an integer and a table's primary key, it's going to be set with a value by a database - it's guaranteed by SQLite. Learn more about it here.

Create a DAO object using a data source, as in Section 2.

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
              

Hint

Instead of initializing DAO object for every table, you can create one MODAOProvider object for all DAOs. Example:

MODAOProvider *provider = [MODAOProvider providerWithDataSource:dataSource];
BookDAO *bookDao = [provider bookDAO];
                  

Add a record to the database.

[bookDao insertBook:book];
              

Now, book.columnId should give you the id of the newly added record.

NSLog(@"Book ID: %@",book.columnId);
              

3.1.2. Adding multiple rows at the same time

The insertBookArray 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:

NSMutableArray *books = [NSMutableArray array];
for (int i=0; i < 10; i++) {
    Book *book = [Book book];
    book.columnTitle = [NSString stringWithFormat:@"Book no:%i", i];
    book.columnIsbn = [NSString stringWithFormat:@"1234567%i", i];
    book.columnPublicationYear = @2015;
    book.columnNumberOfPages = @(300 + i);
    book.columnGenreId = @1;
    [books addObject: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 by one. You can do the following instead.

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
[bookDao insertBookArray: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, read and display contents of an entire table:

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSArray *books = [bookDao getBookArray];

for (Book *book in books) {
    NSLog(@"%@", book);
}
              

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 using ORDER BY clause.

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSArray *sortedBooks = [bookDao getBookArrayOrderAscByColumn:bookDao.ID];

for (Book *book in sortedBooks) {
    NSLog(@"%@", sortedBooks);
}
              

The list has been sorted in an ascending order by id, for descending order there is a method getBookArrayOrderDescByColumn:.

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSArray *sortedBooks = [bookDao getBookArrayOrderDescByColumn:bookDao.ID];

for (Book *book in sortedBooks) {
    NSLog(@"%@", sortedBooks);
}
              

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 = [BookDAO daoWithDataSource:dataSource];
NSNumber *currentBookId = @1;
Book *book = [bookDao getById:currentBookId];
NSLog(@"%@", book);
                
3.2.2.2. Using an alternate key

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

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSString *isbn = @"sample-isbn-code";

Book *book = [Book book];
book.columnTitle = @"Sample title";
book.columnIsbn = isbn;
book.columnPublicationYear = @2000;
book.columnNumberOfPages = @42;
book.columnGenreId = @1;

bookDao.insertBook(book);

Book *bookFromDb = [bookDao getByIsbn:isbn];
NSLog(@"%@", bookFromDb);
                
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 a genre having a book. The foreign key, genre_id, references the genre object.

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSNumber *currentBookId = @1;
Book *book = [bookDao getById:currentBookId];

GenreDAO *genreDao = [GenreDAO daoWithDataSource:dataSource];
Genre *genre = [genreDao getByBook:book];
NSLog(@"%@", genre);
                

3.2.3. Query language

DAO methods are not the only way of retrieving records; a 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 Objective-C classes that represent SQL and SQL queries in Objective-C. 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 = [BookDAO daoWithDataSource:dataSource];
NSArray *books = [bookDao getBookArrayWhere:[bookDao.ID isLessThanInteger:10]];

for (Book *book in books) {
    NSLog(@"%@", book);
}
              

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 = [BookDAO daoWithDataSource:dataSource];

NSNumber *currentBookId = @1;
Book *book = [bookDao getById:currentBookId];
book.columnTitle = @"Updated title";
bookDao.updateBook(book);

Book *updatedBook = [bookDao getById:currentBookId];
NSLog(@"%@", updatedBook);
            

3.3.1. A general mechanism

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 = [BookDAO daoWithDataSource:dataSource];

NSNumber *currentBookId = @1;
Book *book = [bookDao getById:currentBookId];

[bookDao deleteBook:book];
              

Or, you can use the primary key, which is even easier:

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];

NSNumber *currentBookId = @1;

[bookDao deleteById:currentBookId];
              

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 = [BookDAO daoWithDataSource:dataSource];

[bookDao deleteWhere:[bookDao.ID isLessThanInteger:20]];
              

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

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
[bookDao deleteWhere:[bookDao.TITLE likeString:@"%sample%"]];
              

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

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];

NSArray *books = [bookDao getBookArrayWhere:[bookDao.ID isLessThanInteger:10]];

[bookDao deleteBookArray:books];
              
3.4.2.1. A general mechanism

Just as it was in the case of an update, you can use the query language to remove records too. You can find a description of the query language in the Query Language Guide.

4. Aggregate functions

To retrieve a book record using a primary key, you would like to check the range of primary keys:

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
        
// SELECT MIN(id) FROM book
NSNumber *min = [bookDao invokeFunction:@"MIN" withArgument:bookDao.ID];

// SELECT MAX(id) FROM book
NSNumber *max = [bookDao invokeFunction:@"MAX" withArgument:bookDao.ID];
        
NSLog(@"min:%@ and max:%@", min, max);
          

Make sure you notice that the name of the function is supplied as a string. By using the string name of the function, you are allowed to use either built-in or custom database functions.

5. Support for BLOB fields

Fields of type BLOB are nowhere to be found in table objects. They can be accessed using a DAO object. The book table that has been used in the previous examples contains such a field: cover_image - an image of the book's cover.

5.1. Inserting

Here is an example showing you how to add a book and its cover image to the database. First, you will add a record to the database, as follows:

Book *book = [Book book];
book.columnTitle = @"Another sample title";
book.columnIsbn = @"123456789";
book.columnPublicationYear = @2015;
book.columnNumberOfPages = @42;
book.columnGenreId = @1;

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
[bookDao insertBook:book];
            

Next, you will populate the description field, as follows:

NSData *image = [NSData dataWithContentsOfFile:@"/absolute/path/to/image.png"];
[bookDao setCoverImage:image forBook:book];
            

To retrieve a book record using a primary key, you would use the following code:

[bookDao getCoverImageById:book.columnId];
            

Warning

Structure of accessing BLOBs in Vertabelo Mobile ORM requires BLOB fields in database to be nullable.

5.2. Editing

You can modify the contents of a field of the type BLOB as follows:

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
NSNumber *currentBookId = @1;

NSData *image = [NSData dataWithContentsOfFile:@"/absolute/path/to/image.png"];
[bookDao setCoverImage:image byId:currentBookId];
            

6. Summary

You've seen an overview of DAO methods - DAO's role is to provide access to database tables. They have methods to perform basic operations on data: create, retrieve, update, and delete (CRUD). DAO methods give you also access to BLOB columns in tables and allow you to invoke aggregate functions. To learn more about Vertabelo Mobile ORM check Query Language Guide.