SpringBoot + MyBatis-plus + Druid 实现简单增删查改、动态条件查询和分页功能分析

虾米哥 阅读:296 2021-03-31 12:54:28 评论:0

本文主要讲解:SpringBoot集成Mybatis-plus,数据库连接池使用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>crm_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> 
		<!-- mybatis-plus 集成 --> 
		<dependency> 
			<groupId>com.baomidou</groupId> 
			<artifactId>mybatis-plus-boot-starter</artifactId> 
			<version>3.4.1</version> 
		</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> 
	</dependencies> 
 
	<!--集成mybatis-generator 自动生成组件 --> 
	<build> 
		<plugins> 
			<plugin> 
				<groupId>org.mybatis.generator</groupId> 
				<artifactId>mybatis-generator-maven-plugin</artifactId> 
				<version>1.3.7</version> 
				<dependencies> 
					<dependency> 
						<groupId>mysql</groupId> 
						<artifactId>mysql-connector-java</artifactId> 
						<version>8.0.12</version> 
					</dependency> 
					<dependency> 
						<groupId>org.mybatis.generator</groupId> 
						<artifactId>mybatis-generator-core</artifactId> 
						<version>1.3.7</version> 
					</dependency> 
				</dependencies> 
				<executions> 
					<execution> 
						<id>Generate MyBatis Artifacts</id> 
						<phase>package</phase> 
						<goals> 
							<goal>generate</goal> 
						</goals> 
					</execution> 
				</executions> 
				<configuration> 
					<!--允许移动生成的文件 --> 
					<verbose>true</verbose> 
					<!-- 是否覆盖 --> 
					<overwrite>true</overwrite> 
					<!-- 自动生成的配置 --> 
					<configurationFile>src/main/resources/generatorConfig.xml</configurationFile> 
				</configuration> 
			</plugin> 
		</plugins> 
	</build> 
 
 
 
 
</project>

项目资源文件配置(application.yml)

server: 
  port: 8098 
