How do I get my data from a SELECT MAX (user_id) FROM user

Question

How do I get my data from a SELECT MAX (user_id) FROM user

Navigation

#1 by (5 votes)

0

I have my next method where I want to get the last record of my user table, but it is bringing me 0 when I should bring 4. Where is it failing?

Method

public static int getIdOfEmployee(){
    Connection con = null;
    int id_usuario = 0;

    try{  
        con = Conexion.getConnection();  
        PreparedStatement ps = con.prepareStatement("SELECT MAX(id_usuario) FROM usuario");   
        ResultSet rs = ps.executeQuery();
        id_usuario = rs.getInt(1); 

        con.close();  
    }catch(Exception ex){ex.printStackTrace();}  

    return id_usuario;  
}

User table

See where I get the correct result in MYSQL

I hope you can help me

mysql java

asked by Javier fr 13.12.2017 в 20:10

source

1 answer

Error modifying a table in MYSQL from Java how to save the result of a subquery in mysql in order to compare it

score 5 · Accepted Answer

The problem is that you must execute rs.next() before you can read the result.

ResultSet rs = ps.executeQuery();
rs.next(); // te falta esto
id_usuario = rs.getInt(1);

Because of this, the call to rs.getInt(1); should be throwing you an exception.

But since you have chosen to ignore the error in your try-catch , then you receive the value with which you initialized the variable id_usuario at the beginning of the method, that is 0 .

Apart from that you must include the call to rs.next() , this should serve as a lesson that it is not a good idea to catch and ignore an exception if you are not going to manage it, because it will simply result in another error in your program, but that It will seem even more mysterious.

I understand that with Java this can become annoying, because if you do not handle the exception, then it forces you to mark the method with throws SQLException , and this in turn has a cascading effect with the methods above in the stack of calls. But if you want to avoid this problem, instead of silencing the exception, the relazes are better as a RuntimeException :

try {
    // ...
} catch (SQLException e) {
    throw new RuntimeException(e);
}

Additionally, it takes the habit of closing the resources (connection, prepared statement, result set, etc ...) using the pattern try-with-resources to avoid problems.

Suggested code:

public static int getIdOfEmployee() throws SQLException {
    try (Connection con = Conexion.getConnection();
         PreparedStatement ps = con.prepareStatement("SELECT MAX(id_usuario) FROM usuario");
         ResultSet rs = ps.executeQuery()) {

        if (!rs.next()) throw new RuntimeException("no hubo resultado");

        return rs.getInt(1);
    }
}

... or if you really want to avoid the throws SQLException in signing the method:

public static int getIdOfEmployee() {
    try (Connection con = Conexion.getConnection();
         PreparedStatement ps = con.prepareStatement("SELECT MAX(id_usuario) FROM usuario");
         ResultSet rs = ps.executeQuery()) {

        if (!rs.next()) throw new RuntimeException("no hubo resultado");

        return rs.getInt(1);
    } catch(SQLException e) {
        throw new RuntimeException(e);
    }
}