springboot整合mybatisPlus

springboot整合

概述

本文只做代码展示，具体核心功能使用讲解查看 mybatisPlus学习

依赖

pom.xml

<!-- jdbc相关 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!--配置druid数据源 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.22</version>
		</dependency>
		<!-- mybatis-plus -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.2</version>
		</dependency>
		<dependency>
			<groupId>p6spy</groupId>
			<artifactId>p6spy</artifactId>
			<version>3.8.5</version>
		</dependency>

mybatisPlus YML配置

application.yml

#========配置数据库=========
#  datasource:
#    url: jdbc:mysql://localhost:3306/springconformity?useUnicode=true&characterEncoding=utf8&useSSL=true&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false
#    driverClassName: com.mysql.cj.jdbc.Driver
#    username: root
#    password: root
#    type: com.alibaba.druid.pool.DruidDataSource

mybatis-plus:
   #配置类别名
  typeAliasesPackage: com.conformity.entity.*
  configuration:  #大部分原生mybatis配置   可以通过xml配置
     #配置日志  log-impl:日志实现
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
     #开启驼峰命名映射
    map-underscore-to-camel-case: true
      #单服务架构中（有且仅有只有一个程序提供相同服务），一级缓存开启不会影响业务，只会提高性能
      #ESSION session级别缓存，同一个session相同查询语句不会再次查询数据库
      #STATEMENT 关闭一级缓存
      #微服务架构中需要关闭一级缓存，原因：Service1先查询数据，若之后Service2修改了数据，之后Service1又再次以同样的查询条件查询数据，因走缓存会出现查处的数据不是最新数据
    local-cache-scope: session
    cacheEnabled: true  #开启二级缓存  默认开启
  global-config:
    db-config:
      logic-delete-field: deleted  # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置@TableLogic)
      logic-delete-value: 1 # 逻辑已删除值(默认为 1)
      logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
    
logging:
  level:
    com.conformity.service.impl: info

数据库配置

jdbc.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springconformity?useUnicode=true&characterEncoding=utf8&useSSL=true&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false
#spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/springconformity?useUnicode=true&characterEncoding=utf8&useSSL=true&zeroDateTimeBehavior=convertToNull&serverTimezone=UTC&autoReconnect=true&allowMultiQueries=true&failOverReadOnly=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
spring.datasource.initial-size=5

#最大连接池数量
spring.datasource.max-active=20

#已经不再使用，配置了也没效果
spring.datasource.min-idle=5

#获取连接时最大等待时间，单位毫秒。配置了maxWait之后
spring.datasource.maxWait=60000

#是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。
#在mysql5.5以下的版本中没有PSCache功能，建议关闭掉。5.5及以上版本有PSCache，建议开启
spring.datasource.pool-prepared-statements=true

#要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
#在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
spring.datasource.max-open-prepared-statements=10
	
#用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
spring.datasource.validation-query=SELECT 1

#申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
spring.datasource.test-on-borrow=false

#归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
spring.datasource.test-on-return=true
	
#建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
spring.datasource.test-while-idle=true

spring.datasource.validation-query-timeout=2000

#有两个含义：1) Destroy线程会检测连接的间隔时间  2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
spring.datasource.time-between-eviction-runs-millis=60000

#不再使用
spring.datasource.min-evictable-idle-time-millis=300000

#属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
#这些插件通过数据源配置类中  配置了
#		DruidDataSource druidDataSource = new DruidDataSource();
#		druidDataSource.addFilters("stat");
#		druidDataSource.addFilters("wall");
#spring.datasource.filters.commons-log.connection-logger-name=stat,wall,log4j

#=========开启监控  ==============
#以下，将来上生产环境以后一定要改！！！！！一定要改！！！！！一定要改！！！！！
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
#访问白名单
spring.datasource.druid.stat-view-servlet.allow=

#访问黑名单
spring.datasource.druid.stat-view-servlet.deny=

