Basic Android support

[migel]

I've tried to use Sql2o in Android. Initially it caused my application to crash. In the log the dalvikvm complained that the Sql2o class has dependencies on several javax.naming.* classes which it didn't find (turns out they are not implemented on the Android platform).
I've looked at the source of the Sql2o class (version 1.5.1) and the dependencies on javax.naming.* are used only in one static function (_getJndiDatasource) which in turn is used by one of the constructors. I've moved the function to its own source file JndiDatasource.java:

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class JndiDatasource {
    private final static Logger logger = LocalLoggerFactory.getLogger(Sql2o.class);

    public static DataSource get(String jndiLookup) {
        Context ctx = null;
        DataSource datasource = null;

        try {
            ctx = new InitialContext();
            datasource = (DataSource) ctx.lookup(jndiLookup);
        }
        catch (NamingException e) {
            throw new RuntimeException(e);
        }
        finally {
            if (ctx != null) {
                try {
                    ctx.close();
                }
                catch (Throwable e) {
                    logger.warn("error closing context", e);
                }
            }
        }

        return datasource;
    }
}

and changed the corresponding constructor in Sql2o.java:

    public Sql2o(String jndiLookup) {
        this(JndiDatasource.get(jndiLookup));
    }

Once I've compiled Sql2o with these changes I was able to run it on Android (with an H2 database). I still get errors in the log about missing sun.reflect.* classes but so far I was able to execute all the queries I needed. Please remove the dependencies on javax.naming.* from Sql2o.java so it can run on Android.

[migel]

Hi Lars,

Thanks for the fix. Here is my experience with Sql2o and Android so far, maybe it will help others:

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 SQLDroid/SQLDroid#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.

[aaberg]

Hi Migel,

This is very valuable information! I have copy/pasted it into the wiki so that it is easier to find others. I will also create a link to the wiki from the web site soon.
https://github.com/aaberg/sql2o/wiki/Sql2o-on-Android

Thank you very much!

~lars