While Resultset.next() returning only one result

Asked

Viewed 1,061 times

0

I have a problem when I put the ResultSet.next() in the while and get the information from the column of the Database returns me only one result.

Table 'People':

Pessoas:      Idade:
Marcos        22
Marcos        24
Marcos        25
Roberto       26
Roberto       21

Code:

private static String pegarTodasAsIdades(String pessoas) {
        query = "SELECT Idade FROM Pessoas WHERE Pessoas=?";
        try {
            ps = conn.prepareStatement(query);
            ps.setString(1, pessoas);
            rs = ps.executeQuery();
            while (rs.next()) {
                String idades = rs.getString("Idade");
                return idades+"\t";
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

But the above code only returns me the age '19' (first of the column)

Obs:

conn = Connection
ps = PreparedStatement
rs = ResultSet
java mysql jdbc resultset

  • Well, if you have one return within the while, how do you think he would return more than one result?

  • Recommended reading: https://answall.com/q/172909/132

  • I modified, the post, Victor Stafusa, has no way to return inside the while? With the function being String?

3 answers

1

There is a Return in the middle of the while... It’s making the while stop. Follows a solution:

private static String pegarTodasAsIdades(String pessoas) {
    query = "SELECT Idade FROM Pessoas WHERE Pessoas=?";
    try {
        ps = conn.prepareStatement(query);
        ps.setString(1, pessoas);
        rs = ps.executeQuery();
        String idades = "";
        while (rs.next()) {
            idades += rs.getString("Idade") + "\t";
        }
        return idades;
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return "";
}

1


There’s a return within your while. When the return is found, the method ends immediately. Therefore, when reading the first result, the method ends without the other results being processed.

Assuming you are managing the connections correctly with the conn (I think this assumption is false, but this is subject for another question), your code should be like this:

private static final String IDADES_SQL =
        "SELECT Idade FROM Pessoas WHERE Pessoas = ?";

private static String pegarTodasAsIdades(String pessoas) {
    try (PreparedStatement ps = conn.prepareStatement(IDADES_SQL)) {
        ps.setString(1, pessoas);
        try (ResultSet rs = ps.executeQuery()) {
            StringBuilder idades = new StringBuilder();
            while (rs.next()) {
                idades.append(rs.getString("Idade")).append("\t");
            }
            return idades.toString();
        }
    } catch (SQLException e) {
        e.printStackTrace();
        throw new RuntimException(e);
    }
}

Don’t let the PreparedStatement, the ResultSet and the Connection are fields of your DAO, unless you know much, but very well what you are doing. And remember to always use the Try-with-Resources.

0

If a single result is coming and you cannot increment it is enough to exchange while(){} for do-while and ensure that the process runs at least once:

private static String pegarTodasAsIdades(String pessoas) {
    query = "SELECT Idade FROM Pessoas WHERE Pessoas=?";
    try {
        ps = conn.prepareStatement(query);
        ps.setString(1, pessoas);
        rs = ps.executeQuery();
        do  {
            String idades = rs.getString("Idade");
            idades += rs.getString("Idade") + "\t";
        }while(rs.next());
        return idades;

    } catch (SQLException e) {
        e.printStackTrace();
    }

}

Browser other questions tagged java mysql jdbc resultset

You are not signed in. Login or sign up in order to post.