CODEWITHSUNDEEP
javahashmapresultset
user1353252
user1353252

Reputation: 39

Transferring ResultSet values into HashMap then adding them

I want to transfer ResultSet values into HashMap then adding them, so I initially did this code:

PreparedStatement pstmt=null;       
ResultSet rs = null; 
String query="SELECT record_id, SUM(ROUND(amtsourcedr, 2)) AS debit, SUM(ROUND(amtsourcecr, 2)) AS credit " +
                "FROM adempiere.fact_acct " +
                "GROUP BY record_id " +
                "ORDER BY record_id ASC";

    try
    {

        pstmt = DB.prepareStatement(query, null);
        rs = pstmt.executeQuery();

        while (rs.next())
        {

            int id = rs.getInt("record_id");
            BigDecimal sourceDr = rs.getBigDecimal("debit");
            BigDecimal sourceCr = rs.getBigDecimal("credit");
            System.out.println(id);
            System.out.println(sourceDr);
            System.out.println(sourceCr);

            MCUS_Exam exam = new MCUS_Exam(getCtx(), 0, null);
            exam.setCUS_Record_ID(id);
            exam.setCUS_AmtSourceDr(sourceDr);
            exam.setCUS_AmtSourceCr(sourceCr);
            exam.save();
        }

Is it possible to do the adding and rounding found in the query through java by using hashmap? if yes, then how do I get on doing it?

Edit: The GROUP BY is actually the one I'm having a hard time with replicating in java.

Below is the original table:

    record_id    amountdr       amountcr
    1000000        0            213.7544
    1000000        0            270.00
    1000000      483.7544          0
    1000001        0            2250.6677
    1000001        0            400.5050
    1000001        0            12867.75
    1000001      15518.9327        0
    1000002        0            27000.6543
    1000002      27000.6543        0

And executing the code I gave will add this to a different table:

    record_id    amountdr       amountcr
    1000000      483.75         483.75   
    1000001      15518.92       15518.93
    1000002      27000.65       27000.65

Upvotes: 1

Views: 6591

Answers (3)

duffymo
duffymo

Reputation: 308733

Of course you can do it - anything that you can do on the database can also be done on the middle tier. The question I'd ask is: which one is the better choice for this calculation? If the query brings back a lot of data, I'd say it's better to let the database server do the work. There's little sense in moving a large data set to the middle tier simply to add and round.

My instinct is that the database is well suited for this calculation. I don't see any reason why you shouldn't allow it to do it.

But, if you must, here's what it might look like: 

Map<String, Double> rs = new HashMap<String, Double>();
// How will you duplicate the GROUP BY?
double sum = 0.0;
for (String key : rs.keySet()) {
    sum += rs.get(key);
}
// Rounding is a display issue; I'd do it elsewhere

The Map already has done the GROUP BY when you think about it: the key has to be unique. When you're mapping from the ResultSet to the Map, you'll have to check to see if a key already exists in the Map. If it does not, add the new key to the Map with its value; if it does, get the existing value, add the current ResultSet value to it, and put the result back into the Map for that key.

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109547

Also a non-answer. Aggregation and so on is better done in the database as less back and forth communication happens, marshalling a database number to a java object and other overhead.

You should have your fields in the database defined as DECIMALS(..., 2) and not DOUBLE for instance. That is the most important sanity measure. As you are using BigDecimal, you probably have a same sound database design already, but that superfluous (?) rounding made me write this answer.

Upvotes: 0

maksimov
maksimov

Reputation: 5811

The answer is yes.

  1. Remove SUM, ROUND and GROUP BY clause from the query.

  2. As you're iterating over your result set, create the MCUS_Exam objects and add them to your hashmap:

    Map<Integer, MCUS_Exam> map = new HashMap<Integer, MCUS_Exam>();
while (rs.next()) {
   // code here
   MCUS_Exam exam = new MCUS_Exam(getCtx(), 0, null);   
   map.put(id, exam);
   // perhaps some more code here...
}

  3. Have a method that takes your map and calculates whatever...

Upvotes: 0

Related Questions