1、mybatis动态sql

2、模糊查询

3、查询返回结果集的处理

4、分页查询

5、特殊字符处理

If、trim、foreach

If 标签判断某一字段是否为空

select * from t_mvc_book and bname like #{bname}

trim 标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

<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=DOUBLE},  
  </if>  
</trim>

foreach 标签 遍历集合，批量查询、通常用于in关键字

select * from t_mvc_book where bid in
#{bid}

List selectByIn(@Param("bookIds")List bookIds);

测试

@Test
public void selectByIn() {
List list = new ArrayList();
list.add(8);
list.add(2);
list.add(3);
list.add(10);
List books = this.bookService.selectByIn(list);
for (Book b : books){
System.out.println(b);
}
}

2.1 参数中直接加入%%

2.2 使用${…}代替#{…}（不建议使用该方式，有SQL注入风险）

关键：#{…}与${…}区别？
参数类型为字符串，#会在前后加单引号[']，$则直接插入值

注：
1) mybatis中使用OGNL表达式传递参数
2) 优先使用#{…}
3) ${…}方式存在SQL注入风险

2.3 SQL字符串拼接CONCAT

select * from t_mvc_book where bname like #{bname}

select * from t_mvc_book where bname like '${bname}'

select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')

List<Book> selectByLike1(@Param("bname")String bname);

List<Book> selectByLike2(@Param("bname")String bname);

List<Book> selectByLike3(@Param("bname")String bname);

测试

@Test  
public void selectByLike() {  

// List books = this.bookService.selectByLike1(StringUtil.toLikeStr("圣墟"));
// List books = this.bookService.selectByLike2("%圣墟% or bid !=1");
List books = this.bookService.selectByLike3("圣墟");
for (Book b : books){
System.out.println(b);
}
}

结果：

resultMap：适合使用返回值是自定义实体类的情况

resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型

3.1 使用resultMap返回自定义类型集合

3.2 使用resultType返回List

3.3 使用resultType返回单个对象

3.4 使用resultType返回List

3.5 使用resultType返回Map，适用于多表查询返回单个结果集

select * from t_mvc_book

select * from t_mvc_book

select * from t_mvc_book where bid in #{bid}
select * from t_mvc_book and bname like #{bname}
select * from t_mvc_book and bid = #{bid}

// 3.1 使用resultMap返回自定义类型集合
List list1();
// 3.2 使用resultType返回List
List list2();
// 3.3 使用resultType返回单个对象
Book list3(BookVo bookVo);
// 3.4 使用resultType返回List

测试：

