目录
一、mybatis动态sql
if判断标签
<update id="updateByPrimaryKeySelective" parameterType="com.ssr.model.Book" >
update t_mvc_book
<set >
<if test="bname != null" >
bname = #{bname,jdbcType=VARCHAR},
</if>
<if test="price != null" >
price = #{price,jdbcType=REAL},
</if>
</set>
where bid = #{bid,jdbcType=INTEGER}
</update>
foreach遍历标签
<select id="selectByIn" resultMap="BaseResultMap" parameterType="java.util.Map" >
select
<include refid="Base_Column_List" />
from t_mvc_book
where bid in
<foreach collection="bookids" open="(" close=")" separator="," item="bid">
#{bid}
</foreach>
</select>
二、模糊查询(3种方式)
<select id="selectBooksLike1" resultType="com.ssr.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like #{bname}
</select>
<select id="selectBooksLike2" resultType="com.ssr.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like '${bname}'
</select>
<select id="selectBooksLike3" resultType="com.ssr.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like concat('%',#{bname},'%')
</select>
注:
1) mybatis中使用OGNL表达式传递参数
2) 优先使用#{...}
3) ${...}方式存在SQL注入风险
三、查询返回结果集
resultMap:适合使用返回值是自定义实体类的情况
resultMap="BaseResultMap"
resultType:适合使用返回值的数据类型是非自定义的,即jdk的提供的类型
resultType="com.ssr.model.Book"
案列:
BookVo
package com.ssr.model;
import java.util.List;
/**
* @author ssr
* @site 2261696885
* @company 四金
* @create 2022--08--11--19:11
*/
public class BookVo extends Book{
private List bookids;
public List getBookids() {
return bookids;
}
public void setBookids(List bookids) {
this.bookids = bookids;
}
}
测试类代码
@Test
public void list1() {
bookBiz.list1().forEach(System.out::println);
}
@Test
public void list2() {
bookBiz.list2().forEach(System.out::println);
}
@Test
public void list3() {
BookVo vo=new BookVo();
vo.setBookids(Arrays.asList(new Integer[]{31,32,33,34}));
bookBiz.list3(vo).forEach(System.out::println);
}
@Test
public void list4() {
bookBiz.list4().forEach(System.out::println);
}
@Test
public void list5() {
Map map=new HashMap();
map.put("bid",32);
System.out.println(bookBiz.list5(map));
}
使用resultMap返回自定义类型集合 (对应list1方法)
<select id="list1" resultMap="BaseResultMap">
select * from t_mvc_book
</select>
四、分页查询
为什么要重写mybatis的分页?
Mybatis的分页功能很弱,它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果),在大数据量的情况下这样的分页基本上是没有用的
导入分页插件 pom.xml
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
将pagehelper插件配置到mybatis中 mybatis.cfg.xml
<!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
</plugin>
BookMapper.xml
<select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
select * from t_mvc_book where bname like concat(concat('%',#{bname},'%'))
</select>
BookBiz
List<Map> listPager(Map map, PageBean PageBean);
BookVo
package com.ssr.model;
import java.util.List;
/**
* @author ssr
* @site 2261696885
* @company 四金
* @create 2022--08--11--19:36
*/
public class BookVo extends Book{
private List bookids;
private int min;
private int max;
public int getMin() {
return min;
}
public void setMin(int min) {
this.min = min;
}
public int getMax() {
return max;
}
public void setMax(int max) {
this.max = max;
}
public List getBookids() {
return bookids;
}
public void setBookids(List bookids) {
this.bookids = bookids;
}
}
BookBizImpl
@Override
public List<Map> listPager(Map map, PageBean PageBean) {
// PageBean分页插件相关的代码
// 判断是否要分页
if(PageBean != null && PageBean.isPagination()){
PageHelper.startPage(PageBean.getPage(),PageBean.getRows());
}
//分页结果
List<Map> maps=bookMapper.listPager(map);
if(PageBean != null && PageBean.isPagination()){
//处理结果的前提是需要分页
PageInfo info=new PageInfo(maps);
PageBean.setTotal(info.getTotal()+"");
}
return maps;
}
BookBizImplTest
@Test
public void listPager() {
Map map=new HashMap();
map.put("bname","圣墟");
PageBean pageBean=new PageBean();
pageBean.setPage(2);
pageBean.setRows(20);
bookBiz.listPager(map,pageBean).forEach(System.out::println);
}
五、特殊字符处理
<![CDATA[ and #{min} < price ]]>
CDATA里正常放符号都后转为sql里能用的符号
< 代表 <
> 代表 >
案例:
BookMapper.xml
<select id="list6" resultType="com.ssr.model.Book" parameterType="com.ssr.model.BookVo">
select * from t_mvc_book
<where>
<if test="null != min and min != ''">
<![CDATA[ and #{min} < price ]]>
</if>
<if test="null != max and max != ''">
<![CDATA[ and #{max} > price ]]>
</if>
</where>
</select>
<select id="list7" resultType="com.ssr.model.Book" parameterType="com.ssr.model.BookVo">
select * from t_mvc_book
<where>
<if test="null != min and min != ''">
and #{min} < price
</if>
<if test="null != max and max != ''">
and #{max} > price
</if>
</where>
</select>
BookBiz
List<Book> list6(BookVo vo);
List<Book> list7(BookVo vo);
BookBizImpl
@Override
public List<Book> list6(BookVo vo) {
return bookMapper.list6(vo);
}
@Override
public List<Book> list7(BookVo vo) {
return bookMapper.list7(vo);
}
BookBizImplTest
@Test
public void list6() {
BookVo vo=new BookVo();
vo.setMin(10);
vo.setMax(40);
bookBiz.list6(vo).forEach(System.out::println);
}
@Test
public void list7() {
BookVo vo=new BookVo();
vo.setMin(10);
vo.setMax(40);
bookBiz.list7(vo).forEach(System.out::println);
}