转载

JOOQ大杂烩

JOOQ(Java Object Oriented Querying)，Java面向对象查询，是一个类型安全的ORM框架

JOOQ使用APT技术，通过分析数据库，对每个数据表和试图生成对应的Bean和查询对象，实现类型安全的ORM操作

JOOQ的配置

1. 添加JOOQ的Maven依赖

SpringBootStarterParent管理了JOOQ依赖，可以不用设置版本号

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>

2. 配置APT插件

JOOQ需要通过APT动态生成需要的Java类，所以需要配置Maven插件

重要的配置项:

executions.execution.goals.goal 如果需要在 mvn compile 的时候执行apt，需要此设置
dependencies.dependency 数据库驱动所在包
configuration.jdbc 数据库连接配置
configuration.generator.database.inputSchema 指定数据库名称
configuration.generator.target.packageName 指定输出包名
configuration.generator.target.packageName 指定输出位置

<build>
    <plugins>
        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>${jooq.version}</version>
            <executions>
                <execution>
                    <goals>
                        <goal>generate</goal>
                    </goals>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>${mysql.version}</version>
                </dependency>
            </dependencies>
            <configuration>
                <jdbc>
                    <url>jdbc:mysql://localhost:3306/foo</url>
                    <user>root</user>
                    <password>root</password>
                </jdbc>
                <generator>
                    <database>
                        <includes>.*</includes>
                        <inputSchema>foo</inputSchema>
                    </database>

                    <target>
                        <packageName>jooq</packageName>
                        <directory>${basedir}/target/generated-sources/java</directory>
                    </target>
                </generator>
            </configuration>
        </plugin>
    </plugins>
</build>

执行APT

显示执行: mvn jooq-codegen:generate
隐式执行: mvn compile

执行成功后，会在 /target/generated-sources 目录下生成所需的Class

JOOQ简单示例

package bj.mybatis;

import com.zaxxer.hikari.HikariDataSource;
import jooq.tables.records.UserRecord;
import org.jooq.*;
import org.jooq.conf.ParamType;
import org.jooq.impl.DSL;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.WebApplicationType;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.quartz.QuartzAutoConfiguration;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import javax.sql.DataSource;
import javax.validation.constraints.NotNull;

import static jooq.tables.User.USER;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/12/11 上午9:57
 */
@SpringBootApplication(exclude = QuartzAutoConfiguration.class)
public class App implements ApplicationListener<ApplicationReadyEvent> {

    public static void main(String[] args) {
        new SpringApplication(App.class) {{
            setWebApplicationType(WebApplicationType.NONE);
        }}.run(args);
    }

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public void onApplicationEvent(@NotNull ApplicationReadyEvent event) {
        initDatabase();
        DSLContext dslContext = DSL.using(dataSource, SQLDialect.MYSQL_8_0);

        // 插入方式1: 插入对象
        UserRecord userRecord = dslContext.newRecord(USER);
        userRecord.setUsername("sky");
        userRecord.setRealName("九天");
        userRecord.insert();
        System.out.println("Inserted user: ");
        System.out.println(userRecord);

        // 插入方式2: 动态插入
        dslContext.insertInto(USER).set(USER.USERNAME, "rain").set(USER.REAL_NAME, "暴雨").execute();

        // 查询方式1: 查询完整对象
        Result<UserRecord> userRecords = dslContext.selectFrom(USER).fetch();
        System.out.println("All records: ");
        System.out.println(userRecords);

        // SEEK分页查询
        System.out.println("SEEK:");
        dslContext.selectFrom(USER).orderBy(USER.USERNAME.asc(), USER.REAL_NAME.asc()).seek("rain", "暴雨").limit(10).forEach(System.out::println);

        // 查询方式2: 查询指定字段
        SelectConditionStep<Record1<String>> where = dslContext.select(USER.REAL_NAME).from(USER).where(USER.ID.eq(2));
        Record1<String> stringRecord1 = where.fetchOne();
        System.out.println("SQL:");
        // 打印SQL。在DSL.using()可以不传dataSource，此时JOOQ做纯SQLBuilder
        System.out.println(where.getSQL(ParamType.INLINED));
        System.out.println("realName: ");
        System.out.println(stringRecord1);
    }

