一. 使用动态SQL进行批量删除
1. 首先给出对应的接口:
public interface BookMapper { int doDeleteBatch(Integer[] bookIds); }
2. 配置mybatis-config.xml,配置内容可以参照一《Mybatis:增删改查》一文中的第四节。
3. 接下编写对应的Mapper.xml,其中:
-
collection:内容为我们要遍历的数据类型,数组:array ,List:list,Set:set
-
item:遍历的集合中的每一个对象
-
open:循环开始之前输出的内容
-
close:循环结束之后输出的内容
-
separator:循环的分隔符
<?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="io.zhangjia.mapper.BookMapper"> <delete id="doDeleteBatch" parameterType="int"> DELETE FROM BOOK WHERE BOOK_ID IN <foreach item="bookId" collection="array" open="(" separator="," close=")"> #{bookId} </foreach> </delete> </mapper>
值得注意的是,forache中的#{bookId}需要和item中的值名称相同,否则会报错。
3. 接下来编写测试类即可:
package io.zhangjia.util; import io.zhangjia.mapper.BookMapper; import io.zhangjia.entity.Book; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.List; public class Main { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession(true); BookMapper mapper = sqlSession.getMapper(BookMapper.class); Integer[] bookIds = {1,2,3,27}; int i = mapper.doDeleteBatch(bookIds); sqlSession.close(); inputStream.close(); } }
二. 使用动态SQL进行批量添加
1. 首先给出对应的接口:
public interface BookMapper { int doInsertBatch(List<Book> books); }
2. 配置mybatis-config.xml,配置内容可以参照一《Mybatis:增删改查》一文中的第四节。
3. 接下编写对应的Mapper.xml,其中:
<?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="io.zhangjia.mapper.BookMapper"> <insert id="doInsertBatch" parameterType="book" > INSERT INTO BOOK( NAME, AUTHOR, PRICE) VALUE <foreach item="book" collection="list" separator=","> (#{book.name}, #{book.author}, #{book.price}) </foreach> </insert> </mapper>
值得注意的是,forache中的#{bookId}需要和item中的值名称相同,否则会报错。
3. 接下来编写测试类即可:
package io.zhangjia.util; import io.zhangjia.entity.Book; import io.zhangjia.mapper.BookMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class Main { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession(true); BookMapper mapper = sqlSession.getMapper(BookMapper.class); List<Book> books = new ArrayList<>(); for (int i = 0; i < 5; i++) { Book book = new Book("书名"+i,"张甲"+i,i*10d); books.add(book); } int i = mapper.doInsertBatch(books); System.out.println("成功插入 " + i + "条"); sqlSession.close(); inputStream.close(); } }
最后执行的sql语句为:
INSERT INTO BOOK( NAME, AUTHOR, PRICE) VALUE (?, ?, ?) , (?, ?, ?) , (?, ?, ?) , (?, ?, ?) , (?, ?, ?)
三. 使用动态SQL进行任意条件查询
1. 首先说一下根据任意查询要实现的功能:
-
可以根据Book类的任意字段查询数据
-
比如给出作者,则可以查询所有同一个作者的所有书籍
-
比如给出作者,书名,则可以查询作者和书名为指定内容的书籍
接下来给出对应的接口:
public interface BookMapper { List<Book> query(Book book); }
2. 配置mybatis-config.xml,配置内容可以参照一《Mybatis:增删改查》一文中的第四节。
3. 接下编写对应的Mapper.xml,一共有两种实现方法,先来看第一种:
<?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="io.zhangjia.mapper.BookMapper"> <!--方法一--> <select id="query" parameterType="book" resultType="book"> SELECT * FROM BOOK WHERE 1 = 1 <if test="bookId != null"> AND BOOK_ID = #{bookId} </if> <if test="name != null"> AND NAME LIKE CONCAT('%',#{name},'%') </if> <if test="author != null"> AND AUTHOR = #{author} </if> <if test="price != null"> AND PRICE = #{price} </if> </select> </mapper>
其中 WHERE 1 = 1 ,是为了避免因AND而引发的错误,假如这里我们没有写1 = 1,而是直接WHERE后面就跟了下面的if语句,那么无论哪个if语句被执行,最后的SQL语句都是:
SELECT * FROM BOOk WHERE AND......
多出来的AND便会引发异常,所以为了解决这个问题,我们可以在WHERE后面加入一个恒等式1 = 1,这样
无论哪个if语句被执行,最后的SQL语句都是:
SELECT * FROM BOOk WHERE 1 = 1 AND......
问题便得到了解决。这是第一种解决方案,Mybatis也考虑到了这个问题,所以我们可以使用Mybatis提供第二种解决方案,添加where 元素:
<?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="io.zhangjia.mapper.BookMapper"> <!--方法二--> <select id="query" parameterType="book" resultType="book"> SELECT * FROM BOOk <where> <if test="bookId != null"> AND BOOK_ID = #{bookId} </if> <if test="name != null"> AND NAME LIKE CONCAT('%',#{name},'%') </if> <if test="author != null"> AND AUTHOR = #{author} </if> <if test="price != null"> AND PRICE = #{price} </if> </where> </select> </mapper>
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
3. 接下来编写测试类即可:
package io.zhangjia.util; import io.zhangjia.entity.Book; import io.zhangjia.mapper.BookMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class Main { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession(true); BookMapper mapper = sqlSession.getMapper(BookMapper.class); Book book = new Book(); book.setName("书名0"); book.setAuthor("张甲0"); List<Book> books = mapper.query(book); for (Book b: books) { System.out.println("b = " + b); } sqlSession.close(); inputStream.close(); } }
上面的测试类执行的sql语句为:
SELECT * FROM BOOk WHERE NAME LIKE CONCAT('%',?,'%') AND AUTHOR = ?
四. 使用动态SQL进行任意值更新
1. 首先说一下根据任意值更新要实现的功能:
-
比如传进来一个book对象,如果book对象只有作者,则只更新作者
-
如果值只有书名,则只更新书名,其他的值均为原值,不修改
接下来给出对应的接口:
public interface BookMapper { int doUpdate(Book book); }
2. 配置mybatis-config.xml,配置内容可以参照一《Mybatis:增删改查》一文中的第四节。
3. 接下编写对应的Mapper.xml,其中:
<?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="io.zhangjia.mapper.BookMapper"> <update id="doUpdate" parameterType="book"> UPDATE BOOK <set> <if test="name != null"> NAME = #{name}, </if> <if test="author != null"> AUTHOR = #{author}, </if> <if test="price != null"> PRICE = #{price} </if> </set> WHERE BOOK_ID = #{bookId} </update> </mapper>
注意,不要遗漏</if>前面的逗号(最后一个if不用)。类似的用于动态更新语句的解决方案叫做 set。set 元素可以用于动态包含需要更新的列,而舍去其它的,set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号
3. 接下来编写测试类即可:
package io.zhangjia.util; import io.zhangjia.entity.Book; import io.zhangjia.mapper.BookMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class Main { public static void main(String[] args) throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = build.openSession(true); BookMapper mapper = sqlSession.getMapper(BookMapper.class); Book book = new Book(); book.setName("书名1"); book.setBookId(43); mapper.doUpdate(book); sqlSession.close(); inputStream.close(); } }
请登录之后再进行评论