pom配置
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.5.3</version>
</dependency>
<!--动态数据源-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
1.User
@Data//lombok-get()、 set()、 toString()
/**
* 1. @TableName: 解决实体类的名称与数据库中的表名不匹配的问题
*/
@TableName(value ="test_user_1")
public class User {
/**
* 1. @TableId:专门用在主键上的注解 :
* value:映射主键字段的名字,即数据库的列名 (不配置则一样/驼峰命名对应)
* type:设置主键类型、主键的生成策略
* AUTO 数据库自增
* NONE MP set主键,雪花算法实现
* INPUT 需要手动赋值
* ASSIGN_ID MP分配ID,Long、Integer、String
* ASSIGN_UUID 分配UUID,String
*
* 2. 主键类型慎用基本数据类型(int),要使用其包装类(Integer)
* 如int默认值为0,在mypatisplus的insert中,将不会使用算法生成id,直接使用默认值进行处理。如下:
* ==> Preparing: INSERT INTO test_user ( id, name, age, desc ) VALUES ( ?, ?, ?, ? )
* ==> Parameters: 0(Integer), 张三03-30 11:50(String), 50(Integer), 插入[张三03-30 11:50](String)
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer ids;
private String name;
private int age;
/**
* 1. @TableField:
* value: 解决实体类的字段名与数据库中的字段名不匹配的问题
* exist = false: 用来解决实体中存在,单数表中不存在的字段
*/
@TableField(value = "description")
private String descr;
/**
* 该字段在数据库中不存在
*/
@TableField(exist = false)
private String zth;
}
2.UserMapper
@Repository
@DS("master")//@DS 可以注解在方法上或类上,同时存在就近原则 方法上注解 优先于 类上注解。
public interface UserMapper extends BaseMapper<User> {
}
3.CRUD
3.1. insert
User user = new User();
user.setName("张三"+ ToolsTime.getHHMM());
user.setAge(ToolsTime.getMM());
user.setDescr("插入["+user.getName()+"]");
int res = mapper.insert(user);
System.err.println("***[mybatisplus] 插入后的用户Id: "+user.getIds());
//1.插入后自动填充其id
//==> Preparing: INSERT INTO test_user_1 ( name, age, description ) VALUES ( ?, ?, ? )
//==> Parameters: 张三03-30 15:05(String), 5(Integer), 插入[张三03-30 15:05](String)
user = new User();
user.setName("李四"+ ToolsTime.getHHMM());
mapper.insert(user);
//2.插入时,为null的属性,不插入
//==> Preparing: INSERT INTO test_user_1 ( name, age ) VALUES ( ?, ? )
//==> Parameters: 李四15:07(String), 0(Integer)
3.2.update
User user = mapper.selectById(3);
//1. updateById
// ==> Preparing: UPDATE test_user_1 SET name=?, age=?, description=? WHERE id=?
//==> Parameters: 张三(String), 18(Integer), 编辑(String), 1(Integer)
user.setDescr("编辑");
mapper.updateById(user);
//2. QueryWrapper
// ==> Preparing:
// UPDATE test_user_1
// SET name=?,
// age=?
// WHERE (name = ?)
//==> Parameters: 李四update(String), 0(Integer), 李四(String)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","李四");
user = new User();
user.setName("李四update");
mapper.update(user, queryWrapper);
//3.QueryWrapper
//==> Preparing: UPDATE test_user_1 SET age=? WHERE (name = ?)
//==> Parameters: 0(Integer), 李四(String)
queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","李四");
mapper.update(new User(), queryWrapper);
//4.UpdateWrapper
//==> Preparing: UPDATE test_user_1 SET age=? WHERE (name = ?)
//==> Parameters: 0(Integer), 李四(String)
UpdateWrapper<User> uw = new UpdateWrapper<>();
uw.eq("name","李四");
mapper.update(new User(), uw);
//5.QueryWrapper
//==> Preparing: UPDATE test_user_1 SET age=?, description=? WHERE (name = ?)
//==> Parameters: 0(Integer), null, 李四(String)
uw = new UpdateWrapper<>();
uw.eq("name","李四");
uw.set("description", null);
mapper.update(new User(), uw);
//6.不创建对象
// ==> Preparing: UPDATE test_user_1 SET description=? WHERE (name = ?)
//==> Parameters: null, 李四(String)
uw = new UpdateWrapper<>();
uw.eq("name","李四");
uw.set("description", null);
mapper.update(null, uw);
3.3.delete
User user = mapper.selectById(1);
//1. deleteById
// ==> Preparing: DELETE FROM test_user_1 WHERE id=?
//==> Parameters: 1(Integer)
mapper.deleteById(user);
//2. delete(T , QueryWrapper)
// ==> Preparing: DELETE FROM test_user_1 WHERE (name = ?)
//==> Parameters: 李四(String)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","李四");
mapper.delete(queryWrapper);
//3. deleteBatchIds
// ==> Preparing: DELETE FROM test_user_1 WHERE id IN ( ? , ? , ? )
//==> Parameters: 8(Integer), 9(Integer), 10(Integer)
mapper.deleteBatchIds(Arrays.asList(8, 9, 10));
//4. deleteByMap
// ==> Preparing: DELETE FROM test_user_1 WHERE name = ? AND age = ?
//==> Parameters: 张三(String), 18(String)
HashMap<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", "18");
int result = mapper.deleteByMap(map);
3.4.select
//1.插入101用户
User user = new User();
user.setIds(101);
user.setName("张三101");
user.setAge(ToolsTime.getMM());
user.setDescr("插入["+user.getName()+"]");
mapper.insert(user);
//2.selectById:通过id查询
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE id=?
//==> Parameters: 101(Integer)
User u = mapper.selectById(101);
System.err.println("***[mybatisplus] selectById: "+u);
//3.selectOne:查询一条数据(多条则报错,org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 4)
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE (name = ?)
//==> Parameters: 张三101(String)
QueryWrapper<User> wrapper = new QueryWrapper<User>();
wrapper.eq("name", "张三101");
u = mapper.selectOne(wrapper);
System.err.println("***[mybatisplus] selectOne: "+u);
//4.selectList:查询数据
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE (name = ?)
//==> Parameters: 张三(String)
wrapper = new QueryWrapper<User>();
wrapper.eq("name", "张三");
List<User> list = mapper.selectList(wrapper);
System.err.println("***[mybatisplus] selectList: "+list);
//5.selectCount: 查询总数
//==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (name = ?)
//==> Parameters: 张三(String)
Long num = mapper.selectCount(wrapper);
System.err.println("***[mybatisplus] selectCount: "+num);
System.err.println("----------------------------------baseMapper 自带分页-------------------------------------------------------");
//6.selectPage: 分页查询(需在MybatisPlusConfig中配置分页插件)
// ==> Preparing: SELECT COUNT(*) AS total FROM test_user_1
// ==> Parameters:
// ==> Preparing: SELECT id AS ids, name, age, description AS descr FROM test_user_1 ORDER BY age ASC LIMIT ?,?
// ==> Parameters: 3(Long), 3(Long)
Page<User> page = new Page<>(2, 3);
page.addOrder(OrderItem.asc("age"));
mapper.selectPage(page, null);
System.err.println("***[mybatisplus] 数据list: "+page.getRecords());
System.err.println("***[mybatisplus] 总条数: "+page.getTotal());
System.err.println("***[mybatisplus] 当前页码: "+page.getCurrent());
System.err.println("***[mybatisplus] 每页数量: "+page.getSize());
System.err.println("***[mybatisplus] 总页数: "+page.getPages());
System.err.println("----------------------------------json 正反序列化-------------------------------------------------------");
String json = JSON.toJSONString(page);
System.err.println(json);
Page<User> page1 = JSON.parseObject(json, new TypeReference<Page<User>>() {});
System.err.println(page1);
4.MybatisPlusConfig
@Configuration
@MapperScan("org.example.mapper")
public class MybatisPlusConfig {
public final static String dbName = "dbName";
/**
* //1.分页插件
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor2(){
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return mybatisPlusInterceptor;
}
}
5.其他查询
/**
* 查询: 大于等于、大于、小于等于、小于、为空、非空,等于,不等于
* ge、gt、le、lt、isNull、isNotNull
*/
@Test
public void select1() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE (
// name IS NOT NULL AND
// msg IS NULL AND
// age >= ?
// AND description = ?
// AND msg <> ?)
//==> Parameters: (String), test_user_1(String), (String)
wrapper
.isNotNull("name") //不为空
.isNull("msg") //为空
.ge("age", "") //大于等于12
.eq("description", "test_user_1") //等于
.ne("msg", "") //不等
;
List<User> list = mapper.selectList(wrapper);
System.err.println("***[mybatisplus] selectList: "+list);
}
/**
* 1、and-or连接
* 2、排序: orderBy、orderByDesc、orderByAsc
* 3、last 直接拼接到 sql 的最后
* 4、查询特定的列
*/
@Test
public void select2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//1.默认and连接
// ==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (age = ? AND name LIKE ?)
//==> Parameters: 18(Integer), %张三%(String)
queryWrapper.eq("age", 18).like("name","张三");
Long res = mapper.selectCount(queryWrapper);
System.err.println("***[mybatisplus] : "+res);
//2.or连接
// ==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (age = ? OR name = ?)
//==> Parameters: 18(Integer), 张三(String)
queryWrapper = new QueryWrapper<>();
queryWrapper.eq("age", 18).or().eq("name","张三");
mapper.selectCount(queryWrapper);
System.err.println("***[mybatisplus] : "+res);
//3.and-or连接
// ==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (name = ? AND (age = ? OR msg IS NULL))
//==> Parameters: 张三(String), 28(Integer)
queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","张三").and(
wrapper -> wrapper.eq("age", 28).
or().isNull("msg")
);
mapper.selectCount(queryWrapper);
System.err.println("***[mybatisplus] : "+res);
//4.排序: orderBy、orderByDesc、orderByAsc
// ==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 ORDER BY age DESC,name ASC
//==> Parameters:
queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age").orderByAsc("name");
List<User> list = mapper.selectList(queryWrapper);
System.err.println("***[mybatisplus] : "+list);
//5.last 直接拼接到 sql 的最后
// ==> Preparing: SELECT id,name,description FROM test_user_1 limit 1
//==> Parameters:
queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");
//6.查询特定的列: 注意为数据库列
queryWrapper.select("id", "name", "description");
User user = mapper.selectOne(queryWrapper);
System.err.println("***[mybatisplus] : "+user);
//7.selectByMap
// ==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE name = ? AND age = ?
//==> Parameters: 张三(String), 28(Integer)
HashMap<String, Object> map = new HashMap<>();
map.put("name", "张三");
map.put("age", 28);
List<User> users = mapper.selectByMap(map);
System.err.println("***[mybatisplus] : "+user);
//8.max(id)
//==> Preparing: SELECT max(id) as ids FROM test_user_1
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("max(id) as ids");
user = mapper.selectOne(wrapper);
System.err.println("***[mybatisplus] : "+user);
}
/**
* 查询: between、notBetween 包含大小边界
*/
@Test
public void select3() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (age BETWEEN ? AND ?)
//==> Parameters: 18(Integer), 20(Integer)
queryWrapper.between("age", 18, 20);
Long count = mapper.selectCount(queryWrapper);
System.err.println(count);
}
/**
* allEq
*/
@Test
public void select4() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("name", "张三");
map.put("age", 18);
queryWrapper.allEq(map);
//==> Preparing: SELECT COUNT( * ) AS total FROM test_user_1 WHERE (name = ? AND id = ? AND age = ?)
//==> Parameters: 张三(String), 2(Integer), 18(Integer)
Long count = mapper.selectCount(queryWrapper);
System.err.println(count);
}
/**
* like、notLike、likeLeft、likeRight
*/
@Test
public void select5() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.likeLeft("name", "三")
.like("description", "test_user_1")
.likeRight("msg", "test_user_1");
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1 WHERE (name LIKE ? AND description LIKE ? AND msg LIKE ?)
//==> Parameters: %三(String), %test_user_1%(String), test_user_1%(String)
List<Map<String, Object>> maps = mapper.selectMaps(queryWrapper);//返回值是Map列表
maps.forEach(System.err::println);
}
/**
* in、notIn、inSql、notinSql、exists、notExists
* notIn("age",{1,2,3})--->age not in (1,2,3)
* notIn("age", 1, 2, 3)--->age not in (1,2,3)
*/
@Test
public void select6() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", 18,28,38);
queryWrapper.inSql("id", "select id from test_user_1 where id < 5");
queryWrapper.notIn("name", "joney","tom");
//==> Preparing: SELECT id AS ids,name,age,description AS descr FROM test_user_1
// WHERE (
// age IN (?,?,?) AND
// id IN (select id from test_user_1 where id < 5)
// AND name NOT IN (?,?)
// )
//==> Parameters: 18(Integer), 28(Integer), 38(Integer), joney(String), tom(String)
List<Object> objects = mapper.selectObjs(queryWrapper);//返回值是Object列表
objects.forEach(System.err::println);
}
/**
* groupBy
*/
@Test
public void select7() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
//==> Preparing: SELECT age, count(*) FROM test_user_1 GROUP BY age
//{num=5, age=18}
//{num=1, age=20}
//{num=1, age=28}
//{num=2, age=38}
wrapper.select("age, count(*) num")
.groupBy("age");
List<Map<String, Object>> maplist = mapper.selectMaps(wrapper);
for (Map<String, Object> mp : maplist) {
System.err.println(mp);
}
}