当前位置: 首页 > 工具软件 > jOOQ > 使用案例 >

JOOQ入门指南

刘昌翰
2023-12-01

JOOQ

JOOQ提供了SQLBuilder功能 ,数据迁移功能,SQL执行能力,执行生命周期的管理等属性。相较hibernate,JOOQ执行SQL的过程对程序员的可见性更高。相较于mybatis SQL脚本的管理更为灵活,在使用上减少了配置文件的管理和配置。

JOOQ的目标并不是成为完整实现JPA协议的ORM框架,其更专注于SQL的执行,想要实现的是OLTP和大数据清洗场景下的数据管理.它并不是JAP框架的替代,相反,它更想成为JPA协议框架的补充。

Whenever SQL is a good fit, jOOQ is a good fit. Whenever you’re operating and persisting the object graph, JPA is a good fit.And sometimes, it’s best to combine both。

代码生成

type1:命令行生成

命令行如下:

 java -classpath jooq-3.13.2.jar;jooq-meta-3.13.2.jar;jooq-codegen-3.13.2.jar;reactive-    streams-1.0.2.jar;mysql-connector-java-8.0.20.jar;. org.jooq.codegen.GenerationTool /jooq.xml

在此之前需要准备1.java运行环境;2.执行命令行涉及的包;3.jooq.xml配置

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
    <!-- 数据库信息 -->
    <jdbc>
        <driver></driver>
        <url></url>
        <user></user>
        <password></password>
    </jdbc>

    <generator>
        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.codegen.JavaGenerator
             - org.jooq.codegen.ScalaGenerator
             Defaults to org.jooq.codegen.JavaGenerator -->
        <name>org.jooq.codegen.JavaGenerator</name>

        <database>
            <!-- The database type. The format here is:
                 org.jooq.meta.[database].[database]Database -->
            <name>org.jooq.meta.mysql.MySQLDatabase</name>

            <!-- 需要生成代码的数据库Schema -->
            <inputSchema>jooq</inputSchema>

            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->
            <includes>.*</includes>

            <!-- 声明需要排除不生成java代码的table-->
            <excludes></excludes>
        </database>
        <generate>
            <!--生成dao和pojo-->
            <daos>true</daos>
            <pojos>true</pojos>

            <!--把数据库时间类型映射到java 8时间类型-->
            <javaTimeTypes>true</javaTimeTypes>
            <!--<interfaces>true</interfaces>-->
            <!--生成的代码中添加spring注释,比如@Repository-->
            <springAnnotations>true</springAnnotations>
            <pojosToString>true</pojosToString>
        </generate>
        <target>
            <!-- The destination package of your generated classes (within the destination directory) -->
            <packageName>包名</packageName>

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->
            <directory>C:/workspace/MySQLTest/src/main/java</directory>
        </target>
    </generator>
</configuration>
type2:maven+springboot 生成
mvn clean install

需要准备1.java运行环境;2.执行命令行涉及的包;3.jooq.xml配置;4.maven 环境 ;5 包含jooq的相关依赖的

pom.xml

pom.xml如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.3.1.RELEASE</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.*.jooq</groupId>
   <artifactId>demo</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>demo</name>
   <description>Demo project for Spring Boot</description>

   <properties>
      <java.version>1.8</java.version>
   </properties>

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

      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-devtools</artifactId>
         <scope>runtime</scope>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <scope>runtime</scope>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
         <exclusions>
            <exclusion>
               <groupId>org.junit.vintage</groupId>
               <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
         </exclusions>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.jooq/jooq-codegen-maven -->

   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
         <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <version>3.13.2</version>
            <executions>
               <execution>
                  <goals>
                     <goal>generate</goal>
                  </goals>
               </execution>
            </executions>
            <dependencies>
               <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>8.0.20</version>
               </dependency>
            </dependencies>
            <configuration>
                <!--jooq 配置文件加载-->
               <configurationFile>src/main/resources/jooq.xml</configurationFile>
            </configuration>
         </plugin>
      </plugins>
   </build>

