Getting Data from a Result Set
A result set contains the results of a SQL query. The results are
kept in a set of rows, one of which is designated the current
row. A row must be made current before data can be retrieved from
it. The result set maintains a reference to the current row called the
cursor.
The cursor is positioned before the first row when a result set
is created. When a result set's next() method is called, the cursor
moves to the first row of the result set and that row becomes the
current row.
There are two ways to retrieve the data from the current row.
The first uses a column index starting from 1. The second uses a
column name. For example, with the query `\cv{SELECT col1, col2 FROM
table}', the value for col2 can be retrieved using a column index
of 2 or with the column name col2. This example demonstrates both
methods.
Here is another example of retrieving data from a result that
uses the various
getXXX() methods.
This example uses the table created in
Creating a MySQL Table to Store Java Types.
try {
// Create a result set containing all data from my_table
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");
// Fetch each row from the result set
while (rs.next()) {
// Get the data from the row using the column index
String s = rs.getString(1);
// Get the data from the row using the column name
s = rs.getString("col_string");
}
} catch (SQLException e) {
}
try {
// Create a result set containing all data from mysql_all_table
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mysql_all_table");
// Fetch each row from the result set
while (rs.next()) {
boolean bool = rs.getBoolean("col_boolean");
byte b = rs.getByte("col_byte");
short s = rs.getShort("col_short");
int i = rs.getInt("col_int");
long l = rs.getLong("col_long");
float f = rs.getFloat("col_float");
double d = rs.getDouble("col_double");
BigDecimal bd = rs.getBigDecimal("col_bigdecimal");
String str = rs.getString("col_string");
Date date = rs.getDate("col_date");
Time t = rs.getTime("col_time");
Timestamp ts = rs.getTimestamp("col_timestamp");
InputStream ais = rs.getAsciiStream("col_asciistream");
InputStream bis = rs.getBinaryStream("col_binarystream");
Blob blob = rs.getBlob("col_blob");
}
} catch (SQLException e) {
}
I guess your example doesn't work too good. Having that while(rs.next()) there, I suppose the first thing the algoritm does is to make the next(), skipping the first element of the result set. Isn't that correct?
Nope because the pointer of the result set is pointing to the position before the first element.
"A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row." ref: http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html
Spot on example, thank you very much. I couldn't find a more concise or accurate example anywhere.