SubQuery using EntityQuery
I am trying to preform a lookup on a database table using EntityQuery where there is a subquery. Using MySQL, I am able to get the records I am wanting using the following SQL.
select * from (SELECT o.* FROM artwork.owner o order by o.numberId, o.purchasedOn desc) as owner group by owner.numberId;
When I place the code in my EntityQuery in java, it does not like the parenthesis.
Questions:
1. Do subqueries need to be in the where clause?
2. Would it be better to not use EntityQuery, in order to use my own query?
Here is a little more information about my database tables.
Edition.java will have multiple numbers(Number.java) for the edition. Each number has an owner(Owner.java) list, which keeps a record of each owner for each number.
For example: Say edition number 100 has had 3 owners: Smith, Jones and Thomas. Thomas is the current owner. When I view the owner Jones and his current listing of items he owns, there should not be anything. When I view Thomas and his current listing of items, I should see the item he owns.
Edition.java
@Entity
public class Edition implements Serializable {
private static final long serialVersionUID = -1742264939963952856L;
// Field names which can be accessed from other programs.
public static final String FIELD_ID = "id";
public static final String FIELD_VERSION = "version";
public static final String FIELD_TO_ITEM = "itemId";
public static final String FIELD_TO_MEDIUMCODE = "mediumCodeId";
public static final String FIELD_TO_EDITIONCODE = "editionCodeId";
public static final String FIELD_TO_ENUM_LIMITED = "limited";
public static final String FIELD_TOTAL = "total";
public static final String FIELD_TO_RESERVE = "reserveId";
//public static final String FIELD_REMAINING = "remaining";
public static final String FIELD_SIGNATURE = "signature";
//seam-gen attributes (you should probably edit these)
private Long id;
private Integer version;
//add additional entity attributes
private Item item;
private Code mediumCode;
private Code editionCode;
private BooleanType limited;
private Long total;
private String signature;
private List<Number> numbers = new ArrayList<Number>(0);
// TODO: Add price listing.
//private List<Price> prices = new ArrayList<Price>(0);
public Edition() {
// Do not predefine any members here or it will cause problems on listing screens.
// Define them in the Home class.
}
//seam-gen attribute getters/setters with annotations (you probably should edit)
@Id @GeneratedValue
@Column(name = FIELD_ID)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Version
@Column(name = FIELD_VERSION)
public Integer getVersion() {
return version;
}
@SuppressWarnings("unused")
private void setVersion(Integer version) {
this.version = version;
}
/**
* @return the item
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_ITEM, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_Edition_to_ItemId")
public Item getItem() {
return item;
}
/**
* @param item the item to set
*/
public void setItem(Item item) {
this.item = item;
}
/**
* @return the medium
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_MEDIUMCODE, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_MediumCode_to_CodeId")
public Code getMediumCode() {
return mediumCode;
}
/**
* @param medium the medium to set
*/
public void setMediumCode(Code mediumCode) {
this.mediumCode = mediumCode;
}
/**
* @return the edition
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_EDITIONCODE, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_EditionCode_to_CodeId")
public Code getEditionCode() {
return editionCode;
}
/**
* @param edition the edition to set
*/
public void setEditionCode(Code editionCode) {
this.editionCode = editionCode;
}
/**
* This defines if this edition has an unlimited number of prints.
* true - this edition is limited to the amount set in FIELD_TOTAL.
* false - this edition is unlimited. Setting this to false will
* disable FIELD_TOTAL and will not display the print number.
*
* @return the limited
*/
@Enumerated
@Column(name = FIELD_TO_ENUM_LIMITED)
public BooleanType getLimited() {
return limited;
}
/**
* @param limited the limited to set
*/
public void setLimited(BooleanType limited) {
this.limited = limited;
}
/**
* @return the size
*/
@Min(value=0)
@Column(name = FIELD_TOTAL)
public Long getTotal() {
return total;
}
/**
* @param size the size to set
*/
public void setTotal(Long total) {
this.total = total;
}
/**
* @return the signature
*/
@Column(name = FIELD_SIGNATURE)
public String getSignature() {
return signature;
}
/**
* @param signature the signature to set
*/
public void setSignature(String signature) {
this.signature = signature;
}
/**
* @return the numbers
*/
@OneToMany(cascade = CascadeType.ALL, mappedBy = "edition", fetch = FetchType.LAZY)
public List<Number> getNumbers() {
return numbers;
}
/**
* @param numbers the numbers to set
*/
public void setNumbers(List<Number> numbers) {
this.numbers = numbers;
}
@Transient
public Boolean getIsLimited() {
return getLimited().equals(BooleanType.TRUE);
}
}
Number.java
@Entity
public class Number implements Serializable {
private static final long serialVersionUID = -1975151947717832408L;
// Field names which can be accessed from other programs.
public static final String FIELD_ID = "id";
public static final String FIELD_VERSION = "version";
public static final String FIELD_SEQUENCE = "sequence";
public static final String FIELD_SUBSEQUENCE = "subSequence"; // Duplicate sequence number.
// public static final String FIELD_TO_ITEM = "itemId";
public static final String FIELD_TO_EDITION = "editionId";
// TODO: Status: stolen, destroyed, reserved (when reserved numbers are assigned), available, owned and unknown.
public static final String FIELD_STATUS = "status";
// seam-gen attributes (you should probably edit these)
private Long id;
private Integer version;
private Long sequence;
private Integer subsequence;
private Edition edition;
private List<Owner> owners = new ArrayList<Owner>(0);
private NumberStatusType status;
@Id
@GeneratedValue
@Column(name = FIELD_ID)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Version
@Column(name = FIELD_VERSION)
public Integer getVersion() {
return version;
}
@SuppressWarnings("unused")
private void setVersion(Integer version) {
this.version = version;
}
/**
* @return the editionNumber. Every edition number starts with 1.
*/
@Min(value=1)
@Column(name = FIELD_SEQUENCE)
public Long getSequence() {
return sequence;
}
/**
* @param editionNumber
* the editionNumber to set
*/
public void setSequence(Long sequence) {
this.sequence = sequence;
}
/**
* @return the subsequence. For each print this will be set to 1.
* This number is incremented for each duplicate.
*/
@Min(value=1)
@Column(name = FIELD_SUBSEQUENCE)
public Integer getSubSequence() {
return subsequence;
}
/**
* @param sequence the sequence to set
*/
public void setSubSequence(Integer subsequence) {
this.subsequence = subsequence;
}
/**
* @return the edition
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_EDITION, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_Number_to_EditionId")
public Edition getEdition() {
return edition;
}
/**
* @param edition
* the edition to set
*/
public void setEdition(Edition edition) {
this.edition = edition;
}
/**
* @return the owners
*/
@OneToMany(cascade = CascadeType.ALL, mappedBy = "number", fetch = FetchType.LAZY)
public List<Owner> getOwners() {
return owners;
}
/**
* @param owners the owners to set
*/
public void setOwners(List<Owner> owners) {
this.owners = owners;
}
/**
* @return the status
*/
@Enumerated
@Column(name = FIELD_STATUS)
public NumberStatusType getStatus() {
return status;
}
/**
* @param status the status to set
*/
public void setStatus(NumberStatusType status) {
this.status = status;
}
}
Owner.java
@Entity
public class Owner implements Serializable {
private static final long serialVersionUID = -8147201531690492865L;
// Field names.
public static final String FIELD_ID = "id";
public static final String FIELD_VERSION = "version";
public static final String FIELD_TO_NUMBER = "numberId";
public static final String FIELD_TO_UZER = "uzerId";
public static final String FIELD_PURCHASEDON = "purchasedOn";
// seam-gen attributes (you should probably edit these)
private Long id;
private Integer version;
private Number number;
private Date purchasedOn;
private Uzer uzer;
// add additional entity attributes
// seam-gen attribute getters/setters with annotations (you probably should
// edit)
@Id
@GeneratedValue
@Column(name = FIELD_ID)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@Version
@Column(name = FIELD_VERSION)
public Integer getVersion() {
return version;
}
@SuppressWarnings("unused")
private void setVersion(Integer version) {
this.version = version;
}
/**
* @return the number
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_NUMBER, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_Owner_to_NumberId")
public Number getNumber() {
return number;
}
/**
* @param number the number to set
*/
public void setNumber(Number number) {
this.number = number;
}
/**
* @return the purchasedOn
*/
@Column(name = FIELD_PURCHASEDON)
public Date getPurchasedOn() {
return purchasedOn;
}
/**
* @param purchasedOn the purchasedOn to set
*/
public void setPurchasedOn(Date purchasedOn) {
this.purchasedOn = purchasedOn;
}
/**
* @return the owner
*/
@ManyToOne
@JoinColumn(name = FIELD_TO_UZER, referencedColumnName = "id", nullable = false)
@org.hibernate.annotations.ForeignKey(name = "fk_Owner_to_UzerId")
public Uzer getUzer() {
return uzer;
}
/**
* @param owner the owner to set
*/
public void setUzer(Uzer uzer) {
this.uzer = uzer;
}
}
