Different results with the same SQL statement

Question

Different results with the same SQL statement

Navigation

#1 by (3 votes)

1

Speak programmers, easy?

I've done a lot of research not to repeat questions here, but I really do not understand why the SQL statement works in MySQL Workbench and the Java / JDBC SQL string does not.

I have two game and category tables, and when listing the categories I'd like to tell you the quantity of games registered in the respective category, eg:

Category: Action ( 3 ) games

Category: Racing ( 1 ) games

Category: Fight ( 1 ) games

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

Well, so far so good. The problem is that before inserting SQL in the preparedStatement("SQL") method 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 in 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 the following:

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

Part of method getAll() of 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 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 distinct results, I'd appreciate it. Thanks!

mysql java jdbc jsp

asked by anonymous 24.08.2018 / 23:54

1 answer

Error creating XML with XmlWriter Can anyone tell me why my console does not show any results?

score 3 · Accepted Answer

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

Oh, and remember to use try-with-resources to manage PreparedStatement and ResultSet properly (and probably conexao should too).

I also suggest using immutable objects with parameters passed in the constructor. If you had used this approach, the problem you have would not have happened. I have an answer about this in another question .