CODEWITHSUNDEEP
javamysql
Vasanth Nag K V
Vasanth Nag K V

Reputation: 4978

fetching data from the result set in java

i am trying to fetch the max data of a column from my java code, but i get an error at the result set

my code is as shown below

Class.forName("com.mysql.jdbc.Driver");

con=DriverManager.getConnection("jdbc:mysql://localhost/testdb123","root","root");

LOGGER.info("connected to the database");
Statement stmt0 = conn.createStatement();           
String qr= "INSERT INTO stumarks " + "VALUES ("+null+","+marks+")";
Statement stmt1 = conn.createStatement();
stmt1.executeUpdate(qr);
String countQuery = "select MAX('seqNum') as count1 from stumarks";
ResultSet res = stmt0.executeQuery(countQuery);
LOGGER.info("result set success!!"); 
int num = res.getInt("count1");``
System.out.println(num);

Upvotes: 0

Views: 2307

Answers (4)

Michael Laffargue
Michael Laffargue

Reputation: 10294

The result set has described in the Javadoc begins at index -1. .next() should be called once to get the first result.

Initially the cursor is positioned before the first row.

From a security point of view, you should avoid appending variable to your request but instead use parameters (SQL Injection matter)

Also don't forget to close your connection and statement to avoid Out of memory and to avoid taking all your DB connection slots. You can close the result set (good practice) even if it's not required using JRE classes :

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Upvotes: 0

Surinder ツ
Surinder ツ

Reputation: 1788

You must use like:

"INSERT INTO stumarks VALUES("+null+", "+marks+")";

good practice to use prepareStatement() method to insert,delete and update.

and use resultset like:

if(rs.next)
{
   int num = res.getInt("count1");
}

Upvotes: 0

chrylis -cautiouslyoptimistic-
chrylis -cautiouslyoptimistic-

Reputation: 77167

The Java ResultSet is a cursor; it logically points to a particular position within the results of some query. From the Javadocs:

A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

You're trying to read from the ResultSet while it's still pointing above the top of the list (the position beforeFirst). To advance the cursor, call res.next(), which returns a boolean indicating whether there are more rows. If you want to print the results from several rows, use a while loop like this:

while(res.next()) {
    System.out.println(res.getInt("count1"));
}

Upvotes: 0

Rahul
Rahul

Reputation: 45060

Before trying to get the value

int num = res.getInt("count1");

you need to call ResultSet#next() (and check that it returns true) to access the first row of the result set

if (rs.next()) {
    // Do what you want
    int num = res.getInt("count1");
    System.out.println(num);
}

To keep iterating over the result set, you can use the while loop.

Upvotes: 3

Related Questions