SpringBoot + JOOQ + MySQL8 +alibaba Druid 实现简单增删改查功能

拼搏现实的明天。 2023-10-10 12:13 12阅读 0赞

### 简介 ###

[官网链接][Link 1]

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导入导出
     *  支持事物回滚

## SpringBoot + JOOQ + MySQL8 +alibaba Druid 项目搭建 ##

### pom.xml ###

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>com.zzg</groupId>
    	<artifactId>car_sys</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.1.2.RELEASE</version>
    	</parent>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter</artifactId>
    		</dependency>
    		
    		<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>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-validation</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-jooq</artifactId>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>org.jooq</groupId>
    			<artifactId>jooq-codegen</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.jooq</groupId>
    			<artifactId>jooq-meta</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>1.1.10</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>fastjson</artifactId>
    			<version>1.2.62</version>
    		</dependency>
    		
    		
    
    	</dependencies>
    
    	<build>
    		<plugins>
    			
    			<plugin>
    				<groupId>org.apache.maven.plugins</groupId>
    				<artifactId>maven-compiler-plugin</artifactId>
    				<configuration>
    					<source>1.8</source>
    					<target>1.8</target>
    					<encoding>UTF-8</encoding>
    				</configuration>
    			</plugin>
    			
    			<plugin>
    				<groupId>org.jooq</groupId>
    				<artifactId>jooq-codegen-maven</artifactId>
    				<version>3.11.9</version>
    				<configuration>
    					
    					<jdbc>
    						<driver>com.mysql.cj.jdbc.Driver</driver>
    						<url>jdbc:mysql://192.168.1.75:3306/auth_shrio?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true</url>
    						<user>root</user>
    						<password>123456</password>
    					</jdbc>
    					<generator>
    						<database>
    							
    							<includes>applicants</includes>
    							
    							<excludes></excludes>
    
    							
    							<inputSchema>auth_shrio</inputSchema>
    						</database>
    						
    						<target>
    							<packageName>com.zzg.jooq</packageName>
    							<directory>/src/main/java</directory>
    						</target>
    					</generator>
    				</configuration>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

### application.yml ###

server:
      port: 8098
    spring:
      ##数据库连接信息
      datasource:
        url: jdbc:mysql://192.168.1.75:3306/auth_shrio?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
        ###################以下为druid增加的配置###########################
        type: com.alibaba.druid.pool.DruidDataSource
        # 下面为连接池的补充设置，应用到上面所有数据源中
        # 初始化大小，最小，最大
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,log4j
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        # 合并多个DruidDataSource的监控数据
        useGlobalDataSourceStat: true
        ###############以上为配置druid添加的配置########################################

### JOOQ 代码生成请参考：[JOOQ 代码生成][JOOQ] ###

注意：在此省略jooq 代码粘贴

### 实体层： ###

package com.zzg.entity;
    
    import java.util.Date;
    
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;
    
    @NoArgsConstructor
    @AllArgsConstructor
    @Getter
    @Setter
    public class ApplicantsEntity implements java.io.Serializable {
    
    	/**   
    	 * @Fields serialVersionUID : TODO(用一句话描述这个变量表示什么)   
    	 */   
    	private static final long serialVersionUID = 1L;
    	
    	private Integer sid;
    	
    	private Integer carSid;
    	
    	private String address;
    	
    	private String aplWhy;
    	
    	private Date aplTime;
    
    }

### service 层 ###

package com.zzg.service;
    
    import java.util.List;
    
    import org.springframework.transaction.annotation.Transactional;
    
    import com.zzg.entity.ApplicantsEntity;
    import com.zzg.jooq.tables.records.ApplicantsRecord;
    
    public interface ApplicantsService {
    	  /** 删除 */
        @Transactional
        public void delete(int id);
        /** 增加*/
        @Transactional
        public void insert(ApplicantsEntity entity);
        /** 更新*/
        @Transactional
        public int update(ApplicantsEntity entity);
        /** 查询单个*/
        public ApplicantsEntity selectById(int id);
        /** 查询全部列表*/
        public List<ApplicantsEntity> selectAll();
    }

