企业电子商务网站有哪些,网络优化的工作内容,wordpress 截取摘要,全面的河南网站建设在上一篇文章SpringBoot#xff1a;与MyBatis一起工作中#xff0c;我们学习了如何使用SpringBoot MyBatis Starter快速启动并运行Spring和MyBatis。 在本文中#xff0c;我们将学习如何使用SpringBoot JOOQ Starter。 JOOQ#xff08;面向Java对象的查询#xff09;是包… 在上一篇文章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 version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0 xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.sivalabs/groupIdartifactIdspringboot-jooq-demo/artifactIdpackagingjar/packagingversion1.0-SNAPSHOT/versionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion1.3.3.RELEASE/version/parentpropertiesproject.build.sourceEncodingUTF-8/project.build.sourceEncodingjava.version1.8/java.version/propertiesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactId/plugin/plugins/builddependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-jooq/artifactId/dependencydependencygroupIdcom.h2database/groupIdartifactIdh2/artifactId/dependencydependencygroupIdmysql/groupIdartifactIdmysql-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, user1gmail.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, user2gmail.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, user1gmail.com, This is comment 1 on post 2, 2016-01-07);步骤3配置JOOQ Maven Codegen插件以生成数据库工件 我们将使用Maven配置文件根据数据库类型配置jooq-codegen-maven配置属性。 profilesprofileidh2/idbuildpluginsplugingroupIdorg.jooq/groupIdartifactIdjooq-codegen-maven/artifactIdexecutionsexecutiongoalsgoalgenerate/goal/goals/execution/executionsdependenciesdependencygroupIdcom.h2database/groupIdartifactIdh2/artifactIdversion${h2.version}/version/dependency/dependenciesconfigurationjdbcdriverorg.h2.Driver/driverurljdbc:h2:~/springbootjooq/url/jdbcgeneratornameorg.jooq.util.DefaultGenerator/namedatabasenameorg.jooq.util.h2.H2Database/nameincludes.*/includesexcludes /inputSchemaPUBLIC/inputSchema/databasetargetpackageNamecom.sivalabs.demo.jooq.domain/packageNamedirectorygensrc/main/java/directory/target/generator/configuration/plugin/plugins/build/profileprofileidmysql/idbuildpluginsplugingroupIdorg.jooq/groupIdartifactIdjooq-codegen-maven/artifactIdexecutionsexecutiongoalsgoalgenerate/goal/goals/execution/executionsdependenciesdependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion${mysql.version}/version/dependency/dependenciesconfigurationjdbcdrivercom.mysql.jdbc.Driver/driverurljdbc:mysql://localhost:3306/test/urluserroot/userpasswordadmin/password/jdbcgeneratornameorg.jooq.util.DefaultGenerator/namedatabasenameorg.jooq.util.mysql.MySQLDatabase/nameincludes.*/includesexcludes /inputSchematest/inputSchema/databasetargetpackageNamecom.sivalabs.demo.jooq.domain/packageNamedirectorygensrc/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目录作为源文件夹。 plugingroupIdorg.codehaus.mojo/groupIdartifactIdbuild-helper-maven-plugin/artifactIdexecutionsexecutionphasegenerate-sources/phasegoalsgoaladd-source/goal/goalsconfigurationsourcessourcegensrc/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 ListComment 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 ListPost getAllPosts(){ ListPost posts new ArrayList(); ResultRecord 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);ResultRecord 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() {ListPost 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);ListComment 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, user4gmail.com, This is my new comment on post1, new Timestamp(System.currentTimeMillis()));Comment savedComment blogService.createComment(comment);Post post blogService.getPost(postId);ListComment comments post.getComments();assertNotNull(comments);for (Comment comm : comments){if(savedComment.getId() comm.getId()){assertEquals(User4, comm.getName());assertEquals(user4gmail.com, comm.getEmail());assertEquals(This is my new comment on post1, comm.getContent());}}}} 假设您已经使用H2概要文件生成了代码那么我们可以在不进行任何进一步配置的情况下运行JUnit测试。 但是如果使用mysql配置文件生成了代码则必须在application.properties中配置以下属性。 spring.datasource.driver-class-namecom.mysql.jdbc.Driver
spring.datasource.urljdbc:mysql://localhost:3306/test
spring.datasource.usernameroot
spring.datasource.passwordadminspring.jooq.sql-dialectMYSQL 请注意我们应该为数据库使用正确的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
