Unable to get ResultSet from the MySql DataBase.

Question

When I execute the following query on a database containing a table "comm_list" and a field "sr_no" of type "Int" in the table "comm_list", I get the correct resultset

SELECT MAX(sr_no) FROM comm_list;

The above query is to get the max. serial no. so that when I enter a new record, I enter the (serial no. + 1) in the "sr_no" coloum so that I can keep track the no. of records in the database.

I know that it can be done automatically by making the field "sr_no" primary key and make it to auto increement itself whenever a new record is entered but I have another primary key in the table already so I can not make this "sr_no" field as the primary key.

When I execute the above query from my java program to get the max. serial no, the returned resultset doesn't contain any entries. I don't understand where is the problem. I am storing records from the java program to the Database by using jdbc driver.

The following is the code-snippet of my java program.

          PreparedStatement getSerial = con.prepareStatement("select max(sr_no) from comm_list"); // "con" is the connection with the database
          getSerial.execute();
          ResultSet rs = getSerial.getResultSet();
          System.out.println(rs.toString()); 
          System.out.println(rs.getFetchSize()); // output - 0
          srNo = rs.getInt(1) + 1;
          System.out.println(srNo);

Answer 1

ResultSets need to be iterated to be used. This is mentioned in the javadocs. The javadocs are always an invaluable reference for these APIs.

You need to call ResultSet's next() method to get the first result.

There are some getting started examples here.

Answer 2

Try using the next method, to move the specified result set to the next row and return whether there is a next row:

PreparedStatement getSerial = con.prepareStatement("select max(sr_no) from comm_list"); // "con" is the connection with the database
getSerial.execute();
ResultSet rs = getSerial.getResultSet();
if(rs.next()) {
   srNo = rs.getInt(1) + 1;
}
System.out.println(srNo);

Answer 3

Statement getSerial = con.createStatement();
ResultSet rs = getSerial.executeQuery("select max(sr_no) from comm_list");
if(rs.next()){
  srNo = rs.getInt(1) + 1;

System.out.println(srNo);