动态sql和分页下(mybatis的分页及特殊字符)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 动态sql和分页下(mybatis的分页及特殊字符)
  • mybatis的分页
  • mybatis的特殊字符

1.mybatis的分页(selectByLikebname是加两个字段分页举例;like4是调用pageHelp分页举例)

为什么要重写mybatis的分页?

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

使用分页插件步奏

  1. 导入pom依赖
  2. Mybatis.cfg.xml配置拦截器
  3. 使用PageHelper进行分页
  4. 处理分页结果
//pom依赖
<?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/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.example</groupId>
  <artifactId>mybatis_01</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>
  <name>mybatis01 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>
  <properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>
  <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>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>compile</scope>
    </dependency>
  </dependencies>
  <build>
    <finalName>mybatis_01</finalName>
    <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>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <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>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>
<?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>
    <!-- 别名 -->
    <typeAliases>
        <!--<typeAlias type="com.javaxl.model.Book" alias="Book"/>-->
    </typeAliases>
<plugins>
    <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/zlj/mapper/BookMapper.xml"/>
    </mappers>
</configuration>
package com.zlj.utils;
import javax.servlet.http.HttpServletRequest;
import java.io.Serializable;
import java.util.Map;
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
        + "]";
  }
}
<?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.zlj.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    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.zlj.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.zlj.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.zlj.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.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <select id="selectByBids" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
<select id="like1" parameterType="java.lang.String" resultType="com.zlj.model.Book">
  select
  <include refid="Base_Column_List" />
  from t_mvc_book
  where bname like #{bname}
</select>
  <select id="like2" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like '${bname}'
  </select>
  <select id="like3" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="list01" resultType="com.zlj.model.Book" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
  <select id="list02" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
<select id="list03" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid and sc.sid=#{sid} and sc.cid=#{cid}
</select>
  <select id="list04" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid
</select>
  <select id="bname1" parameterType="java.lang.Integer" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <select id="bname2" parameterType="java.lang.String" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="selectByLikebname" parameterType="java.util.Map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like #{bname} limit #{start},#{size}
  </select>
  <select id="like4" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
</mapper>
package com.zlj.mapper;
import com.zlj.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);
    List<Book> selectByBids(@Param("bids") List bids);
    List<Book> like1(@Param("bname") String bname);
    List<Book> like2(@Param("bname") String bname);
    List<Book> like3(@Param("bname") String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(@Param("bid") Integer bid);
    List<String> bname2(@Param("bname") String bname);
    List<Book> selectByLikebname(Map map);
    List<Book> like4(@Param("bname") String bname);
}
package com.zlj.biz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import java.util.List;
import java.util.Map;
public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    BookMapper setBookMapper(BookMapper mapper);
    List<Book> selectByBids(List bids);
    List<Book> like1(String bname);
    List<Book> like2(String bname);
    List<Book> like3(String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(Integer bid);
    List<String> bname2(String bname);
    List<Book> selectByLikebname(Map map);
    List<Book> like4(String bname, PageBean pageBean);
}
package com.zlj.biz.impl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zlj.biz.BookBiz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 8:49
 */
