Combine similar rows into one entity in Spring (database)

Question

I have a problem. My db table named "table" looks like this (now consists of 3 rows that are actually the same entity, but with different codes)

And also I have an entity

public class Entity {
    private int id;
    private List<String> codes;
    private String description;

    //constructor + getters/setters
}

When I do the following, it works as expected

@Repository
class EntityRepository {

    private final ResultSetExtractor<List<Entity>> resultSetExtractor =
            JdbcTemplateMapperFactory
                    .newInstance()
                    .addKeys("id")
                    .unorderedJoin()
                    .newResultSetExtractor(Entity.class);

    @Autowired
    private NamedParameterJdbcOperations jdbcOperations;

    List<Entity> getAll() {
        return jdbcOperations.query(
            "SELECT id, code as codes, description FROM table", resultSetExtractor);
    }

}

result: Entity[id=1, codes={"111", "222", "333"}, description="description1"]

BUT

when I add a field into the entity like this

public class Entity {
    private int id;
    private List<String> codes;
    private List<Integer> anotherCodes;
    private String description;

    //constructor + getters/setters
}

@Repository
class EntityRepository {

    private final ResultSetExtractor<List<Entity>> resultSetExtractor =
            JdbcTemplateMapperFactory
                    .newInstance()
                    .addKeys("id")
                    .unorderedJoin()
                    .newResultSetExtractor(Entity.class);

    @Autowired
    private NamedParameterJdbcOperations jdbcOperations;

    List<Entity> getAll() {
        return jdbcOperations.query(
            "SELECT id, 
             code as codes, 
             anothercode as anotherCodes, 
             description FROM table", resultSetExtractor);
    }

}

and then i do the same and get the result:

Entity[id=1, 
codes={"111", "111", "111", "222", "222", "222", "333", "333", "333"}, 
anotherCodes={"1111", "2222", "2222", "1111", "2222", "2222", "1111", "2222", "2222"}
description="description1"]

but i'd like to get this:

Entity[id=1, 
    codes={"111", "222", "333"}, 
    anotherCodes={"1111", "2222"}
    description="description1"]

How can i fix it ??? Please, don;t suggest to alter table or separate it on some ones. I already have production project so I can not do this

What is your database ? Mysql, PostgreSQL, something else ? – Thomas Martin Dec 16 '19 at 13:23 — Thomas Martin, Dec 16 '19 at 13:23
@ThomasMartin maria db – Valentyn Anzhurov Dec 16 '19 at 13:32 — Valentyn Anzhurov, Dec 16 '19 at 13:32

Thomas Martin · Answer 1 · 2019-12-16T13:37:16.313

0

You should GROUP BY id, and use GROUP_CONCAT :

List<Entity> getAll() {
        return jdbcOperations.query(
            "SELECT id, 
             GROUP_CONCAT(code) as codes, 
             GROUP_CONCAT(anothercode) as anotherCodes, 
             description FROM table
             GROUP BY id", resultSetExtractor);
    }

Note that depending of your version, the DB could be complaining about the selection of description because it is not in the GROUP BY clause. In that case you should either

put it in the GROUP BY, but if you have several descriptions for the same id you'll get two lines.
Make an arbitrary choice, like FIRST_VALUE(description)

edited Dec 16 '19 at 13:37

answered Dec 16 '19 at 13:32

Thomas Martin

678
8
19

i cannot use group by in that query. I wrote just an example. actually this query is huge and one more group by gonna break it – Valentyn Anzhurov Dec 16 '19 at 13:38
1

Then you'll need to please improve your question, because it seems that is exactly a GROUP BY that you want to achieve with your example. – Thomas Martin Dec 16 '19 at 13:40

Combine similar rows into one entity in Spring (database)

1 Answers1