目录
一、mybatis动态SQL
1.if
使用if的动态SQL语句
<insert id="insertSelective" parameterType="com.sjy.model.Book" >
insert into t_mvc_book
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="bid != null" >
bid,
</if>
<if test="bname != null" >
bname,
</if>
<if test="price != null" >
price,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="bid != null" >
#{bid,jdbcType=INTEGER},
</if>
<if test="bname != null" >
#{bname,jdbcType=VARCHAR},
</if>
<if test="price != null" >
#{price,jdbcType=REAL},
</if>
</trim>
</insert>
当接收参数时,进行if判断,判断每个列是否为空,如果为空就不增加该列段,否则就增加,
当我们进行修改时,如果只需要修改一个字段,就不要把其他字段传进来了,之前的方式是必须把其他字段传进来,否则修改之后没有传进来的参数就变为空了
2.foreach
<select id="selectBooksIn" resultType="com.javaxl.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>
collection:集合的变量(相当于遍历里面的items) open 自动补充的前缀 close 自动补充的后缀 separator 分隔符 item 集合变量当前元素的引用(相当于遍历里面的var)
bookService
List<Book> selectBooksIn(List bookIds);
bookMapper
List<Book> selectBooksIn(@Param("bookIds")List bookIds);
bookServiceImpl
@Override public List<Book> selectBooksIn(List bookIds) { return bookMapper.selectBooksIn(bookIds); }
bookServiceImplTest
@Test
public void testselectBooksIn() {
System.out.println("foreach标签");
List<Book> list = this.bookService.selectBooksIn(Arrays.asList(new Integer[]{67, 68, 69}));
list.forEach(System.out::println);
}
结果
二、模糊查询
<select id="selectBooksLike1" resultType="com.javaxl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like #{bname}
</select>
<select id="selectBooksLike2" resultType="com.javaxl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like '${bname}'
</select>
<select id="selectBooksLike3" resultType="com.javaxl.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>
#{...}:自带引号,模糊查询时%要在后台自己拼接
${...}:有SQL注入的风险,模糊查询时%要在后台自己拼接
concat:通过concat函数自动拼接%
bookService和bookMapper一样
List<Book> selectBooksLike1(@Param("bname") String bname); List<Book> selectBooksLike2(@Param("bname") String bname); List<Book> selectBooksLike3(@Param("bname") String bname);
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); }
bookServiceImplTest
@Test
public void testselectBooksLike1() {
List<Book> list = this.bookService.selectBooksLike1("%圣%");
list.forEach(System.out::println);
}
@Test
public void testselectBooksLike2() {
List<Book> list = this.bookService.selectBooksLike2("%圣%");
list.forEach(System.out::println);
}@Test
public void testselectBooksLike3() {
List<Book> list = this.bookService.selectBooksLike3("圣");
list.forEach(System.out::println);
}
结果
三、查询返回结果集的处理
resultMap:适合使用返回值是自定义实体类的情况,适合多表
resultType:适合使用返回值的数据类型是非自定义的,即jdk的提供的类型,适合单表
bookService和bookMapper一致
/**
* 使用resultMap返回自定义类型集合
* @return
*/
List<Book> list1();
/**
* 使用resultType返回List<T>
* @return
*/
List<Book> list2();
/**
* 使用resultType返回单个对象
* @return
*/
Book list3(BookVo bookVo);
/**
* 使用resultType返回List<Map>,适用于多表查询返回结果集
* @return
*/
List<Map> list4();
/**
* 使用resultType返回Map<String,Object>,适用于多表查询返回单个结果集
* @return
*/
Map list5(Map book);
bookMapper.xml
<select id="list1" resultMap="BaseResultMap">
select * from t_mvc_book
</select>
<select id="list2" resultType="com.sjy.model.Book">
select * from t_mvc_book
</select>
<select id="list3" resultType="com.sjy.model.Book" parameterType="com.sjy.vo.BookVo">
select * from t_mvc_book where bid in
<foreach collection="bookIds" open="(" close=")" separator="," item="bid">
#{bid}
</foreach>
</select>
<select id="list4" resultType="java.util.Map">
select * from t_mvc_book
</select>
<select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
select * from t_mvc_book where bid = #{bid}
</select>
BookVo
package com.sjy.vo;
import com.sjy.model.Book;
import java.util.List;
public class BookVo extends Book {
private List<Integer> bookIds;
public List<Integer> getBookIds() {
return bookIds;
}
public void setBookIds(List<Integer> bookIds) {
this.bookIds = bookIds;
}
}
bookServiceImpl
@Override
public List<Book> list1() {
return bookMapper.list1();
}
@Override
public List<Book> list2() {
return bookMapper.list2();
}
@Override
public Book list3(BookVo bookVo) {
return bookMapper.list3(bookVo);
}
@Override
public List<Map> list4() {
return bookMapper.list4();
}
@Override
public Map list5(Map book) {
return bookMapper.list5(book);
}
list3如果传多个参数会报以下错误,因为他返回的是list类型,但是方法定义中是Book类型
只传一个id结果
bookServiceImplTest
@Test
public void testlist1() {
List<Book> list = this.bookService.list1();
list.forEach(System.out::println);
} @Test
public void testlist2() {
List<Book> list = this.bookService.list2();
list.forEach(System.out::println);
}
@Test
public void testlist3() {
BookVo bookVo=new BookVo();
bookVo.setBookIds(Arrays.asList(new Integer[]{64}));
System.out.println(this.bookService.list3(bookVo));
}
@Test
public void testlist4() {
List<Map> maps = this.bookService.list4();
maps.forEach(System.out::println);
}
@Test
public void testlist5() {
Map map=new HashMap();
map.put("bid",65);
System.out.println(this.bookService.list5(map));
}
list5结果
其他的结果一致
结论:多表使用resultMap时,需要在BookMapper.xml中将表的列段进行配置
<resultMap id="BaseResultMap" type="com.sjy.model.Book" >
<constructor >
<idArg column="bid" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="bname" jdbcType="VARCHAR" javaType="java.lang.String" />
<arg column="price" jdbcType="REAL" javaType="java.lang.Float" />
</constructor>
</resultMap>
多表查询也可用List<Map>,这种方式不需要配置列段,但时间久了可能会忘记
四、分页查询
1.为什么要重写mybatis的分页?
Mybatis的分页功能很弱,它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果),在大数据量的情况下这样的分页基本上是没有用的
mybatis是借用了拦截器引入第三方完成分页的
2.使用分页插件步骤
导入pom依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
Mybatis.cfg.xml配置拦截器
<plugins>
<!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
</plugin>
</plugins>
必须配置到environments标签之前,否则会报错
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>
BookMapper
List<Map> listPager(Map map);
BookService
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.getPageNum());
System.out.println("页大小:"+pageInfo.getPageSize());
System.out.println("总记录:"+pageInfo.getTotal());
pageBean.setTotal(pageInfo.getTotal()+"");
}
return list;
}
测试
@Test
public void testlistPager() {
Map map=new HashMap();
map.put("bname","圣");
List<Map> maps = this.bookService.listPager(map,new PageBean());
maps.forEach(System.out::println);
}
结果
五、特殊字符处理
>(>) <(<) &(&) 空格(&) <![CDATA[ <= ]]>
sql语句映射
<select id="list6" resultType="com.javaxl.model.Book" parameterType="com.javaxl.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.javaxl.model.Book" parameterType="com.javaxl.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>
BookMapper 和 BookService 一致
/**
* 处理特殊字符
* @param bookVo
* @return
*/
List<Book> list6(BookVo bookVo);
/**
* 处理特殊字符
* @param bookVo
* @return
*/
List<Book> list7(BookVo bookVo);
BookVo增加min和max字段并实现get、set方法
BookServiceImpl
@Override
public List<Book> list6(BookVo bookVo) {
return bookMapper.list6(bookVo);
}
@Override
public List<Book> list7(BookVo bookVo) {
return bookMapper.list7(bookVo);
}
测试
@Test
public void testlist6() {
BookVo bookVo=new BookVo();
bookVo.setMin(66f);
bookVo.setMax(70f);
List<Book> books = this.bookService.list6(bookVo);
books.forEach(System.out::println);
}
@Test
public void testlist7() {
BookVo bookVo=new BookVo();
bookVo.setMin(66f);
bookVo.setMax(70f);
List<Book> books = this.bookService.list7(bookVo);
books.forEach(System.out::println);
}
结果