目标
1.mybatis动态sql
2.模糊查询
3.查询返回结果集的处理
4.分页查询
5.特殊字符处理
一、mybatis动态sql
常用:If、foreach
if标签解释:
foreach:
1、BookMapper.xml增加foreach标签
<select id="selectBooksIn" resultType="com.mwy.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>
2、dao层调用
BookMapper.java
有且只有一个变量,指名
List<Book> selectBooksIn(@Param("bookIds") List bookIds);
3、做测试
①、BookService.java
List<Book> selectBooksIn(List bookIds);
②、实现接口方法(BookServiceImpl.java)
public List<Book> selectBooksIn(List bookIds) {
return bookMapper.selectBooksIn(bookIds);
}
③、测试类测试(BookServiceImplTest.java)
二、模糊查询
(是一道面试题)
#{...} 、 ${...} 、 Concat 三个都可以实现模糊查询
注意:#{...}自带引号,${...}不带引号,有sql注入的风险
在不考虑做通用的业务功能增删改查的时候选用Concat;
做通用的业务功能增删改查的时候选用${...};
除了做模糊查询不用#{...},其他都用#{...};
#与$符号的区别:
1、BookMapper.xml增加三种写法
#{...}:%号需要在后台拼接;
<select id="selectBooksLike1" resultType="com.lsy.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like #{bname} </select> <select id="selectBooksLike2" resultType="com.lsy.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like '${bname}' </select> <select id="selectBooksLike3" resultType="com.lsy.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%') </select>
2、dao层调用
BookMapper.java
List<Book> selectBooksLike1(@Param("bname") String bname);
List<Book> selectBooksLike2(@Param("bname") String bname);
List<Book> selectBooksLike3(@Param("bname") String bname);
3、做测试
①、BookService.java
@Override
public List<Book> selectBooksLike1(String bname) {
return bookMapper.selectBooksLike1(bname);
}
@Override
public List<Book> selectBooksLike2(String bname) {
return bookMapper.selectBooksLike2(bname);
}
@Override
public List<Book> selectBooksLike3(String bname) {
return bookMapper.selectBooksLike3(bname);
}
③、测试类测试(BookServiceImplTest.java)
@Test public void testSelectBooksLike1() { System.out.println("mybatis中模糊查询#与$的区别....."); List<Book> books=this.bookService.selectBooksLike1("%圣墟%"); books.forEach(System.out::println); } @Test public void testSelectBooksLike2() { System.out.println("mybatis中模糊查询#与$的区别....."); List<Book> books=this.bookService.selectBooksLike2("%圣墟%"); books.forEach(System.out::println); } @Test public void testSelectBooksLike3() { System.out.println("mybatis中模糊查询#与$的区别....."); List<Book> books=this.bookService.selectBooksLike3("圣墟");