CODEWITHSUNDEEP
javasqlcollectionshashmap
jMarcel
jMarcel

Reputation: 993

Performing SQL query programatically with Java Collections/Map

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!

Upvotes: 0

Views: 341

Answers (1)

vbezhenar
vbezhenar

Reputation: 12326

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);
        }
    }

Upvotes: 1

Related Questions