Dynamic Query

[David Gitonga, modificado 8 Anos atrás.]

Hi Guys, I just run into a problem here, hope someone will be able to help me out.

Here is the situation.
I have a dynamic query which is returning values, below is query and result.

session = openSession();
	        
String sql =  "SELECT id,
    MAX(CASE WHEN (key_ = 'field1') THEN val ELSE NULL END) AS filed1,
    MAX(CASE WHEN (key_ = 'field2') THEN val ELSE NULL END) AS field2,
    MAX(CASE WHEN (key_ = 'field3') THEN val ELSE NULL END) AS field3 
FROM  keyvaluetable GROUP BY id ORDER BY id ";
	  	    
SQLQuery q = session.createSQLQuery(sql);
	q.setCacheable(false);
	 QueryPos qPos = QueryPos.getInstance(q);
 return (List ) QueryUtil.list(q, getDialect(), start, end);

Result returned converted to json:

[[229201,"Value1","Value2","Value3"],
[229203,"Value1","Value2","Value3"]]

My Broblem is that the result does not contain key:
My Expected result should be like....

[{ "id":229201,"field1":"Value1","field2":"Value2""field3":,"Value3"},
{229203,"field1":"Value1","field2":"Value2","field3":"Value3"}]

[Fabio Pezzutto, modificado 8 Anos atrás.]

Hi, this because you are converting a List of array to JSON, the natural transformation is an json array of values (how the JSON transformer know the name of fields).
you could map the results into a List of entities, otherwise, you can transform it to JSON manually using JSONFactoryUtil class.

[Raja Seth, modificado 8 Anos atrás.]

Hi David,

This is not a dynamic query snippet of code which you have shown as an example. It is custom SQL query. You need to write your custom logic to in which ever format you required your result.

Regards,
Raja

[David Gitonga, modificado 8 Anos atrás.]

Thank you Guys For taking time to review my question.