ORM框架---JOOQ简单介绍

一，什么是JOOQ

继Hibernate Mybatis 之后的又一ORM框架

二，特点

1.get back in control of your sql

2.DSL(Domain Specific Language )风格，代码够简单和清晰

3.支持主流的RDMS�和更多的特性，如self-joins，union，存储过程，复杂的子查询等等。

4.

 

三，使用

pom

<?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.1.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>springdemo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>spring-demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
		<jooq.version>3.12.1</jooq.version>
		<jdbc.driver>com.mysql.jdbc.Driver</jdbc.driver>
		<jdbc.url>jdbc:mysql://127.0.0.1:3306/db_test?characterEncoding=UTF-8</jdbc.url>
		<jdbc.user>root</jdbc.user>
		<jdbc.password>123456</jdbc.password>
		<jdbc.database.name>db_test</jdbc.database.name>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>

		<!-- jooq -->
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq</artifactId>
			<version>${jooq.version}</version>
		</dependency>
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq-meta</artifactId>
			<version>${jooq.version}</version>
		</dependency>
		<dependency>
			<groupId>org.jooq</groupId>
			<artifactId>jooq-codegen</artifactId>
			<version>${jooq.version}</version>
		</dependency>

		<!-- druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.6</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</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>
		</plugins>
	</build>



	<!-- jooq 代码自动生成 maven插件-->
	<profiles>
		<profile>
			<id>jooq</id>
			<properties />
			<activation>
				<property>
					<name>jooq</name>
				</property>
			</activation>
			<build>
				<plugins>
					<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>
							<jdbc>
								<driver>${jdbc.driver}</driver>
								<url>${jdbc.url}</url>
								<user>${jdbc.user}</user>
								<password>${jdbc.password}</password>
							</jdbc>
							<generator>
								<database>
									<name>org.jooq.meta.mysql.MySQLDatabase</name>
									<includes>.*</includes>
									<excludes />
									<inputSchema>${jdbc.database.name}</inputSchema>
									<forcedTypes>
										<forcedType>
											<name>BOOLEAN</name>
											<types>(?i:TINYINT(\s*\(\d+\))?(\s*UNSIGNED)?)</types>
										</forcedType>
									</forcedTypes>
								</database>
								<generate>
									<deprecated>false</deprecated>
								</generate>

								<!-- 生成代码存放目录,一定要是空文件夹，否则会出现问题：清空整个文件夹 -->
								<!-- 使用方法：在pom.xml所在的目录下敲mvn命令:   mvn clean install -Djooq -->
								<target>
									<packageName>com.example.demo.jooq</packageName>
									<directory>src/main/java</directory>
								</target>
								<generate>
									<pojos>false</pojos>
									<daos>false</daos>
								</generate>
							</generator>
						</configuration>
					</plugin>
				</plugins>
			</build>
		</profile>
	</profiles>

</project>

自动生成的代码结构：

service

package com.example.demo.service;

import com.example.demo.jooq.Tables;
import com.example.demo.jooq.tables.Student;
import com.example.demo.jooq.tables.records.StudentRecord;
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.List;

/**
 * Created by zyl on 2019/11/20.
 */
@Service
public class JooqService {

    @Autowired
    DSLContext dslContext;


    public int insert(){
        int count = dslContext.insertInto(Tables.STUDENT)
                .set(Student.STUDENT.ID,1001012)
                .set(Student.STUDENT.NAME,"朱二龙")
                .set(Student.STUDENT.AGE,18)
                .set(Student.STUDENT.ADDRESS,"湖南")
                .set(Student.STUDENT.GENDER,"male")
                .execute();
        return count;
    }

    public int query(){
        int pageNum  = 2;//页数，从0开始
        int pageSize = 1;//每页数据数
        //查总数
        Integer into = dslContext.selectCount().from(Tables.STUDENT).fetchOne().into(Integer.class);
        System.out.println("查总数: "+ into);

        //分页查询list
        List<StudentRecord> studentRecords = dslContext.select().from(Tables.STUDENT)
                .orderBy(Tables.STUDENT.ID.desc())
                .limit(pageSize).offset(pageNum-1)
                .fetchInto(StudentRecord.class);

        studentRecords.forEach(s->{
            System.out.println(s.toString());
        });

        return studentRecords.size();
    }

    public int delete(){
        int execute = dslContext.delete(Tables.STUDENT)
                .where(Tables.STUDENT.ID.eq(1001012))
                .execute();
        return execute;
    }
    public int update(){
        int count = dslContext.update(Tables.STUDENT)
                .set(Tables.STUDENT.ADDRESS, "湖南省长沙市")
                .execute();
        return count;
    }






}

OVER