课程目标
- mybatis动态sql
- 模糊查询
- 查询返回结果集的处理
- 分页查询
- 特殊字符处理
一.mybatis动态sql
首先在BookMapper.xml中添加forEach的标签
<!-- collection:集合的变量 open:自动补充的前缀 close:自动补充的后缀 separator分隔符 item:集合变量当前元素的引用 --> <select id="selectBooksIn" resultType="com.zking.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.java
List<Book> selectBooksIn(@Param("bookIds") List bookIds);
BookService.java
List<Book> selectBooksIn(List bookIds);
BookServiceImpl.java
@Override public List<Book> selectBooksIn(List bookIds) { return bookMapper.selectBooksIn(bookIds); }
BookServiceImplTest.java
查询数据库中id为29,30,31的数据
代码如下:
@Test public void selectBooksIn() { System.out.println("mybatis中的foreach标签..."); List<Book> books =this.bookService.selectBooksIn(Arrays.asList(new Integer[]{29,30,31})); books.forEach(System.out::println); }
查询结果:
二.模糊查询
#{...}
${...}
Concat
注意:#{...}自带引号,${...}有sql注入的风险
BookMapper.xml
<select id="selectBooksLike1" resultType="com.zking.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like #{bname} </select> <select id="selectBooksLike2" resultType="com.zking.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like '${bname}' </select> <select id="selectBooksLike3" resultType="com.zking.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%') </select>
BookMapper.java
List<Book> selectBooksLike1(@Param("bname") String bname); List<Book> selectBooksLike2(@Param("bname") String bname); List<Book> selectBooksLike3(@Param("bname") String bname);
BookService.java
List<Book> selectBooksLike1(@Param("bname") String bname); List<Book> selectBooksLike2(@Param("bname") String bname); List<Book> selectBooksLike3(@Param("bname") String bname);
BookServiceImpl.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 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); }
查询结果: