Hi,
I want to apply a multiselect query to an Entity with a OneToMany Relation. The Entity looks like this:
Code:
@Entity
@Table(name = "TRAVEL_ENTITY")
public class TravelEntity {
// instance data
private UUID uuid;
private String description = "";
private Set<MatterOfExpenseEntity> mattersOfExpense = new HashSet<MatterOfExpenseEntity>();
@Type(type = "com.hrs.svl.travel.commons.type.usertypes.UUIDUserType")
@Id
@Column(name = "UUID", length = 36)
public UUID getUuid() {
return this.uuid;
}
public void setUuid(UUID uuid) {
this.uuid = uuid;
}
@Column(name = "DESCRIPTION", length = 100)
public String getDescription() {
return this.description;
}
public void setDescription(String description) {
this.description = description;
}
@OneToMany(mappedBy = "travel", cascade = CascadeType.ALL)
public Set<MatterOfExpenseEntity> getMattersOfExpense() {
return this.mattersOfExpense;
}
public void setMattersOfExpense(Set<MatterOfExpenseEntity> mattersOfExpense) {
this.mattersOfExpense = mattersOfExpense;
}
}
@Entity
@Table(name = "MATTER_OF_EXPENSE_ENTITY")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "TID", discriminatorType = DiscriminatorType.STRING)
public class MatterOfExpenseEntity {
// instance data
private UUID uuid;
private TravelEntity travel;
// public
@Type(type = "com.hrs.svl.travel.commons.type.usertypes.UUIDUserType")
@Id
@Column(name = "UUID", length = 36)
@Attribute(primaryKey = true, index = 1)
public UUID getUuid() {
return this.uuid;
}
public void setUuid(UUID uuid) {
this.uuid = uuid;
}
@ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, optional = false, targetEntity = TravelEntity.class)
@JoinColumn(name = "OR_TRAVEL")
@Relation(target = TravelEntity.class, inverse = TravelEntity.MATTERS_OF_EXPENSE, index = 3)
public TravelEntity getTravel() {
return this.travel;
}
public void setTravel(TravelEntity travel) {
this.travel = travel;
}
}
The query looks like this:
Code:
CriteriaBuilder cb = jpa.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root from = cq.from(TravelEntity.class);
cq.multiselect(from.get("mattersOfExpense").alias("MATTER"), from.get("uuid").alias("ID"));
Predicate pr = cb.equal(cb.literal(UUID.fromString("3f1302f7-1f2f-11e1-94ec-78acc0b04e2e")), from.get("uuid"));
cq.from(TravelEntity.class);
cq.where(pr);
Object o3 = jpa.createQuery(cq).getResultList();
This query results in a following SQL-Query which starts with the following select-clause:
Code:
select . as col_0_0_,
This is obversely not valid SQL-Code (the dot after the select is nonsense) and the execution of the query results in the following exception:
Code:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as col_0_0_
The problem seems to be the OneToMany relation because if I change this to OneToOne relation everything is fine.
What is wrong with my query?
Is there an alternative way to generate the query?
Thanks for your help
Stephan