整合过程
1.利用Spring Initializr快速搭建web项目
2.导入相关依赖
- mybatis-plus依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
- 数据库驱动依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
- 数据源依赖,我们使用Druid数据源
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
3.在配置文件中进行相关属性的配置
spring:
datasource:
username: root
password: root
url: jdbc:mysql://localhost:3306/mp
driver-class-name: com.mysql.jdbc.Driver
druid:
aop-patterns: com.haust.* #监控SpringBean
filters: stat,wall # 底层开启功能,stat(sql监控),wall(防火墙)
stat-view-servlet: # 配置监控页功能
enabled: true
login-username: admin
login-password: admin
resetEnable: false
web-stat-filter: # 监控web
enabled: true
urlPattern: /*
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'
filter:
stat: # 对上面filters里面的stat的详细配置
slow-sql-millis: 1000
logSlowSql: true
enabled: true
wall:
enabled: true
config:
drop-table-allow: false
mybatis-plus:
type-aliases-package: com.haust.pojo
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#配置日志
4.数据库连接测试
综上整合部分已经完成。
mybatis-plus入门
入门案例
创建相关测试数据库
ROP TABLE IF EXISTS USER;
CREATE TABLE USER
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
NAME VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (id)
);
INSERT INTO USER (id, NAME, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');
创建对应的POJO对象
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
@TableId(type = IdType.AUTO) //表示id列为自增列,在数据库里面需要进行相应的配置
private Long id;
private String name;
private int age;
private String email;
}
创建mapper类
public interface UserMapper extends BaseMapper<User> {
}
我们进入BaseMapper类中查看源码
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import java.io.Serializable;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
public interface BaseMapper<T> extends Mapper<T> {
int insert(T entity);
int deleteById(Serializable id);
int deleteByMap(@Param("cm") Map<String, Object> columnMap);
int delete(@Param("ew") Wrapper<T> queryWrapper);
int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);
int updateById(@Param("et") T entity);
int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
T selectById(Serializable id);
List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);
List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);
T selectOne(@Param("ew") Wrapper<T> queryWrapper);
Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);
List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);
List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);
<E extends IPage<T>> E selectPage(E page, @Param("ew") Wrapper<T> queryWrapper);
<E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param("ew") Wrapper<T> queryWrapper);
}
会发现已经帮我们实现了很多方法了
测试
@SpringBootTest
public class test {
// 继承了BaseMapper,所有的方法都来自己父类
// 我们也可以编写自己的扩展方法!
@Autowired
UserMapper userMapper;
@Test
void test1(){
// 参数是一个 Wrapper ,条件构造器,这里我们先填null
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}
结果:
User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
User(id=6, name=lilei, age=3, email=123@qq.com)
User(id=7, name=lilei, age=3, email=123@qq.com)
CRUD扩展
插入操作
@Test
void test2(){
User user = new User();
user.setAge(18);
user.setName("lisi");
user.setEmail("hello@qq.com");
userMapper.insert(user);
System.out.println(user);
}
结果:
User(id=8, name=lisi, age=18, email=hello@qq.com)
//我们可以发现id会回填并且自增,那么需要在数据库表中将id列设为自增,并且在user类对应的id属性加@TableId(type = IdType.AUTO)注解
主键生成策略
默认 ID_WORKER 全局唯一i,@TableId默认值
分布式系统唯一id生成:https://www.cnblogs.com/haoxinyue/p/5208136.html
雪花算法:
snowflake是Twitter开源的分布式ID生成算法,结果是一个long型的ID。其核心思想是:使用41bit作为
毫秒数,10bit作为机器的ID(5个bit是数据中心,5个bit的机器ID),12bit作为毫秒内的流水号(意味
着每个节点在每毫秒可以产生 4096 个 ID),最后还有一个符号位,永远是0。可以保证几乎全球唯
一!
@TableId其他值解析
public enum IdType {
AUTO(0), // 数据库id自增
NONE(1), // 未设置主键
INPUT(2), // 手动输入
ID_WORKER(3), // 默认的全局唯一id
UUID(4), // 全局唯一id uuid
ID_WORKER_STR(5); //ID_WORKER 字符串表示法
}
更新操作
@Test
void test3(){
User user = new User();
user.setId(8L);
user.setAge(20);
user.setName("lisi");
user.setEmail("hello@qq.com");
//注意:参数为对象,并不是简单的id
userMapper.updateById(user);
}
结果:
==> Preparing: UPDATE user SET name=?, age=?, email=? WHERE id=?
==> Parameters: lisi(String), 20(Integer), hello@qq.com(String), 8(Long)
<== Updates: 1
User(id=8, name=lisi, age=20, email=hello@qq.com)
查询操作
1.根据id查询
@Test
void test5(){
User user = userMapper.selectById(1L);
System.out.println(user);
}
结果:
==> Preparing: SELECT id,name,age,email FROM user WHERE id=?
==> Parameters: 1(Long)
<== Columns: id, name, age, email
<== Row: 1, Jone, 18, test1@baomidou.com
<== Total: 1
User(id=1, name=Jone, age=18, email=test1@baomidou.com)
2.根据条件查询
@Test
void test6(){
HashMap<String, Object> hashMap = new HashMap<>();
hashMap.put("name","Tom");
hashMap.put("age",28);
List<User> users = userMapper.selectByMap(hashMap);
users.forEach(System.out::println);
}
结果:
从结果来看,hashmap中多一个k-v就会多一个and条件限制
==> Preparing: SELECT id,name,age,email FROM user WHERE name = ? AND age = ?
==> Parameters: Tom(String), 28(Integer)
<== Columns: id, name, age, email
<== Row: 3, Tom, 28, test3@baomidou.com
<== Total: 1
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
3.分页查询
//第一步需要首先注册bean
@Configuration
public class MyConfig {
// 最新版
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
}
//测试
@Test
void test7(){
//参数一:当前页
//参数二:页面大小
Page<User> page = new Page<>(1, 5);
userMapper.selectPage(page,null);
//返回查询的结果
page.getRecords().forEach(System.out::println);
//返回全部数据个数
System.out.println(page.getTotal());
}
结果:
==> Preparing: SELECT COUNT(*) FROM user
==> Parameters:
<== Columns: COUNT(*)
<== Row: 8
<== Total: 1
==> Preparing: SELECT id,name,age,email FROM user LIMIT ?
==> Parameters: 5(Long)
<== Columns: id, name, age, email
<== Row: 1, Jone, 18, test1@baomidou.com
<== Row: 2, Jack, 20, test2@baomidou.com
<== Row: 3, Tom, 28, test3@baomidou.com
<== Row: 4, Sandy, 21, test4@baomidou.com
<== Row: 5, Billie, 24, test5@baomidou.com
<== Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@d48e998]
User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
8
删除操作
1.根据id删除
@Test
void test7(){
userMapper.deleteById(1);
}
结果:
==> Preparing: DELETE FROM user WHERE id=?
==> Parameters: 1(Integer)
<== Updates: 1
2.批量删除
@Test
void test7(){
//Arrays.asList方法用于将数组转换为list
userMapper.deleteBatchIds(Arrays.asList(2L,3L));
}
结果:
==> Preparing: DELETE FROM user WHERE id IN ( ? , ? )
==> Parameters: 2(Long), 3(Long)
<== Updates: 2
3.根据map删除
@Test
void test7(){
HashMap<String, Object> hashMap = new HashMap<>();
hashMap.put("name","lilei");
int result = userMapper.deleteByMap(hashMap);
}
结果:
==> Preparing: DELETE FROM user WHERE name = ?
==> Parameters: lilei(String)
<== Updates: 2
条件构造器Wrapper
支持链式编程
官方地址:https://baomidou.com/pages/10c804/#gt
简单理解就是构造查询条件
代码举例:
例1:
@Test
void test7(){
//查询name不为空的用户,并且邮箱不为空的用户,年龄大于等于12
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.isNotNull("name")
.isNotNull("email")
.ge("age",12);
userMapper.selectList(wrapper).forEach(System.out::println);
}
结果:
==> Preparing: SELECT id,name,age,email FROM user WHERE (name IS NOT NULL AND email IS NOT NULL AND age >= ?)
==> Parameters: 12(Integer)
<== Columns: id, name, age, email
<== Row: 4, Sandy, 21, test4@baomidou.com
<== Row: 5, Billie, 24, test5@baomidou.com
<== Row: 8, lisi, 20, hello@qq.com
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60e06f7d]
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
User(id=8, name=lisi, age=20, email=hello@qq.com)
例2:
@Test
void test7(){
//查询年龄在20~30岁之间的用户
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.between("age",20,30);//区间
Integer integer = userMapper.selectCount(wrapper);//查询结果数
System.out.println(integer);
}
结果:
==> Preparing: SELECT COUNT( * ) FROM user WHERE (age BETWEEN ? AND ?)
==> Parameters: 20(Integer), 30(Integer)
<== Columns: COUNT( * )
<== Row: 3
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6a1d526c]
3
例3:
@Test
void test7(){
//查询以邮箱以com结尾的用户
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.likeLeft("email","com");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
结果:
==> Preparing: SELECT id,name,age,email FROM user WHERE (email LIKE ?)
==> Parameters: %com(String)
<== Columns: id, name, age, email
<== Row: 4, Sandy, 21, test4@baomidou.com
<== Row: 5, Billie, 24, test5@baomidou.com
<== Row: 8, lisi, 20, hello@qq.com
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@26ca61bf]
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
User(id=8, name=lisi, age=20, email=hello@qq.com)
例4:
@Test
void test7(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
//通过age进行排序
wrapper.orderByAsc("age");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
结果:
==> Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC
==> Parameters:
<== Columns: id, name, age, email
<== Row: 8, lisi, 20, hello@qq.com
<== Row: 4, Sandy, 21, test4@baomidou.com
<== Row: 5, Billie, 24, test5@baomidou.com
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@550e9be6]
User(id=8, name=lisi, age=20, email=hello@qq.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
代码生成器注意
适用版本:mybatis-plus-generator 3.5.1 以下版本
AutoGenerator 是 MyBatis-Plus 的代码生成器,通过 AutoGenerator 可以快速生成 Entity、Mapper、Mapper XML、Service、Controller 等各个模块的代码,极大的提升了开发效率。
添加依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>2.3</version>
</dependency>
编写代码生成器类
package com.haust.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
public class GeneratorClass {
public static void main(String[] args) {
// 需要构建一个 代码自动生成器 对象
AutoGenerator generator = new AutoGenerator();
// 全局配置
GlobalConfig gc = new GlobalConfig();
String path = System.getProperty("user.dir");//设置代码的生成位置,磁盘的目录
gc.setOutputDir(path+"/src/main/java");//生成的代码位置
gc.setOpen(false);//代码生成后是否打开文件夹
gc.setFileOverride(false); // 是否覆盖
gc.setServiceName("%sService"); // 去Service的I前缀
gc.setMapperName("%sMapper");//所有的dao类都是以Mapper结尾的,例如UserMapper
gc.setServiceName("%sService");//UserService
gc.setServiceImplName("%sServiceImpl");//UserServiceImpl
gc.setControllerName("%sController");//设置controller类的命名,UserController
gc.setAuthor("xxx");//设置作者
gc.setIdType(IdType.AUTO);//设置为主键自增
gc.setDateType(DateType.ONLY_DATE);//设置日期类型
generator.setGlobalConfig(gc);
//设置数据源DataSource
DataSourceConfig config = new DataSourceConfig();
config.setDriverName("com.mysql.jdbc.Driver");//驱动
config.setUrl("jdbc:mysql://localhost:3306/mp?useUnicode=true&characterEncoding=utf-8");//设置url
config.setUsername("root");//设置数据库的用户名
config.setPassword("root");//设置密码
config.setDbType(DbType.MYSQL);//设置数据库类型为MySQL
generator.setDataSource(config);//将DataSourceConfig赋值给autogenerator
//设置package信息
PackageConfig pc = new PackageConfig();
pc.setModuleName("haust");//设置模块名称,相当于报名,在这个包的下面有mapper,service,controller...
pc.setParent("com");//设置父包名,user就在父包的下面生成
pc.setEntity("pojo");//设置包名
pc.setMapper("mapper");
pc.setService("service");
pc.setController("controller");
generator.setPackageInfo(pc);
//设置策略
StrategyConfig sc = new StrategyConfig();
sc.setInclude("user","user2"); // 设置要映射的表名
sc.setNaming(NamingStrategy.underline_to_camel);//支持驼峰的命名规则
sc.setColumnNaming(NamingStrategy.underline_to_camel);
sc.setEntityLombokModel(true); // 自动lombok;
generator.setStrategy(sc);
//执行代码的生成
generator.execute();
}
}
运行即可,相关文件已经生成
教你一招
mybatis多表查询解决方案:
例如执行
SELECT user.* FROM `user`,user2 WHERE user.`id`=user2.`id` AND user.`id`=5 AND user2.`id`=5
可能因为版本问题,代码生成器在使用后需要将其及其的依赖注释掉!
否则会出现以下错误:
java.lang.NoSuchMethodError: com.baomidou.mybatisplus.core.toolkit.StringUti
方案一:注解方式
public interface UserMapper extends BaseMapper<User> {
@Select("SELECT user.* FROM `user`,user2 WHERE user.`id`=user2.`id` AND user.`id`=#{id}")
public User selectByTbls(Integer id);
}
//测试
@Test
void test8(){
User user = userMapper.selectByTbls(5);
System.out.println(user);
}
结果:
==> Preparing: SELECT user.* FROM `user`,user2 WHERE user.`id`=user2.`id` AND user.`id`=?
==> Parameters: 5(Integer)
<== Columns: id, name, age, email
<== Row: 5, Billie, 24, test5@baomidou.com
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4dde8976]
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
方式二:xml文件方式
在mybatis-plus中
mapperLocations 自动配置好的。有默认值。classpath*:/mapper/**/*.xml;任意包的类路径下的所有mapper文件夹下任意路径下的所有xml都是sql映射文件。 建议以后sql映射文件,放在 mapper下。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haust.mapper.UserMapper">
<select id="selectByTbls" resultType="user">
SELECT user.* FROM `user`,user2 WHERE user.`id`=user2.`id` AND user.`id`=#{id}
</select>
</mapper>
//测试
@Test
void test8(){
User user = userMapper.selectByTbls(5);
System.out.println(user);
}
结果:
==> Preparing: SELECT user.* FROM `user`,user2 WHERE user.`id`=user2.`id` AND user.`id`=?
==> Parameters: 5(Integer)
<== Columns: id, name, age, email
<== Row: 5, Billie, 24, test5@baomidou.com
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4dde8976]
User(id=5, name=Billie, age=24, email=test5@baomidou.com)