动态sql和分页
Mybatis动态SQL
If、trim、foreach
BookMapper
/** * 如果形参要在mapper.xml中使用需要加上面注解 * map.name: zs age: 12 * @param bookIds * @return */ List<Book> selectBooksIn(@Param("bookIds") List bookIds);
BookService
1 List<Book> selectBooksIn(List bookIds);
BookServiceImpl
@Override public List<Book> selectBooksIn(List bookIds) { return bookMapper.selectBooksIn(bookIds); }
Bookmapper.xml
<select id="selectBooksIn" resultType="com.Mybatis.model.Book" parameterType="java.util.List"> select * from t_mvc_book where bid in <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> #{bid} </foreach> </select>
结果:
模糊查询
Mybatis对模糊查询共有三种方式
{}${}concat
#与$的区别$会引起sql攻击 Java代码差别(#):%圣墟% select * from t_mvc_book where bname like #{bname} select * from t_mvc_book where bname like %圣墟% select * from t_mvc_book where bname like ‘${bname}‘ 人为加的引导 %圣墟%正常传值 比如:‘%圣墟% or 1=1‘ select * from t_mvc_book where bname like ‘‘%圣墟% or 1=1‘‘BookMapper
/** *Mybatis对模糊查询共有三种方式 * #{} * ${} * concat * @param bname * @return */ List<Book> selectBooksLike1(@Param("bname")String bname); List<Book> selectBooksLike2(@Param("bname")String bname); List<Book> selectBooksLike3(@Param("bname")String bname);
BookService
/** *Mybatis对模糊查询共有三种方式 * #{} * ${} * concat * @param bname * @return */ List<Book> selectBooksLike1(String bname); List<Book> selectBooksLike2(String bname); List<Book> selectBooksLike3(String bname);
Bookmapper.xml
<select id="selectBooksLike1" resultType="com.Mybatis.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like #{bname} </select> <select id="selectBooksLike2" resultType="com.Mybatis.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like ‘${bname}‘ </select> <select id="selectBooksLike3" resultType="com.Mybatis.model.Book" parameterType="java.lang.String"> select * from t_mvc_book where bname like concat(concat(‘%‘,#{bname}),‘%‘) </select>
BookServiceImpl
@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); }
测试:
Mybatis结果集处理的五种情况
resultMap:适合使用返回值是自定义实体类的情况
resultType:适合使用返回值的数据类型是非自定义的,即jdk的提供的类型
使用resultMap返回自定义类型集合
使用resultType返回List<T>
使用resultType返回单个对象
使用resultType返回List<Map>,适用于多表查询返回结果集
使用resultType返回Map<String,Object>,适用于多表查询返回单个结果集
BookMapper
List<Book> list1(); List<Book> list2(); List<Book> list3(BookVo bookVo); List<Map> list4(Map map); Map list5(Map map);
Bookmapper.xml
<select id="list1" resultMap="BaseResultMap"> select * from t_mvc_book </select> <select id="list2" resultMap="BaseResultMap"> select * from t_mvc_book </select> <select id="list3" resultType="com.Mybatis.model.Book" parameterType="com.Mybatis.model.vo.BookVo"> select * from t_mvc_book where bid in <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> #{bid} </foreach> </select> <select id="list4" resultType="java.util.Map" parameterType="java.util.Map"> select * from t_mvc_book where bid in <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> #{bid} </foreach> </select> <select id="list5" resultType="java.util.Map" parameterType="java.util.Map"> select * from t_mvc_book where bid = #{bid} </select>
BookService
List<Book> list1(); List<Book> list2(); List<Book> list3(BookVo bookVo); List<Map> list4(Map map); Map list5(Map map);
BookServiceImpl
@Override public List<Book> list2() { return bookMapper.list2(); } @Override public List<Book> list3(BookVo bookVo) { return bookMapper.list3(bookVo); } @Override public List<Map> list4(Map map) { return bookMapper.list4(map); } @Override public Map list5(Map map) { return bookMapper.list5(map); }
测试:
分页查询
为什么要重写mybatis的分页?
Mybatis的分页功能很弱,它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果),在大数据量的情况下这样的分页基本上是没有用的
1、导入pom依赖
<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.2</version> </dependency>
Mybatis.cfg.xml配置拦截器
BookMapper
/** * 分页 * @param map * @return */ List<Map> listPager(Map map);
Bookmapper.xml
<select id="listPager" resultType="java.util.Map" parameterType="java.util.Map"> select * from t_mvc_book where bname like #{bname} </select>
BookService
/** * 分页 * @param map * @return */ List<Map> listPager(Map map, PageBean pageBean);
BookServiceImpl
@Override public List<Map> listPager(Map map, PageBean pageBean) { if (pageBean != null && pageBean.isPagination()){ PageHelper.startPage(pageBean.getPage(),pageBean.getRows()); } List<Map> list = bookMapper.listPager(map); if (pageBean != null && pageBean.isPagination()){ PageInfo pageInfo = new PageInfo(list); System.out.println("总记录数:"+pageInfo.getTotal()); System.out.println("当前页:"+pageInfo.getPageNum()); System.out.println("页大小:"+pageInfo.getPageSize()); pageBean.setTotal(pageInfo.getTotal()+""); System.out.println("总页数:"+pageBean.getMaxPage()); } return list; }
测试:
特殊字符处理
>(>)
<(<)
&(&)
空格( )
<![CDATA[ <= ]]>
BookMapper
/** * 特殊字符处理 * @param bookVo * @return */ List<Book> list6(BookVo bookVo);
Bookmapper.xml
1 <select id="list6" resultType="com.Mybatis.model.Book" parameterType="com.Mybatis.model.vo.BookVo"> 2 select * from t_mvc_book where <![CDATA[ price > #{min} and price < #{max} ]]> 3 </select>
BookService
/** * 特殊字符处理 * @param bookVo * @return */ List<Book> list6(BookVo bookVo);
BookServiceImpl
@Override public List<Book> list6(BookVo bookVo) { return bookMapper.list6(bookVo); }
测试:
相关推荐
chenjiazhu 2020-07-08
xiuyangsong 2020-05-31
dingchaochao0 2020-03-11
liqiancao 2020-05-08
② Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相同。④ Mapper.xml文件中的namespace即是mapper接口的类路径。
cuterabbitbaby 2020-05-08
技术驱动人生 2020-05-06
kevincheung 2020-04-23
happinessaflower 2020-04-18
cnflat0 2020-04-17
kevincheung 2020-04-10
cuterabbitbaby 2020-03-08
GechangLiu 2020-02-02
dongxurr 2020-01-19
dongxurr 2019-12-31
cuterabbitbaby 2020-01-06
javamagicsun 2020-01-04
zhiyuan0 2020-01-04