How to manage Java code automatically generated by jOOQ & Flyway sample

TL;DR

• Java source code for O / R mapping automatically generated by jOOQ should not be managed by git. Let's make it the target of .gitignore.
• Otherwise, conflicts are likely to occur during team development, and you will not be able to take advantage of jOOQ's ability to withstand DB changes (additions, changes, etc. of tables and columns).
• The above can be easily realized by setting up the local development environment, that is, using Docker, gradle-jooq-plugin, flyway-gradle-plugin properly.
• Incidentally, in the sense of DDD (Domain Driven Design), it is bad to use the entity class automatically generated by jOOQ as it is as a domain class. Refill the values into your own domain class, even if it's a hassle.
• Click here for sample code
• The reading of jOOQ is "Juke".

Detail

At JJUG-CCC (Japan Java Users Group Cross Community Conference) in December 2018 Talking about O / R mapper and DB migration while comparing tools ) Was given the title.

Among the many O / R mappers and DB migration tools, jOOQ was recommended and Flyway was recommended. On the other hand, although it is a story that I hear from blogs and people on the street, it seems that there are many cases where they are used slightly incorrectly. ** What's particularly bad is that there are some cases where jOOQ automatically generated Java source code is versioned as a git commit target. You had better stop it. ** If multiple developers inadvertently add columns to the same table at the same time, a large amount of Java code automatically generated by jOOQ will always cause a conflict.

jOOQ reads the structure of the specified DB schema accurately and incomparably, and automatically generates Java code that makes O / R mapping easier. ** This means that it is the DB schema definition, that is, the so-called DDL statements such as CREATE TABLE and ALTER TABLE, that should be versioned accurately and unmatched. Flyway makes that possible. ** **

Since jOOQ automatically generates Java code, I want to specify the output destination under src / main / java, but that is the pitfall. If you are using gradle, you can compile by specifying the location of the source code after specifying the output destination of the automatically generated code of jOOQ under build /. There is no problem on IntelliJ IDEA. However, I don't know about the ancient weapon called Eclipse.

Try running the sample code

Just prepare a PC with git, JDK8 or above, docker and execute the following command.

git clone [email protected]:nabedge/jooq-flyway-spboot-sample.git
cd jooq-flyway-spboot-sample
sh setup.sh
sh ./gradlew run -p pj-web
Http in browser://localhost:Open 8080

Try importing the sample code into IntelliJ IDEA as a project

1. Complete the steps above to run sh setup.sh
2. File -> New -> Project from existing sources
3. Open by specifying build.gradle directly under the jooq-flyway-spboot-sample directory
4. Check use auto import and press the OK button
5. Click SampleApplication.java in the pj-web project to launch it
6. Open http: // localhost: 8080 in your browser

Points on how to use Docker

