AI 助理
备案控制台
开发者社区 数据库 文章 正文

动态sql和分页下（mybatis的分页及特殊字符）

2023-11-30 103
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
云数据库 RDS PostgreSQL，高可用系列 2核4GB
简介： 动态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);
    }
}

文章标签：
云数据库 RDS MySQL 版
Java
mybatis
数据库连接
SQL
关键词：
MyBatis分页
MyBatis sql
SQL字符
SQL分页
SQL mybatis
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
朱琳均
目录
相关文章
t2ssulhocemlc
|
19天前
|
SQL Java 数据库连接
MyBatis分页
MyBatis作为Java持久层框架，需结合数据库特性或插件实现分页。分页分为物理分页（如MySQL的LIMIT）和逻辑分页（内存截取），推荐使用PageHelper插件自动注入分页语句，提升开发效率与性能。需注意索引优化、深分页问题及多表关联时的兼容性，结合业务场景选择合适方案。
t2ssulhocemlc
63 4
蓝易云
|
2月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言，通过MyBatis的XML配置实现灵活的动态SQL查询，可以让开发者以声明式的方式构建SQL语句，既保证了SQL操作的灵活性，又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
蓝易云
167 18
鱼的爱情看不出泪水
|
3月前
|
SQL
SQL中如何将一列中的值显示出字符指定位置与指定长度
在对比系统生日与身份证号时，如何提取特定位置的值？例如，从身份证第7位开始取8位数字，可用SQL的`SUBSTRING`函数实现。
鱼的爱情看不出泪水
118 48
游客kufrkwrbkmpsa
|
7月前
|
SQL Java 数据库连接
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
游客kufrkwrbkmpsa
112 1
wanderInk
|
6月前
|
SQL Java 数据库连接
MyBatis动态SQL字符串空值判断，这个细节99%的程序员都踩过坑！
本文深入探讨了MyBatis动态SQL中字符串参数判空的常见问题。通过具体案例分析，对比了`name != null and name != &#39;&#39;`与`name != null and name != &#39; &#39;`两种写法的差异，指出后者可能引发逻辑混乱。为避免此类问题，建议在后端对参数进行预处理（如trim去空格），简化MyBatis判断逻辑，提升代码健壮性与可维护性。细节决定成败，严谨处理参数判空是写出高质量代码的关键。
wanderInk
724 0
Floweroath
|
2月前
|
SQL Java 数据库连接
SSM相关问题-1--#{}和${}有什么区别吗？--Mybatis都有哪些动态sql？能简述一下动 态sql的执行原理吗？--Spring支持的几种bean的作用域 Scope
在MyBatis中，`#{}`是预处理占位符，可防止SQL注入，适用于大多数参数传递场景；而`${}`是直接字符串替换，不安全，仅用于动态表名、列名等特殊场景。二者在安全性、性能及使用场景上有显著区别。
Floweroath
61 0
Quan_Chen
|
7月前
|
SQL Java 数据库连接
微服务——MyBatis分页
本文介绍了分页的多种实现方式，包括自带RowBounds分页、第三方插件PageHelper分页、SQL分页、数组分页及拦截器分页。其中，RowBounds是先查询全部结果再内存分页；PageHelper通过修改SQL动态添加分页关键字；SQL分页依赖数据库自身的分页功能如`LIMIT`；数组分页则是查询全量数据后用`subList`方法截取；拦截器分页则统一在SQL后添加分页语句。最后总结逻辑分页适合小数据量，但大数据量易内存溢出；物理分页虽小数据量效率较低，但更适合大数据场景，优先推荐使用。
Quan_Chen
92 0
游客kufrkwrbkmpsa
|
7月前
|
SQL Oracle 关系型数据库
【YashanDB知识库】Mybatis-Plus调用YashanDB怎么设置分页
【YashanDB知识库】Mybatis-Plus调用YashanDB怎么设置分页
游客kufrkwrbkmpsa
69 0
蓝易云
|
5月前
|
SQL Java 数据安全/隐私保护
发现问题：Mybatis-plus的分页总数为0，分页功能失效，以及多租户插件的使用。
总的来说，使用 Mybatis-plus 确实可以极大地方便我们的开发，但也需要我们理解其工作原理，掌握如何合适地使用各种插件。分页插件和多租户插件是其中典型，它们的运用可以让我们的代码更为简洁、高效，理解和掌握好它们的用法对我们的开发过程有着极其重要的意义。
蓝易云
521 15
Bluening
|
5月前
|
SQL XML Java
菜鸟之路Day35一一Mybatis之XML映射与动态SQL
本文介绍了MyBatis框架中XML映射与动态SQL的使用方法，作者通过实例详细解析了XML映射文件的配置规范，包括namespace、id和resultType的设置。文章还对比了注解与XML映射的优缺点，强调复杂SQL更适合XML方式。在动态SQL部分，重点讲解了`&lt;if&gt;`、`&lt;where&gt;`、`&lt;set&gt;`、`&lt;foreach&gt;`等标签的应用场景，如条件查询、动态更新和批量删除，并通过代码示例展示了其灵活性与实用性。最后，通过`&lt;sql&gt;`和`&lt;include&gt;`实现代码复用，优化维护效率。
Bluening
373 5

热门文章

最新文章

  • 1
    MyBatis 的映射关系
  • 2
    MyBatis的常见配置
  • 3
    MyBatis分页
  • 4
    MyBatis 持久层框架技术详解与实践指南
  • 5
    【万字长文，建议收藏】《高性能ODPS SQL章法》——用古人智慧驾驭大数据战场
  • 6
    阿里云数据库RDS费用价格：MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
  • 7
    通过 SQL 快速使用 OceanBase 向量检索学习笔记
  • 8
    生成更智能，调试更轻松，SLS SQL Copilot 焕新登场！
  • 9
    SQL Server 2025 RC1 发布 - 从本地到云端的 AI 就绪企业数据库
  • 10
    阿里云数据库收费价格：MySQL、PostgreSQL、SQL Server和MariaDB引擎费用整理
  • 1
    CTE vs 子查询：深入拆解PostgreSQL复杂SQL的隐藏性能差异
    334
  • 2
    GitHub 热门！MindsDB 破解 AI + 数据库瓶颈，究竟有什么惊艳亮点？只需 SQL 即可实现智能预测
    767
  • 3
    什么！我把SQL编辑器装进了大模型？
    754
  • 4
    实现MySQL与SQL Server之间数据迁移的有效方法
    336
  • 5
    菜鸟之路Day35一一Mybatis之XML映射与动态SQL
    373
  • 6
    SQL Server 2025 - 从本地到云端的 AI 就绪企业数据库
    301
  • 7
    狂揽20.2k星！还在傻傻的写SQL吗，那你就完了！这款开源项目，让数据分析像聊天一样简单？再见吧SQL
    219
  • 8
    【SQL周周练】：利用行车轨迹分析犯罪分子作案地点
    130
  • 9
    SQL在线美化工具
    379
  • 10
    Dataphin V5.0：支持创建异步调用API，实现慢 SQL 复杂计算的直连消费
    175

    • 相关课程

    更多
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询
  • MyBatis持久层框架入门

    • 相关电子书

    更多
  • SQL Server云化思考与实践
  • SQL Server在电子商务中的应用与实践
  • Java Spring Boot开发实战系列课程【第6讲】：Spring Boot 2.0实战MyBatis与优化(Java面试题)
    • 下一篇
    阿里云免费云服务器:个人用户每月750小时免费