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

Answer 1

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

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