一、动态Sql
在BookMapper.xml新加:
<!-- collection:集合的变量
open:自动补充的前缀
close:自动补充的后缀
separator:分隔符
item:集合变量当前元素的引用
-->
<select id="selectBooksIn" resultType="com.dzl.model.Book" parameterType="java.util.List">
select * from t_mvc_book where bid in
<foreach collection="bookIds" open="(" close=")" separator="," item="bid">
#{bid}
</foreach>
</select>
BookMapper:
List<Book> selectBooksIn(@Param("bookIds") List bookIds);
BookService:
List<Book> selectBooksIn(List bookIds);
BookServiceImpl:
@Override
public List<Book> selectBooksIn(List bookIds) {
return bookMapper.selectBooksIn(bookIds) ;
}
BookServiceImplTest:测试
@Test
public void selectBooksIn() {
System.out.println("MyBatis中的foreach标签。。。。");
List<Book> books = this.bookService.selectBooksIn(Arrays.asList(new Integer[]{22, 24, 28, 29}));
books.forEach(System.out::println);
}
二、模糊查询
BookMapper.xml:
<!-- 模糊查询
#{...}
${...}
Concat
注意:#{...}自带引号,${...}有sql注入的风险-->
<select id="selectBooksLike1" resultType="com.dzl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like #{bname}
</select>
<select id="selectBooksLike2" resultType="com.dzl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like '${bname}'
</select>
<select id="selectBooksLike3" resultType="com.dzl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>
BookMapper:
List<Book> selectBooksLike1(@Param("bname") String bname);
List<Book> selectBooksLike2(@Param("bname") String bname);
List<Book> selectBooksLike3(@Param("bname") String bname);
BookServise:
List<Book> selectBooksLike1(@Param("bname") String bname);
List<Book> selectBooksLike2(@Param("bname") String bname);
List<Book> selectBooksLike3(@Param("bname") String bname);
BookServiceimpl:
@Override
public List<Book> selectBookLike1(String bname) {
return bookMapper.selectBooksLike1(bname) ;
}
@Override
public List<Book> selectBookLike1(String bname) {
return bookMapper.selectBooksLike2(bname) ;
}
@Override
public List<Book> selectBookLike1(String bname) {
return bookMapper.selectBooksLike3(bname) ;
}
BookServiceImplTest:测试
@Test
public void selectBookslike1() {
System.out.println("MyBatis中的模糊查询#与$");
List<Book> books = this.bookService.selectBooksLike1("%圣墟%");
books.forEach(System.out::println);
}
@Test
public void selectBookslike2() {
System.out.println("MyBatis中的模糊查询#与$");
List<Book> books = this.bookService.selectBooksLike2("%圣墟%");
books.forEach(System.out::println);
}
@Test
public void selectBookslike3() {
System.out.println("MyBatis中的模糊查询#与$");
List<Book> books = this.bookService.selectBooksLike3("圣墟");
books.forEach(System.out::println);