每个User
都有一个Box
。每个Box
可能含有一些Item
。
我的架构是:
Item
:
@Entity
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Key key;
@ManyToOne(fetch=FetchType.LAZY)
@Column(allowsNull = "false")
@Unowned
private Box location;
...
Box
:
@Entity
public class Box {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Key key;
@OneToMany(mappedBy="location", cascade={CascadeType.ALL})
private List<Item> items;
...
User
:
@Entity
public class User{
@Id
@Column(name="USER_ID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Key key;
@OneToOne(fetch=FetchType.LAZY, cascade={CascadeType.REMOVE})
@Column(allowsNull = "false")
@JoinColumn(name="BOX_ID")
private Box box;
如果这个结构有什么错误,请告诉我。
现在我想检索属于User
的所有Item
,最好使用javax.persistence.EntityManager
,但我根本做不到。
我想做的是:
Query q1 = em.createQuery("select u from User u where u.username='"+username+"'");
User user = // ... well known code
Query q2 = em.createQuery("select i from Item i where i.location='"+user.box+"'");
Item item = // ... well known code
现在我收到了这个异常:
SELECT u FROM database.entities.Item u WHERE u.location.key='database.entities.Box@3872bb09': Can only reference properties of a sub-object if the sub-object is embedded.
首先,您不应该使用连接将动态参数传递给查询。这是低效的,如果参数包含一个单引号,将失败,并使您的代码受到SQL注入攻击。使用命名参数:
select ... where u.username = :name
...
query.setParameter("name", username);
现在看第二个查询。它搜索与Box实例的toString()值相等的位置项。这说不通啊。您必须将该框作为参数传递:
Query q2 = em.createQuery("select i from Item i where i.location = :box");
q2.setParameter("box", user.getBox());
请注意,您不需要两个查询。一个就足够了:
select item from User user
inner join user.box box
inner join box.items item
where user.username = :name