Databases
StackOverflow2013
Postsdbo
POJPA CriteriaQuery projection with lists
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJPA CriteriaQuery projection with lists
    text
    copied!<p>I'm trying to make a query using a wrapper class as result type. Sorry for the long post but I want to make my question as complete as possible. The root class has 3 lists that I want to retrieve:</p> <pre><code>@Entity @Table(name = "cash") public final class Cash extends BaseSimpleModel { @Id @SequenceGenerator(name = "id", sequenceName = "cash_seq") @GeneratedValue(strategy = GenerationType.AUTO, generator = "id") private Long cashID; @Column(length = 50, unique = true) private String description; @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true) @JoinColumn(name = "cashID") private List&lt;CashAllowedValue&gt; allowedValueList; @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true) @JoinColumn(name = "cashID") private List&lt;CashAllowedCurrency&gt; allowedCurrencyList; @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true) @JoinColumn(name = "cashID") private List&lt;CashAllowedCashier&gt; allowedCashierList; ... getters and setters } </code></pre> <p>Here is my wrapper class:</p> <pre><code>public class CashQueryResult extends QueryResult { private Long cashID; private String description; private List&lt;CashAllowedValue&gt; allowedValueList; private List&lt;CashAllowedCurrency&gt; allowedCurrencyList; private List&lt;CashAllowedCashier&gt; allowedCashierList; public CashQueryResult(Long id, String description, List&lt;CashAllowedValue&gt; allowedValueList, List&lt;CashAllowedCurrency&gt; allowedCurrencyList, List&lt;CashAllowedCashier&gt; allowedCashierList) { this.cashID = id; this.description = description; this.allowedValueList = allowedValueList; this.allowedCurrencyList = allowedCurrencyList; this.allowedCashierList = allowedCashierList; } ... getters } </code></pre> <p>And here is my query:</p> <pre><code>public final List&lt;CashQueryResult&gt; getQRList(final CashQueryParameter cashQP, final QueryHint queryHint) { List&lt;CashQueryResult&gt; cashQRL = null; try { final CriteriaBuilder cb = em.getCriteriaBuilder(); final PredicateBuilder pb = new PredicateBuilder(cb); final CriteriaQuery&lt;CashQueryResult&gt; cq = cb.createQuery(CashQueryResult.class); final Root&lt;Cash&gt; cash = cq.from(getModelClass()); // Joins. final ListJoin&lt;Cash, CashAllowedValue&gt; allowedValueList = cash.join(Cash_.allowedValueList, JoinType.LEFT); final ListJoin&lt;Cash, CashAllowedCurrency&gt; allowedCurrencyList = cash.join(Cash_.allowedCurrencyList, JoinType.LEFT); final ListJoin&lt;Cash, CashAllowedCashier&gt; allowedCashierList = cash.join(Cash_.allowedCashierList, JoinType.LEFT); // Paths. final Path&lt;ValueTypeEnum&gt; valueType = allowedValueList.get(CashAllowedValue_.valueType); final Path&lt;Currency&gt; currency = allowedCurrencyList.get(CashAllowedCurrency_.currency); final Path&lt;IntranetUser&gt; intranetUser = allowedCashierList.get(CashAllowedCashier_.cashier); // Expressions. Just for testing purposes. final Expression&lt;List&lt;CashAllowedValue&gt;&gt; cashAllowedValueListExpression = cash.get(Cash_.allowedValueList); final Expression&lt;List&lt;CashAllowedCurrency&gt;&gt; cashAllowedCurrencyExpression = cash.get(Cash_.allowedCurrencyList); final Expression&lt;List&lt;CashAllowedCashier&gt;&gt; cashAllowedCashierExpression = cash.get(Cash_.allowedCashierList); cq.distinct(true); cq.select(cb.construct(CashQueryResult.class, cash.get(Cash_.cashID), cash.get(Cash_.description), // cash.get(Cash_.allowedValueList), cash.get(Cash_.allowedCurrencyList), cash.get(Cash_.allowedCashierList) // does not work // cashAllowedValueListExpression, cashAllowedCurrencyExpression, cashAllowedCashierExpression // does not work allowedValueList, allowedCurrencyList, allowedCashierList // does not work )); // cq.multiselect(cash.get(Cash_.cashID), cash.get(Cash_.description), // allowedValueList, allowedCurrencyList, allowedCashierList // ); // does not work cq.where(cb.and(pb.like(cash.get(Cash_.description), cashQP.getDescription()), pb.equal(valueType, cashQP.getValueType()), pb.equal(currency.get(Currency_.currencyID), cashQP.getCurrencyID()), pb.equal(intranetUser.get(IntranetUser_.agentID), cashQP.getIntranetUserID()))); cq.orderBy(cb.asc(cash.get(Cash_.description))); final TypedQuery&lt;CashQueryResult&gt; tq = em.createQuery(cq); tq.setFirstResult(queryHint.getFirstResult()); tq.setMaxResults(queryHint.getMaxResults()); cashQRL = tq.getResultList(); } catch (Throwable t) { throw new EJBException(t.getMessage()); } return cashQRL; } </code></pre> <p>Finally, the exception (it varies depending on what select method I try but it's always something like this):</p> <pre><code>2011-09-12 16:12:26,165 ERROR [org.hibernate.hql.PARSER] (http-127.0.0.1-8080-2) Unable to locate appropriate constructor on class [com.ebizlink.adonis.erp.model.support.CashQueryResult] [cause=org.hibernate.PropertyNotFoundException: no appropriate constructor in class: com.ebizlink.adonis.erp.model.support.CashQueryResult] 2011-09-12 16:12:26,169 SEVERE [javax.enterprise.resource.webcontainer.jsf.context] (http-127.0.0.1-8080-2) javax.ejb.EJBException: org.hibernate.hql.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.ebizlink.adonis.erp.model.support.CashQueryResult] [select distinct new com.ebizlink.adonis.erp.model.support.CashQueryResult(generatedAlias0.cashID, generatedAlias0.description, generatedAlias1, generatedAlias2, generatedAlias3) from com.ebizlink.adonis.erp.model.Cash as generatedAlias0 left join generatedAlias0.allowedValueList as generatedAlias1 left join generatedAlias0.allowedCurrencyList as generatedAlias2 left join generatedAlias0.allowedCashierList as generatedAlias3 where ( 1=1 ) and ( 1=1 ) and ( 1=1 ) and ( 1=1 ) order by generatedAlias0.description asc] </code></pre> <p>For reference, here is the <a href="http://code.google.com/p/pandora2/source/browse/products/pandora2/trunk/src/main/java/com/ebizlink/pandora2/server/ejb/util/PredicateBuilder.java" rel="nofollow">PredicateBuilder</a> just in case. I searched the web but I couldn't find somebody that is trying to retrieve many lists. Am I missing something obvious? Multiple fetches are a no go (hibernate bug), and I can't make the lists Eager either.</p> <p>Another related question would be: Can I make a query so, that the lists of my wrapper class are also entity wrappers and not entities? (For example: Instead of using Cashiers, just get first name and last name, and use a list of CashierWrapper which holds both strings)</p> <p>I really thank you and I hope you can help me.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload