MyBatis教程

alt+Enter快捷键本文idea多处用到

foreach

foreach 就是循环的意思，collection代表要被循环参数集合。open和close代表开始和结束拼接字符串。separator代表item之间的分割符。item就是当前正在循环的变量定义。就当java中的foreach

<!--foreach-->
  <select id="selectBooksIn" resultType="com.wxm.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>

bookServer,bookMapper.xml

List selectByIn(@Param(“userIds”) List userIds);

bookServerImpl

 @Override
    public List<book> selectBooksIn(List bookIds) {
        return bookmapper.selectBooksIn(bookIds);
    }

test

//测试foreach
@Test
public void selectBooksLike1() {
List list=new ArrayList();
list.add(2);
list.add(11);
List books= this.bookService.selectBooksIn(list);
for (book book :books ) {
System.out.println(book);
}
}

效果

模糊查询

这里演示 3种 like查询的区别

#{uname}常用
${uname}
concat

bookMapper.java,bookServer

 /**
     * 模糊查询
     * 有三种方式
     * @param bname
     * @return
     */
    List<book> selectBooksLike1(@Param("bname") String bname);

    List<book> selectBooksLike2(@Param("bname") String bname);

    List<book> selectBooksLike3(@Param("bname") String bname);

bookMapper.xml

    <!--模糊查的第一种-->
<select id="selectBooksLike1" resultType="com.wxm.model.book" parameterType="java.lang.String">
  select * from t_mvc_book where bname like #{bname}
</select>


    <!--模糊查的第二种-->
<select id="selectBooksLike2" resultType="com.wxm.model.book" parameterType="java.lang.String">
  select * from t_mvc_book where bname like '${bname}'
</select>


    <!--模糊查的第三种-->
 <select id="selectBooksLike3" resultType="com.wxm.model.book" parameterType="java.lang.String">
    select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>

test

    /**
     *  # 与$ 的区别
     *  $ 会引起sql的攻击
     */
     @Test
    public void selectBooksLike() {
        String bname="圣墟";
//        List<book> books=   this.bookService.selectBooksLike1("%"+bname+"%");
//        List<book> books=   this.bookService.selectBooksLike2("%"+bname+"%");
        List<book> books=   this.bookService.selectBooksLike3(bname);

        for (book book :books ) {
            System.out.println(book);
        }
    }

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

#{uname}测试结果

${uname}测试结果

concat测试结果

总结

MyBatis中#和$的区别

1. #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。 如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by ‘111’,
   如果传入的值是id，则解析成的sql为order by “id”.

2. $将传入的数据直接显示生成在sql中。 如：order by $use{r}_{i}d$，如果传入的值是111,那么解析成sql时的值为order by user_id,
   如果传入的值是id，则解析成的sql为order by id.

3. #方式能够很大程度防止sql注入。

4. $方式无法防止Sql注入。

5. $方式一般用于传入数据库对象，例如传入表名.

6. 一般能用#的就别用$.

查询返回结果集的处理

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

3.1 使用resultMap返回自定义类型集合
3.2 使用resultType返回List
3.3 使用resultType返回单个对象
3.4 使用resultType返回List

bookServer/bookMapper.java

 /**
     *  使用resultMap返回自定义类型集合
     * @return
     */
    List<book> list1();

    /**
     *  使用resultMap返回List<T>
     * @return
     */
    List<book> list2();

    /**
     *  使用resultMap返回单个对象
     * @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.wxm.model.book">
        select  * from t_mvc_book
    </select>

    <select id="list3" resultType="com.wxm.model.book" 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="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>

bookServerImpl
重写方法

   @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 map) {
        return bookmapper.list5(map);
    }

效果:使用resultMap返回自定义类型集合

效果:使用resultMap返回List

效果:使用resultType返回单个对象

效果:使用resultType返回List

分页查询

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

使用分页插件步奏

1、导入pom依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.2</version>
</dependency>

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

<plugins>
    <!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
    <plugin   interceptor="com.github.pagehelper.PageInterceptor">
    </plugin>
</plugins>

PageBean

package com.wxm.util;

import javax.servlet.http.HttpServletRequest;
import java.util.Map;

/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-16 11:15
 */
public class PageBean {

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


}

3、使用PageHelper插件进行分页

mapper.xml

<select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
  select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>

Mapper层

List

Service层

List

@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;
}

4、处理分页结果

test

   //分页
    @Test
    public void listPage(){
        Map map = new HashMap();
        map.put("bname", "%"+"圣"+"%");
        PageBean pageBean = new PageBean();
        pageBean.setPage(3);
        pageBean.setRows(1);
        List<Map> maps = this.bookService.listPager(map, pageBean);
        for(Map m : maps){
            System.out.println(m);
        }
    }

结果

特殊字符处理

