使用JPA2按地图的键和值进行过滤

Question

对于定义如下的实体：

@Entity
public class Acl implements Serializable {

@Id
@GeneratedValue
@Column(name = "acl_id")
private Long id;

@ElementCollection(fetch=FetchType.EAGER)
@JoinTable(name = "acl_permits")
private Map<String, Integer> permits = new HashMap<String, Integer>();

查询：

query = em.createQuery("select a FROM Acl a " +
"JOIN a.permits p WITH KEY(p) = '" + user + "' and VALUE(p) = " + permit + "");

转换为：

select acl0_.acl_id as acl1_1_ from Acl acl0_ 
inner join acl_permits permits1_ on acl0_.acl_id=permits1_.Acl_acl_id 
and (permits1_.permits_KEY='user1' 
and (select permits1_.permits from acl_permits permits1_ where acl0_.acl_id=permits1_.Acl_acl_id)=1)

要将其转换为以下内容，查询应该是什么？

select acl0_.acl_id as acl1_1_ from Acl acl0_ 
inner join acl_permits permits1_ on acl0_.acl_id=permits1_.Acl_acl_id 
and (permits1_.permits_KEY='user1' and acl0_.acl_id=permits1_.Acl_acl_id=1)

Answer 1

您可以在JPA中使用原生SQL。所以你可以这样做。

query = em.createNativeQuery("select acl0_.acl_id as acl1_1_ from Acl acl0_ 
    inner join acl_permits permits1_ on acl0_.acl_id=permits1_.Acl_acl_id 
    and (permits1_.permits_KEY=?user and acl0_.acl_id=permits1_.Acl_acl_id=?permit_id)");