我可以在JPA中使用从派生表中进行选择吗? [英] Can I use select from derived tables with JPA?
问题描述
我有一个表'asset',它与一个名为'asset_properties'的表具有1到n的关系,该表包含该资产的属性列表,以及一个多对多关系(使用中间表'asset_has_tag')与包含标签列表的表标签".
I have a table 'asset' that has a 1 to n relation with a table called 'asset_properties' that contains a list of properties of that asset, and a many to many (using an intermediate table 'asset_has_tag') relation with table 'tags' that contains a list of tags.
我需要获取同时具有某些特定标记和某些属性值的资产列表.
I need to get a list of assets that have BOTH some specific tags AND some property values.
如果我需要带有一些标签或某些属性的资产,则可以将以下jpa查询的两个结果都添加到java.util.Set中.
If I needed assets that have EITHER some tags OR some properties I could simply add both results of the following jpa queries to a java.util.Set.
使用以下查询,可以使用本机SQL获得所需的信息.
I can get what I want with native SQL using the following query.
SELECT a.*
FROM (SELECT ap.*
FROM asset ap JOIN asset_property p
WHERE p.value LIKE "%asd%" OR ap.name LIKE "%asd%" OR ap.description LIKE "%asd%"
) a
JOIN asset_has_tag r, tag h
WHERE a.uuid = r.asset_id AND h.uuid=r.tag_id AND h.category IN ("asd", "qwe", "zxc")
GROUP BY a.uuid
JPA查询:
String findByAssetAndTagValues =
"select distinct(a) from Asset a join a.Tags h where a.name like :assetname or a.description like :assetdescription and h.name in :tagnames and h.category in :tagcategories and h.uuid=:taguuids"
String findAssetsWithPropertyByValue =
"select distinct(a) from Asset a join a.assetProperties p where p.value like :value"
实体(删除了空的构造方法,getter和setter方法)
@Entity
public class Asset implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(generator = "myUUID")
@GenericGenerator(name="myUUID", strategy="uuid2")
@Column(unique = true, nullable = false, length = 16)
private UUID uuid;
private String description;
// bi-directional many-to-one association to assetProperty
@OneToMany(mappedBy = "asset", fetch = FetchType.LAZY)
private List<AssetProperty> assetProperties;
// bi-directional many-to-many association to tag
@ManyToMany(mappedBy = "assets", fetch = FetchType.LAZY)
private Set<tag> tags;
@Override
public boolean equals(Object obj) {
return (obj != null && obj instanceof Asset && ((Asset)obj).getUuid().equals(uuid));
}
}
@Entity
public class AssetProperty implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(unique=true, nullable=false)
private int id;
@Column(nullable=false, length=255)
private String name;
@Column(length=512)
private String value;
//bi-directional many-to-one association to Asset
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="asset_id", nullable=false)
private Asset asset;
}
@Entity
@Table(name = "hardtag")
public class Hardtag implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
@Column(unique = true, nullable = false)
private UUID uuid;
@Column(length = 255)
private String category;
@Column(nullable = false, length = 255)
private String name;
// bi-directional many-to-many association to Asset
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "asset_has_tag", joinColumns = { @JoinColumn(name = "tag_id", nullable = false) }, inverseJoinColumns = { @JoinColumn(name = "asset_id", nullable = false) })
private Set<Asset> assets;
@Override
public boolean equals(Object obj) {
return obj instanceof Hardtag && ((Hardtag) obj).getUuid().equals(uuid);
}
}
JPA还不支持它吗?
推荐答案
从派生表中选择(或在FROM子句中具有子查询). builds/1.2.3/apache-openjpa/docs/jpa_langref.html#jpa_langref_subqueries"rel =" nofollow> JPA .
Selecting from derived tables (or having subqueries in the FROM clause) is not currently supported by JPA.
这篇关于我可以在JPA中使用从派生表中进行选择吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!