Merge data by date

Question

I have below data which I fetched from database by using Hibernate NamedQuery

  TXN_ID   END_DATE 
---------- ------------
       121 15-JUN-16
       122 15-JUN-16
       123 16-MAY-16

Each row data can be store in Java class Object. Now I want to combined data depending on the END_DATE. If END_DATE are same then merge TXN_ID data.

From the above data output would be :

TXN_ID     END_DATE 
---------- ------------
121|122    15-JUN-16
123        16-MAY-16

I want to do this program in java. What is the easy program for that?

what you had tried so far ..?? – Simmant May 19 '16 at 07:34 — Simmant, May 19 '16 at 07:34

score 0 · Answer 1 · edited May 23 '17 at 11:44

Using the accepted function printMap, to iterate through the hashmap in order to see if output is correct.

With the code below:

public static void main(String[] args) {
    String[][] b = {{"1","15-JUN-16"},{"2","16-JUN-16"},{"3","13-JUN-16"},{"4","16-JUN-16"},{"5","17-JUN-16"}};
    Map<String, String> mapb = new HashMap<String,String>();

    for(int j=0; j<b.length; j++){
        String c = mapb.get(b[j][1]);
        if(c == null)
            mapb.put(b[j][1], b[j][0]);
        else
            mapb.put(b[j][1], c+" "+b[j][0]);
    }
    printMap(mapb);
}

You get the following output:

13-JUN-16 = 3
16-JUN-16 = 2 4
17-JUN-16 = 5
15-JUN-16 = 1

I think this will solve your problem.

score 0 · Answer 2 · answered May 19 '16 at 09:46

With hibernate you can put query result in a list of object

Query q = session.createSQLQuery( sql ).addEntity(ObjDataQuery.class);
List<ObjDataQuery> res = q.list();

Now you can create an hashmap to storage final result, to populate this object you can iterate over res

Map<String, String> finalResult= new HashMap<>();
for (int i=0; i<res.size(); i++){
    if (finalResult.get(res.get(i).date!=null){
       //new element
       finalResult.put(res.get(i).date,res.get(i).txn)
    } else {
       //update element
       finalResult.put(res.get(i).date,
                       finalResult.get(res.get(i).date) + res.get(i).txn)
    }
}

I've not tested it by logic should be correct.

Another way is to change the query to obtain direct the final result (in oracle see LISTAGG)

Merge data by date

2 Answers2