JOOQ入门指南
刘昌翰
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);
}
类似资料：