>(>)   
<(&lt;)  
&(&amp;) 
空格(&nbsp;)
<![CDATA[ <= ]]> 

相关代码配置

   <select id="list6" resultType="com.wxm.model.book" parameterType="com.wxm.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.wxm.model.book" parameterType="com.wxm.model.BookVo">
        select * from t_mvc_book
        <where>
            <if test="null != min and min != ''">
                and #{min} &lt; price
            </if>
            <if test="null != max and max != ''">
                and #{max} &gt; price
            </if>
        </where>
    </select>

/**
 * 处理特殊字符
 * @param bookVo
 * @return
 */
List<Book> list6(BookVo bookVo);


/**
 * 处理特殊字符
 * @param bookVo
 * @return
 */
List<Book> list7(BookVo bookVo);

test

  @Test
    public void sqlSpecial(){
        BookVo bookVo = new BookVo();
        bookVo.setMax(3000);
        bookVo.setMin(1000);
        List<book> bs = this.bookService.list7(bookVo);
        for(book b : bs){
            System.out.println(b);
        }
    }

效果

完整代码

bookMapper

package com.wxm.mapper;

import com.wxm.model.BookVo;
import com.wxm.model.book;
import org.apache.ibatis.annotations.Param;

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

public interface bookMapper {
    int deleteByPrimaryKey(Integer bid);

    int insert(book record);

    int insertSelective(book record);

    book selectByPrimaryKey(Integer bid);

    int updateByPrimaryKeySelective(book record);

    int updateByPrimaryKey(book record);

    /**
     * @param bookIds
     * @return
     * @Param：如果形参在mapper.xml使用就需要加上面的注释
     */
    List<book> selectBooksIn(@Param("bookIds") List bookIds);

    /**
     * 模糊查询
     * 有三种方式
     * @param bname
     * @return
     */
    List<book> selectBooksLike1(@Param("bname") String bname);

    List<book> selectBooksLike2(@Param("bname") String bname);

    List<book> selectBooksLike3(@Param("bname") String bname);

    /**
     * mybayis结果集处理的五种情况
     * @return
     */
    List<book> list1();
    List<book> list2();
    book list3(BookVo bookVo);
    List<Map> list4();
    Map list5(Map map);

    /**
     * 分页
     * @param map
     *
     * @return
     */
    List<Map> listPager(Map map);

    /**
     * 处理特殊字符
     * @param bookVo
     * @return
     */
    List<book> list6(BookVo bookVo);


    /**
     * 处理特殊字符
     * @param bookVo
     * @return
     */
    List<book> list7(BookVo bookVo);


}

bookMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wxm.mapper.bookMapper">
    <resultMap id="BaseResultMap" type="com.wxm.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>
    <sql id="Base_Column_List">
    bid, bname, price
  </sql>
    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from t_mvc_book
        where bid = #{bid,jdbcType=INTEGER}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
    <insert id="insert" parameterType="com.wxm.model.book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
    <insert id="insertSelective" parameterType="com.wxm.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>
    <update id="updateByPrimaryKeySelective" parameterType="com.wxm.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>
    <update id="updateByPrimaryKey" parameterType="com.wxm.model.book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>


    <!--mybatis2-->


    <!--foreach-->
    <select id="selectBooksIn" resultType="com.wxm.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>

    <!--模糊查的第一种-->
    <select id="selectBooksLike1" resultType="com.wxm.model.book" parameterType="java.lang.String">
  select * from t_mvc_book where bname like #{bname}
</select>


    <!--模糊查的第二种-->
    <select id="selectBooksLike2" resultType="com.wxm.model.book" parameterType="java.lang.String">
  select * from t_mvc_book where bname like '${bname}'
</select>


    <!--模糊查的第三种-->
    <select id="selectBooksLike3" resultType="com.wxm.model.book" parameterType="java.lang.String">
    select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>

    <!--查询返回结果集处理的五种情况-->

    <select id="list1" resultMap="BaseResultMap">
        select  * from t_mvc_book
    </select>
    <select id="list2" resultType="com.wxm.model.book">
        select  * from t_mvc_book
    </select>

    <select id="list3" resultType="com.wxm.model.book" 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="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>
 <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
  select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>


    <select id="list6" resultType="com.wxm.model.book" parameterType="com.wxm.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.wxm.model.book" parameterType="com.wxm.model.BookVo">
        select * from t_mvc_book
        <where>
            <if test="null != min and min != ''">
                and #{min} &lt; price
            </if>
            <if test="null != max and max != ''">
                and #{max} &gt; price
            </if>
        </where>
    </select>
</mapper>

BookVo

package com.wxm.model;

import java.util.List;

