在上一篇文章SpringBoot:与MyBatis一起工作中,我们学习了如何使用SpringBoot MyBatis Starter快速启动并运行Spring和MyBatis。 在本文中,我们将学习如何使用SpringBoot JOOQ Starter。
JOOQ(面向Java对象的查询)是包含SQL的持久性框架。
JOOQ提供以下功能:
- 使用DSL API构建Typesafe SQL
- 使用代码生成引用Typesafe数据库对象
- 易于使用的API用于查询和数据提取
- SQL记录和调试等
SpringBoot提供了一个启动程序spring-boot-starter-jooq ,以便能够与JOOQ快速集成。
在本文中,我们将逐步介绍如何使用spring-boot-starter-jooq 。
步骤1:创建SpringBoot Maven项目
创建一个基于SpringBoot Maven的项目并配置spring-boot-starter-jooq依赖项。
<?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
http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.sivalabs</groupId>
<artifactId>springboot-jooq-demo</artifactId>
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.3.3.RELEASE</version>
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.8</java.version>
</properties>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
</project>
我们将首先使用H2内存数据库,稍后将了解如何使用MySQL。
步骤2:创建数据库初始化脚本。
我们将创建一个包含2个表的简单数据库。
src / main / resources / schema.sql
DROP TABLE IF EXISTS POSTS;
CREATE TABLE POSTS (
ID int(11) NOT NULL AUTO_INCREMENT,
TITLE varchar(200) NOT NULL,
CONTENT LONGTEXT DEFAULT NULL,
CREATED_ON datetime DEFAULT NULL,
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS COMMENTS;
CREATE TABLE COMMENTS (
ID int(11) NOT NULL AUTO_INCREMENT,
POST_ID int(11) NOT NULL,
NAME varchar(200) NOT NULL,
EMAIL varchar(200) NOT NULL,
CONTENT LONGTEXT DEFAULT NULL,
CREATED_ON datetime DEFAULT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (POST_ID) REFERENCES POSTS(ID)
);
我们将使用data.sql脚本填充一些示例数据。
src / main / resources / data.sql
insert into posts(id, title, content, created_on) values(1, 'Post 1', 'This is post 1', '2016-01-03');
insert into posts(id, title, content, created_on) values(2, 'Post 2', 'This is post 2', '2016-01-05');
insert into posts(id, title, content, created_on) values(3, 'Post 3', 'This is post 3', '2016-01-07');
insert into comments(id, post_id, name, email, content, created_on)
values(1, 1, 'User1', 'user1@gmail.com', 'This is comment 1 on post 1', '2016-01-07');
insert into comments(id, post_id, name, email, content, created_on)
values(2, 1, 'User2', 'user2@gmail.com', 'This is comment 2 on post 1', '2016-01-07');
insert into comments(id, post_id, name, email, content, created_on)
values(3, 2, 'User1', 'user1@gmail.com', 'This is comment 1 on post 2', '2016-01-07');
步骤3:配置JOOQ Maven Codegen插件以生成数据库工件
我们将使用Maven配置文件根据数据库类型配置jooq-codegen-maven配置属性。
<profiles>
<profile>
<id>h2</id>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>${h2.version}</version>
</dependency>
</dependencies>
<configuration>
<jdbc>
<driver>org.h2.Driver</driver>
<url>jdbc:h2:~/springbootjooq</url>
</jdbc>
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.h2.H2Database</name>
<includes>.*</includes>
<excludes />
<inputSchema>PUBLIC</inputSchema>
</database>
<target>
<packageName>com.sivalabs.demo.jooq.domain</packageName>
<directory>gensrc/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
</plugins>
</build>
</profile>
<profile>
<id>mysql</id>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<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>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/test</url>
<user>root</user>
<password>admin</password>
</jdbc>
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes />
<inputSchema>test</inputSchema>
</database>
<target>
<packageName>com.sivalabs.demo.jooq.domain</packageName>
<directory>gensrc/main/java</directory>
</target>
</generator>
</configuration>
</plugin>
</plugins>
</build>
</profile>
</profiles>
我们已经使用适当的JDBC配置参数配置了两个配置文件( h2和mysql )。
我们已指定生成代码工件,并将其放置在gensrc / main / java目录下的 com.sivalabs.demo.jooq.domain包中。
我们可以如下运行激活H2或mysql配置文件的Maven构建:
mvn clean install -P h2 (or)
mvn clean install -P mysql
步骤4:配置Maven build-helper-maven-plugin插件以将生成的源添加为sources文件夹
我们将配置build-helper-maven-plugin插件,以便maven将添加JOOQ生成的代码驻留在
gensrc / main / java目录作为源文件夹。
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>add-source</goal>
</goals>
<configuration>
<sources>
<source>gensrc/main/java</source>
</sources>
</configuration>
</execution>
</executions>
</plugin>
步骤5:创建域对象。
我们可以使用这些域对象在整个层上传递数据,并使用JOOQ生成的数据库工件与数据库进行对话。
public class Post
{
private Integer id;
private String title;
private String content;
private Timestamp createdOn;
private List<Comment> comments = new ArrayList<>();
//setters & getters
}
public class Comment
{
private Integer id;
private Post post;
private String name;
private String email;
private String content;
private Timestamp createdOn;
//setters & getters
}
步骤6:使用JOOQ实施数据持久化方法,如下所示。
package com.sivalabs.demo;
import static com.sivalabs.demo.jooq.domain.tables.Posts.POSTS;
import static com.sivalabs.demo.jooq.domain.tables.Comments.COMMENTS;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.sivalabs.demo.entities.Comment;
import com.sivalabs.demo.entities.Post;
import com.sivalabs.demo.jooq.domain.tables.records.CommentsRecord;
import com.sivalabs.demo.jooq.domain.tables.records.PostsRecord;
@Service
@Transactional
public class BlogService
{
@Autowired
private DSLContext dsl;
public Post createPost(Post post){
PostsRecord postsRecord = dsl.insertInto(POSTS)
.set(POSTS.TITLE, post.getTitle())
.set(POSTS.CONTENT, post.getContent())
.set(POSTS.CREATED_ON, post.getCreatedOn())
.returning(POSTS.ID)
.fetchOne();
post.setId(postsRecord.getId());
return post;
}
public List<Post> getAllPosts(){
List<Post> posts = new ArrayList<>();
Result<Record> result = dsl.select().from(POSTS).fetch();
for (Record r : result) {
posts.add(getPostEntity(r));
}
return posts ;
}
public Post getPost(Integer postId){
Record record = dsl.select().
from(POSTS)
.where(POSTS.ID.eq(postId))
.fetchOne();
if(record != null)
{
Post post = getPostEntity(record);
Result<Record> commentRecords = dsl.select().
from(COMMENTS)
.where(COMMENTS.POST_ID.eq(postId))
.fetch();
for (Record r : commentRecords) {
post.addComment(getCommentEntity(r));
}
return post;
}
return null;
}
public Comment createComment(Comment comment){
CommentsRecord commentsRecord = dsl.insertInto(COMMENTS)
.set(COMMENTS.POST_ID, comment.getPost().getId())
.set(COMMENTS.NAME, comment.getName())
.set(COMMENTS.EMAIL, comment.getEmail())
.set(COMMENTS.CONTENT, comment.getContent())
.set(COMMENTS.CREATED_ON, comment.getCreatedOn())
.returning(COMMENTS.ID)
.fetchOne();
comment.setId(commentsRecord.getId());
return comment;
}
public void deleteComment(Integer commentId){
dsl.deleteFrom(COMMENTS)
.where(COMMENTS.ID.equal(commentId))
.execute();
}
private Post getPostEntity(Record r){
Integer id = r.getValue(POSTS.ID, Integer.class);
String title = r.getValue(POSTS.TITLE, String.class);
String content = r.getValue(POSTS.CONTENT, String.class);
Timestamp createdOn = r.getValue(POSTS.CREATED_ON, Timestamp.class);
return new Post(id, title, content, createdOn);
}
private Comment getCommentEntity(Record r) {
Integer id = r.getValue(COMMENTS.ID, Integer.class);
Integer postId = r.getValue(COMMENTS.POST_ID, Integer.class);
String name = r.getValue(COMMENTS.NAME, String.class);
String email = r.getValue(COMMENTS.EMAIL, String.class);
String content = r.getValue(COMMENTS.CONTENT, String.class);
Timestamp createdOn = r.getValue(COMMENTS.CREATED_ON, Timestamp.class);
return new Comment(id, postId, name, email, content, createdOn);
}
}
观察到我们正在将DSLContext实例自动连接到我们的Spring Bean中,并使用它来构建TypeSafe查询。
步骤7:创建入口点类和JUnit测试。
@SpringBootApplication
public class SpringbootJooqDemoApplication
{
public static void main(String[] args) {
SpringApplication.run(SpringbootJooqDemoApplication.class, args);
}
}
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(SpringbootJooqDemoApplication.class)
public class SpringbootJooqDemoApplicationTests
{
@Autowired
private BlogService blogService;
@Test
public void findAllPosts() {
List<Post> posts = blogService.getAllPosts();
assertNotNull(posts);
assertTrue(!posts.isEmpty());
for (Post post : posts)
{
System.err.println(post);
}
}
@Test
public void findPostById() {
Post post = blogService.getPost(1);
assertNotNull(post);
System.out.println(post);
List<Comment> comments = post.getComments();
System.out.println(comments);
}
@Test
public void createPost() {
Post post = new Post(0, "My new Post",
"This is my new test post",
new Timestamp(System.currentTimeMillis()));
Post savedPost = blogService.createPost(post);
Post newPost = blogService.getPost(savedPost.getId());
assertEquals("My new Post", newPost.getTitle());
assertEquals("This is my new test post", newPost.getContent());
}
@Test
public void createComment() {
Integer postId = 1;
Comment comment = new Comment(0, postId, "User4",
"user4@gmail.com", "This is my new comment on post1",
new Timestamp(System.currentTimeMillis()));
Comment savedComment = blogService.createComment(comment);
Post post = blogService.getPost(postId);
List<Comment> comments = post.getComments();
assertNotNull(comments);
for (Comment comm : comments)
{
if(savedComment.getId() == comm.getId()){
assertEquals("User4", comm.getName());
assertEquals("user4@gmail.com", comm.getEmail());
assertEquals("This is my new comment on post1", comm.getContent());
}
}
}
}
假设您已经使用H2概要文件生成了代码,那么我们可以在不进行任何进一步配置的情况下运行JUnit测试。
但是,如果使用mysql配置文件生成了代码,则必须在application.properties中配置以下属性。
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=admin
spring.jooq.sql-dialect=MYSQL
请注意,我们应该为数据库使用正确的SqlDialect ,否则在运行时可能会遇到SQL语法错误。
- 您可以在我的Github存储库中找到本文的源代码
https://github.com/sivaprasadreddy/springboot-tutorials/tree/master/springboot-jooq-demo
有关JOOQ的更多信息,请访问http://www.jooq.org/learn/
翻译自: https://www.javacodegeeks.com/2016/03/springboot-working-jooq.html