spring boot 中使用jooq管理数据
一、jooq是什么?
jOOQ是Data Geekery提供的基于Java的轻量级数据库访问库,通过特定的语法将类以及数据库模型翻译成对应的SQL语句实现实体与关系的映射,在数据库与类之间建立起一一对应关系,也可以让我们通过它的流式API构建出类型安全的SQL查询。jOOQ是一个基于Java编写SQL的工具包,具有简单、轻量、函数式编程写SQL等独特优势,非常适合敏捷快速迭代开发。jOOQ不管是商业版,还是开源版本都能跟Spring Boot一块使用。
二、jooq 的特点:
JOOQ是一套持久层框架,主要特点是:
- 逆向工程,自动根据数据库结构生成对应的类
- 流式的API,像写SQL一样
- 提供类型安全的SQL查询,JOOQ的主要优势,可以帮助我们在写SQL时就做检查
- 支持几乎所有DDL,DML
- 可以内部避免SQL注入安全问题
- 支持SQL渲染,打印,绑定
- 使用非常轻便灵活
- 可以用JPA做大部分简单的查询,用JOOQ写复杂的
- 可以只用JOOQ作为SQL执行器
- 可以只用来生成SQL语句(类型安全)
- 可以只用来处理SQL执行结果
- 支持Flyway,JAX-RS,JavaFX,Kotlin,Nashorn,Scala,Groovy,NoSQL
- 支持XML,CSV,JSON,HTML导入导出
- 支持事物回滚
配置依赖和插件
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.2.2.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>springboot-jooq</artifactId> <version>0.0.1-SNAPSHOT</version> <name>springboot-jooq</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.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </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> </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> <executions> <execution> <goals> <goal>generate</goal> </goals> </execution> </executions> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency> </dependencies> <configuration> <!-- <jdbc>--> <!-- <url>jdbc:mysql://localhost:3306/demo?serverTimezone=UTC</url>--> <!-- <user>root</user>--> <!-- <password>root</password>--> <!-- <driver>com.mysql.cj.jdbc.Driver</driver>--> <!-- </jdbc>--> <!-- <!–逆向生成配置文件–>--> <configurationFile>src/main/resources/library.xml</configurationFile> <generator> <generate> <pojos>true</pojos> <fluentSetters>true</fluentSetters> </generate> </generator> </configuration> </plugin> </plugins> </build> </project>
逆向工程配置文件
在项目的resources目录下新建library.xml,由于网上JOOQ的教程比较少,且比较老,所以建议去官网拷贝对应版本的配置文件,并酌情修改,否则会无法生成。
library.xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd"> <!-- Configure the database connection here --> <jdbc> <driver>com.mysql.cj.jdbc.Driver</driver> <url>jdbc:mysql://localhost:3306/demo?serverTimezone=UTC</url> <user>root</user> <password>root</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> <!-- The database schema (or in the absence of schema support, in your RDBMS this can be the owner, user, database name) to be generated --> <inputSchema>demo</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> <!-- All elements that are excluded from your schema (A Java regular expression. Use the pipe to separate several expressions). Excludes match before includes, i.e. excludes have a higher priority --> <excludes></excludes> </database> <target> <!-- The destination package of your generated classes (within the destination directory) --> <packageName>com.example.springbootjooq.generated</packageName> <!-- The destination directory of your generated classes. Using Maven directory layout here --> <directory>src/main/java</directory> </target> </generator> </configuration>
数据表
-- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
使用maven点击lifecycle下单compile 来实施逆向工程生产相关的类
下面以user表为了使用jooq进行增删改查
controller
package com.example.springbootjooq.demo; import com.example.springbootjooq.generated.tables.pojos.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; /** * @author chenling */ @RestController @RequestMapping("/demo/") public class DemoController { @Autowired private DemoService service; @RequestMapping("/insert/user/{name}/{age}") public void insert(@PathVariable String age, @PathVariable String name){ service.insert(new User().setAge(age).setName(name)); } @RequestMapping("/update/user/{name}/{age}") public void update(@PathVariable String age, @PathVariable String name){ service.update(new User().setAge(age).setName(name)); } @RequestMapping("/delete/user/{id}") public void delete(@PathVariable Integer id){ service.delete(id); } @RequestMapping("/select/user/{id}") public User selectByID(@PathVariable Integer id){ return service.selectById(id); } @RequestMapping("/select/user/") public List<User> selectByID(){ return service.selectAll(); } }
service
package com.example.springbootjooq.demo; import com.example.springbootjooq.generated.tables.pojos.User; import org.springframework.transaction.annotation.Transactional; import java.util.Iterator; import java.util.List; /** * @author chenling */ public interface DemoService { /** 删除 */ @Transactional public void delete(int id); /** 增加*/ @Transactional public void insert(User user); /** 更新*/ @Transactional public int update(User user); /** 查询单个*/ public User selectById(int id); /** 查询全部列表*/ public List<User> selectAll(); }
serviceimpl
package com.example.springbootjooq.demo; import com.example.springbootjooq.generated.tables.pojos.User; 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 java.util.Iterator; import java.util.List; @Service public class DemoServiceImpl implements DemoService { @Autowired DSLContext create; com.example.springbootjooq.generated.tables.User USER = com.example.springbootjooq.generated.tables.User.USER; @Override public void delete(int id) { create.delete(USER).where(USER.ID.eq(id)).execute(); } @Override public void insert(User user) { create.insertInto(USER) .columns(USER.NAME,USER.AGE) .values(user.getName(), user.getAge()) .execute(); } @Override public int update(User user) { create.update(USER).set((Record) user); return 0; } @Override public User selectById(int id) { return create.select(USER.NAME,USER.AGE).from(USER).where(USER.ID.eq(id)).fetchInto(User.class).get(0); } @Override public List<User> selectAll() { return create.select().from(USER).fetchInto(User.class); } }