/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-16 9:41
 * vo介绍
 *  * mybatis ,hibernate 都是orm框架,表所存在的列段在实体类model都有映射
 *  * 实际开发中，会因为某一些需求该变model,破坏model封装性
 *  * 此时为了保证Model的封装性，就可以使用vo类来完成指定的需求
 */
public class BookVo extends book  {


    private  float min;
    private  float max;
    private List<Integer> bookIds;

    public float getMin() {
        return min;
    }

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

    public float getMax() {
        return max;
    }

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

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

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

    @Override
    public String toString() {
        return "BookVo{" +
                "min=" + min +
                ", max=" + max +
                ", bookIds=" + bookIds +
                '}';
    }
}

BookService

package com.wxm.service;

import com.wxm.model.BookVo;
import com.wxm.model.book;
import com.wxm.util.PageBean;
import org.apache.ibatis.annotations.Param;

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

/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-13 18:47
 */
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);



//    mybatis02
List<book> selectBooksIn(@Param("bookIds") List bookIds);


    /**
     * 模糊查询
     * 有三种方式
     * @param bname
     * @return
     */
    List<book> selectBooksLike1(@Param("bname") String bname);

    List<book> selectBooksLike2(@Param("bname") String bname);

    List<book> selectBooksLike3(@Param("bname") String bname);

    /**
     *  使用resultMap返回自定义类型集合
     * @return
     */
    List<book> list1();

    /**
     *  使用resultMap返回List<T>
     * @return
     */
    List<book> list2();

    /**
     *  使用resultMap返回单个对象
     * @return
     */
    book list3(BookVo bookVo);


    /**
     * 使用resultType返回List<Map>，适用于多表查询返回结果集
     * @return
     */
    List<Map> list4();

    /**
     * 使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集
     * @return
     */
    Map list5(Map map);

    /**
     * 分页
     * @param map
     * @param pageBean
     * @return
     */
    List<Map> listPager(Map map, PageBean pageBean);



    /**
     * 处理特殊字符
     * @param bookVo
     * @return
     */
    List<book> list6(BookVo bookVo);


    /**
     * 处理特殊字符
     * @param bookVo
     * @return
     */
    List<book> list7(BookVo bookVo);


}

BookServiceImpl

package com.wxm.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.wxm.mapper.bookMapper;
import com.wxm.model.BookVo;
import com.wxm.model.book;
import com.wxm.service.BookService;
import com.wxm.util.PageBean;

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

/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-13 19:07
 */
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> selectBooksIn(List bookIds) {
        return bookmapper.selectBooksIn(bookIds);
    }

    @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);
    }

    @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 map) {
        return bookmapper.list5(map);
    }


    /**
     * 分页
     *
     * @param map
     * @param pageBean
     * @return
     */
    @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.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;
    }

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

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


}

SessionUtil

package com.wxm.util;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-13 18:08
 */
public class SessionUtil {
    private static SqlSessionFactory sessionFactory;



    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();

    static {
        sessionFactory = new SqlSessionFactoryBuilder().build(SessionUtil.class.getResourceAsStream("/mybatis.cfg.xml"));
    }

    public static SqlSession openSession() {
        SqlSession session = threadLocal.get();
        if (null == session) {
            session = sessionFactory.openSession();
            threadLocal.set(session);
        }
        return session;
    }

    public static void main(String[] args) {
        SqlSession session = openSession();
//        System.out.println(session.getConnection());
        session.close();
//        System.out.println(session.getConnection());
    }

}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

    <modelVersion>4.0.0</modelVersion>
    <packaging>war</packaging>

    <name>mybatis01</name>
    <groupId>com.wxm</groupId>
    <artifactId>mybatis01</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <build>
        <resources>
            <!--解决mybatis-generator-maven-plugin运行时没有将XxxMapper.xml文件放入target文件夹的问题-->
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <!--解决mybatis-generator-maven-plugin运行时没有将jdbc.properites文件放入target文件夹的问题-->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>jdbc.properties</include>
                    <include>*.xml</include>
                </includes>
            </resource>
        </resources>


        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <dependencies>
                    <!--使用Mybatis-generator插件不能使用太高版本的mysql驱动 -->
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.44</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- ********************** junit单元测试依赖 ********************** -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- ********************** Java Servlet API  ********************** -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.0</version>
            <scope>provided</scope>
        </dependency>

        <!-- ********************** Mybatis依赖 ********************** -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <!-- ********************** Mysql JDBC驱动 ********************** -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

        <!-- **********************  日志配置  ********************** -->
        <!--记得修改mybatis.cfg.xml添加如下内容-->
        <!--<setting name="logImpl" value="LOG4J2"/>-->
        <!--核心log4j2jar包-->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.9.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.9.1</version>
        </dependency>
        <!--web工程需要包含log4j-web，非web工程不需要-->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-web</artifactId>
            <version>2.9.1</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.2</version>
        </dependency>
    </dependencies>

