> -- why don't we provide a way to restrict the columns while fetching the data? > Ex: object having set of columns from which want fetch the data for only few > columns...using the object entity.
ORM (Cayenne including) is based on the idea of object identity. In other words each persistent Java object should have one and only one DB row. DB rows are identified by unique PK of course. So based on that statement you can't fetch a list of objects for your query that essentially aggregates multiple rows. Having said that, EJBQLQuery ( http://cayenne.apache.org/doc/ejbqlquery.html ) does support scalar results, so you can still kind of do it, expressing your columns as properties of the ObjEntity: SELECT DISTINCT e.storyId FROM QCExtract e WHERE executionStatus in ('PASSED','BLOCKED') This will return a List<Integer>. You will still have to build the EJBQL query as String (we'll be adding API-based assembly in the upcoming releases), but at least this is not SQL. All the names are Java class properties, so you can use generated constants from your java classes, etc. Andrus On Oct 30, 2012, at 2:37 PM, Sampath Uppula <[email protected]> wrote: > Thanks for the solution. > But I don't want to write the sql query in the java code. > > Is there any way to achieve this? > > -- why don't we provide a way to restrict the columns while fetching the data? > Ex: object having set of columns from which want fetch the data for only few > columns...using the object entity. > > Thanks, > Sampath Uppula > > > -----Original Message----- > From: Felipe Martín Santos [mailto:[email protected]] > Sent: Tuesday, October 30, 2012 4:40 PM > To: [email protected] > Subject: Re: Problem - fetching the Distinct rows based on a Property > > // Extract is the obj-entity you have for the T_QC_EXTRACT db-entity > SQLTemplate query = new SQLTemplate(Extract.class, "SELECT DISTINCT > STORY_ID FROM T_QC_EXTRACT WHERE EXECUTION_STATUS IN ('PASSED','BLOCKED')"); > > SQLResult resultDescriptor = new SQLResult(); > resultDescriptor.addColumnResult("STORY_ID"); > query.setResult(resultDescriptor); > > List<DataObject> dataObjects = context.performQuery(query); > > > Is this what you need?? > > You can see more here: > http://cayenne.apache.org/doc/sqltemplate-result-mapping.html > > > 2012/10/30 Sampath Uppula <[email protected]> > >> Hi, >> >> I have a table T_QC_EXTRACT with many columns, few of them are STORY_ID, >> EXECUTION_STATUS. >> I want to fetch the distinct STORY_ID based on EXECUTION_STATUS IN >> ('PASSED','BLOCKED'); >> >> SELECT DISTINCT STORY_ID FROM T_QC_EXTRACT WHERE EXECUTION_STATUS IN >> ('PASSED','BLOCKED'); >> >> Please suggest a way to achieve this using Cayenne. >> >> Thanks, >> Sampath Uppula >> >> ============================================================================================================================Disclaimer: >> This message and the information contained herein is proprietary and >> confidential and subject to the Tech Mahindra policy statement, you may >> review the policy at <a href="http://www.techmahindra.com/Disclaimer.html >> ">http://www.techmahindra.com/Disclaimer.html</a> externally and <a href=" >> http://tim.techmahindra.com/tim/disclaimer.html";> >> http://tim.techmahindra.com/tim/disclaimer.html</a> internally within >> Tech >> Mahindra.============================================================================================================================ > > ============================================================================================================================Disclaimer: > This message and the information contained herein is proprietary and > confidential and subject to the Tech Mahindra policy statement, you may > review the policy at <a > href="http://www.techmahindra.com/Disclaimer.html";>http://www.techmahindra.com/Disclaimer.html</a> > externally and <a > href="http://tim.techmahindra.com/tim/disclaimer.html";>http://tim.techmahindra.com/tim/disclaimer.html</a> > internally within Tech > Mahindra.============================================================================================================================ >
