最近在翻阅springboot官方文档时,看见官方文档中有推荐一款ORM框架——jOOQ,可能很多朋友和我一样都没有用过这款框架,于是百度了一下,发现用过的朋友的都说它在代码层面比Mybatis简洁得多,而且性能也非常优异,抱着学习的态度,通过查询相关资料,在本地写了一个demo工程,体验了一下,在此记录一下。demo基于springboot 2.2.0,jooq相关组件版本为3.12.1
创建springboot工程,引入jooq的starter
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
引入jooq插件依赖
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
</dependency>
plugin配置
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq.version}</version>
<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>
<configurationFile>src/main/resources/JooqConfig.xml</configurationFile>
</configuration>
</plugin>
在工程的src/main/resources目录下创建JooqConfig.xml文件,内容如下:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<jdbc>
<driver>com.mysql.jdbc.Driver</driver>
<!-- 配置数据库地址 -->
<url>jdbc:mysql://127.0.0.1:3306/students?characterEncoding=UTF-8</url>
<!-- 配置数据库用户名-->
<user>root</user>
<!-- 配置数据库密码-->
<password>1234</password>
</jdbc>
<generator>
<!-- 代码生成器 -->
<!-- <name>org.jooq.meta.mysql.MySQLDatabase</name>-->
<database>
<!--强制为scheme1模式下所有的含有id域生成id-->
<!--是否重写主键-->
<name>org.jooq.meta.mysql.MySQLDatabase</name>
<!--include和exclude用于控制为数据库中哪些表生成代码-->
<includes>.*</includes>
<excludes></excludes>
<!--数据库名称-->
<inputSchema>students</inputSchema>
</database>
<generate>
<!--是否生成dao和pojo-->
<daos>true</daos>
<pojos>true</pojos>
<!--是否把数据库时间类型映射到java 8时间类型-->
<javaTimeTypes>true</javaTimeTypes>
<!--<interfaces>true</interfaces>-->
<!--是否在生成的代码中添加spring注释,比如@Repository-->
<springAnnotations>false</springAnnotations>
</generate>
<target>
<!--生成代码文件的包名及放置目录-->
<packageName>com.twp.spring.jooq</packageName>
<directory>src/main/java</directory>
</target>
</generator>
</configuration>
在数据库创建表,完成之后,启动jooq-codegen插件
CREATE TABLE celebrity (
id INT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE,
birth_place VARCHAR(50),
PRIMARY KEY (`id`)
)ENGINE=INNODB CHARSET=utf8;
执行之后,会在项目目录中生成如下代码,其中会有两个以表名命名的类,一个代表数据的表,一个代表实体类
代码生成后,下面尝试实现简单的CRUD功能,其他的复杂查询后续再摸索
public interface CelebrityService {
int createCelebrity(Celebrity celebrity);
List<Celebrity> findAll();
int updateCelebrity(Celebrity celebrity);
int deleteCelebrity(int id);
}
@Service
public class CelebrityServiceImpl implements CelebrityService {
private final DSLContext dslContext;
@Autowired
public CelebrityServiceImpl(DSLContext dslContext){
this.dslContext = dslContext;
}
@Override
public int createCelebrity(Celebrity celebrity) {
com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
int execute = dslContext.insertInto(celeTbl)
.columns(celeTbl.ID, celeTbl.FIRST_NAME, celeTbl.LAST_NAME, celeTbl.DATE_OF_BIRTH, celeTbl.BIRTH_PLACE)
.values(celebrity.getId(), celebrity.getFirstName(), celebrity.getLastName(), celebrity.getDateOfBirth(), celebrity.getBirthPlace())
.execute();
return execute;
}
@Override
public List<Celebrity> findAll() {
com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
List<Celebrity> celebrities = dslContext.select().from(celeTbl).fetchInto(Celebrity.class);
return celebrities;
}
@Override
public int updateCelebrity(Celebrity celebrity) {
com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
int execute = dslContext.update(celeTbl)
.set(celeTbl.FIRST_NAME,celebrity.getFirstName())
.set(celeTbl.LAST_NAME,celebrity.getLastName())
.set(celeTbl.DATE_OF_BIRTH,celebrity.getDateOfBirth())
.set(celeTbl.BIRTH_PLACE,celebrity.getBirthPlace())
.set(celeTbl.BIRTH_PLACE, celebrity.getBirthPlace())
.where(celeTbl.ID.equal(celebrity.getId()))
.execute();
return execute;
}
@Override
public int deleteCelebrity(int id) {
com.twp.spring.jooq.tables.Celebrity celeTbl = new com.twp.spring.jooq.tables.Celebrity();
int execute = dslContext.delete(celeTbl).where(celeTbl.ID.equal(id)).execute();
return execute;
}
}
模拟数据进行测试
@SpringBootTest
@RunWith(SpringRunner.class)
public class SpringJooqApplicationTests {
@Autowired
private CelebrityService service;
@Test
public void createTest(){
Celebrity celebrity1 = new Celebrity(1,"张","爱玲", LocalDate.of(1920,9,30),"上海");
Celebrity celebrity2 = new Celebrity(2,"鲁","迅", LocalDate.of(1881,9,25),"浙江绍兴");
Celebrity celebrity3 = new Celebrity(3,"老","舍", LocalDate.of(1899,2,3),"北京");
Celebrity celebrity4 = new Celebrity(4,"巴","金", LocalDate.of(1904,11,25),"四川成都");
List<Celebrity> celebrityList = new ArrayList<Celebrity>(){{
add(celebrity1);
add(celebrity2);
add(celebrity3);
add(celebrity4);
}};
celebrityList.forEach((cele)->{
int create = service.createCelebrity(cele);
System.out.println("create:"+create);
});
}
@Test
public void retrievallTest(){
service.findAll().forEach((cele)->{
System.out.println("celebrity:" + cele.toString());
});
}
@Test
public void updateTest(){
Celebrity celebrity = new Celebrity(2,"鲁","迅", LocalDate.of(1881,9,25),"浙江省绍兴府会稽县");
int update = service.updateCelebrity(celebrity);
System.out.println("update:" + update);
}
@Test
public void deleteTest(){
int i = service.deleteCelebrity(1);
System.out.println("delete:" + i);
}
}
新增:
查询:
更新:
删除:
jOOQ支持很多的复杂查询功能,在编写代码时只需以面向对象编程的语法组装查询条件,代码可读性更高,操作也比较简单,配置较少,很大程度上可以提高开发效率。