Different results with the same SQL statement

Question

Talk programmers(as), quiet?

I’ve done a lot of research not to repeat questions here, but I really don’t understand why the SQL statement works on Mysql Workbench and the Java/JDBC SQL string doesn’t.

I have two tables game and category, and when listing the categories I would like to inform the amount of games registered in the respective category, ex:

Category: Action (3) games

Category: Racing (1) games

Category: Fighting (1) games

A relationship where a category has many games and each game has its own category.

Well, so far so good. The problem is that before inserting SQL in the method preparedStatement("SQL") in java I do a test in Mysql Worckbench and the test returned me a satisfactory result:

SELECT c.id, c.categoria, COUNT(j.id_categoria) as qtd 
FROM categoria c INNER JOIN jogo j ON c.id = j.id_categoria GROUP BY c.id;

Result on the Workbench

id   categoria   qtd
1      Ação      3
2     Corrida    1
3      Luta      1

But with the same SQL in java the result is sequinte:

id   categoria   qtd
1      Ação       1
1      Ação       1
1      Ação       1

Part of the method getAll() class CategoriaDAO.

PreparedStatement ps = conexao.preparedStatement("SQL_ACIMA");
ResultSet rs = ps.executeQuery();
List<Categoria> categorias = new ArrayList<>();
while(rs.next()) {
    categoria.setId(rs.getInt("c.id"));
    categoria.setCategoria(rs.getString("c.categoria"));
    categoria.setQtd(rs.getInt("qtd"));
    categorias.add(categoria);
}
return categorias;

In the JSP:

<jsp:useBean id="categorias" class="br.com.jogos.CategoriaDAO"></jsp:useBean>
<c:forEach var="cat" items="${categorias.getAll()}">        
    <tr>
        <td>${cat.getCategoria()}</td>
        <td>${cat.getQtd()}</td>
    </tr>
</c:forEach>

If anyone has any idea why the same SQL statement has two different results, I’d appreciate it. Thanks!

Answer 1

PreparedStatement ps = conexao.preparedStatement("SQL_ACIMA");
ResultSet rs = ps.executeQuery();
List<Categoria> categorias = new ArrayList<>();
while(rs.next()) {
    categoria.setId(rs.getInt("c.id"));
    categoria.setCategoria(rs.getString("c.categoria"));
    categoria.setQtd(rs.getInt("qtd"));
    categorias.add(categoria);
}
return categorias;

Note that you are always using the same category instance and not different instances! What you wanted was this:

try (
    PreparedStatement ps = conexao.preparedStatement("SQL_ACIMA");
    ResultSet rs = ps.executeQuery();
) {
    List<Categoria> categorias = new ArrayList<>();
    while (rs.next()) {
        Categoria c = new Categoria();
        c.setId(rs.getInt("c.id"));
        c.setCategoria(rs.getString("c.categoria"));
        c.setQtd(rs.getInt("qtd"));
        categorias.add(c);
    }
    return categorias;
}

Ah, and remember to use the Try-with-Resources to manage the PreparedStatement and the ResultSet adequately (and probably the conexao should also).

I also suggest using immutable objects with parameters passed in the constructor. If you had used this approach, the problem you have wouldn’t have happened. I have an answer on that in another question.