</project>

mybatis.conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 引入外部配置文件 -->
    <properties resource="jdbc.properties"/>

    <settings>
        <setting name="logImpl" value="LOG4J2"/>
    </settings>
    <plugins>
        <!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
        </plugin>
    </plugins>
    <!-- 配置mybatis运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- type="JDBC" 代表使用JDBC的提交和回滚来管理事务 -->
            <transactionManager type="jdbc"/>

            <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI -->
            <!-- POOLED 表示支持JDBC数据源连接池 -->
            <!-- UNPOOLED 表示不支持数据源连接池 -->
            <!-- JNDI 表示支持外部数据源连接池 -->
            <dataSource type="POOLED">
                <property name="driver"
                          value="${jdbc.driver}"/>
                <property name="url"
                          value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/wxm/mapper/bookMapper.xml"/>
    </mappers>
</configuration>

test

import com.wxm.mapper.bookMapper;
import com.wxm.model.BookVo;
import com.wxm.model.book;
import com.wxm.service.BookService;
import com.wxm.service.impl.BookServiceImpl;
import com.wxm.util.PageBean;
import com.wxm.util.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

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

/**
 * @author wxm
 * @site www.wxm.com
 * @company xxx公司
 * @create 2019-11-13 19:10
 */
public class BookServiceTest {
    private BookService bookService;
    private SqlSession sqlSession;

    @Before
    public void setUp() {
        BookServiceImpl bookService = new BookServiceImpl();
        sqlSession = SessionUtil.openSession();
        bookMapper mapper = sqlSession.getMapper(bookMapper.class);
        System.out.println(mapper);
        bookService.setBookmapper(mapper);
        this.bookService = bookService;
    }

    @Test
    public void insert() {
        book book = new book();
        book.setBid(1);
        book.setBname("红楼梦");
        book.setPrice(90f);
        bookService.insert(book);
    }

    @Test
    public void selectByPrimaryKey() {
        this.bookService.selectByPrimaryKey(27);
//        this.bookService.deleteByPrimaryKey(27);
        System.out.println(this.bookService.selectByPrimaryKey(37));
    }


    //测试foreach
    @Test
    public void selectBooksIn() {
//        List list = new ArrayList();
//        list.add(2);
//        list.add(11);
//
//
//        List<book> books = this.bookService.selectBooksIn(list);
//        for (book book : books) {
//            System.out.println(book);
//
//        }
    }


    //测试模糊查询

    /**
     * # 与$ 的区别
     * $ 会引起sql的攻击
     */
    @Test
    public void selectBooksLike() {
//        String bname = "圣墟";
//        List<book> books=   this.bookService.selectBooksLike1("%"+bname+"%");
//        List<book> books=   this.bookService.selectBooksLike2("%"+bname+"%");
//        List<book> books = this.bookService.selectBooksLike3(bname);
//
//        for (book book : books) {
//            System.out.println(book);
//        }
    }

    /**
     * 查询结果集
     */
    @Test
    public void testListReturn() {
//        使用resultMap返回自定义类型集合
//               List<book> books=this.bookService.list1();
//        System.out.println(books);
//        for (book b :books   ) {
//            System.out.println(b);
//
//        }


//         使用resultType返回List<T>
//        List<book> books = this.bookService.list2();
//        for (book b :books   ) {
//            System.out.println(b);
//
//        }


//        使用resultType返回单个对象
//        List list = new ArrayList();
//        list.add("20");
//        list.add("27");
//        BookVo bookVo = new BookVo();
//        bookVo.setBookIds(list);
//        book books = this.bookService.list3(bookVo);
//        System.out.println(books);

//使用resultType返回List<Map>，适用于多表查询返回结果集
//        List<Map> books = bookService.list4();
//        for (Map book : books  ) {
//            System.out.println(book);
//        }

//        使用resultType返回Map<String,Object>，适用于多表查询返回单个结果
//        Map map=new HashMap();
//        map.put("bid",20);
//        Map book=bookService.list5(map);
//        System.out.println(book);
    }
    //分页
    @Test
    public void listPage(){
        Map map = new HashMap();
        map.put("bname", "%"+"圣"+"%");
        PageBean pageBean = new PageBean();
        pageBean.setPage(3);
        pageBean.setRows(1);
        List<Map> maps = this.bookService.listPager(map, pageBean);
        for(Map m : maps){
            System.out.println(m);
        }

    }


    @Test
    public void sqlSpecial(){
        BookVo bookVo = new BookVo();
        bookVo.setMax(3000);
        bookVo.setMin(1000);
        List<book> bs = this.bookService.list7(bookVo);
        for(book b : bs){
            System.out.println(b);
        }
    }
    @After
    public void tearDowm() {
        sqlSession.commit();
        sqlSession.close();
    }
}