Best SQL Builder - use jOOQ on Android

Best SQL Builder. Using jOOQ on Android

Introduction

When developing Android applications, it is quite natural to use the SQLite database as the main storage. Usually, databases on mobile devices have very simple charts and consist of 10-15 tables. For such cases, almost any SQL Builder, ORM, and even the bare SQLite API are suitable.

But, alas, not all developers are lucky, and sometimes it falls to our lot to describe large data models, use stored procedures, customize work with custom data types, or write 10 INNER JOIN in a query for a very thick entity. So no luck and your humble servant, from which there was material for this article. Well, harsh times require harsh measures. So, roll jOOQ on Android.

All is good, but

But there are two facts to cope with. The first of them lurks us at the very beginning of work with jOOQ: at the ideological stage. In order to initiate the code generation process, you need, in fact, to have a database to which the jooq plugin will connect. This problem is easily solved, we create a template project with a description of the gradle task for generation, after which we create the database locally, set the paths in the configs, launch the plugin and copy the resulting sources into our project.

Next, let's say we have generated all the necessary classes. Just so we can not copy them into the Android project - additional dependencies will be required, the first of which is on javax annotations. Option two, both banal. Either add the library (org.glassfish: javax.annotation), or - use the wonderful tool - find & replace in scope.

And it would seem that everything is fine, all the preliminary settings are made, the classes are copied and imported into the project. You may even be able to launch the application, and there is a chance that it will work. If you are required to support the Android API Level <24 - do not get fooled, this is not the end of our journey. The fact is that jOOQ currently in the open-source version largely uses Java 8, which, as you know, is friendly with Android quite arbitrarily. This problem is also solved in two ways: either we buy jOOQ, write to support and tearfully ask for a version for Java 6 or Java 7 (they have, judging by the articles in the network), or if you, like me, do not have a hard the need to have all the latest innovations of the library, as well as the desire to pay, that is, the second way. jOOQ started switching to Java 8 not so long ago.groovy version = '3.6.0' and support older versions of devices.

And the last thing that awaits enthusiasts who have gone along this path of despair. In Android, in principle, there is no JDBC, which means that it's time to cross your fingers to look for 3rd-party solutions. Fortunately, there is a similar library - SQLDroid.

Everything. The main stages and actions on them are fluently written. Now let's move on to the code, everything is pretty logical here, but in order to shorten your time, I’ll give examples from my own project.

Code Generation

Setting jOOQ plugin will look like this:

buildScript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath "nu.studer:gradle-jooq-plugin:$jooq_plugin_version"
    }
}
apply plugin: 'nu.studer.jooq'
dependencies {
    jooqRuntime "org.xerial:sqlite-jdbc:$xerial_version"
}
jooq {
    version = '3.6.0'
    edition = 'OSS'
    dev(sourceSets.main) {
        jdbc {
            driver = 'org.sqlite.JDBC'
            url = 'jdbc:sqlite:/Path/To/Database/database.db3'
        }
        generator {
            name = 'org.jooq.util.DefaultGenerator'
            strategy {
                name = 'org.jooq.util.DefaultGeneratorStrategy'
            }
            database {
                name = 'org.jooq.util.sqlite.SQLiteDatabase'
            }
            generate {
                relations = true
                deprecated = false
                records = true
                immutablePojos = true
                fluentSetters = true
            }
            target {
                packageName = 'com.example.mypackage.data.database'
            }
        }
    }
}

Android

Required dependencies:

implementation"org.jooq:jooq:$jooq_version"
implementation "org.sqldroid:sqldroid:$sqldroid_version"
implementation "org.glassfish:javax.annotation:$javax_annotations_version"

And now the source code of the wrapper class, for working with jOOQ via SQLiteOpenHelper. In general, it would have been possible to do without it, but it is much more convenient (in my opinion) in order to safely use both one and the second API.

