动态sql和分页

目录

一、mybatis动态sql

foreach标签

二、模糊查询

 MyBatis中#和$的区别

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

四、分页查询

五、特殊字符处理

---

一、mybatis动态sql

foreach标签

在BookMapper.xml中使用foreach标签查询

<?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.maomao.mapper.BookMapper" >
  <resultMap id="BaseResultMap" type="com.maomao.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>



  <!--select * from t_mvc_book where bid in (1,2,3,4,5,6)
    bookIds 就是{1,2,3,4,5,6}
  -->
  <select id="selectByIn" resultMap="BaseResultMap" parameterType="java.util.List" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid in
    <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
      #{bid}
    </foreach>
  </select>




  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.maomao.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.maomao.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.maomao.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.maomao.model.Book" >
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
</mapper>

BookBiz.java 

package com.maomao.biz;

import com.maomao.model.Book;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);

    Book selectByPrimaryKey(Integer bid);

    List<Book> selectByIn(List bookIds);
}

BookBizImpl.java

package com.maomao.biz.impl;

import com.maomao.biz.BookBiz;
import com.maomao.mapper.BookMapper;
import com.maomao.model.Book;

import java.util.List;

public class BookBizImpl implements BookBiz {
    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 Book selectByPrimaryKey(Integer bid) {
        return bookMapper.selectByPrimaryKey(bid);
    }

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


}

 BookBizImplTest.java

package com.maomao.biz.impl;


import com.maomao.biz.BookBiz;
import com