SpringBoot：与JOOQ合作

最新推荐文章于 2024-03-13 21:24:03 发布

dnc8371

最新推荐文章于 2024-03-13 21:24:03 发布

阅读量458

点赞数

文章标签：数据库 mysql java mybatis maven

在上一篇文章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

dnc8371

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot：与JOOQ合作

在上一篇文章SpringBoot：与MyBatis一起工作中，我们学习了如何使用SpringBoot MyBatis Starter快速启动并运行Spring和MyBatis。在本文中，我们将学习如何使用SpringBoot JOOQ Starter。 JOOQ（面向Java对象的查询）是包含SQL的持久性框架。 JOOQ提供以下功能：使用DSL API构建Typesafe...
复制链接

扫一扫