#用户名密码
spring.datasource.druid.stat-view-servlet.login-username=
spring.datasource.druid.stat-view-servlet.login-password=

#开启servlet
spring.datasource.druid.stat-view-servlet.enabled= true

#是否允许重置
spring.datasource.druid.stat-view-servlet.reset-enable= false

#===============插件配置 ====================
# 配置statFilter
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=3000
spring.datasource.druid.filter.stat.enabled=true
 
# 配置WallFilter
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
spring.datasource.druid.filter.wall.config.delete-allow=false
spring.datasource.druid.filter.wall.config.drop-table-allow=false

# WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.html,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*

该配置配置了druid数据源和druid监控

配置类

config配置

MybatisPlusConfig.java

/** MybatisPlus 配置类 
 * 包含配置数据源和druid监控
 * @author Admin
 *
 */
@Configuration
@PropertySource(value = {"classpath:jdbc.properties"},encoding = "utf-8")
public class MybatisPlusConfig {

	/** 配置数据源
	 * @return
	 * @throws SQLException 
	 */
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource")
	public DataSource druidDatasource() throws SQLException{
		DruidDataSource druidDataSource = new DruidDataSource();
		//添加插件配置
		druidDataSource.addFilters("stat");
		druidDataSource.addFilters("wall");
		//		druidDataSource.addFilters("log4j");
		return druidDataSource;
	}
	
	//配置自定义主键生成
	@Bean
	public IdentifierGenerator myIdentifierGenerator() {
		return new MyIdentifierGenerator();
	}

	//配置自动填充
	@Bean
	public MetaObjectHandler myMetaObjectHandler() {
		return new MyMetaObjectHandler();
	}

	/**
	 * 自定义 SqlInjector
	 * 里面包含自定义的全局方法
	 */
	@Bean
	public MyLogicSqlInjector myLogicSqlInjector() {
		return new MyLogicSqlInjector();
	}

	/*
	 * 添加插件(since 3.4.0)  3.4后插件通过该方式注入
	 * 目前已有的功能:
		多租户: TenantLineInnerInterceptor
		动态表名: DynamicTableNameInnerInterceptor
		自动分页: PaginationInnerInterceptor
		乐观锁: OptimisticLockerInnerInterceptor
		sql性能规范: IllegalSQLInnerInterceptor
		防止全表更新与删除: BlockAttackInnerInterceptor
		
		使用多个功能需要注意顺序关系,建议使用如下顺序
		多租户,动态表名,分页,乐观锁,sql性能规范,防止全表更新与删除
	**/
	@Bean("optimisticLockerInnerInterceptor")
	public MybatisPlusInterceptor optimisticLockerInnerInterceptor(){
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//自动分页
		interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());//乐观锁
//		interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor());//sql性能规范
		interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());//防止全表更新删除
		
		return interceptor;

	}

	/** 配置druid监控servlet  本次通过属性文件jdbc.properties配置
	 * @return
	 */
	//	@Bean
	//	public ServletRegistrationBean<StatViewServlet> statServlet() {
	//		ServletRegistrationBean<StatViewServlet> srb = new ServletRegistrationBean<>();
	//		srb.setServlet(new StatViewServlet());
	//		
	//		List<String> urlList = new ArrayList<>();
	//		urlList.add("/druid/*");
	//		srb.setUrlMappings(urlList);
	//
	//		Map<String,String> initParams = new HashMap<>();
	//		initParams.put("loginUsername","admin");
	//		initParams.put("loginPassword","123456");
	//		initParams.put("allow","");//默认就是允许所有访问
	//		initParams.put("deny",""); //黑名单
	//		initParams.put("resetEnable", "false");
	//		srb.setInitParameters(initParams);
	//		
	//		return srb;
	//	}

	//	2、配置一个web监控的filter   本次通过属性文件jdbc.properties配置
	//    @Bean
	//    public FilterRegistrationBean<WebStatFilter> webStatFilter(){
	//        FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>();
	//        bean.setFilter(new WebStatFilter());
	//
	//        Map<String,String> initParams = new HashMap<>();
	//        initParams.put("exclusions","*.js,*.css,/druid/*,*.html");
	//        bean.setInitParameters(initParams);
	//
	//        bean.setUrlPatterns(Arrays.asList("/*"));
	//
	//        return  bean;
	//    }
}

