项目中使用springboot jpa进行持久层操作,大部分的操作不用手写sql。最近需要根据部门查询所有的用户,但是只需要用户id和用户userName两个字段,并不需要所有的字段。
一. 一般字段返回
1.首先需要自定义vo来接收字段,这里注意需要使用 @JsonProperty将属性与数据库字段对应上,并需要重写构造方法,不然会报错
@Data
public class UserDto {
/**
* 用户id
*/
@JsonProperty("id")
private Long userId;
/**
* 真实姓名
*/
@JsonProperty("real_name")
private String realName;
public UserDto(Long userId, String realName) {
this.userId = userId;
this.realName = realName;
}
}
2.jpaRespority中查询代码
@Query(value = "select new com.common.dto.UserDto("
+ "s.id,s.realName) FROM User s WHERE "
+ "s.departmentCode = ?1 AND s.locked = false AND s.del = false")
List<UserDto> findDepartment(String departmentCode);
其中User 为用户表实体,com.common.dto为UserDto的路径
二、sum,count等字段返回
这种字段没有对应的列明,在dto中无法定义JsonProperty,这时可以不指定列名。具体如下:
1.接收数据的dto格式及参数如下:
public class ExperimenterDetectionRecordDataDto {
/**
* 检测结果
*/
private Long sumDetection;
/**
* 检测费用
*/
private BigDecimal sumFee;
public ExperimenterDetectionRecordDataDto(Long sumDetection, BigDecimal sumFee) {
this.sumDetection = sumDetection;
this.sumFee = sumFee;
}
public Long getSumDetection() {
return sumDetection;
}
public BigDecimal getSumFee() {
return sumFee;
}
public void setSumDetection(Long sumDetection) {
this.sumDetection = sumDetection;
}
public void setSumFee(BigDecimal sumFee) {
this.sumFee = sumFee;
}
public ExperimenterDetectionRecordDataDto() {
}
}
2.jpaRepository中的sql
@Query(value = "SELECT new com.XXX.detection.model.entrust.ExperimenterDetectionRecordDataDto(SUM(detectionNumber), SUM(totalFee)) FROM ExperimenterDetectionRecord WHERE testerId IN ?1 AND del = FALSE AND completed = TRUE AND completeExperimentDate BETWEEN ?2 AND ?3 GROUP BY testerId")
List<ExperimenterDetectionRecordDataDto> findDetectionRecordAndTime(List<String> testId, Date startTime, Date endTime);
如果是有的是有字段,有的没有,也可以使用Object来接收,此时不需要定义dto,直接接收,jpaRepository代码如下:
@Query(value = "SELECT testerId, SUM(detectionNumber), SUM(totalFee) FROM ExperimenterDetectionRecord WHERE testerId IN ?1 AND del = FALSE AND completed = TRUE AND completeExperimentDate BETWEEN ?2 AND ?3 GROUP BY testerId")
List<Object> findDetectionRecordAndTime(List<String> testId, Date startTime, Date endTime);