java - 通过 Hibernate Annotation 和 JPA 标准在一个公共列的基础上加入两个表 查询未按预期工作
问题描述
我有两张桌子device_data
和device_connection
. 我想根据 column 加入这两个表customerId
。两个表都有customerId
列。
设备数据
import java.io.Serializable;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.Objects;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
@Entity
@Table(name = "device_data")
public class DeviceData implements Serializable,Cloneable {
@Id
@Column(name = "identifier")
private BigInteger identifier;
@Id
@Column(name = "device_time")
private Timestamp deviceTime;
@Column(name = "ctr_id")
private BigInteger customerId;
@Column(name = "signal_strength")
private Double signalStrength;
@OneToOne
@JoinColumn(name = "ctr_id", nullable = false, insertable = false, updatable = false, referencedColumnName = "ctr_id")
private DeviceConnection deviceConnection;
public BigInteger getIdentifier() {
return identifier;
}
public DeviceData setIdentifier(BigInteger identifier) {
this.identifier = identifier;
return this;
}
public Timestamp getDeviceTime() {
return deviceTime;
}
public DeviceData setDeviceTime(Timestamp deviceTime) {
this.deviceTime = deviceTime;
return this;
}
public BigInteger getCustomerId() {
return customerId;
}
public DeviceData setCustomerId(BigInteger customerId) {
this.customerId = customerId;
return this;
}
public DeviceConnection getDeviceConnection() {
return deviceConnection;
}
public DeviceData setDeviceConnection(
DeviceConnection deviceConnection) {
this.deviceConnection = deviceConnection;
return this;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
DeviceData that = (DeviceData) o;
return identifier.equals(that.identifier) &&
deviceTime.equals(that.deviceTime);
}
@Override
public int hashCode() {
return Objects.hash(identifier, deviceTime);
}
}
设备连接
import java.io.Serializable;
import java.math.BigInteger;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;
@Entity
@Table(name = "device_connection")
public class DeviceConnection implements Serializable , Cloneable {
public DeviceConnection() {
}
@Column(name = "id")
private BigInteger id;
@Column(name = "ctr_id")
private BigInteger customerId;
@Column(name = "low_signal_strength_limit")
private Integer lowSignalStrengthLimit;
public BigInteger getCustomerId() {
return customerId;
}
public DeviceConnection setCustomerId(BigInteger customerId) {
this.customerId = customerId;
return this;
}
public Integer getLowSignalStrengthLimit() {
return lowSignalStrengthLimit;
}
public DeviceConnection setLowSignalStrengthLimit(
Integer lowSignalStrengthLimit) {
this.lowSignalStrengthLimit = lowSignalStrengthLimit;
return this;
}
public BigInteger getId() {
return id;
}
public DeviceConnection setId(BigInteger id) {
this.id = id;
return this;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
DeviceConnection that = (DeviceConnection) o;
return Objects.equals(getId(), that.getId());
}
@Override
public int hashCode() {
return Objects.hash(getId());
}
}
我写了一个查询来从这两个表的连接中获取数据。
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<DeviceData> cq = cb.createQuery(DeviceData.class);
Root<DeviceData> root = cq.from(DeviceData.class);
Join<DeviceData, DeviceConnection> join = (Join<DeviceData, DeviceConnection>) root
.fetch(DeviceData_.deviceConnection);
List<Predicate> conditions = new ArrayList<>();
conditions.add(cb.equal(root.get(DeviceData_.CUSTOMER_ID), join.get(
DeviceConnection_.CUSTOMER_ID)));
conditions.add(cb.greaterThanOrEqualTo(root.get(DeviceData_.DEVICE_TIME),
config.getDataStartTime()));
if (isNotNull(config.getDataEndTime())) {
conditions.add(cb.lessThanOrEqualTo(root.get(DeviceData_.DEVICE_TIME),
config.getDataEndTime()));
}
cq.where(conditions.toArray(new Predicate[]{}))
.orderBy(cb.asc(root.get(DeviceData_.DEVICE_TIME)));
return session.createQuery(cq);
我将属性 SHOW_SQL 设置为 TRUE。所以,这里是hibernate生成的查询。
select
devicedata0_.occurrence_time as occurren1_3_0_, devicedata0_.identifier as identifier2_3_0_, connection1_.id as id1_0_1_, devicedata0_.ctr_id as ctr_id9_3_0_, devicedata0_.signal_strength as signal_15_3_0_, connection1_.ctr_id as ctr_id7_0_1_, connection1_.low_signal_strength_limit as low_sig14_0_1_
from
device_data devicedata0_
inner join device_connection connection1_ on
devicedata0_.ctr_id=connection1_.ctr_id
where
devicedata0_.ctr_id=connection1_.ctr_id and
devicedata0_.created_at>='2018-06-12 12:00:00'
order by
devicedata0_.created_at asc
limit 2000;
当我在 My Sql 工作台上运行此查询时,它将按预期工作并给我结果。但是hibernate不断给我异常我不知道为什么?
例外
java.lang.ClassCastException:com.sbi.model.DeviceConnection 不能转换为 java.math.BigInteger
解决方案
推荐阅读
- kubernetes - 如何在 Kubernetes 的 Pod 内登录
- html - 图片高度与文章标签相同
- python - 保存为 html 时,jupyter notebook 不呈现图像/绘图
- angular - 承诺未在 Angular 9 应用程序的 IE11 中定义
- reactjs - 无法通过可访问名称找到警报
- php - 尝试在codeigniter php中上传多个调整大小的图像
- java - 如何在 Android/Java 中使用 AsyncTask 将 URI 传递给 API?
- python - Spyder - 如何在当前控制台中执行 python 脚本?
- reactjs - React 自定义钩子和 useMemo 钩子
- ios - 单击时执行特定操作的自定义 MKAnnotation 引脚 - 自定义引脚未显示