JPA 用自定义实体接收查询返回

一、创建对应的实体

@Data
public class OrderStatusDTO implements Serializable {

	@ExcelProperty(value = "表头1", index = 0)
	private String area;

	@ExcelProperty(value = "表头2", index = 1)
	private String apart;

	@ExcelProperty(value = "表头3", index = 2)
	private String clientName;

	@ExcelProperty(value = "表头4", index = 3)
	private String orderStateText;

	@ExcelIgnore
	private byte orderState;

	@ExcelIgnore
	private String orderId;

	@ExcelIgnore
	private Date createDate;

	@ExcelIgnore
	private BigInteger orderCount;

	public String getStatusCount() {
		return  this.orderCount.toString();
	}

	@ExcelProperty(value = "表头5", index = 4)
	private String statusCount;

	@Override
	public String toString() {
		return "OrderInfo{" +
				"orderId=" + orderId +
				"statusCount=" + statusCount +
				'}';
	}

}

 

 

二、自定义查询，查询字段的别名和实体对应即可

String sql = "SELECT order_id as orderId, create_date as createDate,area as area, apart, " +
                "client_name as clientName, order_state as orderState," +
				"count(1) as orderCount from order_info where %s GROUP BY client_name,order_state limit %s,%s";

sql = String.format(sql, getWhereClause(criteria), offset, pageable.getPageSize());

// 用自定义实体接收，保证字段和数据类型一致
List<OrderStatusDTO> orderInfoList1 = entityManager.createNativeQuery(sql).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(OrderStatusDTO.class)).list();
             

三、可以使用非原生语句，并且给实体实现构造函数，来进行接收

@Data
public class OrderTest implements Serializable {

    public OrderTest(String orderId, String erpOrder) {
        this.orderId = orderId;
        this.erpOrder = erpOrder;

    }

    private String orderId;

    private String erpOrder;

}

四、自定义原生查询

@Query(value = "select new OrderTest(o.orderId, o.erpOrder) from OrderInfo o  where o.orderId = ?1", nativeQuery = false)
OrderTest selectTest(String orderId);