CODEWITHSUNDEEP
javaspringspring-boot
Raj
Raj

Reputation: 55

How to construct JSON Object based on StoredProcedureQuery Result List - SpringBoot Rest API?

I would need to display stored procedure response(multiple OUT parameters) without creating any java class for that response.

ResultList may contain multiple cursors or single cursor or single string or multiple string or it could be even combination of cursors and string. I need to parse it all and display it as JSON Object. Do we have any mapper which does these functionalities automatically?

Now I could get data in following format using List<Object[]> as return object,

FirstCursor(Returns List of Object - No column names): 

[  
 [  
  "XYZ",  
  18653  
 ]  
]

However I want column names too along with value.

[
 {
  "uname": "XYZ",
  "phone": 18653
 }
]

Service code for your reference,

public List<Object[]> getOrder(String orderNo) 
{  
StoredProcedureQuery query = entityManager.createStoredProcedureQuery("xx.xxx.get_order_details"); 
 query.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);     query.registerStoredProcedureParameter(2, void.class, ParameterMode.REF_CURSOR);     query.setParameter(1, "OR-1001");   
List<Object[]> results = query.getResultList();  
returns results;   
}

Above code returns only value and not it's respective column names.

Upvotes: 0

Views: 1514

Answers (3)

user2393260
user2393260

Reputation: 11

Try this .. this code is working for me:

Object obj = this.em.createNativeQuery("select * from function_name('P1',16418,'*Alcohol')").getSingleResult();
  System.out.println("--->"+obj.toString());

Console output will be printed json that my stored procedure/function returns.

My stored procedure:

CREATE OR REPLACE FUNCTION public.funciton_name
    (P1 character varying, 
     P2 bigint, 
     P3 character varying)
RETURNS TABLE(j text)
AS $function$
BEGIN
RETURN QUERY
select row_to_json(audit)::text as audits

Upvotes: 1

Pratik Tambat
Pratik Tambat

Reputation: 31

This is also best solution for this try it.

List<Map> ret = new ArrayList();
for(Object[] result : results) {
            Map row = new HashMap<>();
            row.put("uname", result[0]);
            row.put("phone", result[1]);
            ret.add(row);
        }
        return ret;

Upvotes: 0

Simon Martinelli
Simon Martinelli

Reputation: 36123

You can return the JSON directly from the stored procedure:

SELECT JSON_ARRAYAGG(
          JSON_OBJECT('uname' VALUE uname,
                      'phone' VALUE phone)
) 
FROM items

Read more about creating JSON:

Upvotes: 1

Related Questions