Construct JSON Object based on Query Result List

Question

Trying to figure out a solution for a problem I am facing, but cannot find any material online so far to help me.

Essentially what I have is a method in a rest controller that passes a query string to my Hibernate DAO and gets in return data requested.

e.g.

@RequestMapping("/submitQuery")
    public Object submitQuery() {
// example of a query string, note this is dynamic and thus never hardcoded
        String query = "SELECT C.amount, C.transactionDate, R.amount, R.transactionDate FROM CAR C, RFT R";

        return  DAO.submitQuery(query);
    }

DAO:

public List<T> submitQuery(String query) {
        Query q = getSession().createQuery(query);
        return q.list();
    }

This query string will be dynamic so the option of creating a entity and attaching it against query is not an option.

The above method will return data in following format:

[
    [
        -4890.38,
        1451826000000,
        25.04,
        1421499600000
    ],
    [
        -660,
        1413205200000,
        25.04,
        1421499600000
    ],
    [
        -10768.53,
        1423054800000,
        25.04,
        1421499600000
    ]
]

So no headers and on top of that dates have been converted into digits. What i want to achieve is have the method return the results in following format:

[
    [
        "amountc" : -4890.38,
        "datec" : "01-03-2014",
        "amountr" : 25.04,
        "dater" : "01-03-2014"
    ],
    [
        "amountc" : -660,
        "datec" : "03-02-2014",
        "amountr" : 25.04,
        "dater" : "03-02-2014"
    ],
    [
        "amountc" : -10768.53,
        "datec" : "01-02-2014",
        "amountr" : 25.04,
        "dater" : "01-02-2014"
    ]
]

Any advice/assistance would be appreciated.

NOTE: Cannot use DTOs or Entities as the Query String is dynamic and changes. Query string i have there is just as example.

Create a Data Transfer Object with the fields ammountc,datec,amountr,dater and instead of returning DAO.submitQuery(query) build a DTO list from this list and return it. — Laurentiu L.
– Laurentiu L., Commented Aug 4, 2015 at 9:07
Query string changes therefore DTO would need to change. Cannot use objects. — Aeseir
– Aeseir, Commented Aug 4, 2015 at 9:23

Community · Accepted Answer · 2017-05-23 10:30:42Z

3

I think Sunil already answered. Check the edited section on how to convert to map. Or try the following

public List<Map<String,Object>> submitQuery(String query) {
        Query q = getSession().createQuery(query);
        return q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
    }

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Sep 8, 2015 at 11:49

Coder

7,09613 gold badges59 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User Over a year ago

Voila! Magic it is.. ;) But column's sequence got changed in JSON format somehow..

jensanity5000 Over a year ago

Thank you.. List<Map<String,Object>> worked perfectly for me.

Sunil · Accepted Answer · 2015-08-06 06:09:09Z

2

create a bean class let ABC.java which contains the setter and getter methods and override toString into DTO class like this.

  public class ABC implements Serializable{

    private double amountc;
    private String datec;
    private double amountr;
    private String  dater;

    ... setter and getters

    @Override
    public String toString(){
        return "[\"amountc:\""+amountc+",\"datec:\""+datec+",\"amountr:\""+amountr+",\"dater:\""+dater+"]";
    }
}

and make few change into DAO implementations class

public List<ABC> submitQuery(String query) {
    Query q = getSession().createQuery(query).setResultTransformer(Transformers.aliasToBean(ABC.class));
    List<ABC> resultList=q.list();
    System.out.println(resultList); //desire output 
    return resultList;
}

please make sure that data types should be same into entity and DTO class ABC

EDITED

in case of you don't want any bean or DTO then you can use Criteria.ALIAS_TO_ENTITY_MAP which transformer the result query into a map object with key-value pair.keys name are same as alias name into sql query.if you are not using alias into sql query then key's are like 0,1,2,3,.... and so on.

public List<ABC> submitQuery(String query) {
        Query q = getSession().createQuery(query).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        List<ABC> resultList=q.list();
        System.out.println(resultList); //desire output 
        return resultList;
    }

edited Aug 6, 2015 at 6:09

answered Aug 4, 2015 at 9:34

Sunil

4473 silver badges12 bronze badges

2 Comments

Aeseir Over a year ago

Did you read the part where it says "cannot use entity or DTO"? What happens when the query changes to "select r.id from car r"?

Sunil Over a year ago

if you don't want DTO or entity then you can use 'Criteria.ALIAS_TO_ENTITY_MAP' transfer the result into hash map with key and values pair.

aviad · Accepted Answer · 2015-08-04 09:08:10Z

0

Hard to tell without knowing how the T looks like... but I believe T's toString method is your friend.

answered Aug 4, 2015 at 9:08

aviad

8,2789 gold badges55 silver badges100 bronze badges

8 Comments

Aeseir Over a year ago

T is irrelevant as it will return a Object containing results. But again i cannot use predefined entities as the query string changes.

aviad Over a year ago

T is not irrelevant as long as it appears in your DAO submitQuery method signature

Aeseir Over a year ago

you don't have to define T, so it will return a object making it irrelevant as to what it is.

aviad Over a year ago

That is not what I was saying. You can enforce certain format of toString though

Aeseir Over a year ago

Right, but that is assuming that there is a specific object/entity to convert into a string. I am not using object/entity as the query is dynamic and can change on a whim.

|

Javy · Accepted Answer · 2015-08-04 09:13:27Z

-1

you can create a object model

for example

class TransObj  {

public double amountc;
public String datec;
public double amountr;
public String  dater;
public double getAmountc() {
    return amountc;
}
public void setAmountc(double amountc) {
    this.amountc = amountc;
}
public String getDatec() {
    return datec;
}
public void setDatec(String datec) {
    this.datec = datec;
}
public double getAmountr() {
    return amountr;
}
public void setAmountr(double amountr) {
    this.amountr = amountr;
}
public String getDater() {
    return dater;
}
public void setDater(String dater) {
    this.dater = dater;
}
}

then modify you code to this

@RequestMapping("/submitQuery")
public List<TransObj> submitQuery() {
    String query = "SELECT C.amount as amountc, C.transactionDate as datec, R.amount as amountr, R.transactionDate as dater FROM CAR C, RFT R";

    return  DAO.submitQuery(query);
}


public List<TransObj> submitQuery(String query) {
        Query q = getSession().createQuery(query);
        return q.list();
    }

answered Aug 4, 2015 at 9:13

Javy

1,0646 silver badges9 bronze badges

1 Comment

Aeseir Over a year ago

With objects its easy, I cannot use objects as the query string is dynamic so the minute it changes your object becomes obsolete and throws a error.

jyapx · Accepted Answer · 2020-09-25 14:37:57Z

-1

Use the select new map syntax in HQL to fetch the results of each row in a Map.

Basically if you structure your HQL query like this:

"SELECT new HashMap(user.id as id, user.firstName as fullName) FROM User user";

Then the return value will form a map structure which is a good format to easily serialize as JSON. You're map (and json) will look like this:

{
"id" : "<id value>",

"fullname" : "<firstname value>"
}

edited Sep 25, 2020 at 14:37

answered Nov 22, 2016 at 20:51

jyapx

2,5892 gold badges17 silver badges18 bronze badges

1 Comment

dckuehn Over a year ago

Please add more details to your post. Link only answers are discouraged because they will not be useful if the link provided ever changes.

Collectives™ on Stack Overflow

Construct JSON Object based on Query Result List

5 Answers 5

2 Comments

2 Comments

8 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related