Spring Data JPA 框架使用自定义（@Query）的查询语句返回自定义的实体对象踩坑记录

使用Spring Data Jpa，根据业务需求，有时候需要进行复杂的数据查询，并返回我们需要的字段（例如分类统计，目前框架下不需好实现），而在该框架下，目前仅仅支持返回数据库映射进行持久化的实体类（使用注解@Entity的类）。虽然在框架上我们可以使用@Query注解执行我们自定义的sql语句，但是其返回值为：List<Object[]> 类型，多个Object[]的List集合。

下面介绍一个我用着比较顺手的方法

自定义的返回实体类

package com.jemcocloud.saas.service.zwwl.dto;

import java.io.Serializable;

public class CountDevicesDto implements Serializable {
	private static final long serialVersionUID = 1L;

	private String status;
	private Long num;
	
	public CountDevicesDto() {
	}

	public CountDevicesDto(String status, Long num) {
		this.status = status;
		this.num = num;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public Long getNum() {
		return num;
	}

	public void setNum(Long num) {
		this.num = num;
	}

	@Override
	public String toString() {
		return "CountDevicesDto [status=" + status + ", num=" + num + "]";
	}
}

实体类（Entity）

 

package com.*********.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import com.*********.constants.DaoConstant;


@Entity
@Table(name = DaoConstant.DEVICE_TABLE_NAME, uniqueConstraints = {@UniqueConstraint(columnNames = {DaoConstant.DEVICE_ICCID})})
public class Device {
	private static final long serialVersionUID = 1L;
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	protected Long id;

	@Column(name = DaoConstant.DEVICE_ICCID)
	private String iccid;
	@Column(name = DaoConstant.DEVICE_IMSI)
	private String imsi;
	@Column(name = DaoConstant.DEVICE_MSISDN)
	private String msisdn;
	@Column(name = DaoConstant.DEVICE_IMEI)
	private String imei;
	@Column(name = DaoConstant.DEVICE_STATUS)
	private String status;
	@Column(name = DaoConstant.DEVICE_RATEPLAN)
	private String ratePlan;
	@Column(name = DaoConstant.DEVICE_COMMUNICATIONPLAN)
	private String communicationPlan;
	@Column(name = DaoConstant.DEVICE_DATEADDED)
	private String dateAdded;
	@Column(name = DaoConstant.DEVICE_DATEUPDATED)
	private String dateUpdated;
	@Column(name = DaoConstant.DEVICE_DATESHIPPED)
	private String dateShipped;
	@Column(name = DaoConstant.DEVICE_ACCOUNTID)
	private String accountId;
	@Column(name = DaoConstant.DEVICE_TOTAL_DATA)
	private Long totalData;
	@Column(name = DaoConstant.DEVICE_TOTAL_DATA_UNIT)
	private String totalDataUnit;
	@Column(name = DaoConstant.DEVICE_TYPE)
	private String type;
	@Column(name = DaoConstant.DEVICE_DEALER)
	private String dealer;
	@Column(name = DaoConstant.DEVICE_CUSTOMER)
	private String customer;
	
	public String getIccid() {
		return iccid;
	}

	public void setIccid(String iccid) {
		this.iccid = iccid;
	}

	public String getImsi() {
		return imsi;
	}

	public void setImsi(String imsi) {
		this.imsi = imsi;
	}

	public String getMsisdn() {
		return msisdn;
	}

	public void setMsisdn(String msisdn) {
		this.msisdn = msisdn;
	}

	public String getImei() {
		return imei;
	}

