Hi Gary, Thanks for your great feedback about using jOOQ and Android! This will certainly help future visitors and it also helps me see the missing features in that particular integration. Further comments inline:
2013/10/30 <[email protected]> > This post is old, but it's the only direct mention of Android, so if > anybody's wondering if they should use JooQ for their Android SQLite... > > I use JooQ on Android for the project I'm developing, and have for several > months. Android tries to provided the "convience" of guiding SQLite use, > but really just gets in the way for anything more than a trivial use case. > Lukas is right; JDBC and Android do not get along well. Since JooQ treats > SQL as SQL, it can be compatible with the Android-provided accessor tools. > I use it to represent my schema and build more complex queries with greater > confidence of type-safety and syntactially- and structurally-valid queries > during dynamic processes. It was previously done through StringBuilders, > utility classes, and POJOs, which was pretty hellish. > > Generation does have to be done seperately, however. I use the same schema > definition script for the app and the JooQ generation, and just keep an > external database up-to-date for JooQ code generation. I use a gradle > plug-in and for JooQ, and a SQLite JDBC driver, to run JooQ code generation > as a standalone, prerequisite gradle task before my Android build task is > run. It could probably be automated further to generate a SQLite DB from > the defined schema file before doing the JooQ code generation, but it > hasn't been enough of an inconvenience to bother to do so yet. > > I like the predicatable, Java-ish structure of the query building. Writing > queries it in a smart IDE like IDEA is a treat. I also use JooQ's > Interfaces to define subprocesses to generate queries and subqueries and > assemble them in an automatic fashion with confidence of the desired result. > > The big drawaback is that SQLite support is somewhat incomplete. The > super-nice feature porting JooQ offers to ther databases mostly doesn't > exist for SQLite. So, no UPSERTs, no MERGEs, no ON DUPLICATE KEY *, etc. I > also haven't found a way to do some inherently supported SQLite things, > such as an "INSERT OR *" statements, or CASE statements, or "BEGIN > TRANSACTION...COMMIT" (since JDBC batches don't apply). > There are two ways of looking at this, of course ;-) After all, the SQLite documentation reads "SQL As Understood By SQLite" http://www.sqlite.org/lang.html Anyway, there's some good points in the above. Let's look at them one-by-one: # INSERT OR *: This is a low-hanging fruit to implement. jOOQ already supports MySQL's INSERT IGNORE: https://github.com/jOOQ/jOOQ/issues/2814 # REPLACE: This one has been on the roadmap for a while: https://github.com/jOOQ/jOOQ/issues/547 # UPSERT and MERGE and ON DUPLICATE KEY These don't exist in SQLite, do they? # BEGIN TRANSACTION ... COMMIT These can be easily implemented using plain SQL: http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/ E.g. with http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#execute(java.lang.String) # CASE statements: See: http://www.jooq.org/doc/3.2/manual/sql-building/column-expressions/case-expressions/ > > Fortunately, JooQ can be used in combination with traditional Strings, so > things like... > > StringBuilder("INSERT OR IGNORE INTO ") > .append(USER.getName()) > .append(" (") > .append(USER.NAME.getName()) > .append(", ") > .append(USER.ADDRESS.getName()) > .append(") VALUES ( ") > .append(name) > .append(", ") > .append(address) > .append(";"); > > ...do work as well for cases JooQ doesn't cover (for now). > You might really want to have a look at jOOQ's plain SQL capabilities. The above can be written as such: DSL.using(configuration) .execute("INSERT OR IGNORE INTO {0} ({1}, {2}) VALUES ({3}, {4})", USER, USER.NAME, USER.ADDRESS, val(name), val(address)); Cheers Lukas > (Lukas, please correct any false feature claims. I don't with to sell JooQ > short ~ it's an amazing product overall.) > > > > On Tuesday, June 19, 2012 6:30:12 AM UTC-7, Lukas Eder wrote: >> >> Hi there, >> >> > One of the first concerns is: how do you run the code generator when >> the >> > database is only accessible on the phone? >> >> Unfortunately, I have no experience with that. The biggest trouble you >> might run into is the fact that Android (per default) circumvents JDBC >> from what I know. jOOQ (as well as jOOQ-meta for the generator) >> depends heavily on JDBC. Having said this, jOOQ is integration tested >> using this inofficial JDBC driver on a Windows machine here: >> >> http://www.zentus.com/**sqlitejdbc/ <http://www.zentus.com/sqlitejdbc/> >> >> > Here are the steps I use when >> > developing for the desktop: >> > >> > 1. Create a sample database using Flyway. >> > 2. Run JOOQ's code generator against it. >> > 3. Compile classes that use the generated classes. >> >> That would work. Other users on this group have successfully used jOOQ >> against several databases, such as Oracle, SQL Server, Postgres and >> other combinations. In essence, you could develop against an H2 >> database and roll out your application on an Android phone with a >> SQLite database. Again, this presumes that you're using JDBC... >> >> > If I find a way to generate a SQLite database, will JOOQ actually work? >> Or >> > is Android support more theoretical than practical at this point? >> >> I have not heard of anyone actually using jOOQ on an Android phone so >> far. Maybe you could get some help on Stack Overflow? >> >> Cheers >> Lukas >> > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
