SubQuery using EntityQuery
sandman202 Sep 29, 2009 8:59 PMI 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; } }