1:项目结构图
2:TUser.java
/** * @Author ZGM * @DateTime 2021/12/31 * @description */ @Data @Entity @Table(name = "t_user") @AllArgsConstructor @NoArgsConstructor @DynamicUpdate @DynamicInsert public class TUser { @Id @JsonSerialize(using = ToStringSerializer.class) private Long id; @Column(name = "user_name") private String userName; @Column(name = "pass_word") @JsonIgnore private String passWord; @Column(name = "nick_name") private String nickName; @Column(name = "sex") private String sex; @Column(name = "age") private Integer age; @Column(name = "status") private String status; @Column(name = "create_time") private Date createTime; @Column(name = "update_time") private Date updateTime; @PreUpdate protected void onUpdate() { updateTime = new Date(); } @PrePersist protected void onCreate() { createTime = new Date(); updateTime = new Date(); } @JsonIgnore @Version @Column(name = "version") private int version; @Column(name = "role") private String role; }
3:UserInfo.java
@Data @Entity @Table(name = "user_info") public class UserInfo { @Id @JsonSerialize(using = ToStringSerializer.class) private Long id; private String profession; private String grade; private String dormitory; }
4:实体类对应的数据库接口
TUserRepository
@Repository public interface TUserRepository extends JpaRepository<TUser, Long>, JpaSpecificationExecutor<TUser> { }
UserInfoRepository
@Repository public interface UserInfoRepository extends JpaRepository<UserInfo, Long>, JpaSpecificationExecutor<UserInfo> { @Query(value = "select new com.example.vo.UserVo(info.id, user.userName, info.profession, info.grade) from UserInfo info left join TUser user on info.id = user.id" + " where ( info.profession = ?1 or ?1 is null) " + " and ( info.grade = ?2 or ?2 is null)") Page<UserVo> allUser(String profession, String grade, Pageable pageable); }
5: UserVo.java
@Data @AllArgsConstructor public class UserVo { Long id; String userName; String profession; String grade; }
6: UserService.java
@Service @RequiredArgsConstructor @RefreshScope public class UserService extends KeyGenService { private final UserInfoRepository infoRepository; public Page<UserVo> list(UserQueryVo vo){ Pageable pageable = PageRequest.of(vo.getPage(),vo.getSize()); return infoRepository.allUser(vo.getProfession(),vo.getGrade(), pageable); } }
7: UserController.java
@RestController @RequestMapping("/v1/user") @RequiredArgsConstructor public class UserController { private final UserService userService; @PostMapping("/list") public Page<UserVo> list(@RequestBody UserQueryVo vo){ return userService.list(vo); } }
8:配置文件根据自己的情况编写即可,我这里用的是nacos配置中心注册的服务,因为我船舰的是微服务架构,UserService只是一个用户模块的服务
9:单元测试
@Test public void ervsd(){ UserQueryVo vo = new UserQueryVo(); vo.setPage(0); vo.setSize(15); System.out.println(userService.list(vo).getContent()); }
结果:
[UserVo(id=123, userName=null, profession=null, grade=null), UserVo(id=575698132303290368, userName=17856419339, profession=计算机, grade=1704), UserVo(id=575716833031299072, userName=17856419331, profession=计算机, grade=1704), UserVo(id=576000665194274816, userName=17856419332, profession=计算机, grade=1703), UserVo(id=576000781602988032, userName=17856419333, profession=计算机, grade=1703), UserVo(id=576000899173523456, userName=17856419334, profession=计算机, grade=1702), UserVo(id=576001007231377408, userName=17856419335, profession=计算机, grade=1702), UserVo(id=576001227939848192, userName=17856419336, profession=计算机, grade=1701), UserVo(id=576001356105195520, userName=17856419337, profession=计算机, grade=1701), UserVo(id=576001504025714688, userName=17856419338, profession=计算机, grade=1701), UserVo(id=576001653640732672, userName=17856419310, profession=计算机, grade=1701), UserVo(id=576001828031504384, userName=17856419311, profession=计算机, grade=1704), UserVo(id=576337740011610112, userName=17856419312, profession=计算机, grade=1703)] 共13条数据,表里面也就13条,sql里要求了好多参数,我现在没传,它就默认没这个条件,即查询的是全部
现在加一些参数
@Test public void ervsd(){ UserQueryVo vo = new UserQueryVo(); vo.setPage(0); vo.setSize(15); vo.setProfession("计算机"); vo.setGrade("1701"); System.out.println(userService.list(vo).getContent()); }
结果:
[UserVo(id=576001227939848192, userName=17856419336, profession=计算机, grade=1701), UserVo(id=576001356105195520, userName=17856419337, profession=计算机, grade=1701), UserVo(id=576001504025714688, userName=17856419338, profession=计算机, grade=1701), UserVo(id=576001653640732672, userName=17856419310, profession=计算机, grade=1701)] 共四条数据,出了分页参数,还加了两个条件专业和班级,它就自动加进去了,是不是很简洁
这种写法比传统的specification或者nativesql拼接字符串查询都要简洁,代码量少,通俗易懂。