简介
JOOQ(Java Object Oriented Querying),是基于Java的轻量级数据库访问库,通过特定的语法将类以及数据库模式翻译成对应的SQL语句实现实体与关系的映射。
JOOQ具有如下的特点:
继承了ORM框架的优点,简单操作,类型安全等。jOOQ将SQL建模为内部DSL,使用Java编译器编译SQL语法,元数据和数据类型。
JOOQ会根据数据库的元数据来生成对应的实体类,省略了原有开发中不断修改对应数据库的类名,属性名。
JOOQ允许运行时配置数据库模式,且支持行级别的安全。 支持联合查询,多表查询,存储过程等数据库高级操作。
入门
Spring Boot集成JOOQ
1.创建springboot基础项目,并添加相关依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.3</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>3.13.1</version>
</dependency>
除了这些之外,还要在pom.xml中添加插件相关内容:
<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>5.1.21</version>
</dependency>
</dependencies>
<configuration>
<configurationFile>src/main/resources/JooqConfig.xml</configurationFile>
</configuration>
</plugin>
2.通过第一步可以看出需要在项目工程中添加JooqConfig.xml到指定目录下,并填充信息:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<jdbc>
<driver>com.mysql.jdbc.Driver</driver>
<!-- 配置数据库地址 -->
<url>jdbc:mysql://192.168.1.105:3306/demo?characterEncoding=UTF-8</url>
<!-- 配置数据库用户名-->
<user>root</user>
<!-- 配置数据库密码-->
<password>Root1234</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>demo</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.demo.main.jooq</packageName>
<directory>src/main/java</directory>
</target>
</generator>
</configuration>
配置application.properties:
spring.datasource.url=jdbc:mysql://192.168.1.105:3306/demo?characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=Root1234
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
3.创建数据库:
4.点击maven项目中的install,等待执行结束,便可以看到工程目录发生改变,在main包下生成很多和jooq相关类:
JOOQ包
tables包
tables包下面有三个自动生成的包,分别是daos,pojos,records。会根据JooqConfig.xml中的配置动态生成包内容。
1.daos
daos包下面的dao层会有自动生成的一些定义好的增删改方法,可以减少基本的crud操作。
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq.tables.daos;
import com.demo.main.jooq.tables.Student;
import com.demo.main.jooq.tables.records.StudentRecord;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.impl.DAOImpl;
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class StudentDao extends DAOImpl<StudentRecord, com.demo.main.jooq.tables.pojos.Student, Integer> {
/**
* Create a new StudentDao without any configuration
*/
public StudentDao() {
super(Student.STUDENT, com.demo.main.jooq.tables.pojos.Student.class);
}
/**
* Create a new StudentDao with an attached configuration
*/
public StudentDao(Configuration configuration) {
super(Student.STUDENT, com.demo.main.jooq.tables.pojos.Student.class, configuration);
}
@Override
public Integer getId(com.demo.main.jooq.tables.pojos.Student object) {
return object.getId();
}
/**
* Fetch records that have <code>id BETWEEN lowerInclusive AND upperInclusive</code>
*/
public List<com.demo.main.jooq.tables.pojos.Student> fetchRangeOfId(Integer lowerInclusive, Integer upperInclusive) {
return fetchRange(Student.STUDENT.ID, lowerInclusive, upperInclusive);
}
/**
* Fetch records that have <code>id IN (values)</code>
*/
public List<com.demo.main.jooq.tables.pojos.Student> fetchById(Integer... values) {
return fetch(Student.STUDENT.ID, values);
}
/**
* Fetch a unique record that has <code>id = value</code>
*/
public com.demo.main.jooq.tables.pojos.Student fetchOneById(Integer value) {
return fetchOne(Student.STUDENT.ID, value);
}
/**
* Fetch records that have <code>classname BETWEEN lowerInclusive AND upperInclusive</code>
*/
public List<com.demo.main.jooq.tables.pojos.Student> fetchRangeOfClassname(String lowerInclusive, String upperInclusive) {
return fetchRange(Student.STUDENT.CLASSNAME, lowerInclusive, upperInclusive);
}
/**
* Fetch records that have <code>classname IN (values)</code>
*/
public List<com.demo.main.jooq.tables.pojos.Student> fetchByClassname(String... values) {
return fetch(Student.STUDENT.CLASSNAME, values);
}
}
2.pojos
序列化java类,包含get,set和toString方法
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq.tables.pojos;
import java.io.Serializable;
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Student implements Serializable {
private static final long serialVersionUID = -750399146;
private Integer id;
private String classname;
public Student() {}
public Student(Student value) {
this.id = value.id;
this.classname = value.classname;
}
public Student(
Integer id,
String classname
) {
this.id = id;
this.classname = classname;
}
public Integer getId() {
return this.id;
}
public void setId(Integer id) {
this.id = id;
}
public String getClassname() {
return this.classname;
}
public void setClassname(String classname) {
this.classname = classname;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder("Student (");
sb.append(id);
sb.append(", ").append(classname);
sb.append(")");
return sb.toString();
}
}
3.records
可以参考https://blog.csdn.net/compassy/article/details/104646748/去理解
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq.tables.records;
import com.demo.main.jooq.tables.Student;
import org.jooq.Field;
import org.jooq.Record1;
import org.jooq.Record2;
import org.jooq.Row2;
import org.jooq.impl.UpdatableRecordImpl;
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class StudentRecord extends UpdatableRecordImpl<StudentRecord> implements Record2<Integer, String> {
private static final long serialVersionUID = -530818566;
/**
* Setter for <code>demo.student.id</code>.
*/
public void setId(Integer value) {
set(0, value);
}
/**
* Getter for <code>demo.student.id</code>.
*/
public Integer getId() {
return (Integer) get(0);
}
/**
* Setter for <code>demo.student.classname</code>.
*/
public void setClassname(String value) {
set(1, value);
}
/**
* Getter for <code>demo.student.classname</code>.
*/
public String getClassname() {
return (String) get(1);
}
// -------------------------------------------------------------------------
// Primary key information
// -------------------------------------------------------------------------
@Override
public Record1<Integer> key() {
return (Record1) super.key();
}
// -------------------------------------------------------------------------
// Record2 type implementation
// -------------------------------------------------------------------------
@Override
public Row2<Integer, String> fieldsRow() {
return (Row2) super.fieldsRow();
}
@Override
public Row2<Integer, String> valuesRow() {
return (Row2) super.valuesRow();
}
@Override
public Field<Integer> field1() {
return Student.STUDENT.ID;
}
@Override
public Field<String> field2() {
return Student.STUDENT.CLASSNAME;
}
@Override
public Integer component1() {
return getId();
}
@Override
public String component2() {
return getClassname();
}
@Override
public Integer value1() {
return getId();
}
@Override
public String value2() {
return getClassname();
}
@Override
public StudentRecord value1(Integer value) {
setId(value);
return this;
}
@Override
public StudentRecord value2(String value) {
setClassname(value);
return this;
}
@Override
public StudentRecord values(Integer value1, String value2) {
value1(value1);
value2(value2);
return this;
}
// -------------------------------------------------------------------------
// Constructors
// -------------------------------------------------------------------------
/**
* Create a detached StudentRecord
*/
public StudentRecord() {
super(Student.STUDENT);
}
/**
* Create a detached, initialised StudentRecord
*/
public StudentRecord(Integer id, String classname) {
super(Student.STUDENT);
set(0, id);
set(1, classname);
}
}
4.DefaultCatalog,Demo,Keys,Tables,( Indexes,因为没创建索引没有)
DefaultCatalog.java:里面存放的是Demo.java也就是数据库名.
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq;
import java.util.Arrays;
import java.util.List;
import org.jooq.Schema;
import org.jooq.impl.CatalogImpl;
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class DefaultCatalog extends CatalogImpl {
private static final long serialVersionUID = -1340258766;
/**
* The reference instance of <code>DEFAULT_CATALOG</code>
*/
public static final DefaultCatalog DEFAULT_CATALOG = new DefaultCatalog();
/**
* The schema <code>demo</code>.
*/
public final Demo DEMO = Demo.DEMO;
/**
* No further instances allowed
*/
private DefaultCatalog() {
super("");
}
@Override
public final List<Schema> getSchemas() {
return Arrays.<Schema>asList(
Demo.DEMO);
}
}
Demo.java里面存放的是数据库的表.
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq;
import com.demo.main.jooq.tables.Student;
import java.util.Arrays;
import java.util.List;
import org.jooq.Catalog;
import org.jooq.Table;
import org.jooq.impl.SchemaImpl;
/**
* This class is generated by jOOQ.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Demo extends SchemaImpl {
private static final long serialVersionUID = 1935931376;
/**
* The reference instance of <code>demo</code>
*/
public static final Demo DEMO = new Demo();
/**
* The table <code>demo.student</code>.
*/
public final Student STUDENT = Student.STUDENT;
/**
* No further instances allowed
*/
private Demo() {
super("demo", null);
}
@Override
public Catalog getCatalog() {
return DefaultCatalog.DEFAULT_CATALOG;
}
@Override
public final List<Table<?>> getTables() {
return Arrays.<Table<?>>asList(
Student.STUDENT);
}
}
Tables.java里面存放的是表名,一般都是通过这个类去访问表和字段的.
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq;
import com.demo.main.jooq.tables.Student;
/**
* Convenience access to all tables in demo
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Tables {
/**
* The table <code>demo.student</code>.
*/
public static final Student STUDENT = Student.STUDENT;
}
Keys.java里面有两个类Identities0存放的自增字段,UniqueKeys0存放的是唯一字段.
/*
* This file is generated by jOOQ.
*/
package com.demo.main.jooq;
import com.demo.main.jooq.tables.Student;
import com.demo.main.jooq.tables.records.StudentRecord;
import org.jooq.TableField;
import org.jooq.UniqueKey;
import org.jooq.impl.Internal;
/**
* A class modelling foreign key relationships and constraints of tables of
* the <code>demo</code> schema.
*/
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Keys {
// -------------------------------------------------------------------------
// IDENTITY definitions
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// UNIQUE and PRIMARY KEY definitions
// -------------------------------------------------------------------------
public static final UniqueKey<StudentRecord> KEY_STUDENT_PRIMARY = UniqueKeys0.KEY_STUDENT_PRIMARY;
// -------------------------------------------------------------------------
// FOREIGN KEY definitions
// -------------------------------------------------------------------------
// -------------------------------------------------------------------------
// [#1459] distribute members to avoid static initialisers > 64kb
// -------------------------------------------------------------------------
private static class UniqueKeys0 {
public static final UniqueKey<StudentRecord> KEY_STUDENT_PRIMARY = Internal.createUniqueKey(Student.STUDENT, "KEY_student_PRIMARY", new TableField[] { Student.STUDENT.ID }, true);
}
}
Indexes.java存放的是表的主键(索引).