I am trying to return a set of objects that are not part of a collection like this:
TypedQuery<Extra> q = em
.createQuery(
"SELECT e FROM Extra e, Arrangement a WHERE "
+ "a.id = :arrid AND e NOT MEMBER OF a.extras",
Extra.class);
q.setParameter("arrid", a.getId());
return q.getResultList();
But I get a bad SQL out of this:
Caused by: org.h2.jdbc.JdbcSQLException: Subquery is not a single column query; SQL statement:
select extra0_.code as code427_, extra0_.edition_number as edition5_427_,
extra0_.totalCost as totalCost427_, extra0_.unitCost as unitCost427_,
extra0_.units as units427_ from extra_arrangements extra0_
cross join arrangements arrangemen1_
where arrangemen1_.id=? and
((extra0_.code, extra0_.edition_number) not in
(select extra3_.code, extra3_.edition_number
from arrangements_extra_arrangements extras2_, extra_arrangements extra3_
where arrangemen1_.id=extras2_.arrangements_id and
extras2_.extras_code=extra3_.code and
extras2_.extras_edition_number=extra3_.edition_number)) [90052-161]
The thing is that this other query works as expected:
TypedQuery<Exhibitor> q = em.createQuery(
"SELECT e FROM Exhibitor e, Edition ed WHERE ed.number = :ednum AND "
+ "e NOT MEMBER OF ed.exhibitors",
Exhibitor.class);
q.setParameter("ednum", e.getNumber());
return q.getResultList();
The only difference being that Exhibitor has a single column id, whereas Extra has a composite key:
@Entity
@Table(name = "exhibitors")
public class Exhibitor implements Serializable, Comparable<Exhibitor> {
@Id
private int id;
@Column(length = 100)
private String code;
@ManyToMany
private List<Edition> edition;
...
}
Extra:
@Entity
@Table(name="extra_arrangements")
public class Extra implements Serializable {
@EmbeddedId
private ExtraCode id;
private double unitCost;
private double units;
...
}
@Embeddable
public class ExtraCode implements Serializable {
private String code;
@ManyToOne
private Edition edition;
//Getters and setters omitted
}
Any idea what I might be doing wrong and how to fix it?
I think it's same problem as this one.
H2 does not currently support that style of IN query.
Problematic place in sql-query:
AND ( ( extra0_.code, extra0_.edition_number ) NOT IN (SELECT ...) )