mybatis-plus: 
  mapper-locations:classpath:/mapper/*Mapper.xml 
  typeAliasesPackage:com.zzg.entity 
spring: 
  ##数据库连接信息 
  datasource: 
    url: jdbc:mysql://192.168.1.73:3306/boot-table?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true 
    username: root 
    password: digipower 
    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添加的配置######################################## 

项目涉及(entity、mapper、service、serviceImpl 和Controller)

Controller 层定义

package com.zzg.controller; 
 
import java.util.Map; 
 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.stereotype.Controller; 
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.RequestParam; 
import org.springframework.web.bind.annotation.ResponseBody; 
 
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; 
import com.baomidou.mybatisplus.extension.plugins.pagination.Page; 
import com.zzg.common.controller.AbstractController; 
import com.zzg.entity.User; 
import com.zzg.service.UserService; 
 
@Controller 
@RequestMapping("/api/user") 
public class UserController extends AbstractController<User> { 
 
	@Autowired 
	private UserService userService; 
 
	// 增 
	@RequestMapping(value = "/insert", method = { RequestMethod.POST }, produces = "application/json;charset=UTF-8") 
	@ResponseBody 
	public Object insert(@RequestBody User user) { 
		return userService.save(user); 
	} 
 
	// 改 
	@RequestMapping(value = "/update", method = { RequestMethod.POST }, produces = "application/json;charset=UTF-8") 
	@ResponseBody 
	public Object update(@RequestBody User user) { 
		return userService.updateById(user); 
	} 
 
	// 删 
	@RequestMapping(value = "/delete/{id}", method = { RequestMethod.DELETE }) 
	@ResponseBody 
	public Object delete(@PathVariable("id") Integer id) { 
		return userService.removeById(id); 
	} 
 
	// 查 
	@RequestMapping(value = "/getUserByName", method = { RequestMethod.GET }) 
	@ResponseBody 
	public Object getUserByName(@RequestParam String userName) { 
		QueryWrapper<User> query = new QueryWrapper<User>(); 
		query.like("username", userName); 
		return userService.getOne(query); 
	} 
 
	// 查 
	@RequestMapping(value = "/getId", method = { RequestMethod.GET }) 
	@ResponseBody 
	public Object getId(@RequestParam Integer id) { 
		return userService.getById(id); 
	} 
	 
	// 查 
	@RequestMapping(value = "/getPage", method = { RequestMethod.POST }) 
	@ResponseBody 
	public Object getPage(@RequestBody Map<String, Object> parame) { 
		Page<User> page = this.initPageBounds(parame); 
		return userService.page(page); 
	} 
	 
 
} 

entity 定义

package com.zzg.entity; 
 
import java.io.Serializable; 
import java.util.Date; 
import com.baomidou.mybatisplus.annotation.IdType; 
import com.baomidou.mybatisplus.annotation.TableId; 
import com.baomidou.mybatisplus.annotation.TableName; 
import lombok.Data; 
 
@SuppressWarnings("serial") 
@Data 
@TableName("user") 
public class User implements Serializable { 
	@TableId(value = "id", type = IdType.AUTO) 
    private Integer id; 
 
    private String username; 
 
    private String password; 
 
    private String email; 
 
    private String phone; 
 
    private String question; 
 
    private String answer; 
 
    private Integer role; 
     
    private Date createTime; 
     
    private Date updateTime; 
 
    
}

mapper 定义:

package com.zzg.mapper; 
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper; 
import com.zzg.entity.User; 
 
public interface UserMapper extends BaseMapper<User> { 
}

service 和serviceImpl 定义:

package com.zzg.service; 
 
import com.baomidou.mybatisplus.extension.service.IService; 
import com.zzg.entity.User; 
 
public interface UserService extends IService<User> { 
} 
package com.zzg.service.impl; 
 
import org.springframework.stereotype.Service; 
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; 
import com.zzg.entity.User; 
import com.zzg.mapper.UserMapper; 
import com.zzg.service.UserService; 
 
@Service 
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService { 
	 
} 

项目涉及(common、util之转换、程序入口、全局配置对象)

common 之公共抽象Controller

package com.zzg.common.controller; 
 
import java.util.Map; 
 
import com.baomidou.mybatisplus.extension.plugins.pagination.Page; 
import com.zzg.util.converter.SimpleTypeConverterUtil; 
 
 
 
public abstract class AbstractController<T> { 
	public static final String PAGE = "page"; 
	 
	public static final String LIMIT = "limit"; 
	 
	/** 
	 * 参数分页参数转换校验 
	 *  
	 * @param param 
	 * @return 
	 */ 
	protected Page<T> initPageBounds(Map<String, Object> param) { 
		int page = SimpleTypeConverterUtil.convertIfNecessary(param.get(PAGE), int.class); 
		int limit = SimpleTypeConverterUtil.convertIfNecessary(param.get(LIMIT), int.class); 
		// 页码和页数取消最大限制 
		page = (page <= 0) ? 1 : page; 
		limit = (limit <= 0) ? 10 : limit; 
		return new Page<T>(page, limit); 
	} 
} 

Util之数据类型转换:

package com.zzg.util.converter; 
 
import java.util.Date; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.SimpleTypeConverter; 
 
 
public class SimpleTypeConverterUtil { 
public static final Logger log = LoggerFactory.getLogger(SimpleTypeConverterUtil.class); 
	 
	private static final SimpleTypeConverter typeConverterDelegate = new SimpleTypeConverter(); 
	static{ 
		typeConverterDelegate.registerCustomEditor(Date.class, new DateEditor()); 
	} 
	 
	/** 
	 * @param <T> 
	 * @param value  待转换值,一般字符串 
	 * @param requiredType 转后类型类对象 
	 * @return 
	 */ 
	public static <T> T convertIfNecessary(Object value, Class<T> requiredType) { 
		T rs = null; 
		try { 
			rs = typeConverterDelegate.convertIfNecessary(value, requiredType); 
		} catch (Exception e) { 
			log.info(e.getMessage()); 
			if(requiredType == int.class || requiredType == Integer.class){ 
				rs = (T)Integer.valueOf(0); 
			} 
		} 
		return rs; 
	} 
} 
package com.zzg.util.converter; 
 
