hibernate - 如何使用条件创建查询
问题描述
我有一个名为 Vote 的课程:
@Table(name = "PHY_VOTE")
public class VoteEntity extends BaseEntity<Long> implements Comparable {
public static final String TITLE = "title";
public static final String VALUE = "value";
public static final String USER = "user";
private String title;
private String value;
private UserEntity user;
public VoteEntity() {}
public VoteEntity(Long id) {
setId(id);
}
public VoteEntity(String title, String value, UserEntity user) {
this.title = title;
this.value = value;
this.user = user;
}
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "VOTE_ID")
@Override
public Long getId() {
return super.getId();
}
@FilterProperty(operation = FilterProperty.ILIKE)
@Column(name = "TITLE", nullable = false, length = 100)
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@FilterProperty(operation = FilterProperty.ILIKE)
@Column(name = "VALUE", nullable = false, length = 4)
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
@ManyToOne
@JoinColumn(name = "USER_ID", foreignKey = @ForeignKey(name = "PYH_VOTE_USER_FK"))
public UserEntity getUser() {
return user;
}
public void setUser(UserEntity user) {
this.user = user;
}
@Transient
@Override
public String getOptionsTitleProperty() {
return TITLE;
}
}
如何使用 Criteria 创建以下命令?
选择 t.TITLE , ROUND(SUM(t.VALUE) / COUNT(t.TITLE) , 2) 来自 PHY_VOTE t GROUP BY t.TITLE 的值
解决方案
您可以考虑使用FluentJPA进行操作:
public AverageByTitle getAverageByTitle() {
FluentQuery query = FluentJPA.SQL((VoteEntity t) -> {
Alias<Number> avg = alias(ROUND(SUM(INT.raw(t.getValue())) / COUNT(t.getTitle()), 2),
AverageByTitle::getAverage);
SELECT(t.getTitle(), avg);
FROM(t);
GROUP(BY(t.getTitle()));
});
return query.createQuery(em, AverageByTitle.class).getSingleResult();
}
// declarations
public static final DataType<Integer> INT = DataTypes.INT;
@Tuple
@Data // lombok
public static class AverageByTitle {
private String title;
private float average;
}
这会产生以下 SQL:
SELECT t0.TITLE, ROUND((SUM(t0.VALUE) / COUNT(t0.TITLE)), 2) AS average
FROM PHY_VOTE t0
GROUP BY t0.TITLE
推荐阅读
- mongodb - 集群 Mongodb 4.x 多文档事务的问题
- magento - 增加 Magento 2.3.5 错误消息的图像名称的最大长度:(未上传。文件名太长;必须为 90 个字符或更少)
- mysql - 将远程 CSV 文件导入 MySQL 数据库 - 脚本
- python - riSSL:CERTIFICATE_VERIFY_FAILED 证书验证失败 (_ssl.c:852)
- python - 如果相关,我如何将变量的值组合在一起?
- c# - 请求模型与响应模型不同时的 ASP.NET Core 验证
- r - 在 MacBookAir 上使用 file.choose() 选择多个文件不起作用
- flask - 使用来自 Flask 的 Ray - init() 失败(带有核心转储)
- apache-flink - 使用 flink runner 时如何在 apache Beam 中执行检查点?
- c++ - 基类是依赖类型时是否有缺陷