</project>

springboot 整合JOOQ

官方给出的方法为在启动配置中添加

#src\main\resources\application.properties 
spring.jooq.sql-dialect=H2

代码生成器生成的dao不可直接使用,这是因为生成的代码中提供了两个dao构造器,其中一个

import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository("authorDao")
public class AuthorDaoExt extends AuthorDao{
    @Autowired
    private DSLContext dsl;
    @Autowired
    public AuthorDaoExt(Configuration configuration) {
        super(configuration);
    }
//    @Autowired  当以这个构造器为spring 托管时会导致springboot的dataSource配置无法生效
//    public AuthorDaoExt() {
//        super();
//    }
}

JOOQ可扩展组件:https://www.jooq.org/doc/3.14/manual/getting-started/use-cases/jooq-for-pros/

JOOQ作为SQL构造器

import org.jooq.DSLContext;
import org.jooq.Query;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamType;
import org.jooq.impl.DefaultDSLContext;
//静态导入
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;


public class JOOQBuilder {
    private static DSLContext create = new DefaultDSLContext(SQLDialect.MYSQL);

    public static void main(String[] args) {
        //将JOOQ做为一个SQL构造器
        Query query = (Query)          create.select().from(table("AUTHOR")).where(field("AUTHOR.ID").eq("1"));
        //打印SQL,PreparedStatement SQL
         System.out.println(query.getSQL());
        //打印SQL
        System.out.println(query.getSQL(ParamType.INLINED));
    }


}

连接数据库

import org.jooq.*;
import org.jooq.impl.DefaultDSLContext;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import static  com.*.jooqdemo.dao.Tables.*;