	public void setImei(String imei) {
		this.imei = imei;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getRatePlan() {
		return ratePlan;
	}

	public void setRatePlan(String ratePlan) {
		this.ratePlan = ratePlan;
	}

	public String getCommunicationPlan() {
		return communicationPlan;
	}

	public void setCommunicationPlan(String communicationPlan) {
		this.communicationPlan = communicationPlan;
	}

	public String getDateAdded() {
		return dateAdded;
	}

	public void setDateAdded(String dateAdded) {
		this.dateAdded = dateAdded;
	}

	public String getDateUpdated() {
		return dateUpdated;
	}

	public void setDateUpdated(String dateUpdated) {
		this.dateUpdated = dateUpdated;
	}

	public String getDateShipped() {
		return dateShipped;
	}

	public void setDateShipped(String dateShipped) {
		this.dateShipped = dateShipped;
	}

	public String getAccountId() {
		return accountId;
	}

	public void setAccountId(String accountId) {
		this.accountId = accountId;
	}

	public Long getTotalData() {
		return totalData;
	}

	public void setTotalData(Long totalData) {
		this.totalData = totalData;
	}

	public String getTotalDataUnit() {
		return totalDataUnit;
	}

	public void setTotalDataUnit(String totalDataUnit) {
		this.totalDataUnit = totalDataUnit;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getDealer() {
		return dealer;
	}

	public void setDealer(String dealer) {
		this.dealer = dealer;
	}

	public String getCustomer() {
		return customer;
	}

	public void setCustomer(String customer) {
		this.customer = customer;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Device() {
	}
	
	public Device(DeviceDto dto) {
		this.iccid = dto.getIccid();
		this.imsi = dto.getImsi();
		this.msisdn = dto.getMsisdn();
		this.imei = dto.getImei();
		this.status = dto.getStatus();
		this.ratePlan = dto.getRatePlan();
		this.communicationPlan = dto.getCommunicationPlan();
		this.dateAdded = dto.getDateAdded();
		this.dateUpdated = dto.getDateUpdated();
		this.dateShipped = dto.getDateShipped();
		this.accountId = dto.getAccountId();
		this.totalData = dto.getTotalData();
		this.totalDataUnit = dto.getTotalDataUnit();
		this.type = dto.getType();
		this.dealer = dto.getDealer();
		this.customer=dto.getCustomer();
	}

	@Override
	public String toString() {
		return "Device [iccid=" + iccid + ", imsi=" + imsi + ", msisdn=" + msisdn + ", imei=" + imei + ", status="
				+ status + ", ratePlan=" + ratePlan + ", communicationPlan=" + communicationPlan + ", dateAdded="
				+ dateAdded + ", dateUpdated=" + dateUpdated + ", dateShipped=" + dateShipped + ", accountId="
				+ accountId + ", totalData=" + totalData + ", totalDataUnit=" + totalDataUnit + ", type=" + type
				+ ", dealer=" + dealer + ", customer=" + customer + ", id=" + id + "]";
	}
}

DAO层的JPA处理接口类：

public interface DeviceRepository extends JpaRepository<Device, Long>, JpaSpecificationExecutor<Device> {
	
	@Query("select new com.*********.dto.CountDevicesDto(status, count(*) as num) from Device where type = :type group by status")
	List<CountDevicesDto> findByTypeGroupByStatus(@Param("type")String type);

    关于DAO层的JPA处理接口类，此处当时我被自己坑了。第一，此处引入的返回自定义实体类中，需要定义含有这两个参数的构造函数，详情见CountDevicesDto类。第二，from后面跟的表名需要修改成表的实体类的类名，也就是大写的Device。另外@Query里面的自定义查询语句可以写成：@Query(value = "select new com.**********.dto.CountDevicesDto(status, count(*) as num) from Device where type = :type group by status) ", nativeQuery = false)，两者作用是一样的。

 

如果大家不想用自定义的返回类也可以的。

public interface DeviceRepository extends JpaRepository<Device, Long>, JpaSpecificationExecutor<Device> {
	
	@Query("select new map(status, count(*) as num) from Device where type = :type group by status")
	List<Map<String, Object>> findByTypeGroupByStatus(@Param("type")String type);

本例中的map需要小写的map，具体原因没有深入追踪，有知道的可以回复联系，谢谢！