1. mybatis动态sql
2. 模糊查询
3. 查询返回结果集的处理
4. 分页查询
5. 特殊字符处理

一、mybatis动态sql

常用的动态sql：if，foreach等

  
    select * from t_mvc_book where bid in
    
      #{bid}
    
  

 BookMapper.java中的方法：

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

BookService

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

BookServiceImpl

@Override
public List selectBooksIn(List bookIds) {

    return bookMapper.selectBooksIn(bookIds);
}

BookServiceImplTest

@Test
public void selectBooksIn() {
    System.out.println("mybatis的foreach方法");
    List books = this.bookService.selectBooksIn(Arrays.asList(new Integer[]{35, 36, 37}));
    books.forEach(System.out::println);
}

运行结果：

二、 模糊查询

三种方式：

#{...}

${...}

Concat

注意：#{...}自带引号，${...}有sql注入的风险

BookMapper.xml

    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}),'%')
   
BookMapper.java
 
  List selectBooksLike1(@Param("bname") String bname);
    List selectBooksLike2(@Param("bname") String bname);
    List selectBooksLike3(@Param("bname") String bname);
 
BookService.java
 
  List selectBooksLike1(@Param("bname") String bname);
    List selectBooksLike2(@Param("bname") String bname);
    List selectBooksLike3(@Param("bname") String bname);
 
BookServiceImpl.java
 
 @Override
    public List selectBooksLike1(String bname) {

        return bookMapper.selectBooksLike1(bname);
    }

    @Override
    public List selectBooksLike2(String bname) {

        return  bookMapper.selectBooksLike2(bname);
    }

    @Override
    public List selectBooksLike3(String bname) {

        return  bookMapper.selectBooksLike3(bname);
    }
 
运行结果：
 
 
 
 
三、查询返回结果集的处理 
 
 
resultMap：适合使用返回值是自定义实体类的情况
 
 
 resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型
 
 

 
    3.1 使用resultMap返回自定义类型集合
 
 
    3.2 使用resultType返回List
 
 
    3.3 使用resultType返回单个对象
 
 
    3.4 使用resultType返回List
 
 
    3.5 使用resultType返回Map，适用于多表查询返回单个结果集
 
 

  
    
    
    
  
 
 
BookMapper.xml
 

    select * from t_mvc_book
  
  
    select * from t_mvc_book
  
  
    select * from t_mvc_book where bid in
    
      #{bid}
    
  
  
    select * from t_mvc_book
  
  
    select * from t_mvc_book where bid = #{bid}
  

BookMapper

 
    List list1();

    
    List list2();

    
    Book list3(BookVo bookVo);

    
    List

BookService

 
    List list1();

    
    List list2();

    
    Book list3(BookVo bookVo);

    
    List

BookServiceImpl

  @Override
    public List list1() {

        return bookMapper.list1();
    }

    @Override
    public List list2() {

        return bookMapper.list2();
    }

    @Override
    public Book list3(BookVo bookVo) {

        return bookMapper.list3(bookVo);
    }

    @Override
    public List

BookServiceImplTest

 @Test
    public void testList1() {
        System.out.println("使用resultMap返回自定义类型集合");
        List books = this.bookService.list1();
        books.forEach(System.out::println);
    }
    @Test
    public void testList2() {
        System.out.println("使用resultType返回List");
        List books = this.bookService.list2();
        books.forEach(System.out::println);
    }
    @Test
    public void testList3() {
        System.out.println("使用resultType返回单个对象");
        BookVo booKVo = new BookVo();
        booKVo.setBookIds(Arrays.asList(new Integer[]{40}));
        System.out.println(this.bookService.list3(booKVo));
    }
    @Test
    public void testList4() {
        System.out.println("使用resultType返回List

运行结果：

 

总结：

  结论1
* resultMap：多表查询会用
* resultType：单表查询
*
  结论2
* List:单表
* List

四、分页查询

对比自定义mvc，Hibernate的分页

 导入pom分页依赖

    com.github.pagehelper
    pagehelper
    5.1.2

Mybatis.cfg.xml配置拦截器

添加PageBean

package com.xhy.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 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 getMap() {
        return map;
    }

    public void setMap(Map 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);
    }

    
    public int getStartIndex() {
        //(当前页码-1)*显示记录数
        return (this.getPage()-1)*this.rows;
    }

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

    
    public int getNextPage() {
        int nextPage=this.page+1;
        if(this.page>=this.getMaxPage())
            nextPage=this.getMaxPage();
        return nextPage;
    }

    
    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
                + "]";
    }
}

BookMapper.xml

    select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
   
BookMapper.java
 
List
 
BookService.java
 
   List
 
BookServiceImpl.java
 
@Override
public List
 
BookServiceImplTest.java
 
 @Test
    public void testPager() {
        System.out.println("分页");
        Map map = new HashMap();
        map.put("bname","圣墟");
        PageBean pageBean = new PageBean();
        pageBean.setPage(4);
//        不分页
//        pageBean.setPagination(false);
        List
 
运行结果：
 
 
五、特殊字符处理 
 类型：
 
 
 
1.>(>)   
 
 
 2.<(<)  
 
 
 3.&(&)
 
 
 4.空格( )
 
 
 5.
 
 
修改BookVo
 
package com.xhy.vo;

import com.xhy.model.Book;

import java.util.List;

public class BookVo extends Book {

    private List bookIds;
    private float min;
    private float max;

    public float getMax() {
        return max;
    }

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

    public float getMin() {
        return min;
    }

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

    public List getBookIds() {
        return bookIds;
    }

    public void setBookIds(List bookIds) {
        this.bookIds = bookIds;
    }
}
 
BookMapper.xml
 

    select * from t_mvc_book
    
      
        
      
      
         price ]]>
      
    
  

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

BookMapper

 
    List list6(BookVo bookVo);


    
    List list7(BookVo bookVo);

BookService

 
    List list6(BookVo bookVo);


    
    List list7(BookVo bookVo);

BookServiceImpl

 @Override
    public List list6(BookVo bookVo) {

        return bookMapper.list6(bookVo);
    }

    @Override
    public List list7(BookVo bookVo) {

        return bookMapper.list7(bookVo);
    }

BookServiceImplTest

    @Test
    public void testlist6(){
        System.out.println("特殊字符的处理--");
        BooKVo booKVo = new BooKVo();
        booKVo.setMin(20f);
        booKVo.setMax(50f);
        List books = this.bookService.list6(booKVo);
        books.forEach(System.out::println);
    }
    @Test
    public void testlist7(){
        System.out.println("特殊字符的处理--");
        BooKVo booKVo = new BooKVo();
        booKVo.setMin(20f);
        booKVo.setMax(50f);
        List books = this.bookService.list7(booKVo);
        books.forEach(System.out::println);
    }

运行结果：