用户高级查询带分页

Mapper层开发

动态sql, 条件动态

条件封装成一个类

sql: 第一条: 查询总记录数

第二条: 当前页记录

/**
     * 查询满足条件的记录数
     * @param userCondition  条件类
     * @return
     */
    int count(@Param("uc") UserCondition userCondition);


    /**
     * 高级查询带分页
     * @param userCondition
     * @return
     */
    List<User> queryByPage(@Param("uc") UserCondition userCondition);

<sql id="column_sql">
         id,name,gender,age,address,email,qq,photo,version
     </sql>

    <select id="count" parameterType="UserCondition" resultType="int">
        select count(1) from tb_user <include refid="where_sql"/>
    </select>

    <select id="queryByPage"   parameterType="UserCondition" resultType="User">
        select <include refid="column_sql"/> from tb_user
           <include refid="where_sql"/>
           <!--分页-->
          <if test="uc!=null">
            <if test="uc.startNo != null and uc.pageSize != null">
                limit #{uc.startNo},#{uc.pageSize}
            </if>
          </if>
    </select>

    <sql id="where_sql">
        <where>
            <if test="uc!=null">
                <if test="uc.name != null and uc.name !=''">
                    and name like '%${uc.name}%'
                </if>
                <if test="uc.gender != null and uc.gender !=''">
                    and gender = #{uc.gender}
                </if>
                <if test="uc.startAge != null">
                    and age &gt;= #{uc.startAge}
                </if>
                <if test="uc.endAge != null">
                    and age &lt;= #{uc.endAge}
                </if>
            </if>
        </where>
    </sql>

Service层开发

创建一个分页实体类, 一个方法, 返回一个Page对象

package com.fs.user.vo;

import lombok.Data;

import java.util.List;

/**
 * 分页实体类
 * @param <T>
 */
@Data
public class Page<T> {
    private Integer size;//页容量
    private Integer current;//当前页码
    private Integer total;//总记录数
    private List<T> records;//当前页记录

    /**
     * 返回总页数
     */
    public int getTotalPages(){
        int pages = total / size;
        return total % size==0?pages:pages+1;
    }

}

 业务层方法

 @Override
    public Page<User> findByPage(UserVO userVO,Integer current, Integer pageSize) {
        //创建一个分页对象
        Page<User> page = new Page<>(pageSize,current);
        //把UserVO 转换为UserCondition
        UserCondition userCondition = new UserCondition();
        //属性拷贝
        BeanUtil.copyProperties(userVO,userCondition);
        userCondition.setStartNo((current-1)*pageSize);
        userCondition.setPageSize(pageSize);

        page.setTotal(userMapper.count(userCondition));
        page.setRecords(userMapper.queryByPage(userCondition));

        return page;
    }

Controller层开发

package com.fs.user.web;

import com.fs.user.pojo.User;
import com.fs.user.service.UserService;
import com.fs.user.util.ResponseData;
import com.fs.user.util.ResponseDataUtil;
import com.fs.user.vo.Page;
import com.fs.user.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("users")
public class UserController {
    @Autowired
    private UserService userService;


    @GetMapping("{current}/{pageSize}")
    public ResponseData<Page<User>> findUsers(UserVO userVO, @PathVariable("current") Integer pageIndex,@PathVariable("pageSize")  Integer pageSize){
        Page<User> page = userService.findByPage(userVO, pageIndex, pageSize);
        return ResponseDataUtil.buildOk(page);
    }
}