• The official image of PostgreSQL, [docker-entrypoint-initdb.d directory](https://github.com/nabedge/jooq-flyway-spboot-sample/tree/master/docker/postgresql/docker-entrypoint-initdb. d) Use the * .sql file under it to execute the function.
• However, it only creates the database itself for the application and its database users.
• In other words, the application is developed on the assumption that only the empty DB and the DB user for the application permitted to access it already exist.

Points on how to use Flyway

• According to theory, [src / main / resources / db / migration](https://github.com/nabedge/jooq-flyway-spboot-sample/tree/master/pj-db/src/main/resources/db/ Under migration), prepare a * .sql file with a certain naming method. The contents are CREATE TABLE statements, etc.
• Even in a local development environment, it is very inconvenient for actual development work unless some test data is stored in the table. Therefore, under src / test / resources / testdata, Let's prepare a file with INSERT statements for test data input
• I want about 100 test data, but it is troublesome to write an INSERT statement with 100 lines. Then, under src / test / java / testdata Let's write Java code that INSERTs test data while looping appropriately. This is ** Flyway's Java Based Migration ** feature.
• In PostgreSQL on Docker, the table is automatically created with the ./gradlew flywayMigrate -p pj -db command. Flyway executes SQL by accurately grasping "Which SQL (DDL) statement has been executed in the target DB?", So this command can be executed with confidence in the production environment as well.
• If you want to install Flyway in the middle of a database / application that is already in production, [Flyway in the middle](https://www.google.co.jp/search?hl=ja&q=%E9%80%94%E4 % B8% AD% E3% 81% 8B% E3% 82% 89 Flyway) Let's google. Various things will come out.
• Test data is populated with ./gradlew flywayMigrate -Dflyway.locations = classpath: testdata. Unless you explicitly give the -Dflyway.locations option, the scripts under these will not work, so you can fully prevent accidents where test data enters the production environment.

Points on how to use jOOQ

• It seems to be persistent, but how to read jOOQ is Juke.
• For projects that use jOOQ and Flyway, it is essential to separate the libraries for DB access they target as subprojects. If you mix it up with regular application code, the build definition file (build.gradle) becomes unnecessarily complicated and can be the beginning of a long-running war. ** Always multi-project configuration. This is basic. ** **
• In the sample project, pj-db and pj-db-custom-strategy are subprojects for DB access. The main application, pj-web, should be developed depending on pj-db.
• A sample of issuing SQL statements by jOOQ is [BookRepository.java](https://github.com/nabedge/jooq-flyway-spboot-sample/blob/master/pj-web/src/main/java/com/ example / web / BookRepository.java).
• Use gradle-jooq-plugin to have jOOQ automatically generate Java code.
• This area of pj-db / build.gradle says jooqRuntime There is an unfamiliar definition. These will allow jOOQ to access the DB, decipher all the table definitions stretched there, and generate Java code for O / R mapping.

jOOQ and build.gradle

Let's explain a little more from around.

jooq {
    version = "${jooqVersion}"
    edition = 'OSS' // if you use oracle, you should pay :-)

    // the name "sample" -> task name "generateSampleJooqSchemaSource" . see below.
    sample (sourceSets.main) {
        jdbc {
            driver = "${jdbcDriver}"
            url = "${dbUrl}"
            user = "${dbUser}"
            password = "${dbPassword}"
        }
        generator {
            target {
                packageName = "${jooqDestPackage}"
                directory = "${jooqDestDir}"
            }
            strategy {
                name = 'com.example.db.jooq.generator.SamplePrefixGeneratorStrategy'
            }
            database() {
                name = 'org.jooq.meta.postgres.PostgresDatabase'
                inputSchema = "public"
            }
            generate() {
                daos = true
                immutablePojos = true
                pojosEqualsAndHashCode = true
            }
        }
    }
}

compileJava {
    dependsOn generateSampleJooqSchemaSource
    sourceSets.main.java.srcDirs(jooqDestDir)
}

• Think of the value "sample" as the abstract name of the DB definition on this sample code. This will be the task name generateSampleJooqSchemaSource.
• Since there is generateSampleJooqSchemaSource as a prerequisite (dependsOn) for the compile Java task, gradle will always try to compile it after auto-generating the code. Also, due to sourceSets.main.java.srcDirs (jooqDestDir), the location of the source code is defined at the beginning instead of src / main / java. -spboot-sample / blob / master / pj-db / build.gradle # L5) The source code is automatically generated in build / jooq-gen.
• ** Of course **, The "build" directory itself is specified in .gitignore

Add a prefix / suffix to the class name of the code automatically generated by jOOQ

As I mentioned in Around 50 pages of actual O / R mapper and DB migration talked while comparing tools, jOOQ is the table name by default. Will create a Java class with exactly the same name as. ** Custom is highly recommended. ** Class name conflicts can lead to considerable stress during the coding process.

So this part of build.gradle above

            strategy {
                name = 'com.example.db.jooq.generator.SamplePrefixGeneratorStrategy'
            }

Becomes important. This class is the only class in the subproject called pj-db-custom-strategy It is implemented as /src/main/java/com/example/db/jooq/generator/SamplePrefixGeneratorStrategy.java).

import org.jooq.codegen.DefaultGeneratorStrategy;
import org.jooq.meta.Definition;

public class SamplePrefixGeneratorStrategy extends DefaultGeneratorStrategy {

    @Override
    public String getJavaClassName(final Definition definition, final Mode mode) {

        String name = super.getJavaClassName(definition, mode);

        switch (mode) {
            case POJO:
                return name + "Vo";
            case DEFAULT:
                return 'J' + name;
        }

        return name;
    }

As you can see, all the Java class prefixes automatically generated by jOOQ have "J". This will give you the actual DB access code (https://github.com/nabedge/jooq-flyway-spboot-sample/blob/master/pj-web/src/main/java/com/example/web/ In BookRepository.java)

        final JBook jBook = JBook.BOOK;
        final List<BookVo> selected = dslContext
                .select(
                        jBook.ISBN,
                        jBook.TITLE,
                        jBook.PUBLISH_DATE
                )
                .from(jBook)
                .orderBy(jBook.PUBLISH_DATE)
                .fetchInto(BookVo.class);
                // .fetchInto(Book.class); // or you can use original class directly !

        return selected
                .stream()
                .map(bookVo -> {
                    Book book = new Book();
                    book.setIsbn(bookVo.getIsbn());
                    book.setTitle(bookVo.getTitle());
                    book.setPublishDate(bookVo.getPublishDate().toLocalDate());
                    return book;
                })
                .collect(Collectors.toList());

in this way,

• Code for O / R mapper automatically generated by jOOQ (JBook.java)
• Also automatically generated entity class (BookVo.java)
• DDD-like class that you should make yourself (Book.java)

You will be able to avoid these name conflicts.

in conclusion

• Last year's material O / R mapper and DB migration actually talked while comparing tools
• [Sample code] made this month (https://github.com/nabedge/jooq-flyway-spboot-sample)
• This blog

If you move the sample while reading each and import it into the IDE, you can see the point. Happy Hacking !

This article is reprinted by the author himself from his own blog. -> https://nabedge.mixer2.org/2019/03/jooq-flyway-sample.html