package com.zzg.service.impl;
    
    import java.sql.Timestamp;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    import org.jooq.DSLContext;
    import org.jooq.Record;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.zzg.entity.ApplicantsEntity;
    import com.zzg.jooq.tables.records.ApplicantsRecord;
    import com.zzg.service.ApplicantsService;
    
    @Service
    public class ApplicantsServiceImpl implements ApplicantsService {
    	@Autowired
    	private DSLContext dslContext;
    	
    	com.zzg.jooq.tables.Applicants APPLICANTS = new com.zzg.jooq.tables.Applicants();
    	
    	@Override
    	public void delete(int id) {
    		// TODO Auto-generated method stub
    		dslContext.delete(APPLICANTS).where(APPLICANTS.SID.eq(id)).execute();
    	}
    
    	@Override
    	public void insert(ApplicantsEntity applicants) {
    		// TODO Auto-generated method stub
    		dslContext.insertInto(APPLICANTS)
              .columns(APPLICANTS.CAR_SID,APPLICANTS.APL_WHY,APPLICANTS.APL_TIME, APPLICANTS.ADDRESS)
              .values(applicants.getCarSid(), applicants.getAplWhy(),new Timestamp(applicants.getAplTime().getTime()),applicants.getAddress())
              .execute();
    	}
    
    	@Override
    	public int update(ApplicantsEntity entity) {
    		// TODO Auto-generated method stub
    		return dslContext.update(APPLICANTS)
    			.set(APPLICANTS.CAR_SID,entity.getCarSid())
    			.set(APPLICANTS.APL_WHY, entity.getAplWhy())
    			.set(APPLICANTS.APL_TIME, new Timestamp(entity.getAplTime().getTime()))
    			.set(APPLICANTS.ADDRESS,entity.getAddress())
    			.where(APPLICANTS.SID.eq(entity.getSid()))
    			.execute();
    	}
    
    	@Override
    	public ApplicantsEntity selectById(int id) {
    		// TODO Auto-generated method stub
    		 
    		Record record = dslContext.select(APPLICANTS.SID,APPLICANTS.CAR_SID, APPLICANTS.APL_WHY,APPLICANTS.APL_TIME,APPLICANTS.ADDRESS).from(APPLICANTS).where(APPLICANTS.SID.eq(id)).fetchOne();
    		if(record != null){
    			ApplicantsRecord applicantsRecord = (ApplicantsRecord) record;
    			ApplicantsEntity entity = new ApplicantsEntity();
    			entity.setSid(applicantsRecord.getSid());
    			entity.setCarSid(applicantsRecord.getCarSid());
    			if(applicantsRecord.getAplTime() != null){
    				entity.setAplTime(new Date(applicantsRecord.getAplTime().getTime()));
    			}
    			entity.setAplWhy(entity.getAplWhy());
    			entity.setAddress(entity.getAddress());
    			return entity;
    			
    		}		
    		return null;
    	}
    
    	@Override
    	public List<ApplicantsEntity> selectAll() {
    		// TODO Auto-generated method stub
    		List<ApplicantsEntity> entitys = new ArrayList<ApplicantsEntity>();
    		List<Record> records  = dslContext.select().from(APPLICANTS).fetch();
    		if(records != null && records.size() > 0){
    			for(Record record : records){
    				ApplicantsRecord applicantsRecord = (ApplicantsRecord) record;
    				ApplicantsEntity entity = new ApplicantsEntity();
    				entity.setSid(applicantsRecord.getSid());
    				entity.setCarSid(applicantsRecord.getCarSid());
    				if(applicantsRecord.getAplTime() != null){
    					entity.setAplTime(new Date(applicantsRecord.getAplTime().getTime()));
    				}
    				entity.setAplWhy(applicantsRecord.getAplWhy());
    				entity.setAddress(applicantsRecord.getAddress());
    				
    				entitys.add(entity);
    			}
    		}
    		return entitys;
    	}
    
    }