自动注入配置

MyMetaObjectHandler.java

/** mybatisplus 自动注入
 * @author Admin
 *
 */
public class MyMetaObjectHandler implements MetaObjectHandler{

	@Override
	public void insertFill(MetaObject metaObject) {
		this.setFieldValByName("createTime", new Date(), metaObject);
		this.setFieldValByName("updateTime", new Date(), metaObject);
		this.setFieldValByName("deleted", 0, metaObject);
	}

	@Override
	public void updateFill(MetaObject metaObject) {
		this.setFieldValByName("updateTime", new Date(), metaObject);
	}

}

自定义id配置

MyIdentifierGenerator.java

/** 自定义id生成
 * @author Admin
 *
 */
public class MyIdentifierGenerator implements IdentifierGenerator{
	private static SimpleDateFormat sf;
	static {
		sf = new SimpleDateFormat("yyyyMMddHHmm");
	}
	@Override
	public Number nextId(Object entity) {
		StringBuilder sb = new StringBuilder();
		for(int i = 0;i<6;i++) {
			int a = new Random().nextInt(10);
			sb.append(a+"");
		}
		String date = sf.format(new Date()).toString();
		Long id = Long.parseLong(date+sb.toString().trim());
		return id;
	}

}

自定义全局接口

自定义全局物理删除接口

MyCustom.java

/** 自定义物理删除方法
 * @author Admin
 *
 */
@SuppressWarnings("serial")
public class MyCustom extends AbstractMethod{

	@Override
	public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
		String sqlInit = "delete from "+tableInfo.getTableName()+" where %s = %s";
		
		String idKey = tableInfo.getKeyColumn();
		String value ="#{"+idKey+"}";
		String sql = String.format(sqlInit,idKey, value);
		SqlSource sqlSource = languageDriver.createSqlSource(
				configuration, 
				sql,
				modelClass
				);
		return this.addDeleteMappedStatement(mapperClass, "physicalDel", sqlSource);
		
	}

}

MyLogicSqlInjector.java

/** 将自定义的sql注册到mybatisplus全局中
 * @author Admin
 *
 */
public class MyLogicSqlInjector extends DefaultSqlInjector{
	
	/**
     * 如果只需增加方法，保留MP自带方法
     * 可以super.getMethodList() 再add
     * @return
     */
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new MyCustom());
        return methodList;
    }
	
}

自定义底层service接口

mapper

MyBaseMapper.java

/** 基础的mapper
 * @author Admin
 *
 * @param <T>
 */
public interface MyBaseMapper<T> extends BaseMapper<T>{
	boolean physicalDel(String id);
}

service

MyBaseService.java

/**基础service
 * @author Admin
 *
 * @param <T>
 */
public interface MyBaseService<T> extends IService<T> {
	
}

serviceImpl

MyBaseServiceImpl.java

/** 基础mybatisplus service的实现类
 * @author Admin
 *
 * @param <M>
 * @param <T>
 */
public class MyBaseServiceImpl<M extends BaseMapper<T>, T> extends ServiceImpl<M,T> implements MyBaseService<T>{
	
	
}

启动类

@SpringBootApplication
@MapperScan(basePackages = {"com.conformity.general.mapper"})
public class SpringbootApplication{

	public static void main(String[] args) {
		SpringApplication.run(SpringbootApplication.class, args);
	}
	

}

业务代码

注：@api为swagger2注解 具体查看：springboot整合knife4j

controller

