[JAVA] I tried using JOOQ with Gradle

I tried using JOOQ with Gradle

Note: This article is a memorandum when using JOOQ in Tomcat (JavaEE) of IntelliJ Community (free version). The environment construction of tomcat using Gradle in IntelliJ is explained in the following link. Prepare the execution environment of Tomcat in IntelliJ Community It seems that ON DUPLICATE KEY UPDATE cannot be used with iciql (code generation also failed), so I migrated.

In addition, the environment is assumed to be Windows 10.

Add jooq to external library

Add the following to the dependencies of build.gradle.

dependencies {
    //Add database connector
    //(In the case of mysql, if you specify a version of 6 or higher, you will get a TimeZone related error.)
    compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.47'

    //Added jooq related jar
    compile group: 'org.jooq', name: 'jooq', version: '3.11.2'
    compile group: 'org.jooq', name: 'jooq-parent', version: '3.11.2'/*, ext: 'pom'*/
    compile group: 'org.jooq', name: 'jooq-meta', version: '3.11.2'
    compile group: 'org.jooq', name: 'jooq-meta-extensions', version: '3.11.2'
    compile group: 'org.jooq', name: 'jooq-codegen', version: '3.11.2'
    compile group: 'org.jooq', name: 'jooq-codegen-maven', version: '3.11.2'
}

It is necessary to add the database connector together, but if you are using MySQL, if you specify version 6.0 or higher, the following error will occur at runtime.

The server time zone value '???? (?W????)' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support.

## Include a plugin that is useful for generating code with JOOQ
 In order to install [JOOQ plugins](https://plugins.gradle.org/plugin/nu.studer.jooq), add plugins of build.gradle.

```groovy
plugins {
    id 'nu.studer.jooq' version '3.0.2'
}

Set up the JOOQ plugin

You can configure the JOOQ plugin by declaring jooq {} in build.gradle and writing it inside.

dependencies {
    jooqRuntime group: 'mysql', name: 'mysql-connector-java', version: '5.1.47'
}

jooq {
    //Specify the version of jooq
    version = '3.11.2'
    edition = 'OSS'
    tables(sourceSets.main) {
        jdbc {
            //Database driver fully qualified class name
            driver = 'com.mysql.jdbc.Driver'
            //URL to connect to SQL
            url = 'jdbc:mysql://localhost:3306/Hoge?serverTimezone=JST'
            //Username to log in to the database
            user = 'user'
            //Password to log in to the database
            password = 'password'
        }
        generator {
            name = 'org.jooq.codegen.DefaultGenerator'
            database {
                //Class name representing the target database(See below)
                name = 'org.jooq.meta.mysql.MySQLDatabase'
                inputSchema = ''
                includes = '.*'
                excludes = ''
            }
            target {
                //Package name of the generated code
                packageName = 'jooq'
                //Directory to place the generated code
                directory = 'src/main/java'
            }
        }
    }
}

If you want to use other than MySQL in the database, specify the name as shown in the table below. The jooq class name used to connect to the database.

・ Jooq Code generation | Java Development

JOOQ code generation

Since there is a task called jooq → generateTablesJooqSchemaSource in the Gradle window, you can automatically generate the corresponding class from the database by executing it.

Let's Programming with JOOQ!

・ Summary of how to use jOOQ