classDatabaseAdapter(privateval context: Context)
    : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    companionobject {
        privateconstval DATABASE_NAME     = "database"privateconstval DATABASE_VERSION  = 1@JvmStaticprivateval OPEN_OPTIONS = mapOf(
                "cache" to "shared",
                "journal_mode" to "WAL",
                "synchronous" to "ON",
                "foreign_keys" to "ON")
    }
    val connectionLock: ReentrantLock = ReentrantLock(true)
    val configuration: Configuration by lazy(mode = LazyThreadSafetyMode.NONE) {
        connectionLock.withLock {
            // ensure the database exists,// all upgrades are performed,// and connection is ready to be setval database = context.openOrCreateDatabase(
                DATABASE_NAME, 
                Context.MODE_PRIVATE, 
                null)
            if (database.isOpen) {
                database.close()
            }
            // register SQLDroid driver to be used for establishing connections// with our database
            DriverManager.registerDriver(
                Class.forName("org.sqldroid.SQLDroidDriver")
                    .newInstance() as Driver)
            DefaultConfiguration()
                    .set(SQLiteSource(
                        context, 
                        OPEN_OPTIONS, 
                        "database", 
                        arrayOf("databases")))
                    .set(SQLDialect.SQLITE)
        }
    }
    overridefunonCreate(db: SQLiteDatabase) {
        // acquire monitor until the database connection is created// this is important as otherwise transactions might be tryingg to run// concurrently that will lead to crashes
        connectionLock.withLock {
            // TODO: Create tables
        }
    }
    overridefunonOpen(db: SQLiteDatabase) {
        // acquire monitor until the database connection is established// this is important as otherwise transactions might be tryingg to run// concurrently that will lead to crashes
        connectionLock.withLock {
            super.onOpen(db)
        }
    }
    overridefunonUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // acquire monitor until the database is upgraded// this is important as otherwise transactions might be tryingg to run// concurrently that will lead to crashes
        connectionLock.withLock {
        }
    }
    infixinlinefun<reified T>transaction(noinline f: (Configuration) -> T): Observable<T>
            = Observable.create { emitter ->
        val tryResult = Try {
            connectionLock.withLock {
                DSL.using(configuration).transactionResult(f)
            }
        }
        when (tryResult) {
            is Try.Success -> {
                emitter.onNext(tryResult.value)
                emitter.onComplete()
            }
            is Try.Failure -> {
                emitter.onError(tryResult.exception)
            }
        }
    }
    funinvalidate() {
        connectionLock.withLock {
            // TODO: Drop tables, vacuum and create tables
        }
    }
    privateclassSQLiteSource(val context: Context,
                               val options: Map<String, String>,
                               val database: String,
                               val fragments: Array<out String>): DroidDataSource() {
        overridefungetConnection(): Connection
                = openConnection(options)
        privatefunopenConnection(options: Map<String, String> = emptyMap()): Connection {
            return DriverManager.getConnection(StringBuilder().apply {
                append("jdbc:sqldroid:")
                append(context.applicationInfo.dataDir)
                append("/")
                append(buildFragments(fragments))
                append(database)
                append("?")
                append(buildOptions(options))
            }.toString())
        }
        privatefunbuildFragments(fragments: Array<outString>)
                = when (fragments.isEmpty()) {
            true  -> ""false -> "${fragments.joinToString("/")}/"
        }
        privatefunbuildOptions(options: Map<String, String>)
                = options.mapTo(mutableListOf<String>()) { entry ->
            "${entry.key}=${entry.value}"
        }
                .joinToString(separator = "&")
    }
}

UPD: added to lazy-initialization mode = LazyThreadSafetyMode.NONE, thanks konstantin_berkow

Instead of conclusion

As it turned out, setting up jOOQ in Android is not such a complicated process. It is enough to do it once, and then you can safely do copy-paste from old projects.

And a small bonus that jOOQ gives to those who use it. As you can see from the example, when opening a connection, use cached mode. What is cymes? Android SDK SQLite API does not provide the ability to work with the database in this mode, severely limiting us in the organization of interprocess communication in applications. Now - you can safely use this mode, which in itself can serve as a reason for switching to this wonderful framework.