@Test  
public void list() {  

// 返回一个resultMap但是使用list接收
// List books = this.bookService.list1();
// 返回的是resulttype使用list接收
// List books = this.bookService.list2();
// 返回的是resulttype使用list接收
// for (Book b : books){
// System.out.println(b);
// }

// 返回的是resulttype使用T接收
// BookVo bookVo =new BookVo();
// List list = new ArrayList();
// list.add(2);
// bookVo.setBookIds(list);
// Book book = this.bookService.list3(bookVo);
// System.out.println(book);

// 返回的是resulttype使用list

结果：

为什么要重写mybatis的分页？

Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的

使用分页插件步奏

1、导入pom依赖

2、Mybatis.cfg.xml配置拦截器

3、使用PageHelper进行分页

4、处理分页结果

Pom依赖

com.github.pagehelper pagehelper 5.1.2

Mybatis.cfg.xml配置拦截器

select * from t_mvc_book and bname like #{bname}

@Override
public List

    if(pageBean!=null && pageBean.isPagination()){  
        PageHelper.startPage(pageBean.getPage(),pageBean.getRows());  
    }

    List<Map> list = this.bookMapper.list4(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 listPager() {
Map map =new HashMap();
map.put("bname",StringUtil.toLikeStr("圣墟"));
PageBean pageBean =new PageBean();
pageBean.setPage(3);
// pageBean.setPagination(false);
List

结果：

>(>)
<(<)
&(&)
空格( )

select * from t_mvc_book and price > #{min} and price < #{max}
select * from t_mvc_book #{min} ]]>

// 特殊字符的处理方式
List

    List<Map> list7(BookVo bookVo);

测试：

@Test  
public void listSpecoal() {  
    BookVo bookVo =new BookVo();  
    bookVo.setMin(100.0);  
    bookVo.setMax(500.0);  

// List

相关代码

PageBean 分页工具类

package com.liuwenwu.util;

import java.io.Serializable;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

public class PageBean implements Serializable {

private static final long serialVersionUID = 2422581023658455731L;

//页码  
private int page=1;  
//每页显示记录数  
private int rows=10;  
//总记录数  
private int total=0;  
//是否分页  
private boolean isPagination=true;  
//上一次的请求路径  
private String url;  
//获取所有的请求参数  
private Map<String,String\[\]> map;

public PageBean() {  
    super();  
}

//设置请求参数  
public void setRequest(HttpServletRequest req) {  
    String page=req.getParameter("page");  
    String rows=req.getParameter("rows");  
    String pagination=req.getParameter("pagination");  
    this.setPage(page);  
    this.setRows(rows);  
    this.setPagination(pagination);  
    this.url=req.getContextPath()+req.getServletPath();  
    this.map=req.getParameterMap();  
}  
public String getUrl() {  
    return url;  
}

public void setUrl(String url) {  
    this.url = url;  
}

public Map<String, String\[\]> getMap() {  
    return map;  
}

public void setMap(Map<String, String\[\]> map) {  
    this.map = map;  
}

public int getPage() {  
    return page;  
}

public void setPage(int page) {  
    this.page = page;  
}

public void setPage(String page) {  
    if(null!=page&&!"".equals(page.trim()))  
        this.page = Integer.parseInt(page);  
}

public int getRows() {  
    return rows;  
}

public void setRows(int rows) {  
    this.rows = rows;  
}

public void setRows(String rows) {  
    if(null!=rows&&!"".equals(rows.trim()))  
        this.rows = Integer.parseInt(rows);  
}

public int getTotal() {  
    return total;  
}

public void setTotal(int total) {  
    this.total = total;  
}

public void setTotal(String total) {  
    this.total = Integer.parseInt(total);  
}

public boolean isPagination() {  
    return isPagination;  
}

public void setPagination(boolean isPagination) {  
    this.isPagination = isPagination;  
}

public void setPagination(String isPagination) {  
    if(null!=isPagination&&!"".equals(isPagination.trim()))  
        this.isPagination = Boolean.parseBoolean(isPagination);  
}

/\*\*  
 \* 获取分页起始标记位置  
 \* @return  
 \*/  
public int getStartIndex() {  
    //(当前页码-1)\*显示记录数  
    return (this.getPage()-1)\*this.rows;  
}

/\*\*  
 \* 末页  
 \* @return  
 \*/  
public int getMaxPage() {  
    int totalpage=this.total/this.rows;  
    if(this.total%this.rows!=0)  
        totalpage++;  
    return totalpage;  
}

/\*\*  
 \* 下一页  
 \* @return  
 \*/  
public int getNextPage() {  
    int nextPage=this.page+1;  
    if(this.page>=this.getMaxPage())  
        nextPage=this.getMaxPage();  
    return nextPage;  
}

/\*\*  
 \* 上一页  
 \* @return  
 \*/  
public int getPreivousPage() {  
    int previousPage=this.page-1;  
    if(previousPage<1)  
        previousPage=1;  
    return previousPage;  
}

@Override  
public String toString() {  
    return "PageBean \[page=" + page + ", rows=" + rows + ", total=" + total + ", isPagination=" + isPagination  
            + "\]";  
}  

}

Bookvo

vo用来存放包括数据库表映射的字段以及多余的查询条件所需属性列，保证实体类纯粹，降低耦合度

package com.liuwenwu.model;

import java.util.List;

/**
* @author LWW
* @site www.lww.com
* @company
* @create 2019-09-20 19:05
* vo用来存放包括数据库表映射的字段以及多余的查询条件所需属性列
*/
public class BookVo extends Book{
private List bookIds;
private Double min;
private Double max;

public Double getMax() {  
    return max;  
}

public void setMax(Double max) {  
    this.max = max;  
}

public Double getMin() {  
    return min;  
}

public void setMin(Double min) {  
    this.min = min;  
}

public List<String> getBookIds() {  
    return bookIds;  
}

public void setBookIds(List<String> bookIds) {  
    this.bookIds = bookIds;  
}  

}

BookService

package com.liuwenwu.service;

import com.liuwenwu.model.Book;
import com.liuwenwu.model.BookVo;
import com.liuwenwu.util.PageBean;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
* @author LWW
* @site www.lww.com
* @company
* @create 2019-09-19 22:23
*/
public interface BookService {
int deleteByPrimaryKey(Integer bid);

int insert(Book record);

int insertSelective(Book record);

Book selectByPrimaryKey(Integer bid);

int updateByPrimaryKeySelective(Book record);

int updateByPrimaryKey(Book record);

List<Book> selectByIn(List bookIds);

List<Book> selectByLike1(String bname);

List<Book> selectByLike2(String bname);

List<Book> selectByLike3(String bname);

List<Book> list1();  
List<Book> list2();  
Book list3(BookVo bookVo);  
List<Map> list4(Map map);  
Map list5(Map map);

List<Map> listPager(Map map, PageBean pageBean);

List<Map> list6(BookVo bookVo);

List<Map> list7(BookVo bookVo);  

}

BookServiceImpl

package com.liuwenwu.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.liuwenwu.mapper.BookMapper;
import com.liuwenwu.model.Book;
import com.liuwenwu.model.BookVo;
import com.liuwenwu.service.BookService;
import com.liuwenwu.util.PageBean;

import java.util.List;
import java.util.Map;

/**
* @author LWW
* @site www.lww.com
* @company
* @create 2019-09-19 22:58
*/
public class BookServiceImpl implements BookService {
private BookMapper bookMapper;

public BookMapper getBookMapper() {  
    return bookMapper;  
}

public void setBookMapper(BookMapper bookMapper) {  
    this.bookMapper = bookMapper;  
}

@Override  
public int deleteByPrimaryKey(Integer bid) {  
    return bookMapper.deleteByPrimaryKey(bid);  
}

@Override  
public int insert(Book record) {  
    return bookMapper.insert(record);  
}

@Override  
public int insertSelective(Book record) {  
    return bookMapper.insertSelective(record);  
}

@Override  
public Book selectByPrimaryKey(Integer bid) {  
    return bookMapper.selectByPrimaryKey(bid);  
}

@Override  
public int updateByPrimaryKeySelective(Book record) {  
    return bookMapper.updateByPrimaryKeySelective(record);  
}

@Override  
public int updateByPrimaryKey(Book record) {  
    return bookMapper.updateByPrimaryKey(record);  
}

@Override  
public List<Book> selectByIn(List bookIds) {  
    return bookMapper.selectByIn(bookIds);  
}

@Override  
public List<Book> selectByLike1(String bname) {  
    return bookMapper.selectByLike1(bname);  
}

@Override  
public List<Book> selectByLike2(String bname) {  
    return bookMapper.selectByLike2(bname);  
}

@Override  
public List<Book> selectByLike3(String bname) {  
    return bookMapper.selectByLike3(bname);  
}

@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(Map map) {  
    return bookMapper.list4(map);  
}

@Override  
public Map list5(Map map) {  
    return bookMapper.list5(map);  
}

@Override  
public List<Map> listPager(Map map, PageBean pageBean) {

    if(pageBean!=null && pageBean.isPagination()){  
        PageHelper.startPage(pageBean.getPage(),pageBean.getRows());  
    }

    List<Map> list = this.bookMapper.list4(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;  
}

@Override  
public List<Map> list6(BookVo bookVo) {  
    return bookMapper.list6(bookVo);  
}

@Override  
public List<Map> list7(BookVo bookVo) {  
    return bookMapper.list7(bookVo);  
}  

}