@RestController
@RequestMapping(value = "/mybatis",method = RequestMethod.POST)
@Api(tags = {"mybatis的测试类"})
public class MybatisController {
	
	@Resource
	private MybatisTestUserService testService;
	
	@RequestMapping("/selectAll")
	@ApiOperation("mybatis测试---获取所有用户实体")
	@ApiOperationSupport(author = "lsx")
	public List<MybatisTestUser> selectAll(){
		return testService.selectAll();
	}
	
	@RequestMapping("/getAllUser")
	@ApiOperation("mybatisPlus测试---获取所有用户实体")
	@ApiOperationSupport(author = "lsx",order = 1)
	public List<MybatisTestUser> getAllUser(){
		List<MybatisTestUser> list = testService.list();
		return list;
	}
	
	@Transactional
	@RequestMapping("/add")
	@ApiOperation("mybatisPlus测试---新增 ")
	@ApiOperationSupport(author = "lsx",order = 2)
	@ApiImplicitParams({   
        @ApiImplicitParam(name = "age", value = "年龄", required = true, paramType = "query"),           
        @ApiImplicitParam(name = "name", value = "姓名", required = true, paramType = "query"),           
        @ApiImplicitParam(name = "email", value = "邮箱", required = true, paramType = "query"),          
	})
	public String add(@ApiIgnore MybatisTestUser user) throws Exception{
		if(testService.save(user)) {
			return "保存成功";
		}else {
			return "保存失败";
		}
	}
	
	@RequestMapping("/selectBypage")
	@ApiOperation("mybatisPlus测试---分页查询 ")
	@ApiOperationSupport(author = "lsx",order = 3)
	@ApiImplicitParams({   
        @ApiImplicitParam(name = "age", value = "年龄", required = false, paramType = "query"),           
        @ApiImplicitParam(name = "name", value = "姓名", required = false, paramType = "query"),           
        @ApiImplicitParam(name = "email", value = "邮箱", required = false, paramType = "query"),          
	})
	public IPage<MybatisTestUser> selectBypage(PageEntity pb,@ApiIgnore MybatisTestUser user){
		IPage<MybatisTestUser> page = new Page<>();
		if (StringUtils.isNotBlank(pb.getCurrent())) {
			page.setCurrent(Long.valueOf(pb.getCurrent()));
		}
		if (StringUtils.isNotBlank(pb.getSize())) {
			page.setSize(Long.valueOf(pb.getSize()));
		}
		IPage<MybatisTestUser> selectBypage = testService.selectBypage(page, user);
		return selectBypage;
	}
	
	@RequestMapping("/removeById")
	@ApiOperation("mybatisPlus测试---逻辑删除 ")
	@ApiOperationSupport(author = "lsx",order = 4)
	public void removeById(String id) {
		testService.removeById(id);
	}
	
	@RequestMapping("/delById")
	@ApiOperation("mybatisPlus测试---物理删除 ")
	@ApiOperationSupport(author = "lsx",order = 5)
	public void delById(String id) {
		testService.PhyDel(id);
	}
	
	@RequestMapping("/updateById")
	@ApiOperation("mybatisPlus测试---修改 ")
	@ApiOperationSupport(author = "lsx",order = 6)
	@ApiImplicitParams({   
		@ApiImplicitParam(name = "id", value = "主键", required = true, paramType = "query"),           
		@ApiImplicitParam(name = "age", value = "年龄", required = false, paramType = "query"),           
		@ApiImplicitParam(name = "name", value = "姓名", required = false, paramType = "query"),           
		@ApiImplicitParam(name = "email", value = "邮箱", required = false, paramType = "query"),          
		@ApiImplicitParam(name = "version", value = "版本号", required = true, paramType = "query"),          
	})
	public String updateById(@ApiIgnore MybatisTestUser user) throws Exception{
		if(testService.updateById(user)) {
			return "修改成功";
		}
		return "修改失败";
	}
	
