Jpa分页查询

1.model

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Entity
@Data
@ApiModel
@Table(name = "user")
public class User implements Serializable {
    @ApiModelProperty(value="用户id")
    @Id
    @GeneratedValue
    private Long id;

    @ApiModelProperty(value="会员编码")
    private Long code;

    @ApiModelProperty(value="姓名")
    private String userName;

    @ApiModelProperty(value="手机")
    private String phone;

    @ApiModelProperty(value="公司")
    private String company;

    @ApiModelProperty(value="联系地址")
    private String address;

    @ApiModelProperty(value="注册类型：1-个人 2-企业")
    private Integer type;

    @ApiModelProperty(value="创建时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    @ApiModelProperty(value="认证状态：0-未认证 1-已认证")
    private Integer pass;

    @ApiModelProperty(value="会员级别")
    @ManyToOne
    private UserLevel userLevel;

    @ApiModelProperty(value="会员状态 0-过期 1-有效")
    private Integer state;

    @ApiModelProperty(value="会员入会时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date enrolTime;

    @ApiModelProperty(value="会员到期时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date expireTime;
}

2.repository

import com.oigcn.association.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User,Long> {
    //分页查询用户名
    @Query(value = "SELECT * FROM user WHERE 1=1 AND (userName=:userName OR :userName IS NULL) AND (pass=:pass OR :pass IS NULL)",countQuery = "SELECT COUNT(*) FROM user WHERE (userName=:userName OR :userName IS NULL) AND (pass=:pass OR :pass IS NULL)",nativeQuery = true)
    Page<User> findUserByQuery(Pageable pageable,@Param("userName") String userName,@Param("pass") Integer pass);
    //分页查询用户
    @Query("select u from User u where 1=1 AND (userName=:#{#user.userName} OR :#{#user.userName} IS NULL) AND (pass=:#{#user.pass} OR :#{#user.pass} IS NULL)")
    Page<User> findUserBy(Pageable pageable,@Param("user") User user);
}

3.service

import com.oigcn.association.model.User;
import org.springframework.data.domain.Page;

public interface UserService {
    Page<User> page(int pageNo,int pageSize,String userName,Integer pass);
    Page<User> pageAll(int pageNo,int pageSize,User user);
}

import com.oigcn.association.model.User;
import com.oigcn.association.repository.UserRepository;
import com.oigcn.association.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;

@Service
@Transactional
public class UserServiceImpl implements UserService {
    private final UserRepository userRepository;

    @Autowired
    public UserServiceImpl(UserRepository userRepository) {
        super();
        this.userRepository = userRepository;
    }
    @Override
    public Page<User> page(int pageNo,int pageSize,String userName,Integer pass) {
        Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序
        Pageable pageable = PageRequest.of(pageNo - 1,pageSize,sort);
        Page<User> page = this.userRepository.findUserByQuery(pageable,userName,pass);
        return page;
    }

    @Override
    public Page<User> pageAll(int pageNo, int pageSize, User user) {
        Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序
        Pageable pageable = PageRequest.of(pageNo - 1,pageSize,sort);
        Page<User> page = this.userRepository.findUserBy(pageable,user);
        return page;
    }
}

4.action

import com.oigcn.association.model.User;
import com.oigcn.association.service.UserService;
import com.oigcn.association.utils.Response;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/user")
@Slf4j
@Api(tags = "会员管理")
public class UserAction {
    @Autowired
    private UserService userService;

    @GetMapping("/page")
    @ApiOperation(value = "会员管理分页查询")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "pageNo",value = "分页开始,默认1"),
            @ApiImplicitParam(name = "pageSize", value = "一页大小,默认50"),
            @ApiImplicitParam(name = "userName", value = "用户名",required = true)
    })
    public Response page(@RequestParam(defaultValue = "1") int pageNo,@RequestParam(defaultValue = "50") int pageSize,@RequestParam(required = false) String userName,@RequestParam(required = false) Integer pass ){
        log.info("用户管理分页查询pageNo={},pageSize={},userName={},pass={}",pageNo,pageSize,userName,pass);
        return Response.success(this.userService.page(pageNo,pageSize,userName,pass));
    }
    @PostMapping("/pageAll")
    public Response pageAll(@RequestParam(defaultValue = "1") int pageNo,@RequestParam(defaultValue = "50") int pageSize,@ModelAttribute User user){
        log.info("用户管理分页查询user={}",user);
        return Response.success(this.userService.pageAll(pageNo,pageSize,user));
    }
}