    @Resource
    private DataSource dataSource;

    private void initDatabase() {
        jdbcTemplate.execute("DROP TABLE IF EXISTS user");
        jdbcTemplate.execute("CREATE TABLE user(id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(16) UNIQUE ,real_name VARCHAR(32) CHARSET 'utf8')");
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource() {{
            this.setJdbcUrl("jdbc:mysql://localhost/foo?characterEncoding=utf-8");
            this.setUsername("root");
            this.setPassword("root");
        }};
    }
}

示例输出:

  .   ____          _            __ _ _
 /// / ___'_ __ _ _(_)_ __  __ _ / / / /
( ( )/___ | '_ | '_| | '_ // _` | / / / /
 ///  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_/__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.0.RELEASE)

2018-12-11 15:04:05.021  INFO 18538 --- [           main] bj.mybatis.App                           : Starting App on MacBook-Air-2.local with PID 18538 (/Users/yuchao/temp/java/hellomaven/target/classes started by yuchao in /Users/yuchao/temp/java/hellomaven)
2018-12-11 15:04:05.027  INFO 18538 --- [           main] bj.mybatis.App                           : No active profile set, falling back to default profiles: default
2018-12-11 15:04:06.422  WARN 18538 --- [           main] o.m.s.mapper.ClassPathMapperScanner      : No MyBatis mapper was found in '[bj.mybatis]' package. Please check your configuration.
2018-12-11 15:04:07.529  INFO 18538 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-12-11 15:04:07.709  INFO 18538 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-12-11 15:04:08.393  WARN 18538 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default LoopResources: DefaultLoopResources {prefix=reactor-http, daemon=true, selectCount=4, workerCount=4}
2018-12-11 15:04:08.393  WARN 18538 --- [           main] reactor.netty.tcp.TcpResources           : [http] resources will use the default ConnectionProvider: PooledConnectionProvider {name=http, poolFactory=reactor.netty.resources.ConnectionProvider$$Lambda$282/589610983@112d1c8e}
2018-12-11 15:04:08.645  INFO 18538 --- [           main] bj.mybatis.App                           : Started App in 4.832 seconds (JVM running for 6.859)
2018-12-11 15:04:08.900  INFO 18538 --- [           main] org.jooq.Constants                       :

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@  @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@        @@@@@@@@@@
@@@@@@@@@@@@@@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@  @@@@  @@  @@    @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@    @@  @@  @@@@  @@@@@@@@@@
@@@@@@@@@@        @@  @  @  @@@@@@@@@@
@@@@@@@@@@        @@        @@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@  @@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  Thank you for using jOOQ 3.11.5

Inserted user:
+----+--------+---------+
|  id|username|real_name|
+----+--------+---------+
|   1|sky     |九天       |
+----+--------+---------+

All records:
+----+--------+---------+
|  id|username|real_name|
+----+--------+---------+
|   1|sky     |九天       |
|   2|rain    |暴雨       |
+----+--------+---------+

SEEK:
+----+--------+---------+
|  id|username|real_name|
+----+--------+---------+
|   1|sky     |九天       |
+----+--------+---------+

SQL:
select `foo`.`user`.`real_name` from `foo`.`user` where `foo`.`user`.`id` = 2
realName:
+---------+
|real_name|
+---------+
|暴雨       |
+---------+

2018-12-11 15:04:09.722  INFO 18538 --- [      Thread-16] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-12-11 15:04:09.738  INFO 18538 --- [      Thread-16] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

原文 https://baijifeilong.github.io/2018/12/11/jooq/

正文到此结束