custom query in service

[Michael Schulz, modificado 11 Anos atrás.]

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!

public class xyzFinderImpl extends BasePersistenceImpl<xyz> implements xyzFinder {
	public [b]List[/b] findCustomThings(long companyId) throws SystemException {
		Session session = null;
		try {
			session = openSession();

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

			SQLQuery q = session.createSQLQuery(base);
			q.setCacheable(false);
			q.addScalar("month", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
			q.addScalar("year", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
			q.addScalar("sum(x)", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
			q.addScalar("sum(y)", com.liferay.portal.kernel.dao.orm.Type.INTEGER);
			
			QueryPos qPos = QueryPos.getInstance(q);
			qPos.add(companyId);

			return QueryUtil.list(q, getDialect(), 0, xyzLocalServiceUtil.getxyzCount());
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
}
</xyz>

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, modificado 11 Anos atrás.]

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, modificado 11 Anos atrás.]

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, modificado 11 Anos atrás.]

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, modificado 11 Anos atrás.]

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 mind, hope it works^^

Thank you very much again for your time and answers!

[Michael Schulz, modificado 11 Anos atrás.]

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 it THANK YOU!!