Get query from Persistence Service to JSON format

[Edwin Lobo, modificado hace 7 años.]

Hello!

I'm trying to build a Custom SQL Query to get on JSON format and load it in D3 visualization library.
My Custom SQL Query in default.xml is described below:

<!--?xml version="1.0" encoding="UTF-8"?-->
<custom-sql>
	<sql id="com.urbanasensor.visualizer.service.persistence.UrbanatweetFinder.findByGroupIdToJson">
	SELECT array_to_json(array_agg(row_to_json(t)))
	FROM ( SELECT * from vz_urbanatweet ) t
	</sql>
</custom-sql>

My Persistence service code isn't complete but I share it for context if neccessary.

public class UrbanatweetFinderImpl extends BasePersistenceImpl<urbanatweet>
		implements UrbanatweetFinder{
	
	public static final String FIND_BY_GROUPID_TO_JSON =
		    UrbanatweetFinder.class.getName() + ".findByGroupIdToJson";
	
	public JSONArray findByGroupIdToJson() {

		    Session session = null;
		    try {
		        session = openSession();

		        String sql = CustomSQLUtil.get(FIND_BY_GROUPID_TO_JSON);

		        SQLQuery q = session.createSQLQuery(sql);
		        q.setCacheable(false);
		        q.addEntity("Urbanatweet", UrbanatweetImpl.class);

		        QueryPos qPos = QueryPos.getInstance(q); 
		        return (List<urbanatweet>) QueryUtil.list(q, getDialect(), begin, end);
		    } catch (Exception e) {
		        try {
		            throw new SystemException(e);
		        } catch (SystemException se) {
		            se.printStackTrace();
		        }
		    } finally {
		        closeSession(session);
		    }
		    return null;
		} 
}
</urbanatweet></urbanatweet>

I want findByGroupIdToJson() method to return a JSONArray or JSONObject, but I don't know how to do it.

My first idea was to cast List to JSONArray and return it, but QueryUtil.list(q, getDialect(), begin, end) in line 21 requires int begin and int end arguments. What are these arguments for? and where are they coming from?

Could anybody explain to me and what idea can you suggest?

Thanks!