springboot整合mybatis-plus
1.MyBatis Plus 简介
Mybatis-Plus(简称MP)是一个 Mybatis 的增强工具,在 Mybatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
特性:
-
无侵入:只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑
-
损耗小:启动即会自动注入基本 CURD,性能基本无损耗,直接面向对象操作
-
强大的 CRUD 操作:内置通用 Mapper、通用 Service,仅仅通过少量配置即可实现单表大部分 CRUD 操作,更有强大的条件构造器,满足各类使用需求
-
支持主键自动生成:支持多达 4 种主键策略(内含分布式唯一 ID 生成器 - Sequence),可自由配置,完美解决主键问题
-
内置代码生成器:采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码,支持模板引擎,更有超多自定义配置等您来使用
-
内置分页插件:基于 MyBatis 物理分页,开发者无需关心具体操作,配置好插件之后,写分页等同于普通 List 查询
-
分页插件支持多种数据库:支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
2.环境搭建
2.1 配置druid.datasource数据源
导入依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
2.1.1 文件配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/j220501?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: YOGr1hAd5Yi65JlFGKdVv010LaxT1M3SfDdSQ9Gqso6ccHAL5fxfjzlon0B8BPLfzEGz0llw97dXfIK8YDs8qw==
druid:
initial-size: 1
min-idle: 3
max-active: 20
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 30000
validation-query: select 'x'
test-while-idle: true
#建议配置为false。获取连接时执行validationQuery检测连接是否有效,这个配置会降低性能
test-on-borrow: false
#建议配置为false。获取连接时执行validationQuery检测连接是否有效,这个配置会降低性能
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
#配置监控统计拦截的filters,去掉后监控界面sql无法统计
filters: config,stat,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connection-properties: ruid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;config.decrypt=true;config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAM1CwAKEYnsOsT44An+pEBJM+uFh7/kmftHZz44ZiwfX6eJX4jxSiR9XF3KJZUwLrdN7twz08y557+yg4/EPaLMCAwEAAQ==
# 合并多个DruidDataSource的监控数据
use-global-data-source-stat: true
#采集数据库监控的数据
web-stat-filter:
url-pattern: /*
enabled: true
exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
#展示监控信息
stat-view-servlet:
url-pattern: /druid/*
allow: 127.0.0.1
reset-enable: false
#是否开启,设置为true才能打开监控页面
enabled: true
#druid监控登录页面的用户名
login-username: root
#druid监控登录页面的密码
login-password: root
数据库密码加密:
druid依赖包提供了ConfigTools类进行数据库密码加密,具体方法为:
找到druid的依赖jar所在目录,在该文件夹的地址栏输入cmd,打开命令提示界面,在该界面输入:
java -cp druid-1.2.8.jar com.alibaba.druid.filter.config.ConfigTools ${password}
其中${password}为数据库登录的密码
执行该命令后,会输出如下结果:
privateKey:MIIBVQIBADANBgkqhkiG9w0BAQEFAASCAT8wggE7AgEAAkEAzULAAoRiew6xPjgCf6kQEkz64WHv+SZ+0dnPjhmLB9fp4lfiPFKJH1cXcollTAut03u3DPTzLnnv7KDj8Q9oswIDAQABAkEAo47y7Y6L4r7cYVXEN/GBivOoXfvqesBAAfvrtbGI5Z+qZqjSUHMfGHt/e640ZI+osCivVm2mjc7AjGWBXy/RcQIhAPm972/Ze1QIVC8REyditAKKEdciBKlX+qqNMwZQjKbvAiEA0md5I4YeiS7rzxl17YCw7lveSxpHWwcIJaPzfVhsen0CIQDJkDBVl7PgCLDZI/DPkuwyOHlZps0YWDepuzh5Wj2K5QIgT2D0Rd+yo4TNOBvd7tWPas3EBseKRiBYBYNeOFrkyi0CIFgHTU6xICwUbnRV3AZa0P903ppyJG3QUfLT/uOh+Ar+
publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAM1CwAKEYnsOsT44An+pEBJM+uFh7/kmftHZz44ZiwfX6eJX4jxSiR9XF3KJZUwLrdN7twz08y557+yg4/EPaLMCAwEAAQ==
password:YOGr1hAd5Yi65JlFGKdVv010LaxT1M3SfDdSQ9Gqso6ccHAL5fxfjzlon0B8BPLfzEGz0llw97dXfIK8YDs8qw==
其中publicKey是公钥,privateKey是密钥,password是加密后的密码
在druid.jar源码中可以查看默认的公钥和密钥。
访问数据库监控页面:
在浏览器输入:http://ip:port/druid/login.html
代码显式创建DruidConfig类:
@Configuration
public class DruidConfig {
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("${spring.datasource.connectionProperties}")
private String connectionProperties;
@Value("${spring.datasource.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
/**
* 注册一个StatViewServlet
*
* @return
*/
@Bean
public ServletRegistrationBean druidStatViewServletBean() {
// org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),
"/druid/*");
// 添加初始化参数:initParams
// 白名单:
servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
// IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not
// permitted to view this page.
// servletRegistrationBean.addInitParameter("deny", "192.168.0.151");
// 登录查看信息的账号密码.
servletRegistrationBean.addInitParameter("loginUsername", "root");
servletRegistrationBean.addInitParameter("loginPassword", "root");
// 是否能够重置数据.
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
/**
* druid监控过滤器
*
* @return
*/
@Bean
public FilterRegistrationBean druidStatFilterBean() {
FilterRegistrationBean filter = new FilterRegistrationBean();
filter.setFilter(new WebStatFilter());
filter.setName("druidWebStatFilter");
filter.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
filter.addUrlPatterns("/*");
// DelegatingFilterProxy proxy = new DelegatingFilterProxy();
// proxy.setTargetFilterLifecycle(true);
// proxy.setTargetBeanName("shiroFilter");
return filter;
}
/**
* 如果不使用代码手动初始化DataSource的话,监控界面的SQL监控会没有数据
*
* @return 声明其为Bean实例,在同样的DataSource中,首先使用被标注的DataSource
*/
@Bean
@Primary
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
// configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
数据库密码加密及监控页面同方式一。
2.2 配置mybatis-plus
引入依赖:
<!--mybatis-plus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<!-- 代码生成器 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.2</version>
</dependency>
<!-- 代码生成器需要读取模板文件,所以要引入freemaker依赖 -->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.28</version>
</dependency>
在yml文件中配置mybatis-plus:
mybatis-plus:
mapper-locations: classpath*:com/hqyj/*/*/mapper/*Mapper.xml
type-aliases-package: com.hqyj.*.*.entity
global-config:
db-config:
#主键类型 AUTO:"数据库ID自增" INPUT:"用户输入ID",ASSIGN_ID:"mybatisplus分配ID,Long,Integer,String", ASSIGN_UUID:"分配UUID,String";
id-type: auto
#字段策略 IGNORED:"忽略判断" NOT_NULL:"非 NULL 判断") NOT_EMPTY:"非空判断"
#filed-strategy在高版本被废弃(3.1.2以后)
#filed-strategy: NOT_EMPTY
insert-strategy: not_empty
select-strategy: not_empty
update-strategy: not_empty
#刷新mapper 调试神器
refresh-mapper: true
#逻辑删除配置
logic-delete-value: 1
logic-not-delete-value: 0
configuration:
#驼峰下划线转换
map-underscore-to-camel-case: true
cache-enabled: false
2.3 代码生成器
创建CodeGenerator类:
public class CodeGenerator {
/**
* <p>
* 读取控制台内容
* </p>
*/
public static String scanner(String tip) {
Scanner scanner = new Scanner(System.in);
StringBuilder help = new StringBuilder();
help.append("请输入" + tip + ":");
System.out.println(help.toString());
if (scanner.hasNext()) {
String ipt = scanner.next();
if (!StringUtils.isEmpty(ipt)) {
return ipt;
}
}
throw new MybatisPlusException("请输入正确的" + tip + "!");
}
public static void main(String[] args) {
// 代码生成器
AutoGenerator mpg = new AutoGenerator();
// 全局配置
GlobalConfig gc = new GlobalConfig();
// String projectPath = System.getProperty("user.dir");
// gc.setOutputDir(projectPath + "/src/main/java");
String projectPath = "D://mybatisCode";
gc.setOutputDir(projectPath);
gc.setAuthor("sdx2009");
//设置完之后是否打开资源管理器
gc.setOpen(false);
gc.setSwagger2(false);
gc.setIdType(IdType.AUTO);
//设置是否覆盖原始生成的文件
gc.setFileOverride(true);
gc.setActiveRecord(false);// 不需要ActiveRecord特性的请改为false
gc.setEnableCache(false);// XML 二级缓存
gc.setBaseResultMap(true);// XML ResultMap
gc.setBaseColumnList(true);// XML columList
//去掉service的i前缀
gc.setServiceName("%sService");
mpg.setGlobalConfig(gc);
// 数据源配置
DataSourceConfig dsc = new DataSourceConfig();
dsc.setDbType(DbType.MYSQL);
dsc.setDriverName("com.mysql.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("123456");
dsc.setUrl(
"jdbc:mysql://127.0.0.1:3306/j220501?useUnicode=true&characterEncoding=utf8");
mpg.setDataSource(dsc);
// 包配置
PackageConfig pc = new PackageConfig();
pc.setModuleName("user");// 模块名
pc.setParent("com.hqyj.springbootdemo01");
mpg.setPackageInfo(pc);
// 自定义配置
InjectionConfig cfg = new InjectionConfig() {
@Override
public void initMap() {
// to do nothing
}
};
List<FileOutConfig> focList = new ArrayList<>();
focList.add(new FileOutConfig("/templates/mapper.xml.ftl") {
@Override
public String outputFile(TableInfo tableInfo) {
// 自定义输入文件名称
return projectPath + "/src/main/resources/mapper/" + pc.getModuleName() + "/"
+ tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
}
});
cfg.setFileOutConfigList(focList);
mpg.setCfg(cfg);
mpg.setTemplate(new TemplateConfig().setXml(null));
// 策略配置
StrategyConfig strategy = new StrategyConfig();
strategy.setTablePrefix(new String[]{""});// 此处可以修改为您的表前缀
strategy.setNaming(NamingStrategy.underline_to_camel);
// strategy.setColumnNaming(NamingStrategy.underline_to_camel);
// strategy.setSuperEntityClass("com.baomidou.ant.common.BaseEntity");
strategy.setRestControllerStyle(true);
// strategy.setSuperControllerClass("com.baomidou.ant.common.BaseController");
strategy.setInclude("user");// 表名
// strategy.setSuperEntityColumns("id");
// strategy.setControllerMappingHyphenStyle(true);
// strategy.setTablePrefix(pc.getModuleName() + "_");
strategy.setEntityTableFieldAnnotationEnable(true);
// strategy.setLogicDeleteFieldName("del_flag");
//设置是否启用Lombok
strategy.setEntityLombokModel(true);
List<TableFill> tableFillList = new ArrayList<TableFill>();
TableFill tableFill1 = new TableFill("create_time", FieldFill.INSERT);
TableFill tableFill2 = new TableFill("creator", FieldFill.INSERT);
TableFill tableFill3 = new TableFill("update_time", FieldFill.UPDATE);
TableFill tableFill4 = new TableFill("operator", FieldFill.UPDATE);
tableFillList.add(tableFill1);
tableFillList.add(tableFill2);
tableFillList.add(tableFill3);
tableFillList.add(tableFill4);
strategy.setTableFillList(tableFillList);
mpg.setStrategy(strategy);
mpg.setTemplateEngine(new FreemarkerTemplateEngine());
mpg.execute();
}
}
修改相关配置后,运行即可产生代码文件,产生的代码文件包含:
*Mapper.xml、entity实体类、mapper类、service接口、service实现类、controller
将以上文件拷贝到项目相应的目录下即可。
自动填充处理器:
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
Object createTime = getFieldValByName("createTime", metaObject);
if (null == createTime) {
setFieldValByName("createTime", LocalDateTime.now(), metaObject);
}
}
@Override
public void updateFill(MetaObject metaObject) {
Object updateTime = getFieldValByName("updateTime", metaObject);
if (null == updateTime) {
setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
}
}
}
3.Mybatis-plus操作数据
3.1 条件构造器
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R7nc3lOg-1666320169229)(1.png)]
3.2 保存数据
单条保存:
@Override
public boolean addUser(String userName, String password) {
User user = new User();
user.setUserName(userName);
user.setPassword(password);
return this.save(user);
}
批量保存:saveBatch
@Override
public boolean addUser(String userName, String password) {
User user = new User();
user.setUserName(userName);
user.setPassword(password);
List<User> userList = new ArrayList<>();
userList.add(user);
return this.saveBatch(userList);
}
3.3 修改数据
根据id修改:updateById
@Override
public boolean updateUser(Integer id, String userName, String password) {
User user = new User();
user.setUserName(userName);
user.setPassword(password);
user.setId(id);
return this.updateById(user);
}
通过updatewrapper作为条件修改:
@Override
public boolean updateUser(Integer id, String userName, String password) {
User user = new User();
user.setUserName(userName);
user.setPassword(password);
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.eq("id", id);
return this.update(user, wrapper);
}
批量修改:
@Override
public boolean updateUser(Integer id, String userName, String password) {
User user = new User();
user.setUserName(userName);
user.setPassword(password);
user.setId(id);
List<User> userList = new ArrayList<>();
userList.add(user);
return this.updateBatchById(userList);
}
3.4 查询数据
条件查询:
@Override
public User getUser(String userName, String password) {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_name", userName);
wrapper.eq("password",password);
return this.getOne(wrapper);
}
两个eq默认为and连接,相当于:
WHERE (user_name = ? AND password = ?)
and与or组合查询:
@Override
public User getUser(String userName, String password) {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_name", userName);
wrapper.eq("password", password);
wrapper.and(w -> w.eq("user_name", userName).or().eq("password", password));
return this.getOne(wrapper);
}
相当于:
WHERE (user_name = ? AND password = ? AND (user_name = ? OR password = ?))
泛型返回结果对象:
@Data
public class Result<T> {
private Integer code;
private String msg;
private T data;
/**
* 返回成功
*
* @return
*/
public Result<T> success() {
return success("操作成功!");
}
public Result<T> success(String message) {
return success(200, message);
}
public Result<T> success(Integer code, String message) {
this.setCode(code);
this.setMsg(message);
return this;
}
public Result<T> put(T obj) {
this.setData(obj);
return this;
}
/**
* 返回失败
*
* @param code
* @param message
* @return
*/
public Result<T> error(Integer code, String message) {
return success(code, message);
}
public Result<T> error(String message) {
return error(500, message);
}
public Result<T> error() {
return error("操作失败!");
}
}
在controller中统一使用Result对象作为返回对象:
@GetMapping("/info")
public Result<?> getUser(String userName, String password) {
User user = userService.getUser(userName, password);
return new Result<>().success().put(user);
}
分页查询:
@Override
public Page<User> getPageUser(Integer pageNumber, Integer pageSize, String userName,
String password) {
//分页信息
Page<User> page = new Page<>();
//每页多少条数据
page.setSize(pageSize);
//第几页
page.setCurrent(pageNumber);
//筛选条件
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("user_name", userName);
wrapper.eq("password", password);
return this.page(page, wrapper);
}
解决MybatisPlus中page的total为0及未分页的问题:添加分页插件
@Configuration
public class MybatisPlusPaginationConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
return paginationInterceptor;
}
}
泛型分页查询结果返回对象:
@Data
public class PageResult<T> {
private Integer code;
private String msg;
private Long total;
private Long totalPage;
private Long pageNumber;
private List<T> rows;
/**
* 默认构造方法
*/
public PageResult() {
}
public PageResult(Long total, List<T> rows, Long pageNumber, Long pageSize) {
this.total = total;
this.rows = rows;
this.pageNumber = pageNumber;
this.code = 200;
this.msg = "";
Long totalPage = total / pageSize;
if (total % pageSize > 0) {
totalPage++;
}
this.totalPage = totalPage;
}
}
在controller中统一使用PageResult对象作为返回对象:
@GetMapping("/page")
public PageResult<?> getPageUser(Integer pageNumber, Integer pageSize, String userName,
String password) {
Page<User> page = userService.getPageUser(pageNumber, pageSize, userName, password);
PageResult<?> result = new PageResult<>(page.getTotal(), page.getRecords());
return result;
}
多表查询:
方式同SSM中查询方式一致
3.5 删除数据
@Override
public boolean deleteById(Integer id) {
return this.removeById(id);
}
totalPage = total / pageSize;
if (total % pageSize > 0) {
totalPage++;
}
this.totalPage = totalPage;
}
}
在controller中统一使用PageResult对象作为返回对象:
```java
@GetMapping("/page")
public PageResult<?> getPageUser(Integer pageNumber, Integer pageSize, String userName,
String password) {
Page<User> page = userService.getPageUser(pageNumber, pageSize, userName, password);
PageResult<?> result = new PageResult<>(page.getTotal(), page.getRecords());
return result;
}
多表查询:
方式同SSM中查询方式一致
3.5 删除数据
@Override
public boolean deleteById(Integer id) {
return this.removeById(id);
}