Mybatis 的动态SQL，批量增删查改
个人博客网：https://wushaopei.github.io/ (你想要这里多有)
批量增删改的接口：
public interface BookService {
//批量增加
int saveList(List<Book> records);
//批量查找
List<Book> selectList(List<Integer> ids);
//批量删除
int deleteList(List<Integer> ids);
//批量修改
int updateList(List<Book> bookList);
}
接口实现类：
@Service
public class BookServiceImpl implements BookService{
@Autowired
BookMapper bookMapper;
@Override
public int saveList(List<Book> list) {
// TODO Auto-generated method stub
int count = bookMapper.inserts(list);
return count;
}
@Override
public List<Book> selectList(List<Integer> ids) {
// TODO Auto-generated method stub
List<Book> books = bookMapper.selectByIds(ids);
return books;
}
@Override
public int deleteList(List<Integer> ids) {
// TODO Auto-generated method stub
return bookMapper.deleteByPrimaryKeys(ids);
}
@Override
public int updateList(List<Book> bookList) {
// TODO Auto-generated method stub
return bookMapper.updateByPrimaryKeys(bookList);
}
}
对应的实体类 JavaBean ：
public class Book {
public Book(Integer id, String name, String author, BigDecimal price, Integer sales, Integer stock) {
super();
this.id = id;
this.name = name;
this.author = author;
this.price = price;
this.sales = sales;
this.stock = stock;
}
............省略mapper.xml 中SQL 语句的编写：
<!-- 批量根据id进行删除 -->
<delete id="deleteByPrimaryKeys" parameterType="java.util.List" >
delete from t_book
where id in
<foreach collection="list" item="id" open="(" close=")" separator="," >
#{id,jdbcType=INTEGER}
</foreach>
</delete>
<!-- 批量进行插入数据 -->
<insert id="inserts" parameterType="java.util.List">
insert into t_book (id,name,author,price,sales,stock) values
<foreach collection="list" item="Book" separator="," index="index">
(null, #{Book.name}, #{Book.author}, #{Book.price}, #{Book.sales}, #{Book.stock})
</foreach>
</insert>
<!-- 批量根据id进行修改 -->
<update id="updateByPrimaryKeys" parameterType="java.util.List" >
update t_book
<trim prefix="set" suffixOverrides=",">
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="Book" index="index">
<if test="Book.name!=null">
when id=#{Book.id} then #{Book.name}
</if>
</foreach>
</trim>
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="Book" index="index">
<if test="Book.author!=null">
when id=#{Book.id} then #{Book.author}
</if>
</foreach>
</trim>
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="Book" index="index">
<if test="Book.price!=null">
when id=#{Book.id} then #{Book.price}
</if>
</foreach>
</trim>
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="Book" index="index">
<if test="Book.sales!=null">
when id=#{Book.id} then #{Book.sales}
</if>
</foreach>
</trim>
<trim prefix="name=case" suffix="end,">
<foreach collection="list" item="Book" index="index">
<if test="Book.stock!=null">
when id=#{Book.id} then #{Book.stock}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="Book" index="index">
id=#{Book.id,jdbcType=INTEGER}
</foreach>
</update>
<!-- 批量根据id查找 -->
<select id="selectByIds" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select id, name, author, price, sales, stock
from t_book
where id in
<foreach collection="list" item="id" open="(" close=")" separator="," >
#{id,jdbcType=INTEGER}
</foreach>
</select>
测试： test 对批量操作进行测试是否成功：
/*
* 批量插入
* */
@Test
public void InsertBookServices()throws SQLException {
List<Book> bookList = new ArrayList<>();
bookList.add(new Book(null,"生活1","奕1君",new BigDecimal(1),1,1));
bookList.add(new Book(null,"生活2","奕2君",new BigDecimal(2),2,2));
bookList.add(new Book(null,"生活3","奕3君",new BigDecimal(3),3,3));
bookList.add(new Book(null,"生活4","奕4君",new BigDecimal(4),4,4));
bookList.add(new Book(null,"生活5","奕5君",new BigDecimal(5),5,5));
int count = bookService.saveList(bookList);
System.out.println(count);
}
/*
* 批量查询
* */
@Test
public void SelectBookService()throws SQLException {
List<Integer> Ids = new ArrayList();
Ids.add(1);
Ids.add(2);
Ids.add(3);
Ids.add(4);
List<Book> Books = bookService.selectList(Ids);
for(Book book : Books) {
System.out.println(book.toString());
}
}
/*
* 批量删除
* */
@Test
public void DeleteBookService()throws SQLException {
List<Integer> Ids = new ArrayList();
Ids.add(1);
Ids.add(2);
Ids.add(3);
Ids.add(4);
int counts = bookService.deleteList(Ids);
System.out.println(counts);
}
/*
* 批量更新
* */
@Test
public void UpdateBookService()throws SQLException {
List<Book> bookList = new ArrayList<>();
bookList.add(new Book(6,"生活6","奕6君",new BigDecimal(1),1,1));
// bookList.add(new Book(7,"生活7","奕7君",new BigDecimal(2),2,2));
bookList.add(new Book(8,"生活8","奕8君",new BigDecimal(3),3,3));
bookList.add(new Book(9,"生活9","奕9君",new BigDecimal(4),4,4));
bookList.add(new Book(10,"生活10","奕10君",new BigDecimal(5),5,5));
int count = bookService.updateList(bookList);
System.out.println(count);
}链接：https://pan.baidu.com/s/1oAYg5X8eeqf18dUTU1bUpA
提取码：jznv
复制这段内容后打开百度网盘手机App，操作更方便哦