	@RequestMapping("/updateAll")
	@ApiOperation("mybatisPlus测试---防止全表更新 ")
	@ApiOperationSupport(author = "lsx",order = 7)
	@ApiImplicitParams({   
		@ApiImplicitParam(value = "姓名",name = "name",required = true, paramType = "query"),           
	})
	public String updateAll(String name) {
		try {
			testService.update(new MybatisTestUser().setName(name),null);
			return "全表更新成功";
		} catch (Exception e) {
			e.printStackTrace();
			return "全表跟新失败";
		}
	
	}
	
}

service

public interface MybatisTestUserService extends MyBaseService<MybatisTestUser>{
	
	List<MybatisTestUser> selectAll();
	
	IPage<MybatisTestUser> selectBypage(IPage<MybatisTestUser> page,MybatisTestUser user);
	
	void PhyDel(String id);
}

serviceImpl

@Service
public class MybatisTestUserServiceImpl extends MyBaseServiceImpl<MybatisPlusTestUserMapper, MybatisTestUser>implements MybatisTestUserService {

	@Resource
	private MybatisPlusTestUserMapper userTestMapper;
	
	@Override
	public List<MybatisTestUser> selectAll() {
		return userTestMapper.selectAll();
	}

	@Override
	public IPage<MybatisTestUser> selectBypage(IPage<MybatisTestUser> page,MybatisTestUser user) {
		QueryWrapper<MybatisTestUser> query= new QueryWrapper<>();
		IPage<MybatisTestUser> iPage = userTestMapper.selectPage(page, query);
		int total = iPage.getRecords().size();
		iPage.setTotal(total);
		return iPage;
	}

	@Override
	public void PhyDel(String id) {
		userTestMapper.physicalDel(id);
		
	}

}

mapper

@Mapper
public interface MybatisPlusTestUserMapper extends MyBaseMapper<MybatisTestUser>{
	
	@Select("select * from mybatistestuser")
	List<MybatisTestUser> selectAll();
	
}

entity

@Data
@NoArgsConstructor
@Accessors(chain = true)
@ApiModel(value = "User",description = "用户实体")
@TableName(value = "mybatistestuser")
public class MybatisTestUser implements Serializable{
	
	private static final long serialVersionUID = 1L;

	
	//分配ID(主键类型为Number(Long和Integer)或String)(since 3.3.0),
//	使用接口IdentifierGenerator的方法nextId(默认实现类为DefaultIdentifierGenerator雪花算法)
//	@TableId(type = IdType.ASSIGN_ID)
	@TableId
	@ApiModelProperty(value = "主键",name = "id")
	private String id;
	
	@ApiModelProperty(value = "年龄",name = "age")
	private Integer age;
	
	@ApiModelProperty(value = "用户名",name = "name")
	private String name;
	
	@ApiModelProperty(value = "邮箱",name = "email")
	private String email;
	
	@ApiModelProperty(value = "是否删除 1是 0否",name = "deleted")
	@TableField(fill = FieldFill.INSERT)
	private Integer deleted;
	
	@ApiModelProperty(value = "创建时间")
	@TableField(fill = FieldFill.INSERT)
	private Date createTime;
	
	@ApiModelProperty(value = "更新时间")
	@TableField(fill = FieldFill.INSERT_UPDATE)
	private Date updateTime;
	
	@Version
	@ApiModelProperty(value = "版本号")
	private Integer version;
}

@SuppressWarnings("serial")
@Data
@Accessors(chain = true)
@ApiModel(value = "User",description = "用户实体")
public class PageEntity implements Serializable{

	@ApiModelProperty(value = "当前页")
	private String current;
	
	@ApiModelProperty(value = "大小")
	private String size;


}

sql

CREATE TABLE `mybatistestuser` (
  `id` varchar(50) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `deleted` tinyint(2) DEFAULT NULL COMMENT '1 删除 0未删除',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `version` int(10) DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8