在上一篇文章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 {@Autowiredprivate 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 {@Autowiredprivate BlogService blogService;@Testpublic void findAllPosts() {List<Post> posts = blogService.getAllPosts();assertNotNull(posts);assertTrue(!posts.isEmpty());for (Post post : posts){System.err.println(post);}}@Testpublic void findPostById() {Post post = blogService.getPost(1);assertNotNull(post);System.out.println(post);List<Comment> comments = post.getComments();System.out.println(comments);}@Testpublic 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());}@Testpublic 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=adminspring.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

總結

以上是生活随笔為你收集整理的SpringBoot：与JOOQ合作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。