public class BookBizImpl implements BookBiz {
    private  BookMapper bookMapper;
    public BookMapper getBookMapper() {
        return 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 BookMapper setBookMapper(BookMapper mapper) {
        return this.bookMapper = mapper;
    }
    @Override
    public List<Book> selectByBids(List bids) {
        return bookMapper.selectByBids(bids);
    }
    @Override
    public List<Book> like1(String bname) {
        return bookMapper.like1(bname);
    }
    @Override
    public List<Book> like2(String bname) {
        return bookMapper.like2(bname);
    }
    @Override
    public List<Book> like3(String bname) {
        return bookMapper.like3(bname);
    }
    @Override
    public List<Book> list01() {
        return bookMapper.list01();
    }
    @Override
    public List<Book> list02() {
        return bookMapper.list02();
    }
    @Override
    public Map list03(Map map){
    return bookMapper.list03(map);
    }
    @Override
    public List<Map> list04(Map map){
    return bookMapper.list04(map);
    }
    @Override
    public String bname1(Integer bid) {
        return bookMapper.bname1(bid);
    }
    @Override
    public List<String> bname2(String bname) {
        return bookMapper.bname2(bname);
    }
    @Override
    public List<Book> selectByLikebname(Map map) {
        return bookMapper.selectByLikebname(map);
    }
    @Override
    public List<Book> like4(String bname, PageBean pageBean) {
        if (pageBean!=null&&pageBean.isPagination()) {
            PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
        }
        List<Book> books = bookMapper.like4(bname);
        if (pageBean!=null && pageBean.isPagination()) {
            PageInfo<Book> info = new PageInfo<>(books);
            System.out.println("当前页:" + info.getPageNum());
            System.out.println("展示记录数:" + info.getPageSize());
            System.out.println("符合查询的总页数:" + info.getTotal());
            pageBean.setTotal((int)info.getTotal());
        }
        return books;
    }
}
package com.zlj.demo;
import com.zlj.biz.impl.BookBizImpl;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import com.zlj.utils.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 9:07
 */
public class demo1 {
    private SqlSession sqlSession;
    private BookBizImpl bookBiz;
    @Before
    public void a(){
        System.out.println("执行测试方法之前会执行的初始化代码块");
        sqlSession= SessionUtil.openSession();
        BookBizImpl bookBiz=new BookBizImpl();
        BookMapper mapper=sqlSession.getMapper(BookMapper.class);
        bookBiz.setBookMapper(mapper);
        this.bookBiz=bookBiz;
    }
    @After
    public void b(){
        System.out.println("执行测试方法之后会执行");
        sqlSession.commit();
    }
    @Test
    public void test1(){
        System.out.println("测试方法。。。");
        Book book = bookBiz.selectByPrimaryKey(33);
        System.out.println(book);
    }
@Test
    public void test2(){
        bookBiz.deleteByPrimaryKey(34);
    }
@Test
public void testById(){
    List<Integer> bids = Arrays.asList(new Integer[]{56, 57, 58, 59, 60});
    bookBiz.selectByBids(bids).forEach(System.out::print);//jdk1.8新特性
//    for (Book selectByBid : bookBiz.selectByBids(bids)) {
//        System.out.println(selectByBid);
//    }
}
    @Test
    public void testlike1(){
        bookBiz.like1("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike2(){
        bookBiz.like2("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike3(){
        bookBiz.like3("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist01(){
        bookBiz.list01().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist02(){
        bookBiz.list02().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist03(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        System.out.println(bookBiz.list03(map));
    }
    @Test
    public void testlist04(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        bookBiz.list04(map).forEach(System.out::print);
    }
    @Test
    public void testbname1(){
        System.out.println(bookBiz.bname1(66));
    }
    @Test
    public void testbname2(){
        bookBiz.bname2("圣墟").forEach(System.out::print);
    }
    @Test
    public void selectByLikebname(){
        Map map=new HashMap();
        map.put("bname","%圣墟%");
        map.put("start",30);
        map.put("size",10);
        this.bookBiz.selectByLikebname(map).forEach(System.out::println);
    }
    @Test
    public void testlike04(){
        PageBean pageBean=new PageBean();
        pageBean.setPage(2);
        pageBean.setRows(20);
//        pageBean.setPagination(false);  //不分页
        bookBiz.like4("圣墟",pageBean).forEach(System.out::println);
    }
}

2.mybatis的特殊字符(三种,示例:queryByMinMax)

<?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.zlj.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    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.zlj.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.zlj.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.zlj.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.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <select id="selectByBids" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
<select id="like1" parameterType="java.lang.String" resultType="com.zlj.model.Book">
  select
  <include refid="Base_Column_List" />
  from t_mvc_book
  where bname like #{bname}
</select>
  <select id="like2" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like '${bname}'
  </select>
  <select id="like3" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="list01" resultType="com.zlj.model.Book" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
  <select id="list02" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
<select id="list03" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid and sc.sid=#{sid} and sc.cid=#{cid}
</select>
  <select id="list04" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid
</select>
  <select id="bname1" parameterType="java.lang.Integer" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <select id="bname2" parameterType="java.lang.String" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="selectByLikebname" parameterType="java.util.Map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like #{bname} limit #{start},#{size}
  </select>
  <select id="like4" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="queryByMinMax" parameterType="com.zlj.dto.BookDto" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    <if test="start != null">
<!--    <![CDATA[  create_time > #{min} and create_time < #{max} ]]>-->
<!--    <if test="aaa">                                                //第三种多表查询-->
<!--    <![CDATA[  create_time > #{min} and create_time < #{max} ]]>-->
     where price > #{min} and price < #{max}   //第一种
<!--    where <![CDATA[-->
<!--    price < #{max} and price > #{min}-->      //第二种
<!--    ]]>-->
  </select>
</mapper>
package com.zlj.dto;
import com.zlj.model.Book;
/**
 * @author zlj
 * @create 2023-08-24 23:59
 */
public class BookDto extends Book {
    private float max;
    private float min;
    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 BookDto(Integer bid, String bname, Float price) {
        super(bid, bname, price);
    }
    public BookDto() {
    }
}
package com.zlj.mapper;
import com.zlj.dto.BookDto;
import com.zlj.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);
    List<Book> selectByBids(@Param("bids") List bids);
    List<Book> like1(@Param("bname") String bname);
    List<Book> like2(@Param("bname") String bname);
    List<Book> like3(@Param("bname") String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(@Param("bid") Integer bid);
    List<String> bname2(@Param("bname") String bname);
    List<Book> selectByLikebname(Map map);
    List<Book> like4(@Param("bname") String bname);
    List<Book> queryByMinMax(BookDto bookDto);
}
package com.zlj.biz;
import com.zlj.dto.BookDto;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import java.util.List;
import java.util.Map;
public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    BookMapper setBookMapper(BookMapper mapper);
    List<Book> selectByBids(List bids);
    List<Book> like1(String bname);
    List<Book> like2(String bname);
    List<Book> like3(String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(Integer bid);
    List<String> bname2(String bname);
    List<Book> selectByLikebname(Map map);
    List<Book> like4(String bname, PageBean pageBean);
    List<Book> queryByMinMax(BookDto bookDto);
}
package com.zlj.biz.impl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zlj.biz.BookBiz;
import com.zlj.dto.BookDto;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 8:49
 */
public class BookBizImpl implements BookBiz {
    private  BookMapper bookMapper;
    public BookMapper getBookMapper() {
        return 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 BookMapper setBookMapper(BookMapper mapper) {
        return this.bookMapper = mapper;
    }
    @Override
    public List<Book> selectByBids(List bids) {
        return bookMapper.selectByBids(bids);
    }
    @Override
    public List<Book> like1(String bname) {
        return bookMapper.like1(bname);
    }
    @Override
    public List<Book> like2(String bname) {
        return bookMapper.like2(bname);
    }
    @Override
    public List<Book> like3(String bname) {
        return bookMapper.like3(bname);
    }
    @Override
    public List<Book> list01() {
        return bookMapper.list01();
    }
    @Override
    public List<Book> list02() {
        return bookMapper.list02();
    }
    @Override
    public Map list03(Map map){
    return bookMapper.list03(map);
    }
    @Override
    public List<Map> list04(Map map){
    return bookMapper.list04(map);
    }
    @Override
    public String bname1(Integer bid) {
        return bookMapper.bname1(bid);
    }
    @Override
    public List<String> bname2(String bname) {
        return bookMapper.bname2(bname);
    }
    @Override
    public List<Book> selectByLikebname(Map map) {
        return bookMapper.selectByLikebname(map);
    }
    @Override
    public List<Book> like4(String bname, PageBean pageBean) {
        if (pageBean!=null&&pageBean.isPagination()) {
            PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
        }
        List<Book> books = bookMapper.like4(bname);
        if (pageBean!=null && pageBean.isPagination()) {
            PageInfo<Book> info = new PageInfo<>(books);
            System.out.println("当前页:" + info.getPageNum());
            System.out.println("展示记录数:" + info.getPageSize());
            System.out.println("符合查询的总页数:" + info.getTotal());
            pageBean.setTotal((int)info.getTotal());
        }
        return books;
    }
    @Override
    public List<Book> queryByMinMax(BookDto bookDto) {
        return bookMapper.queryByMinMax(bookDto);
    }
}
package com.zlj.demo;
import com.zlj.biz.impl.BookBizImpl;
import com.zlj.dto.BookDto;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.PageBean;
import com.zlj.utils.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 9:07
 */
public class demo1 {
    private SqlSession sqlSession;
    private BookBizImpl bookBiz;
    @Before
    public void a(){
        System.out.println("执行测试方法之前会执行的初始化代码块");
        sqlSession= SessionUtil.openSession();
        BookBizImpl bookBiz=new BookBizImpl();
        BookMapper mapper=sqlSession.getMapper(BookMapper.class);
        bookBiz.setBookMapper(mapper);
        this.bookBiz=bookBiz;
    }
    @After
    public void b(){
        System.out.println("执行测试方法之后会执行");
        sqlSession.commit();
    }
    @Test
    public void test1(){
        System.out.println("测试方法。。。");
        Book book = bookBiz.selectByPrimaryKey(33);
        System.out.println(book);
    }
@Test
    public void test2(){
        bookBiz.deleteByPrimaryKey(34);
    }
@Test
public void testById(){
    List<Integer> bids = Arrays.asList(new Integer[]{56, 57, 58, 59, 60});
    bookBiz.selectByBids(bids).forEach(System.out::print);//jdk1.8新特性
//    for (Book selectByBid : bookBiz.selectByBids(bids)) {
//        System.out.println(selectByBid);
//    }
}
    @Test
    public void testlike1(){
        bookBiz.like1("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike2(){
        bookBiz.like2("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike3(){
        bookBiz.like3("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist01(){
        bookBiz.list01().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist02(){
        bookBiz.list02().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist03(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        System.out.println(bookBiz.list03(map));
    }
    @Test
    public void testlist04(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        bookBiz.list04(map).forEach(System.out::print);
    }
    @Test
    public void testbname1(){
        System.out.println(bookBiz.bname1(66));
    }
    @Test
    public void testbname2(){
        bookBiz.bname2("圣墟").forEach(System.out::print);
    }
    @Test
    public void selectByLikebname(){
        Map map=new HashMap();
        map.put("bname","%圣墟%");
        map.put("start",30);
        map.put("size",10);
        this.bookBiz.selectByLikebname(map).forEach(System.out::println);
    }
    @Test
    public void testlike04(){
        PageBean pageBean=new PageBean();
        pageBean.setPage(2);
        pageBean.setRows(20);
//        pageBean.setPagination(false);  //不分页
        bookBiz.like4("圣墟",pageBean).forEach(System.out::println);
    }
    @Test
    public void minmax(){
        BookDto dto=new BookDto();
        dto.setMin(41);
        dto.setMax(60);
        bookBiz.queryByMinMax(dto).forEach(System.out::println);
    }
}

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8天前
|
SQL 数据库
小课堂 -- 绕过去除特殊字符的sql注入
小课堂 -- 绕过去除特殊字符的sql注入
9 0
|
2月前
|
SQL Oracle 关系型数据库
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
整合Mybatis-Plus高级,Oracle 主键Sequence,Sql 注入器实现自定义全局操作
78 0
|
7天前
|
SQL XML Java
【JavaEE进阶】 MyBatis之动态SQL
【JavaEE进阶】 MyBatis之动态SQL
|
10天前
|
XML 监控 druid
【Java专题_02】springboot+mybatis+pagehelper分页插件+druid数据源详细教程
【Java专题_02】springboot+mybatis+pagehelper分页插件+druid数据源详细教程
33 0
|
10天前
|
存储 SQL 数据安全/隐私保护
Mybatis-Plus 拦截SQL语句实现加解密存储
Mybatis-Plus 拦截SQL语句实现加解密存储
17 0
|
12天前
|
SQL Java 数据库连接
MyBatis 的 3 种分页方式
MyBatis 的 3 种分页方式
85 1
MyBatis 的 3 种分页方式
|
25天前
|
SQL
sql语句将数字格式修改为字符
sql语句将数字格式修改为字符
|
27天前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
16 0
|
1月前
|
SQL Java 关系型数据库
|
2月前
|
SQL 缓存 Java
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
58 0
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句