import java.lang.management.ManagementFactory; 
import java.text.DateFormat; 
import java.text.Format; 
import java.text.SimpleDateFormat; 
import java.util.ArrayList; 
import java.util.Date; 
import java.util.List; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
 
 
public class DateUtils { 
public static final Logger log = LoggerFactory.getLogger(DateUtils.class); 
	 
	public static final String YYYY = "yyyy" ; 
 
    public static final String YYYY_MM = "yyyy-MM" ; 
 
    public static final String YYYY_MM_DD = "yyyy-MM-dd" ; 
 
    public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss" ; 
 
    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss" ; 
 
    private static String[] parsePatterns = { 
            YYYY_MM_DD , YYYY_MM_DD_HH_MM_SS , "yyyy-MM-dd HH:mm" , YYYY_MM , 
            "yyyy/MM/dd" , "yyyy/MM/dd HH:mm:ss" , "yyyy/MM/dd HH:mm" , "yyyy/MM" , 
            "yyyy.MM.dd" , "yyyy.MM.dd HH:mm:ss" , "yyyy.MM.dd HH:mm" , "yyyy.MM"}; 
 
    /** 
     * 获取当前Date型日期 
     * 
     * @return Date() 当前日期 
     */ 
    public static Date getNowDate() { 
        return new Date(); 
    } 
 
    /** 
     * 获取当前日期, 默认格式为yyyy-MM-dd 
     * 
     * @return String 
     */ 
    public static String getDate() { 
        return dateTimeNow(YYYY_MM_DD); 
    } 
 
    public static final String getTime() { 
        return dateTimeNow(YYYY_MM_DD_HH_MM_SS); 
    } 
 
    public static final String dateTimeNow() { 
        return dateTimeNow(YYYYMMDDHHMMSS); 
    } 
 
    public static final String dateTimeNow(final String format) { 
        return parseDateToStr(format, new Date()); 
    } 
 
    public static final String dateTime(final Date date) { 
        return parseDateToStr(YYYY_MM_DD, date); 
    } 
 
    public static final String parseDateToStr(final String format, final Date date) { 
    	if (date == null) { 
            return null; 
        } 
 
        Format formatter = new SimpleDateFormat(format); 
        return formatter.format(date); 
    } 
 
    /** 
     * 获取服务器启动时间 
     */ 
    public static Date getServerStartDate() { 
        long time = ManagementFactory.getRuntimeMXBean().getStartTime(); 
        return new Date(time); 
    } 
     
