首页 编程语言正文

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

java哥 阅读：501 2021-03-31 12:54:22 评论：0

简介

官网链接

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> 
		<!--starter --> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter</artifactId> 
		</dependency> 
		<!-- test --> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter-test</artifactId> 
			<scope>test</scope> 
		</dependency> 
		<!--web --> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter-web</artifactId> 
		</dependency> 
		<!--validation --> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter-validation</artifactId> 
		</dependency> 
		<!--lombok --> 
		<dependency> 
			<groupId>org.projectlombok</groupId> 
			<artifactId>lombok</artifactId> 
		</dependency> 
		<!--jooq starter：导入JOOQ框架 --> 
		<dependency> 
			<groupId>org.springframework.boot</groupId> 
			<artifactId>spring-boot-starter-jooq</artifactId> 
		</dependency> 
 
		<!--jooq 代码映射插件 --> 
		<dependency> 
			<groupId>org.jooq</groupId> 
			<artifactId>jooq-codegen</artifactId> 
		</dependency> 
		<dependency> 
			<groupId>org.jooq</groupId> 
			<artifactId>jooq-meta</artifactId> 
		</dependency> 
		<!-- 数据库连接池druid --> 
		<dependency> 
			<groupId>com.alibaba</groupId> 
			<artifactId>druid-spring-boot-starter</artifactId> 
			<version>1.1.10</version> 
		</dependency> 
		<!--mysql 驱动程序 --> 
		<dependency> 
			<groupId>mysql</groupId> 
			<artifactId>mysql-connector-java</artifactId> 
			<scope>runtime</scope> 
		</dependency> 
		<!-- alibab fastjson --> 
		<dependency> 
			<groupId>com.alibaba</groupId> 
			<artifactId>fastjson</artifactId> 
			<version>1.2.62</version> 
		</dependency> 
		<!-- apache common 工具包  --> 
		 
 
	</dependencies> 
 
	<build> 
		<plugins> 
			<!-- jdk版本编译 --> 
			<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> 
			<!--jooq 代码生成插件 --> 
			<plugin> 
				<groupId>org.jooq</groupId> 
				<artifactId>jooq-codegen-maven</artifactId> 
				<version>3.11.9</version> 
				<configuration> 
					<!-- mysql8 连接 --> 
					<jdbc> 
						<driver>com.mysql.cj.jdbc.Driver</driver> 
						<url>jdbc:mysql://192.168.1.75:3306/auth_shrio?useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true</url> 
						<user>root</user> 
						<password>123456</password> 
					</jdbc> 
					<generator> 
						<database> 
							<!--include用于控制数据库中哪些表生成 --> 
							<includes>applicants</includes> 
							<!--exclude用于控制数据库中哪些表不生成 --> 
							<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 代码粘贴

实体层：

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;

 

标签:java编程语言
声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。

相关文章
发表评论
搜索
排行榜
最近发表
关注我们

一个IT知识分享的公众号

友情链接IM LINKS