Mybatis三种分页+批量增删改
9、Mybatis分页
9.1、sql的limit分页
从1开始到2结束
数据库信息第一个下标为0
select * from tb_user limit 1,2
select * from tb_user limit 2; [0,2]
方式一
9.9.1、接口
// 分页
List<UserBean> getUserByLimit(Map<String,Integer> map);
9.9.1、Mapper
<resultMap id="userMap" type="UserBean">
<id column="tb_id" property="id"/>
<result column="tb_name" property="name"/>
<result column="tb_pwd" property="pwd"/>
</resultMap>
<!-- 分页查询 -->
<select id="getUserByLimit" parameterType="map" resultMap="userMap">
SELECT * from tb_user limit #{startIndex},#{pageSize}
</select>
9.9.1、测试
@Test
public void getUserList(){
// 获取sqlSession对象
SqlSession sqlSession = MapperUtils.getSqlSession();
Map<String, Integer> map = new HashMap<>();
map.put("startIndex",1);
map.put("pageSize",3);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<UserBean> userList = mapper.getUserByLimit(map);
for (UserBean userBean : userList) {
System.out.println(userBean);
}
// 关闭sqlSession
sqlSession.close();
}
方式二：使用对象来进行分页，RowBounds
9.2.1、接口
// 分页 方式2
List<UserBean> getUserByRowBounds();
9.2.1、Mapper
<select id="getUserByRowBounds" resultMap="userMap">
SELECT * from tb_user
</select>
9.2.1、测试
@Test
public void getUserByRowBounds(){
RowBounds rowBounds = new RowBounds(1,2);
// 获取sqlSession对象
SqlSession sqlSession = MapperUtils.getSqlSession();
List<UserBean> user = sqlSession.selectList("com.wen.mapper.UserMapper.getUserByRowBounds",null,rowBounds);
for (UserBean userBean : user) {
System.out.println(userBean);
}
// 关闭sqlSession
sqlSession.close();
}
方式三：使用分页插件，Mybatis-PageHelper
9.3.1、如何使用
网页文档：https://pagehelper.github.io/docs/howtouse/
9.3.1.1、导入Maven依赖引入分页插件
<!--Mybatis-PageHelper分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.0.0</version>
</dependency>
9.3.1.2、配置拦截器插件
<!-- 在 MyBatis.config.xml 配置中配置拦截器插件 匿名下面typeAliases-->
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageHelper">
<!-- 方言 -->
<property name="dialect" value="mysql"/>
<!-- 该参数默认为false -->
<!-- 设置为true时，使用RowBounds分页会进行count查询 -->
<property name="rowBoundsWithCount" value="true"/>
</plugin>
</plugins>
9.3.1.3、代码中使用 接口
// 分页 方式3
List<UserBean> getUserByPageHelper(UserBean userBean);
9.3.1.4、代码中使用 Mapper
<!--分页3-->
<select id="getUserByPageHelper" resultMap="userMap">
SELECT * from tb_user
</select>
9.3.1.4、代码中使用 测试
@Test
public void getUserByPageHelper(){
SqlSession sqlSession = MapperUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
PageHelper.startPage(2,2,true);
List<UserBean> userList = mapper.getUserByPageHelper(new UserBean());
PageInfo<UserBean> pageInfo = new PageInfo<UserBean>(userList);
System.out.println("数据总数" + pageInfo.getTotal()); // pageInfo 多个方法
System.out.println("数据总页数"+pageInfo.getPages());
System.out.println("数据最后一页"+pageInfo.getLastPage());
for (UserBean userBean : pageInfo.getList()) {
System.out.println(userBean);
}
sqlSession.close();
}
10、批量增删改
10.1、批量增加
10.1.1、接口
// 批量增加
int addUsers(List<UserBean> user);
10.1.2、Mapper
<!--批量增加-->
<insert id="addUsers" parameterType="list" keyProperty="id" useGeneratedKeys="true">
insert into tb_user (tb_id,tb_name,tb_pwd) values
<foreach collection="list" item="u" index="list" separator=",">
(#{u.id},#{u.name},#{u.pwd})
</foreach>
</insert>
10.1.3、测试
@Test
public void addUsers(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<UserBean> userList = new ArrayList<>();
userList.add(new UserBean("麦穗儿2","66666"));
userList.add(new UserBean("玉米粒儿3","77777"));
userList.add(new UserBean("小米儿4","88888"));
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int nums = mapper.addUsers(userList);
if (nums>0){
logger.info("批量添加成功===========");
} else {
logger.info("批量添加失败-----------");
}
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
10.2、批量删除
10.2.1、接口
// 批量删除
int delUsers(@Param("idList") List<Integer> list);
// 批量删除
int delUsersMap(Map<String,Object> map);
10.2.2、Mapper
<!--批量删除-->
<delete id="delUsers" parameterType="list">
delete from tb_user where tb_id in
<foreach collection="idList" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
<!--批量删除 2-->
<delete id="delUsersMap" parameterType="hashmap">
delete from tb_user where tb_id in
<foreach collection="uid" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
10.2.3、测试
@Test
public void delUsers(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<Integer> list = new ArrayList<>();
list.add(16);
list.add(13);
list.add(8);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int nums = mapper.delUsers(list);
System.out.println(nums);
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
@Test
public void delUsersMap(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<Integer> list = new ArrayList<>();
list.add(25);
list.add(26);
list.add(27);
Map<String,Object> map = new HashMap<String,Object>();
map.put("uid",list);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.delUsersMap(map);
System.out.println(i);
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
10.3、批量修改
10.3.1、接口
// 批量修改 两个id 修改1个值
int updateUserByMapOne(Map<String,Object> map);
// 批量修改 两个id 修改两个值
int updateUserByMapTwo(Map<String,Object> map);
// 简化批量修改 两个id 修改两个值
int updateUserByMapThree(Map<String,Object> map);
10.3.2、Mapper
<!--两个id修改一个值-->
<update id="updateUserByMapOne" parameterType="map">
update tb_user set tb_name = #{name} where tb_id in
<foreach collection="uidList" item="uid" separator="," open="(" close=")">
#{uid}
</foreach>
</update>
<!--批量修改 两个id修改两个值-->
<update id="updateUserByMapTwo" parameterType="hashmap">
update tb_user set tb_name = #{name},tb_pwd = #{pwd}
where tb_id in
<foreach collection="userid" item="uid" open="(" close=")" separator=",">
#{uid}
</foreach>
</update>
<!--简化修改-->
<update id="updateUserByMapThree" parameterType="hashmap">
update tb_user
<set>
<if test="name != null">
tb_name = #{name},
</if>
<if test="pwd != null">
tb_pwd = #{pwd}
</if>
</set>
where tb_id in
<foreach collection="userid" item="uid" open="(" close=")" separator=",">
#{uid}
</foreach>
</update>
10.3.3、测试
// 根据两个id修改一个值
@Test
public void updateUserByMap(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<Integer> list = new ArrayList<>();
Map<String,Object> map = new HashMap<String,Object>();
list.add(28);
list.add(29);
map.put("uidList",list);
map.put("name","麦穗儿");
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUserByMapOne(map);
System.out.println(i);
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
// 两个id修改两个值
@Test
public void updateUserByMapTwo(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<Integer> list = new ArrayList<>();
Map<String,Object> map = new HashMap<String,Object>();
list.add(28);
list.add(29);
map.put("userid",list);
map.put("name","麦穗儿2");
map.put("pwd","22222");
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUserByMapTwo(map);
System.out.println(i);
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
// 简化批量修改
@Test
public void updateUserByMapThree(){
SqlSession sqlSession = MapperUtils.getSqlSession();
List<Integer> list = new ArrayList<>();
Map<String,Object> map = new HashMap<String,Object>();
for (int i = 31; i <=33 ; i++) {
list.add(i);
}
map.put("userid",list);
map.put("name","麦穗儿");
map.put("pwd","Three");
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUserByMapThree(map);
System.out.println(i);
// 增删改需要commit() 提交事务
sqlSession.commit();
sqlSession.close();
}
浙公网安备 33010602011771号