java - 从查询中获取结果作为自定义对象列表
问题描述
我想为显示过去 10 天每天的交易量的条形图实现 SQL 查询。例如我有这个表结构:
CREATE TABLE `payment_transactions` (
`id` int(11) NOT NULL,
`amount` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
);
我得到这个示例结果(每天分组):
Date | Amount| Number of transactions per day |
11-11-2018 | 30 | 3 |
11-12-2018 | 230 | 13 |
JPA 查询:
public List<DashboardDTO> findAll() {
String hql = "SELECT date(created_at) AS cdate, sum(amount) AS amount, count(id) AS nooftransaction "
+ "FROM payment_transactions WHERE date(created_at)>=date(now()- interval 10 DAY) "
+ "AND date(created_at)<date(now()) GROUP BY date(created_at)";
TypedQuery<DashboardDTO> query = entityManager.createQuery(hql, Merchants.class);
List<DashboardDTO> data = query.getResultList();
return data;
}
Java 对象:
public class DashboardDTO {
private Date date;
private int amount;
private int number_of_transactions;
public DashboardDTO(Date date, int amount, int number_of_transactions) {
this.date = date;
this.amount = amount;
this.number_of_transactions = number_of_transactions;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
public int getNumber_of_transactions() {
return number_of_transactions;
}
public void setNumber_of_transactions(int number_of_transactions) {
this.number_of_transactions = number_of_transactions;
}
}
如何正确实施查询?我想在List<DashboardDTO>
不使用实体的情况下获得结果?
解决方案
简单的方法是将@Entity
,添加@Id
到您DashboardDTO
的本地 sql 别名中,并使您的本地 sql 别名与 DTO 的属性名称匹配。
@Entity
public class DashboardDTO {
@Id
private Date cdate;
private int amount;
private int numberOfTransactions;
...
}
public List<DashboardDTO> findAll() {
String hql = "SELECT ... AS cdate, ... AS amount, ... AS number_of_transactions FROM ...";
return entityManager.createNativeQuery(hql, DashboardDTO.class).getResultList();
}
推荐阅读
- batch-file - 想要创建文件夹并在其上放置图像,从带有脚本的 .txt 中读取其路径
- android - Gradle 同步失败:找不到带有哈希字符串“android-28”的目标
- java - 如何根据最后一列的最大值对多个字符串的数组列表进行排序?
- php - php curl 与我的服务器 IP 地址执行 sftp 连接
- expression - SQL Server Reporting:如何根据同一列的先前计算值计算值?
- spring - 如何在 JPA、HIBERNATE 中使用多个 WHERE 子句?
- python - 异步任务取消。是同步的吗?
- angular-akita - 如何将多个模型查询存储到单个状态存储状态管理
- core-data - 有没有办法解决这个错误:“CloudKit 集成需要不支持有序关系。”
- php - Laravel 查询生成器,根据条件在哪里?