ResultSet rs = rs.executeQuery();
if(rs.next())
{
rs = rs.executeQuery();
while(rs.next())
{
//do code part
}
}
else
{
//else if no result set
}
It is better to re execute query because when we call if(rs.next()){....} first row of ResultSet will be executed and after it inside while(rs.next()){....} we'll get result from next line. So I think re-execution of query inside if is the better option.
This is a practical and easy read piece I believe.
if (res.next()) {
do {
// successfully in. do the right things.
} while (res.next());
} else {
// no results back. warn the user.
}
if (!resultSet.isAfterLast() ) {
System.out.println("No data");
}
isAfterLast() also returns false for empty result set but since cursor is before first row anyways, this method seems more clear.
That would work if you want to see if there are any rows in the result set yes.
Note that next() always moves to the next row, so if you are planning on doing any reading from the result set you need to take that into account.
Usual usage with ResultSet (when simply reading) is:
while (resultSet.next())
{
... read from the row here ...
}
Which obviously won't work correctly if you invoked next() once already to check if the result set was empty, so watch out for that. Although there are methods for "backing up", they are not supported for all types of result sets.
To be totally sure of rather the resultset is empty or not regardless of cursor position, I would do something like this:
public static boolean isMyResultSetEmpty(ResultSet rs) throws SQLException {
return (!rs.isBeforeFirst() && rs.getRow() == 0);
}
This function will return true if ResultSet is empty, false if not or throw an SQLException if that ResultSet is closed/uninitialized.
Initially, the result set object (rs) points to the BFR (before first record). Once we use rs.next(), the cursor points to the first record and the rs holds "true". Using the while loop you can print all the records of the table. After all the records are retrieved, the cursor moves to ALR (After last record) and it will be set to null. Let us consider that there are 2 records in the table.
if(rs.next()==false){
// there are no records found
}
while (rs.next()==true){
// print all the records of the table
}
In short hand, we can also write the condition as while (rs.next()).
Why not use rs.getRow()?
int getRow()
throws SQLException
Retrieves the current row number. The first row is number 1, the second number 2, and so on.
Note:Support for the getRow method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY
Returns:
the current row number; 0 if there is no current row
Throws:
SQLException - if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException - if the JDBC driver does not support this method
Since:
1.2
For me check "if (rs.getRow() != 0)" seems to work just fine.
The best thing for to do is to check the first row so that when you intend to get the data you can avoid the mistake of skipping a row. Something like: if (!resultSet.first() ) { System.out.println("no data"); }
Best to use ResultSet.next() along with the do {...} while() syntax for this.
The "check for any results" call ResultSet.next() moves the cursor to the first row, so use the do {...} while() syntax to process that row while continuing to process remaining rows returned by the loop.
This way you get to check for any results, while at the same time also processing any results returned.
if(resultSet.next()) { // Checks for any results and moves cursor to first row,
do { // Use 'do...while' to process the first row, while continuing to process remaining rows
} while (resultSet.next());
}
Assuming you are working with a newly returned ResultSet whose cursor is pointing before the first row, an easier way to check this is to just call isBeforeFirst(). This avoids having to back-track if the data is to be read.
As explained in the documentation, this returns false if the cursor is not before the first record or if there are no rows in the ResultSet.
if (!resultSet.isBeforeFirst() ) {
System.out.println("No data");
}
According to the most viable answer the suggestion is to use "isBeforeFirst()". That's not the best solution if you don't have a "forward only type".
There's a method called ".first()". It's less overkill to get the exact same result. You check whether there is something in your "resultset" and don't advance your cursor.
The documentation states: "(...) false if there are no rows in the result set".
if(rs.first()){
//do stuff
}
You can also just call isBeforeFirst() to test if there are any rows returned without advancing the cursor, then proceed normally. – SnakeDoc Sep 2 '14 at 19:00
However, there's a difference between "isBeforeFirst()" and "first()". First generates an exception if done on a resultset from type "forward only".
Compare the two throw sections: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#isBeforeFirst() http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#first()
Okay, basically this means that you should use "isBeforeFirst" as long as you have a "forward only" type. Otherwise it's less overkill to use "first()".
I created the following method to check if a ResultSet is empty.
public static boolean resultSetIsEmpty(ResultSet rs){
try {
// We point the last row
rs.last();
int rsRows=rs.getRow(); // get last row number
if (rsRows == 0) {
return true;
}
// It is necessary to back to top the pointer, so we can see all rows in our ResultSet object.
rs.beforeFirst();
return false;
}catch(SQLException ex){
return true;
}
}
It is very important to have the following considerations:
CallableStatement object must be setted to let to ResultSet object go at the end and go back to top.
TYPE_SCROLL_SENSITIVE: ResultSet object can shift at the end and go back to top. Further can catch last changes.
CONCUR_READ_ONLY: We can read the ResultSet object data, but can not updated.
CallableStatement proc = dbconex.prepareCall(select, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet result = stmt.executeQuery(sqlQuery);
if (!result.next())
status = "ERROR";
else
status = "SUCCESS";
I've been attempting to set the current row to the first index (dealing with primary keys). I would suggest
if(rs.absolute(1)){
System.out.println("We have data");
} else {
System.out.println("No data");
}
When the ResultSet is populated, it points to before the first row. When setting it to the first row (indicated by rs.absolute(1)) it will return true denoting it was successfully placed at row 1, or false if the row does not exist. We can extrapolate this to
for(int i=1; rs.absolute(i); i++){
//Code
}
which sets the current row to position i and will fail if the row doesn't exist. This is just an alternative method to
while(rs.next()){
//Code
}
I think the easiest way for checking result set is via CollectionUtils under package org.apache.commons.collections.CollectionUtils
if(CollectionUtils.isNotEmpty(resultList)){
/**
* do some stuff
*/
}
This will check for null as well as empty result set condition.
For more detail information you can refer to the following doc. CollectionUtils
By using resultSet.next() you can easily get the result, whether resultSet containing any value or not
ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next())
//resultSet contain some values
else
// empty resultSet
you could always do the next up front, and just do a post loop check
if (!resultSet.next() ) {
System.out.println("no data");
} else {
do {
//statement(s)
} while (resultSet.next());
}
you can do something like this
boolean found = false;
while ( resultSet.next() )
{
found = true;
resultSet.getString("column_name");
}
if (!found)
System.out.println("No Data");
You would usually do something like this:
while ( resultSet.next() ) {
// Read the next item
resultSet.getString("columnName");
}
If you want to report an empty set, add a variable counting the items read. If you only need to read a single item, then your code is adequate.
if(resultSet.first) {
} else {
system.out.println("No raw or resultSet is empty");
}
Because if ResultSet has no raw then resultSet.first returns false.
Source: Stackoverflow.com