Fóruns

Início » Liferay Portal » English » 3. Development

Visualização combinada Visão plana Exibição em árvore
Tópicos [ Anterior | Próximo ]
toggle
Michael Schulz
custom query in service
9 de Julho de 2012 11:11
Resposta

Michael Schulz

Ranking: Junior Member

Mensagens: 27

Data de entrada: 8 de Março de 2012

Mensagens recentes

hello folks,

is it possible to define a custom query like

select month,year,sum(x),sum(y) from tablename where companyId = ? group by month,year order by year desc,month desc

in the finderImpl of my service?


my problem is that all description for custom querys, custom finders ... i find return the complete entity (select * from... ) built in the service but i only need som custom attributes in the resultSet ( custom entity with month, year, sumx and sumy )!? is that possible?

i tried to

q.addScalar("month", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
q.addScalar("year", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
q.addScalar("sumx", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
q.addScalar("sumy", com.liferay.portal.kernel.dao.orm.Type.INTEGER);

but which cast i have to use for

return (?) QueryUtil.list(q, getDialect(), start, end);

or how do i acces the attributes of an entry if i return List??


Thank you for your time and help!


 1
 2public class xyzFinderImpl extends BasePersistenceImpl<xyz> implements xyzFinder {
 3    public [b]List[/b] findCustomThings(long companyId) throws SystemException {
 4        Session session = null;
 5        try {
 6            session = openSession();
 7
 8            String base = "select month,year,sum(x),sum(y) from tablename where companyId = ? group by month,year order by year desc,month desc";
 9
10            SQLQuery q = session.createSQLQuery(base);
11            q.setCacheable(false);
12            q.addScalar("month", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
13            q.addScalar("year", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
14            q.addScalar("sum(x)", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
15            q.addScalar("sum(y)", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
16            
17            QueryPos qPos = QueryPos.getInstance(q);
18            qPos.add(companyId);
19
20            return QueryUtil.list(q, getDialect(), 0, xyzLocalServiceUtil.getxyzCount());
21        } catch (Exception e) {
22            throw new SystemException(e);
23        } finally {
24            closeSession(session);
25        }
26    }
27}

entity xyz has colums: userId,companyId,x,y,year and month
this is the code i'm trying with at the moment but i don't know how to access the atributes in the list returned, any hints would be great!?
Daniel Wilmes
RE: custom query in service
9 de Julho de 2012 11:11
Resposta

Daniel Wilmes

Ranking: Regular Member

Mensagens: 164

Data de entrada: 23 de Maio de 2011

Mensagens recentes

Yes you can. In your portlet you can create an xml file in the custom sql folder you can define an custom sql xml file with tags:

<custom-sql>
<sql id="com.mypackage.countAll">
<![CDATA[
SELECT * FROM SOME TABLE
]]>
</sql>
</custom-sql>

You can then create a persistence custom package in the persistence layer to deal with returning what ever you want from your custom sql file.

You would use methods like:

public static String FIND_ALL = MYCLASS.class.getName() + ".findAll";

String sql = CustomSQLUtil.get(FIND_ALL);

You can then actually put in place holders in your sql xml file and replace them in your java persistence class.

Hope that helps.
Michael Schulz
RE: custom query in service
9 de Julho de 2012 12:18
Resposta

Michael Schulz

Ranking: Junior Member

Mensagens: 27

Data de entrada: 8 de Março de 2012

Mensagens recentes

wow this was very quick, thank you!!

can you give me some more example code please?
maybe i understand wrong but for me it sounds like:
(example)

Select * from table;

and then extract the data i need from resultSet in java

i don't wanna do that^^

i wanna do somthing like:

select year,month,sum(x),sum(y) from table group by year,month;

so i can call for example:

int x = resultSet.getSumX(); in javacode
David H Nebinger
RE: custom query in service
9 de Julho de 2012 12:22
Resposta

David H Nebinger

Community Moderator

Ranking: Liferay Legend

Mensagens: 11081

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

Sure. In your XxxxLocalServiceImpl class, you can use the persistence instance to open a session and use createSQLQuery() and run the query. Your result is a List<Object[]>, where the members of the Object[] array are the values from the query columns, in this case the last one, Object[2] and Object[3] would be instances of Number (Integer, I believe, but Number would work) that would have the sums...
Michael Schulz
RE: custom query in service
9 de Julho de 2012 13:29
Resposta

Michael Schulz

Ranking: Junior Member

Mensagens: 27

Data de entrada: 8 de Março de 2012

Mensagens recentes

oha this sounds very interesting!

i will test as soon as possible tomorrow..
tried some casts to int[], List<Object> and something like that but somehow Object[] wasn't in my mindemoticon, hope it works^^

Thank you very much again for your time and answers!
Michael Schulz
RE: custom query in service
15 de Agosto de 2012 07:35
Resposta

Michael Schulz

Ranking: Junior Member

Mensagens: 27

Data de entrada: 8 de Março de 2012

Mensagens recentes

David H Nebinger:
Sure. In your XxxxLocalServiceImpl class, you can use the persistence instance to open a session and use createSQLQuery() and run the query. Your result is a List<Object[]>, where the members of the Object[] array are the values from the query columns, in this case the last one, Object[2] and Object[3] would be instances of Number (Integer, I believe, but Number would work) that would have the sums...



Yay, List<Object[]> did itemoticon THANK YOU!!