public class JOOQConnter {
    private static DSLContext create;
    private static Connection connection;
    static {
        //基于JDBC创建数据库连接
        final String userName ="root";
        final String password ="*****";
        final String url ="jdbc:mysql://localhost:3306/jooq?serverTimezone=UTC";
        //Driver Conn

        try {
            connection =DriverManager.getConnection(url,userName,password);
            //需要注意,使用jdbc作为JOOQ数据库的连接方案时,连接的生命周期完全被交个用户自己管理,JOOQ不会介入连接的提交或事务回滚和关闭。
            //在实际的ee项目应用中通常会使用datasource作为数据库连接方案,
            // 此时JOOQ自身提供了org.jooq.impl.DataSourceConnectionProvider作为连接管理方案,此外也可以使用其他第三方管理工具
            create = new DefaultDSLContext(connection, SQLDialect.MYSQL);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    public static void main(String[] args) {
           if(create != null){
               SelectConditionStep query = create.select(AUTHOR.FIRST_NAME).from(AUTHOR).where(AUTHOR.ID.eq(1));
               System.out.println("执行脚本为:"+query.getSQL());
               System.out.println(query.getBindValues());
               //查询返回对象为result
               Result result=query.fetch();
               System.out.println(String.format("返回结果为:\n%s",result));
           }
        try {
            if (connection != null){
                connection.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

}

自定义JOOQ执行监听器

其作用类似与mybtais拦截器

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;

import static com.*.jooqdemo.dao.Tables.AUTHOR;

public class JOOQExecuteListener extends DefaultExecuteListener {
    private static DSLContext create;
    private static Connection connection;
    static {
        //基于JDBC创建数据库连接
        final String userName ="root";
        final String password ="*";
        final String url ="jdbc:mysql://localhost:3306/jooq?serverTimezone=UTC";
        //Driver Conn

        try {

            connection = DriverManager.getConnection(url,userName,password);

            Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.MYSQL);
            //注册自定义执行监听器
            //ExecuteListeners are a useful tool to...
            //
            //implement custom logging 自定义日志
            //apply triggers written in Java  添加触发器开关
            //collect query execution statistics 执行信息统计
            configuration.set(new DefaultExecuteListenerProvider(new JOOQExecuteListener()));
            create = new DefaultDSLContext(configuration);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    public static void main(String[] args) {
        if(create != null){
            SelectConditionStep query =          create.select(AUTHOR.FIRST_NAME).from(AUTHOR).where(AUTHOR.ID.eq(1));
            Result result=query.fetch();
            System.out.println(String.format("返回结果为:\n%s",result));
        }
        try {
            if (connection != null){
                connection.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    private LocalDateTime beginDate;
    private String executeSql ;
    @Override
    //示例代码用于统计SQL执行
    public void executeStart(ExecuteContext ctx) {
        System.out.println("++++开始执行++++++");
        beginDate = LocalDateTime.now();
        executeSql = ctx.query().getSQL();
        super.executeStart(ctx);
    }

    @Override
    public void executeEnd(ExecuteContext ctx) {
        super.executeEnd(ctx);
        Duration duration =  Duration.between(beginDate,LocalDateTime.now());
        System.out.println(String.format("%s执行时间为%d    ms",executeSql,duration.toMillis()));
    }
}

jooq 事务

jooq本身提供了事务管理的功能,同时也支持事务托管给第三方如spring.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import static com.*.jooqdemo.dao.tables.Author.AUTHOR;

public class JOOQTransactionDemo {

    private DSLContext create;
    private Connection connection;

    private DSLContext initDSL() {
        //基于JDBC创建数据库连接
        final String userName = "root";
        final String password = "*";
        final String url = "jdbc:mysql://localhost:3306/jooq?serverTimezone=UTC";
        //Driver Conn

        try {

            connection = DriverManager.getConnection(url, userName, password);

            Configuration configuration = new DefaultConfiguration().set(connection).set(SQLDialect.MYSQL);
            create = new DefaultDSLContext(configuration);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return create;
    }

    public void transaction() {
        create.transaction(configuration1 -> {
            DSL.using(configuration1)
                    .insertInto(AUTHOR, AUTHOR.ID,AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
                    .values(6,"George", "Orwell")
                    .returning()
                    .fetchOne();
            //发生异常 事务回滚
            throw new RuntimeException("异常");


        });


    }

    public static void main(String[] args) {
        JOOQTransactionDemo jooqTransactionDemo = new JOOQTransactionDemo();
        jooqTransactionDemo.initDSL();
        jooqTransactionDemo.transaction();
    }
}

many to many

/**
 * 返回当前作者名下的书籍
 * author 和 book为一对多关系
 * public class Author implements Serializable {
 *
 *     private static final long serialVersionUID = 223223344;
 *
 *     private Integer id;
 *     private String  firstName;
 *     private String  lastName;
 *     private List<Book> books;
 *     }
 * JOOQ本身并不没有提供many to many 和one to many的支持,但通过扩展Mapper可以实现这样的功能
 * https://www.petrikainulainen.net/programming/jooq/jooq-tips-implementing-a-read-only-   one-to-many-relationship/
 * @param id
 * @return
 */

public com.*.jooq.demo.dao.tables.pojos.Author getAuthorBooks(int id){
    
    Stream<com.*.jooq.demo.dao.tables.pojos.Author> stream = null;
    JdbcMapper<com.*.jooq.demo.dao.tables.pojos.Author> jdbcMapper = = JdbcMapperFactory.newInstance().addKeys("id","books").newMapper(com.*.jooq.demo.dao.tables.pojos.Author.class);
    try {
         Author a = Author.AUTHOR.as("a");
         Book b = Book.BOOK.as("b");
         ResultSet resultSet       =dslContext.select(a.ID.as("id"),a.FIRST_NAME.as("first_name"),
                 a.LAST_NAME.as("last_name"),b.ID.as("books_id"),b.BOOK_NAME.as("books_book_name"),b.PUBLISH_DATE.as("books_publish_date"))
                 .from(a).join(b).on(a.ID.eq(b.AUTHOR_ID)).where(a.ID.eq(id)).fetchResultSet();
         stream = jdbcMapper.stream(resultSet);
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
   return  stream.collect(Collectors.toList()).get(0);
}
 类似资料: