Mybatis-Plus简单查询
# 多个 id 批量查询
// 多个id的批量查询
@Test
public void testSelect1() {
List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
System.out.println(users);
}
1
2
3
4
5
6
2
3
4
5
6
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d37f1c] was not registered for synchronization because synchronization is not active
2022-02-23 21:24:53.180 INFO 94650 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-23 21:24:53.450 INFO 94650 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@934640468 wrapping com.mysql.cj.jdbc.ConnectionImpl@5c1f6d57] will not be managed by Spring
==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? , ? )
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<== Columns: id, name, age, email, create_time, update_time, version
<== Row: 1, Jone, 18, 123@qq.com, null, null, null
<== Row: 2, jack, 19, dwq@qq.com, null, null, null
<== Row: 3, tom, 20, tom@qq.com, null, null, null
<== Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d37f1c]
[User(id=1, name=Jone, age=18, email=123@qq.com, createTime=null, updateTime=null, version=null), User(id=2, name=jack, age=19, email=dwq@qq.com, createTime=null, updateTime=null, version=null), User(id=3, name=tom, age=20, email=tom@qq.com, createTime=null, updateTime=null, version=null)]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
即:select * from table where id in (1, 2, 3)
# 简单条件查询
使用Map
类型组装条件语句进行查询
// 简单条件查询
@Test
public void testSelect2() {
Map<String, Object> columnMap = new HashMap<>();
columnMap.put("name", "张三");
columnMap.put("age", 20);
List<User> users = userMapper.selectByMap(columnMap);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76d05cc9] was not registered for synchronization because synchronization is not active
2022-02-23 21:27:20.203 INFO 94714 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-23 21:27:20.465 INFO 94714 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@48071225 wrapping com.mysql.cj.jdbc.ConnectionImpl@472698d] will not be managed by Spring
==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user WHERE name = ? AND age = ?
==> Parameters: 张三(String), 20(Integer)
<== Columns: id, name, age, email, create_time, update_time, version
<== Row: 1495378144188751873, 张三, 20, 12@qq.com, 2022-02-20 20:41:47, 2022-02-20 20:52:14, 2
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@76d05cc9]
[User(id=1495378144188751873, name=张三, age=20, email=12@qq.com, createTime=Sun Feb 20 20:41:47 CST 2022, updateTime=Sun Feb 20 20:52:14 CST 2022, version=2)]
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT id,name,age,email,create_time,update_time,version FROM user WHERE name = "张三" AND age = 20;
1
# 分页查询
配置分页插件
在配置类中加入
Mybatis-Plus
的分页插件/** * 分页插件 */ @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }
1
2
3
4
5
6
7编写分页代码
插件
Page
对象,传入 2 个参数:- 当前页
- 每页记录数
调用
Mybatis-Plus
的方法实现分页@Test public void testSelectPage() { Page<User> page = new Page<>(1, 3); // 第二个参数为查询条件 Page<User> userPage = userMapper.selectPage(page, null); // 返回对象得到分页所有数据 // 得到总页数 long pages = userPage.getPages(); // 得到当前页 long userPageCurrent = userPage.getCurrent(); // 当前查询数据的集合 List<User> userList = userPage.getRecords(); // 表中的总记录数 long total = userPage.getTotal(); // 判断当前页是否有下一页 boolean hasNext = userPage.hasNext(); // 判断当前页是否有上一页 boolean hasPrevious = userPage.hasPrevious(); System.out.println(pages); System.out.println(userPageCurrent); System.out.println(userList); System.out.println(total); System.out.println(hasNext); System.out.println(hasPrevious); }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e57b5e9] was not registered for synchronization because synchronization is not active 2022-02-23 21:35:45.485 INFO 94964 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-02-23 21:35:45.754 INFO 94964 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@736868175 wrapping com.mysql.cj.jdbc.ConnectionImpl@58cf8f94] will not be managed by Spring JsqlParserCountOptimize sql=SELECT id,name,age,email,create_time,update_time,version FROM user ==> Preparing: SELECT COUNT(1) FROM user ==> Parameters: <== Columns: COUNT(1) <== Row: 6 ==> Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user LIMIT ?,? ==> Parameters: 0(Long), 3(Long) <== Columns: id, name, age, email, create_time, update_time, version <== Row: 1, Jone, 18, 123@qq.com, null, null, null <== Row: 2, jack, 19, dwq@qq.com, null, null, null <== Row: 3, tom, 20, tom@qq.com, null, null, null <== Total: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e57b5e9] 2 1 [User(id=1, name=Jone, age=18, email=123@qq.com, createTime=null, updateTime=null, version=null), User(id=2, name=jack, age=19, email=dwq@qq.com, createTime=null, updateTime=null, version=null), User(id=3, name=tom, age=20, email=tom@qq.com, createTime=null, updateTime=null, version=null)] 6 true false
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
使用selectMapsPage
分页
当指定了特定的查询时,希望分页结果列表只返回被查询的列,而不是很多 null 值
测试 selectMapsPage
分页:结果集是Map
@Test
public void testSelectMapsPage() {
// Page 不需要泛型
Page<Map<String, Object>> page = new Page<>(1, 5);
Page<Map<String, Object>> pageParam = userMapper.selectMapsPage(page, null);
List<Map<String, Object>> records = pageParam.getRecords();
records.forEach(System.out::println);
System.out.println(pageParam.getCurrent());
System.out.println(pageParam.getPages());
System.out.println(pageParam.getSize());
System.out.println(pageParam.getTotal());
System.out.println(pageParam.hasNext());
System.out.println(pageParam.hasPrevious());
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
编辑 (opens new window)
上次更新: 2022/02/23, 22:31:06