JPA左联接查询 [英] JPA left join query
问题描述
给出以下两个表:
CREATE TABLE `x` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name_hash` char(32) NOT NULL,
`access_time` bigint(20) unsigned NOT NULL,
`name` varchar(1024) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_hash` (`name_hash`),
KEY `access_time` (`access_time`),
CONSTRAINT `x_ibfk_1` FOREIGN KEY (`access_time`) REFERENCES `update_time` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `y` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`x` bigint(20) unsigned NOT NULL,
`update_time` bigint(20) unsigned NOT NULL,
`reason` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `x` (`x`,`update_time`),
KEY `reason` (`reason`),
KEY `update_time` (`update_time`),
CONSTRAINT `y_ibfk_1` FOREIGN KEY (`reason`) REFERENCES `reason` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `y_ibfk_2` FOREIGN KEY (`x`) REFERENCES `x` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `y_ibfk_3` FOREIGN KEY (`update_time`) REFERENCES `update_time` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我使用NetBeans创建了以下JPA类(X和Y不是真实名称，以为我做了所有必需的更改):
I used NetBeans to create the following JPA classes (X and Y are not the real names, think I did all of the required changes):
@Entity
@Table(name = "X", catalog = "topiclymobile", schema = "", uniqueConstraints = {
@UniqueConstraint(columnNames = {"name_hash"})})
@NamedQueries({
@NamedQuery(name = "X.findAll", query = "SELECT t FROM X t"),
@NamedQuery(name = "X.findById", query = "SELECT t FROM X t WHERE t.id = :id"),
@NamedQuery(name = "X.findByNameHash", query = "SELECT t FROM X t WHERE t.nameHash = :nameHash"),
@NamedQuery(name = "X.findByName", query = "SELECT t FROM X t WHERE t.name = :name")})
public class X implements Serializable
{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id", nullable = false)
private Long id;
@Basic(optional = false)
@Column(name = "name_hash", nullable = false, length = 32)
private String nameHash;
@Basic(optional = false)
@Column(name = "name", nullable = false, length = 1024)
private String name;
@JoinColumn(name = "access_time", referencedColumnName = "id", nullable = false)
@ManyToOne(optional = false)
private UpdateTime accessTime;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "X")
private List<Y> YList;
public X() {
}
public X(Long id) {
this.id = id;
}
public X(Long id, String nameHash, String name) {
this.id = id;
this.nameHash = nameHash;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getNameHash() {
return nameHash;
}
public void setNameHash(String nameHash) {
this.nameHash = nameHash;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public UpdateTime getAccessTime() {
return accessTime;
}
public void setAccessTime(UpdateTime accessTime) {
this.accessTime = accessTime;
}
public List<Y> getYList() {
return YList;
}
public void setYList(List<Y> YList) {
this.YList = YList;
}
@Override
public int hashCode() {
int hash = 5;
hash = 89 * hash + (this.nameHash != null ? this.nameHash.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final X other = (X) obj;
if ((this.nameHash == null) ? (other.nameHash != null) : !this.nameHash.equals(other.nameHash)) {
return false;
}
return true;
}
}
@Entity
@Table(name = "Y", catalog = "topiclymobile", schema = "", uniqueConstraints = {
@UniqueConstraint(columnNames = {"X", "update_time"})})
@NamedQueries({
@NamedQuery(name = "Y.findAll", query = "SELECT t FROM Y t"),
@NamedQuery(name = "Y.findById", query = "SELECT t FROM Y t WHERE t.id = :id")})
public class Y implements Serializable
{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id", nullable = false)
private Long id;
@JoinColumn(name = "reason", referencedColumnName = "id", nullable = false)
@ManyToOne(optional = false)
private Reason reason;
@JoinColumn(name = "X", referencedColumnName = "id", nullable = false)
@ManyToOne(optional = false)
private X X;
@JoinColumn(name = "update_time", referencedColumnName = "id", nullable = false)
@ManyToOne(optional = false)
private UpdateTime updateTime;
public Y() {
}
public Y(Long id) {
this.id = id;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Reason getReason() {
return reason;
}
public void setReason(Reason reason) {
this.reason = reason;
}
public X getX() {
return X;
}
public void setX(X X) {
this.X = X;
}
public UpdateTime getUpdateTime() {
return updateTime;
}
public void setUpdateTime(UpdateTime updateTime) {
this.updateTime = updateTime;
}
@Override
public int hashCode() {
int hash = 7;
hash = 13 * hash + (this.X != null ? this.X.hashCode() : 0);
hash = 13 * hash + (this.updateTime != null ? this.updateTime.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final Y other = (Y) obj;
if (this.X != other.X && (this.X == null || !this.X.equals(other.X))) {
return false;
}
if (this.updateTime != other.updateTime && (this.updateTime == null || !this.updateTime.equals(other.updateTime))) {
return false;
}
return true;
}
}
在所有给定时间内"x"没有对应的"y"(access_time和update_time是同一件事)的情况下.
What I am after is all of the cases that "x" does not have a corresponding "y" for a given time (access_time and update_time are the same thing).
此SQL查询有效，我似乎无法将其转换为JPA查询:
This SQL query works, I just cannot seem to translate it into an JPA query:
SELECT t.id FROM x t LEFT JOIN y r ON t.id = r.x WHERE r.x IS NULL AND t.access_time = 1
推荐答案
查看您的实体类以构造实际查询会有所帮助，但是JPA确实支持 LEFT JOIN .此博客文章有一个完整的示例，此问题也有喜欢
It'd be helpful to see your entity classes to construct the actual query, but JPA does support LEFT JOINs. This blog post has a full example, as does this question, but something like
SELECT x FROM X x LEFT JOIN x.y ...
我不确定查询的其余部分应该是什么，因为您发布的内容看起来不像是有效的SQL(您有 WHERE rx IS NULL ，但是给定的架构在表y上定义了x表示为 NOT NULL ；类似地，使用 WHERE rx IS NULL 应该使您的左连接不匹配，因为 t.id = rx 总是会求值到 NULL ).
I'm not sure what the rest of the query should be as what you posted does not look like valid SQL (you have WHERE r.x IS NULL, but the schema given defines x on table y as NOT NULL; similarly, having WHERE r.x IS NULL ought to make your left join match nothing, since t.id = r.x would always evaluate to NULL).
我仍然对您的示例SQL如何有效查询感到困惑，但是类似这样的东西似乎应该转换为您提供的SQL:
I'm still confused as to how your sample SQL is a valid query, but something like this seems like it ought to translate into the SQL you provided:
SELECT x FROM X x LEFT JOIN x.yList y where y.x IS NULL and x.accessTime = :accessTime
其中:accessTime 参数是 entityManager.getReference(UpdateTime.class，1)的值.
不过，同样，x.id = yx上的 FROM x LEFT JOIN y yx IS NULL 应该与Y中的行完全不匹配，而(因为它是 LEFT JOIN )，它将包含X中的所有行.换句话说，我认为您的查询等同于:
Again, though, the FROM x LEFT JOIN y on x.id = y.x WHERE y.x IS NULL should match precisely no rows in Y, whereas (since it's a LEFT JOIN), it will include all the rows in X. In other words, I think your query is equivalent to:
SELECT x.id FROM X where x.access_time = 1
在JPA中会是这样的
SELECT x FROM X x where x.accessTime = :accessTime
这篇关于JPA左联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
