Sql2o on Android

Copy/pasted from a comment by migel on issue #140.

I've tested Sq2o with both H2 and Sqlite. Using H2 is quite simple, just put the sql2o-1.5.2.jar and h2-1.4.181.jar into the libs folder of the Android project. Then in onCreate (not shown below but you could also use flyway http://flywaydb.org/documentation/api/ to manage schema upgrades):

File dbDir = context.getDir("db", 0);
dbUrl = "jdbc:h2:file:" + dbDir.getPath() + "/mydb" +
        ";FILE_LOCK=FS;PAGE_SIZE=1024;CACHE_SIZE=8192";
dbUser = "sa";
sql2o = new Sql2o(dbUrl, dbUser, "");

The options for H2 are as recommended in http://h2database.com/html/tutorial.html#android .

Android doesn't support try with resources prior to API level 19 so you have to use the following pattern instead:

Connection con = sql2o.open();
try {

}
finally {
    con.close();
}

I've tested simple queries; the ones I've found to work include inserts and updates with addParameter() and bind(). Select queries also work for POJOs using fields and using accessors functions; addColumnMapping also works.

Using sqlite is more complicated. Android doesn't include a JDBC driver for the built in sqlite. There is a project https://github.com/SQLDroid/SQLDroid that implements a JDBC driver above the android.database.* classes. To use it put the sqldroid-1.0.3.jar in the libs folder.

So far I've run into issues with this driver that are related to generated keys: it doesn't implement the Connection.prepareStatement(String sql, int autoGeneratedKeys) and PreparedStatement.getGeneratedKeys() methods yet.

The main branch of SqlDroid seems to have a fix for the first missing method but for now you can work around it by creating a quirks class:

import java.util.Map;

import org.sql2o.converters.Converter;
import org.sql2o.quirks.NoQuirks;

public class SqlDroidQuirks extends NoQuirks {
    public SqlDroidQuirks() {
        super();
    }
    
    public SqlDroidQuirks(Map<Class, Converter> converters) {
        super(converters);
    }
    
    @Override
    public boolean returnGeneratedKeysByDefault() {
        return false;
    }
}

And to use it:

SQLiteDatabase sqliteDB;
sqliteDB = context.openOrCreateDatabase("mydb", 0, null);
dbUrl = "jdbc:sqlite:" + sqliteDB.getPath();
dbUser = "";
sqliteDB.close();

sql2o = new Sql2o(dbUrl, dbUser, "", new SqlDroidQuirks());

These missing methods also mean you can't use Sql2o Connection.getKey() to retrieve the generated key. I've opened an issue about it https://github.com/SQLDroid/SQLDroid/issues/50. For now you can use a workaround:

Connection con = sql2o.beginTransaction();
try {
    con.createQuery("INSERT ...")
       .executeUpdate();
    int key = con.createQuery("SELECT last_insert_rowid();")
                 .executeScalar(Integer.class);
    con.commit();
    return key;
}
finally {
    con.close();
}

Finally, the Android log complains about missing some sun.reflect.* classes and the java.beans.Introspector.decapitalize method but so far it doesn't seem to affect anything.