Intel IXC1100 IXC1100

Writing out a SQL query can often be much more efficient than relying on the ORM to build and execute the needed queries. This comes into play when dealing with data stored in multiple tables, as in the case with foreign objects discussed earlier. In per- formance-critical areas, it’s more efficient to write a SQL join rather than relying on the DAO methods to automatically or selectively refresh objects.

Performing a raw SQL query involves first obtaining a DAO, and then using one overload of the queryRaw() method. Each signature of the queryRaw() method expects a variable number of strings as the last parameter. This is to allow developers to parameterize queries and have the ORM handle escaping the values. This is extremely important when performing queries based on user input; otherwise, your database will be open to SQL injection attacks.

The overloads of queryRaw() allow us to fine-tune exactly what we receive as the result for our queries. Our choices are

 A list of string arrays, one array per result, in which each array holds the raw string values of the columns selected

 A list of object arrays, one array per result, which are typed based on our input  A list of fully baked class instances, given a parameterized RawRowMapper

We’ll demo the RawRowMapper case, because it involves the most explanation, yet often results in code that is easiest to reuse. Suppose we want a list of all the articles in the database along with their category names (along with IDs). Using the ORM to perform this operation would result in an amount of queries that is proportional to the num- ber of entries in the database. We can do better by using one query that joins three tables, namely, the tables for Article, Category, and the cross-reference class ArticleCategory. Our query will be this:

select a.title, a._id, c.name, c._id from articles a, categories c, articlecategories ac

where ac.article_id = a._id and ac.category_id = c._id; First, let’s define a class to hold our results:

class ArticleCategoryName {

public String articleTitle, categoryName; public Integer articleId, categoryId; }

Next, we implement the RawRowMapper, which will be invoked on each record returned by our query. Its job is to turn the raw string array representing the columns returned by the database into an instance of our desired type, which is Article- CategoryName in this case (note the use of generics):

class ArticleWithCategoryMapper

implements RawRowMapper<ArticleCategoryName> { @Override

public ArticleCategoryName mapRow(String[] columnNames, String[] resultColumns) throws SQLException {

ArticleCategoryName result = new ArticleCategoryName(); result.articleTitle = resultColumns[0]; result.articleId = Integer.parseInt(resultColumns[1]); result.categoryName = resultColumns[2]; result.categoryId = Integer.parseInt(resultColumns[3]); return result; } }

When parsing results in the mapRow() method, it’s important to check for data consis- tency. Putting all the components together, we can get a list of all the article names and their categories using this:

GenericRawResults<ArticleCategoryName> rawResults;

String query = "select a.title, a._id, c.name, c._id from articles a, categories c, articlecategories ac

where ac.article_id = a._id and ac.category_id = c._id";

ArticleWithCategoryMapper mapper = new ArticleWithCategoryMapper(); rawResults = articleDao.queryRaw(query, mapper);

List<ArticleCategoryName> results = rawResults.getResults();

41.10 Transactions

Transactions are a key component in database operations, because they allow multiple statements to be treated as a single atomic unit. A transaction guarantees that one of two possibilities will happen:

 All statements will be executed and committed if no errors are encountered.  If an error is encountered at any point in a transaction, the entire transaction is

rolled back.

As a convenience, ORMLite provides a class called TransactionManager that wraps the details of beginning a transaction, marking one as successful, and ending a transac- tion. A TransactionManager exposes just one interesting method, which is call- InTransaction(). This method accepts a Callable, which is just like a Runnable, except Callable has a return value.

To run a transaction, we choose to expose this feature as a method of our Orm- LiteSqliteOpenHelper subclass, DatabaseHelper:

public class DatabaseHelper extends OrmLiteSqliteOpenHelper { public <T> T callInTransaction(Callable<T> callback) {

try {

TransactionManager manager;

manager = new TransactionManager(getConnectionSource()); return manager.callInTransaction(callback);

} catch (SQLException e) {

Log.e(TAG, "Exception occurred in transaction.", e); throw new RuntimeException(e);

} } }

147

Building databases with ORMLite

Running a transaction is as simple as putting our database operations inside a Call- able. Here’s an example method that performs two writes inside a transaction and returns the resulting Article:

public Article createArticleInCategory(Context context,

final String title, final String text, final Category category) { final DatabaseHelper helper = DatabaseHelper.getInstance(context); return helper.callInTransaction(new Callable<Article>() {

@Override

public Article call() throws SQLException {

Article article = new Article(new Date(), text, title);

Make new instance of Article

Dao<Article, Integer> articleDao; articleDao = helper.getArticleDao(); Add it to database using a DAO articleDao.create(article);

Dao<ArticleCategory, Void> articleCategoryDao;

Add cross- reference entry

articleCategoryDao = helper.getArticleCategoryDao();

articleCategoryDao.create(new ArticleCategory(article, category)); return article;

} }); }

We chose to use a transaction in this case because we want both write operations to succeed, or in the case of failure, to have no writes committed. This approach is rec- ommended when performing multiple writes, for data consistency. Additionally, trans- actions can in some cases increase the performance of a combination of statements, especially a mix of reads and writes.

41.11 The bottom line

ORMLite can greatly simplify database development in an Android application. It can be used to create an entire database instance just by properly annotating your Java classes. It also handles mapping database queries to instances of your classes, remov- ing the need for boilerplate code.

For performance-critical operations that involve multiple tables, consider writing join statements by hand, and use the queryRaw() method on a DAO. This, in practice, will be much more efficient than querying additional tables one by one, as in the case of ORM-generated statements. Furthermore, consider using transactions to batch together several writes to ensure data consistency. Last, a singleton pattern is encour- aged for your subclass of SQLiteOpenHelper to eliminate problems when writing from multiple threads.

41.12 External links

http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_1.html

http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_2.html#IDX195 http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

Hack 42

Creating custom functions in SQLite