Performing SQL query programatically with Java Collections/Map

Question

I have 2 tables with a stringColum in common as below:

Table A (full):

stringColum       doubleColumn
name a            1
name a            2
name b            5
name b            4
name b            1
name c            2
[...]

Table B (may have some missing values in stringColum present in TableA):

stringColum       doubleColumn
name b            10
name b            20
name b            30
name c            11
[...]

How can I get an object with its values summed by doubleColumn and grouped by stringColum as below (I mean, do this for table A and B so I can compare them after this)?

Table A (summed/grouped):

stringColum       doubleColumn
name a            3
name b            10
name c            2
[...]

Table B (summed/grouped):

stringColum       doubleColumn
name b            60
name c            11
[...]

Instead of using SQL/JPQL to perform DB join/group, how can I do that with Collections as I've started below?

List<UorPos> uorsList = uf.findAllUPB();
List<Treinamentos> treinamentosList = tf.findAll();

Map<String, Double> orcMap = new HashMap<>();
Map<String, Double> rlzMap = new HashMap<>();

for (UorPos u : uorsList) {
    orcMap.put(u.getNomeUorPos(), u.getOrc());
}

for (Treinamentos r : treinamentosList) {
    rlzMap.put(r.getDivisao(), r.getValorCurso());
}

The for loop above gets only the first pair of register from the table. E.g.: TableA resultList after the for loop (similar to TableB resultList):

stringColum       doubleColumn
name a            1
name b            5
name c            2
[...]

How can I get all the values and then sum them, grouping by the stringColumn?

After that, I'll need to compare the stringColumn present in the two tables, filling Table B with the pair 'missingString Column','0'. E.g.:

Table B (summed/grouped):

stringColum       doubleColumn
name a            0 // --> this pair of register will be inserted in TableB if a stringColum present in TableA doesn't exist in the Table B
name b            60
name c            11
[...]

Thanks in advance!

Why not use a SQL join/group? That will most likely be easier to write and more efficient to run... — assylias
– assylias, Commented Mar 2, 2015 at 13:18

vbezhenar · Accepted Answer · 2015-03-02 16:07:25Z

1

To group and sum use the following code:

    for (UorPos u : uorsList) {
        orcMap.merge(u.getNomeUorPos(), u.getOrc(), (x, y) -> x + y);
    }

    for (Treinamentos r : treinamentosList) {
        rlzMap.merge(r.getDivisao(), r.getValorCurso(), (x, y) -> x + y);
    }

or if you don't use Java 8:

    for (UorPos u : uorsList) {
        String key = u.getNomeUorPos();
        if (orcMap.containsKey(key)) {
            orcMap.put(key, orcMap.get(key) + u.getOrc());
        } else {
            orcMap.put(key, u.getOrc());
        }
    }

    for (Treinamentos r : treinamentosList) {
        String key = r.getDivisao();
        if (rlzMap.containsKey(key)) {
            rlzMap.put(key, rlzMap.get(key) + r.getValorCurso());
        } else {
            rlzMap.put(key, r.getValorCurso());
        }
    }

To add missing items to the second map use the following code:

    for (String key : orcMap.keySet()) {
        rlzMap.putIfAbsent(key, 0.0);
    }

or if you don't use Java 8:

    for (String key : orcMap.keySet()) {
        if (!rlzMap.containsKey(key)) {
            rlzMap.put(key, 0.0);
        }
    }

edited Mar 2, 2015 at 16:07

answered Mar 2, 2015 at 13:47

vbezhenar

12.5k10 gold badges60 silver badges78 bronze badges

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

3 Comments

jMarcel Over a year ago

hi vbezhenar! Man, can you provide a version with no Lambda expression, since I'm using Java 7. Thanks a lot!

jMarcel Over a year ago

Unfortunatelly I can't use the merge and putIfAbsent , since I'm using JDK 7. Is there any workaround for this?

vbezhenar Over a year ago

Those methods are indeed from Java 8. I added Java 7 versions.

Collectives™ on Stack Overflow

Performing SQL query programatically with Java Collections/Map

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related