时间戳

首页 > 技术文章 > mybatis 拦截器 分页sql

mybatis 拦截器 分页sql

lewskay 2017-11-30 18:26 原文

项目结构基于SSM结构：已经搭建成型

项目结构如下

 

简单的建一个用户表  user

 

构建mvc三层结构  

 创建实体类

public class User { //实体类
    private Integer id;

    private String name;

    private Integer age;

    private Date createDate;

    private String createBy;

    private Date updateDate;

    private String updateBy;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getCreateBy() {
        return createBy;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy == null ? null : createBy.trim();
    }

    public Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy;
    }
//重写一下tostring方便打印
@Override
public String toString() {
    return "User{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", age=" + age +
            ", createDate=" + createDate +
            ", createBy='" + createBy + '\'' +
            ", updateDate=" + updateDate +
            ", updateBy='" + updateBy + '\'' +
            '}';
}
}

　　创建Dao层接口

public interface UserMapper { 　　//Dao层接口
    int deleteByPrimaryKey(Integer id);

    int insert(User record);

    int insertSelective(User record);

    User selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(User record);

    int updateByPrimaryKey(User record);
}

　　创建dao层的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.xpds.cloud.mapper.UserMapper" >
  <resultMap id="BaseResultMap" type="com.xpds.cloud.entity.User" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
    <result column="create_date" property="createDate" jdbcType="TIMESTAMP" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="update_date" property="updateDate" jdbcType="TIMESTAMP" />
    <result column="uodate_by" property="uodateBy" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, name, age, create_date, create_by, update_date, uodate_by
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.xpds.cloud.entity.User" >
    insert into user (id, name, age, 
      create_date, create_by, update_date, 
      uodate_by)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, 
      #{createDate,jdbcType=TIMESTAMP}, #{createBy,jdbcType=VARCHAR}, #{updateDate,jdbcType=TIMESTAMP}, 
      #{uodateBy,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.xpds.cloud.entity.User" >
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="age != null" >
        age,
      </if>
      <if test="createDate != null" >
        create_date,
      </if>
      <if test="createBy != null" >
        create_by,
      </if>
      <if test="updateDate != null" >
        update_date,
      </if>
      <if test="uodateBy != null" >
        uodate_by,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
      <if test="createDate != null" >
        #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null" >
        #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="updateDate != null" >
        #{updateDate,jdbcType=TIMESTAMP},
      </if>
      <if test="uodateBy != null" >
        #{uodateBy,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.xpds.cloud.entity.User" >
    update user
    <set >
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="createDate != null" >
        create_date = #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="createBy != null" >
        create_by = #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="updateDate != null" >
        update_date = #{updateDate,jdbcType=TIMESTAMP},
      </if>
      <if test="uodateBy != null" >
        uodate_by = #{uodateBy,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.xpds.cloud.entity.User" >
    update user
    set name = #{name,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER},
      create_date = #{createDate,jdbcType=TIMESTAMP},
      create_by = #{createBy,jdbcType=VARCHAR},
      update_date = #{updateDate,jdbcType=TIMESTAMP},
      uodate_by = #{uodateBy,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

　　简单些一下controller  和service  打印一下查询     http://localhost:8082/user/get/id=1

查看打印结果   成功查询 出来了  

 

然后写一个列表查询函数

controller      查询sql      

 看一看数据库的数据

快看一下查询结果

 

创建一个分页基础类  然后用实体类进行继承

/**
 * 分页基础类
 */
public class BaseEntity {

    private Integer pageSize=10;    //分页行数
    private Integer pageNum =1;     //页码数
    private Integer startNum;       //启启示页码数

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getStartNum() {
        return pageNum-1;
    }
}

看一下传统分页写法 sql

  <select id="findPage" resultMap="BaseResultMap" >
    select
    <include refid="Base_Column_List" />
    from user  limit #{startNum},#{pageSize}
  </select>

　　但是这里我们不这么写：

先创建一个基础分页接口

 

 修改一下我们的持久层接口

 

 改一下我们的分页sql   不包含分页

 

推荐阅读