    private static final List<DateFormat> formarts = new ArrayList<>(5); 
	static { 
		formarts.add(new SimpleDateFormat("yyyy-MM")); 
		formarts.add(new SimpleDateFormat("yyyy-MM-dd")); 
		formarts.add(new SimpleDateFormat("yyyy-MM-dd hh:mm")); 
		formarts.add(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")); 
		formarts.add(new SimpleDateFormat("yyyy.MM.dd")); 
	} 
 
	public static Date formatDateStr(String source) { 
		String value = source.trim(); 
		if ("".equals(value)) { 
			return null; 
		} 
		try { 
			if (source.matches("^\\d{4}-\\d{1,2}$")) { 
				return formarts.get(0).parse(source); 
			} else if (source.matches("^\\d{4}-\\d{1,2}-\\d{1,2}$")) { 
				return formarts.get(1).parse(source); 
			} else if (source.matches("^\\d{4}-\\d{1,2}-\\d{1,2} {1}\\d{1,2}:\\d{1,2}$")) { 
				return formarts.get(2).parse(source); 
			} else if (source.matches("^\\d{4}-\\d{1,2}-\\d{1,2} {1}\\d{1,2}:\\d{1,2}:\\d{1,2}$")) { 
				return formarts.get(3).parse(source); 
			} else if (source.matches("^\\d{4}.\\d{1,2}.\\d{1,2}$")) { 
				return formarts.get(4).parse(source); 
			} else { 
				throw new IllegalArgumentException("Invalid boolean value '" + source + "'"); 
			} 
		} catch (Exception e) { 
			log.warn("DateUtils.formatDateStr error", e); 
			return null; 
		} 
	} 
 
    /** 
     * 计算两个时间差 
     */ 
    public static String getDatePoor(Date endDate, Date nowDate) { 
        long nd = (long)1000 * 24 * 60 * 60; 
        long nh = (long)1000 * 60 * 60; 
        long nm = (long)1000 * 60; 
        // 获得两个时间的毫秒时间差异 
        long diff = endDate.getTime() - nowDate.getTime(); 
        // 计算差多少天 
        long day = diff / nd; 
        // 计算差多少小时 
        long hour = diff % nd / nh; 
        // 计算差多少分钟 
        long min = diff % nd % nh / nm; 
        // 计算差多少秒//输出结果 
        return day + "天" + hour + "小时" + min + "分钟" ; 
    } 
} 
package com.zzg.util.converter; 
 
import java.beans.PropertyEditorSupport; 
 
 
public class DateEditor extends PropertyEditorSupport { 
	@Override 
	public void setAsText(String text) throws IllegalArgumentException { 
		setValue(DateUtils.formatDateStr(text)); 
	} 
} 

程序入口:

package com.zzg; 
 
import org.mybatis.spring.annotation.MapperScan; 
import org.springframework.boot.SpringApplication; 
import org.springframework.boot.autoconfigure.SpringBootApplication; 
 
 
@SpringBootApplication 
@MapperScan("com.zzg.mapper") 
public class CRMApplication { 
 
	public static void main(String[] args) { 
		// TODO Auto-generated method stub 
		SpringApplication.run(CRMApplication.class, args); 
		System.out.println("============= SpringBoot CRM Project Start Success ============="); 
	} 
 
} 

全局配置对象

package com.zzg.config; 
 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
 
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; 
 
@Configuration 
public class MybatisPlusConfig { 
	/** 
     * 分页插件 
     */ 
    @Bean 
    public PaginationInterceptor paginationInterceptor() { 
        PaginationInterceptor page = new PaginationInterceptor(); 
        //设置方言类型 
        page.setDialectType("mysql"); 
        return page; 
    } 
} 

项目初始化SQL

CREATE TABLE `user` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id', 
  `username` varchar(50) NOT NULL COMMENT '用户名', 
  `password` varchar(50) NOT NULL COMMENT '用户密码,MD5加密', 
  `email` varchar(50) DEFAULT NULL, 
  `phone` varchar(20) DEFAULT NULL, 
  `question` varchar(100) DEFAULT NULL COMMENT '找回密码问题', 
  `answer` varchar(100) DEFAULT NULL COMMENT '找回密码答案', 
  `role` int(4) NOT NULL COMMENT '角色0-管理员,1-普通用户', 
  `create_time` datetime NOT NULL COMMENT '创建时间', 
  `update_time` datetime NOT NULL COMMENT '最后一次更新时间', 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `user_name_unique` (`username`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; 
SET FOREIGN_KEY_CHECKS = 1; 
 
INSERT INTO `user`(`id`, `username`, `password`, `email`, `phone`, `question`, `answer`, `role`, `create_time`, `update_time`) VALUES (1, 'admin', '432D38237BD939443EC5D48E24FD3B1A', 'admin@admin.com', '13800138000', '问题', '答案', 1, '2016-11-06 16:56:45', '2017-04-04 19:27:36'); 

技术文档:

MyBatis-plus 官网

github 代码地址:https://github.com/zhouzhiwengang/crm_sys.git

声明

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

发表评论
搜索
KIKK导航

KIKK导航

排行榜
关注我们

一个IT知识分享的公众号