### controller 层 ###

package com.zzg.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    import com.zzg.entity.ApplicantsEntity;
    import com.zzg.service.ApplicantsService;
    
    @RestController
    @RequestMapping("/api/applicants")
    public class ApplicantsController {
    	@Autowired
    	private ApplicantsService applicantsService;
    	
    	// 增
    		@RequestMapping(value = "/insert", method = { RequestMethod.POST }, produces = "application/json;charset=UTF-8")
    		public Object insert(@RequestBody ApplicantsEntity entity) {
    			applicantsService.insert(entity);
    			return "";
    		}
    	 
    		// 改
    		@RequestMapping(value = "/update", method = { RequestMethod.POST }, produces = "application/json;charset=UTF-8")
    		public Object update(@RequestBody ApplicantsEntity entity) {
    			return applicantsService.update(entity);
    		}
    	 
    		// 删
    		@RequestMapping(value = "/delete/{id}", method = { RequestMethod.DELETE })
    		public Object delete(@PathVariable("id") Integer id) {
    			applicantsService.delete(id);
    			return "";
    		}
    	 
    		// 查
    		@RequestMapping(value = "/selectAll", method = { RequestMethod.GET })
    		public Object selectAll() {
    			return applicantsService.selectAll();
    		}
    	 
    
    }

### 程序入口 ###

package com.zzg;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class CarApplication {
    	public static void main(String[] args){
    		SpringApplication.run(CarApplication.class, args);
    		System.out.println("============= SpringBoot Car Project Start Success =============");
    	}
    }

### 程序配置参数 ###

package com.zzg.config;
    
    import java.util.List;
    
    import org.springframework.context.annotation.Configuration;
    import org.springframework.http.converter.HttpMessageConverter;
    import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
    
    import com.alibaba.fastjson.serializer.SerializerFeature;
    import com.alibaba.fastjson.support.config.FastJsonConfig;
    import com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter;
    
    @Configuration
    public class SpringMVCConfigurer extends WebMvcConfigurerAdapter {
    
    	/**
    	 * 设置HttpMessageConverter 为fastjson
    	 * <p>Title: configureMessageConverters</p>   
    	 * <p>Description: </p>   
    	 * @param converters   
    	 * @see org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter#configureMessageConverters(java.util.List)
    	 */
    	@Override
    	public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
    		// TODO Auto-generated method stub
    		super.configureMessageConverters(converters);
    		// 初始化转换器
    	    FastJsonHttpMessageConverter fastConvert = new FastJsonHttpMessageConverter();
    	    // 初始化一个转换器配置
    	    FastJsonConfig fastJsonConfig = new FastJsonConfig();
    	    fastJsonConfig.setSerializerFeatures(SerializerFeature.PrettyFormat);
    	    // 将配置设置给转换器并添加到HttpMessageConverter转换器列表中
    	    fastConvert.setFastJsonConfig(fastJsonConfig);
    	 
    	    converters.add(fastConvert);
    	}
    
    	
    }

### 建库脚本： ###

DROP TABLE IF EXISTS `applicants`;
    CREATE TABLE `applicants`  (
      `sid` int(12) NOT NULL AUTO_INCREMENT COMMENT '申请ID',
      `car_sid` int(12) NULL DEFAULT NULL COMMENT '车辆ID',
      `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '地址',
      `apl_why` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '申请事由',
      `apl_time` datetime(0) NULL DEFAULT NULL COMMENT '申请时间',
      PRIMARY KEY (`sid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

[Link 1]: https://www.jooq.org/
[JOOQ]: https://blog.csdn.net/zhouzhiwengang/article/details/107074544