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拼接字符串查询都要简洁,代码量少,通俗易懂。