Why this mysql find max value stament won't run in Java JDBC

Question

I have a table with column id, i want to select max value of id and +1 to that value because i'm running a loop. But the sql stament not work, but why? i have test it with mysql comand and it work well! Thank you!

Error: Exception in thread "main" java.sql.SQLException: Before start of result set at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862) at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2469) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2580) at MyPackage.TestMax.main(TestMax.java:23) C:\Users\lam\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1 BUILD FAILED (total time: 0 seconds)

> The second error when try to define my_max_val: Exception in thread

Exception in thread "main" java.sql.SQLException: Before start of result set at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862) at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2469) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2580) at MyPackage.TestMax.main(TestMax.java:23) C:\Users\lam\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1 BUILD FAILED (total time: 0 seconds)

public static void main(String[] args) throws Exception {
        Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","user","pass");
        Statement myStmt = myConn.createStatement();
        ResultSet myRs = myStmt.executeQuery("SELECT MAX(ID) FROM table");
        int i = myRs.getInt("MAX(ID)")+1;
        System.out.println(i);
}

The second code

public static void main(String[] args) throws Exception {

    Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","user","pass");
    Statement myStmt = myConn.createStatement();
    ResultSet myRs = myStmt.executeQuery("SELECT MAX(ID) as my_max_val FROM table");
    int i = myRs.getInt("my_max_val")+1;
    System.out.println(i);

}

@Iam: It's good that you put the new error message. But so that it makes sense for those reading, make sure you put your new code that goes with it as well. Otherwise, it's confusing. — sstan, Dec 10 '15 at 01:28

Iliiaz Akhmedov · Accepted Answer · 2015-12-10T01:29:49.293

2

You have to define a name for the max value, so, that it will understand which output you want, as below, plus, move the iterator of the result set:

    Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","user","pass");
    Statement myStmt = myConn.createStatement();
    ResultSet myRs = myStmt.executeQuery("SELECT MAX(ID) as my_max_val FROM table");

    myRs.next(); //you might also have forgotten this
    int i = myRs.getInt("my_max_val")+1;
    System.out.println(i);

edited Dec 10 '15 at 01:29

answered Dec 10 '15 at 01:16

Iliiaz Akhmedov

867
7
17

I have tried it, it's not working! – lam vuong Dec 10 '15 at 01:20
Is it the same exception? Don't forget to do ```myRs.next();``` – Iliiaz Akhmedov Dec 10 '15 at 01:21
OP may not have noticed that you added ` as my_max_val` to the query. – sstan Dec 10 '15 at 01:26
@lamvuong Did you try the updated code? There was a space i didn't notice – Iliiaz Akhmedov Dec 10 '15 at 01:30
I have deleted that space – lam vuong Dec 10 '15 at 01:32
Thank, the problem is i forgot while (myRs.next()) {}. Problem solved – lam vuong Dec 10 '15 at 05:31

score 0 · Answer 2 · edited May 23 '17 at 10:27

You do not have to define a name for max value if you simply use an columnIndex to retrieve the value

e.g.

 int i = myRs.getInt(1)+1;
 System.out.println(i);

columnIndex - the first column is 1, the second is 2, ...

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getInt(int)

Why this mysql find max value stament won't run in Java JDBC

> The second error when try to define my_max_val: Exception in thread

The second code

2 Answers2