How To Banking Concern Fit If Resultset Is Empty Inward Jdbc - Coffee Example

The JDBC ResultSet doesn't render whatever isEmpty(), length() or size() method to banking concern gibe if its empty or not. Hence, when a Java programmer needs to create upward one's heed if ResultSet is empty or not, it simply calls the next() method as well as if next() render false it agency ResultSet is empty. This is right but the principal occupation amongst this approach is if the ResultSet is non empty thence you lot may missy the source row if you lot follow the criterion idiom to iterate over ResultSet as well as acquire the rows which involve calling next() method of ResultSet inwards a land loop. The telephone substitution thing, which you lot require to retrieve is that initially the ResultSet's cursor points to earlier the source row when you lot telephone band the next() method it points to the source row as well as if you lot don't acquire this information as well as calls the next() method 1 time again thence you lot volition lose the source row.

Alternatively, you lot require to telephone band the beforeFirst() method to reset the cursor where it was earlier i.e. earlier the source row. Since beforeFirst() is non guaranteed to endure supported inwards all database e.g. HSQL database doesn't back upward it, you lot should rather role a do-while loop to iterate over ResultSet. This way you lot won't lose the source row every bit shown inwards our sample program.

If you lot desire to know to a greater extent than close how to function effectively amongst a database inwards Java application I advise reading Practical Database Programming amongst Java By Ying Bai, he has explained many subtle details of JDBC as well as Java, which are essential for whatever experienced Java programmer.

Wrong way to banking concern gibe if ResultSet is empty

When you lot enquire a Java developer to impress something when ResultSet is empty as well as display the termination otherwise, this is how most of them write at source :

    Statement stmt = con.createStatement();     ResultSet rs = stmt.executeQuery("SELECT * from Employee");      // checking if ResultSet is empty     if (rs.next() == false) {       System.out.println("ResultSet inwards empty inwards Java");     }      // Iterating over ResultSet, since you lot are calling     // the side past times side method again, it volition at nowadays indicate to     // the instant row as well as you lot bring missed the source row     while (rs.next()) {       String data = rs.getString("MIC");       System.out.println(data);     }

This seems pretty straightforward as well as many Java developer volition non uncovering whatever occupation or põrnikas unless person reports to them that around information is missing. Since this code doesn't throw whatever compile fourth dimension fault or runtime exception but introduces a subtle logical bug, which is really difficult to describe specially if you lot are non a Java veteran working inwards JDBC for years.

The code volition function fine if ResultSet is empty but if ResultSet is non empty it introduces a difficult to uncovering bug, where the source row of the termination is missed. When you lot telephone band the next() method source time, the ResultSet cursor moves to the source row but this fourth dimension if you lot don't telephone band getXXX() method to retrieve information from the row as well as telephone band the next() method 1 time again than ResultSet cursor volition indicate to the instant row as well as you lot bring lost the source row, at to the lowest degree your application volition think that entirely N-1 records are returned.

If you lot are non familiar amongst retrieving information from ResultSet, you lot tin read Core Java, Volume II--Advanced Features past times Cay S. Horstmann to start learning basics of JDBC. One of the meliorate mass to larn JDBC.

Right way to banking concern gibe if ResultSet is empty

There are span of solution of higher upward occupation e.g. calling the first() or beforeFirst() to reset the ResultSet cursor dorsum to the source row, but these methods are non guaranteed to endure supported inwards all database e.g. HSQL doesn't back upward first() and beforeFirst().

H5N1 meliorate way to solve this occupation is to acquire the information from the source row earlier calling the next() method 1 time again as well as you lot tin create that past times using create land loop, which checks the status at the terminate of the loop every bit oppose to start of the loop inwards instance of a land loop. This way, you lot volition non lose the source row from ResultSet.

 if (rs.next() == false) {         System.out.println("ResultSet inwards empty inwards Java");       } else {          do {           String data = rs.getString("emp_name");           System.out.println(data);         } while (rs.next());       }

You tin run across that nosotros are source checking if rs.next() as well as thence straight off printing the termination using a do-while loop to avoid losing the source row. This is the right way to banking concern gibe if ResultSet is empty as well as entirely procedure tape when it actually has. See Java How to Program past times Dietel to larn to a greater extent than close getting as well as setting information from ResultSet.

Java instance to banking concern gibe if ResultSet is empty or not

Here is a sample Java plan to demonstrate how to banking concern gibe if a ResultSet returned every bit termination of executeQuery() method is empty or not. This method assumes that you lot bring an Employee tabular array as well as you lot are running an SQL Server instance inwards your machine i.e. localhost. The query fetches all employees for a subdivision as well as displays message if the resultset is empty or listing of employees otherwise.

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet;  public class DBTest {    public static void main(String args[]) {      Connection con = null;     try {        String url = "jdbc:sqlserver://localhost:42588;";        // no require to telephone band Class.forName(driver) from JDK half-dozen onward       con = DriverManager.getConnection(url, "admin", "root");        Statement stmt = con.createStatement();       ResultSet rs = stmt.executeQuery("SELECT * from Employee");        if (rs.next() == false) {         System.out.println("ResultSet inwards empty inwards Java");       } else {          do {           String data = rs.getString("emp_name");           System.out.println(data);         } while (rs.next());       }      } catch (Exception e) {       e.printStackTrace();     }    }  }

That's all close how to banking concern gibe if JDBC ResultSet is empty inwards Java or not. Though you lot tin role the isBeforeFirst() method to banking concern gibe if ResultSet contains whatever row or not, it's non guaranteed to endure supported inwards all database e.g. HSQL DB doesn't back upward it. Instead of using traditional do-while loop approach is guaranteed to function inwards all database e.g. Oracle, SQL Server, MySQL, as well as PostgreSQL. It's simple, elegant as well as readable.


Other JDBC tutorials you lot may like

• 10 JDBC Best Practices Every Java Programmer should follow (read)
• Difference betwixt Type 1 as well as Type iv JDBC drivers (answers)
• JDBC Batch Insert as well as Update instance using PreparedStatement (tutorial)
• Top 10 JDBC Interview Question for Java programmers (read)
• How to connect to MySQL database from Java Program? (tutorial)
• Difference betwixt java.sql.Date, java.sql.Timestamp, as well as java.util.Date inwards JDBC? (answer)
• Java Program to connect Oracle database using JDBC (tutorial)
• 6 Essential JDBC Performance tips for Java Programmers (tips)
• How to connect Microsoft SQL Server from Eclipse? (tutorial)
• How to convert java.util.Date to java.sql.Date inwards Java? (example)
• Why role PreparedStatement inwards Java? (answer)

Further Learning
JSP, Servlets as well as JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 as well as 2
Java Platform: Working amongst Databases Using JDBC

Share this post