jooq基本使用
1、jooq定义及作用
jOOQ，是一个ORM框架，利用其生成的Java代码和流畅的API，可以快速构建有类型约束的安全的SQL语句。
2、demo搭建
测试使用数据库
pom文件 依赖以及自动生成模型plugin
<?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.5.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>jooq</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>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>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
</dependency>
<!-- 省略get/set等方法 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</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>5.1.45</version>
</dependency>
</dependencies>
<configuration>
<!--逆向生成配置文件-->
<configurationFile>src/main/resources/JooqConfig.xml</configurationFile>
<generator>
<generate>
<pojos>true</pojos>
<fluentSetters>true</fluentSetters>
</generate>
</generator>
</configuration>
</plugin>
</plugins>
</build>
</project>
jooq自动生成模型设置
<?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.jdbc.Driver</driver>
<url>jdbc:mysql://127.0.0.1:3306/test</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>test</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.jooq.model</packageName>
<!-- The destination directory of your generated classes. Using Maven directory layout here -->
<directory>src/main/java</directory>
</target>
</generator>
</configuration>
maven插件点击 generate即可生成com.example.jooq.model 下的文件
利用jooq进行crud
@Service
public class ProductServiceImpl implements ProductService {
@Autowired
private DSLContext dslContext;
private com.example.jooq.model.tables.Category category = com.example.jooq.model.tables.Category.CATEGORY;
private com.example.jooq.model.tables.Product product = com.example.jooq.model.tables.Product.PRODUCT;
public List<Category> getAllCategory() {
return dslContext.select().from(category)
.fetch().into(Category.class);
}
/**
* 插入商品信息
* @param productInfo
* @return
*/
@Override
public int insertProduct(Product productInfo) {
ProductRecord productRecord = dslContext.newRecord(product);
productInfo.setId(productInfo.getId());
productInfo.setTitle(productInfo.getTitle());
productInfo.setDetailurl(productInfo.getDetailurl());
productInfo.setImageurl(productInfo.getImageurl());
productInfo.setCateid(productInfo.getCateid());
productInfo.setPrice(productInfo.getPrice());
productInfo.setShop(productInfo.getShop());
productInfo.setShopurl(productInfo.getShopurl());
return productRecord.update();
}
/**
* 根据id删除商品
* @param productInfo
* @return
*/
@Override
public int deleteProduct(Product productInfo) {
ProductRecord productRecord = dslContext.newRecord(product);
productRecord.setId(productInfo.getId());
return productRecord.delete();
}
/**
* 根据类目id查询下属的商品
* @param cateId
* @return
*/
@Override
public List<Product> getProduct(Integer cateId) {
return dslContext.select().from(product)
.where(product.CATEID.eq(cateId))
.fetch().into(Product.class);
}
/**
* 根据类目统计商品数
* @return
*/
@Override
public List<CateSumInfo> countSum() {
List<CateSumInfo> sumInfos = dslContext.select(category.ID.as("cateId"), category.NAME.as("name"), count().as("count")).from(category)
.innerJoin(product).on(category.ID.eq(product.CATEID))
.where(category.ID.eq(1))
.fetch().into(CateSumInfo.class);
return sumInfos;
}
}
5、复杂查询样例
/** 一个复杂的查询
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
AND BOOK.PUBLISHED > DATE '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
LIMIT 2
OFFSET 1
**/
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID))
.where(BOOK.LANGUAGE.eq("DE"))
.and(BOOK.PUBLISHED.gt(date("2008-01-01")))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().gt(5))
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(2)
.offset(1)
6、详细介绍