1.mybatis动态sql
1. if 页面数据有十条 点击修改 只会改其中一条
2. foreach
遍历集合,批量查询、通常用于in关键字
在BookMapper.xml增加
<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>
在 BookMapper.java且只有一个变量名
List<Book> selectBooksIn(@Param("bookIds") List bookIds);
BookService.java
List<Book> selectBooksIn(List bookIds);
BookServiceImpl
public List<Book> selectBooksIn(List bookIds) { return bookMapper.selectBooksIn(bookIds); }
BookServiceImplTest1
@Test public void selectBooksIn() { System.out.println("调用foreach方法"); List<Book> books=this.bookService.selectBooksIn(Arrays.asList(new Integer[]{23,30,89})); books.forEach(System.out::println); }
运行结果
2.模糊查询(3种方式)
2.1 参数中直接加入%%
2.2 使用${...}代替#{...}(不建议使用该方式,有SQL注入风险)
关键:#{...}与${...}区别?
参数类型为字符串,#会在前后加单引号['],$则直接插入值
注:
1) mybatis中使用OGNL表达式传递参数
2) 优先使用#{...}
3) ${...}方式存在SQL注入风险
2.3 SQL字符串拼接CONCAT
#{...}
${...}
Concat
注意:#{...}自带引号,${...}有sql注入的风险
<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(String bname); List<Book> selectBooksLike2(String bname); List<Book> selectBooksLike3(String bname);
BookService.java
List<Book> selectBooksLike1(String bname);
List<Book> selectBooksLike2(String bname);
List<Book> selectBooksLike3(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 selectBooksLike2() { System.out.println("m模糊查询selectBooksIn#与$的区别2"); List<Book> books=this.bookService.selectBooksLike2("%圣经%"); books.forEach(System.out::println); } @Test public void selectBooksLike3() { System.out.println("模糊查询selectBooksIn#与$的区别3"); List<Book> books=this.bookService.selectBooksLike3("%圣经%"); books.forEach(System.out::println); } @Test public void selectByPrimaryKey() { System.out.println(this.bookService.selectByPrimaryKey(99)); }
@Test public void selectBooksLike1() { System.out.println("m模糊查询selectBooksIn#与$的区别1"); List<Book> books=this.bookService.selectBooksLike1("%圣经%"); books.forEach(System.out::println); }
运行结果
3.查询返回结果集
resultMap:适合使用返回值是自定义实体类的情况