Select Records JDBC Example

Select Records JDBC Example: To retrieve records from Employee table in Oracle Database.

By the time this program is executed, see some records exist in Employee table.

Example on Select Records JDBC Example

import java.sql.*;
public class RetrieveRecords
{
  public static void main(String args[]) throws Exception
  {                                            // standard 3 JDBC statements
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
    Statement stmt = con.createStatement();
                                               // send a SQL query to retrieve records
    ResultSet res = stmt.executeQuery("select *from Employee");
                                               // process the data
    while(res.next())
    {
      System.out.println(res.getInt(1) + "\t" + res.getString(2) + "\t" + res.getDouble(3));
    }
                                               // close the database connection
    res.close();
    stmt.close();
    con.close();
   }
}  

a) Statement interface includes two methods (discussed in Learning JDBC Programming).

1. executeQuery(String) used for select statements that returns an object ResultSet interface.

2. executeUpdate(String) used for any statement other than select statement that returns a primitive data type int.

b)System.out.println(res.getInt(1)+"\t"+res.getString(2)+"\t"
+res.getDouble(3));

Column number parameters in the above statement can be replaced with column names as follows:

System.out.println(res.getInt("empid")+"\t"+res.getString("empname")+"
\t"+res.getDouble("empsal"));

c) For printing purpose we can also write:

System.out.println(res.getString(1)+"\t"+res.getString(2)
+"\t".getString(3));

OR

System.out.println(res.getString("empid")+"\t"+res.gettring("empname")
+"\t"+res.getString("empsal"));

d) The next() method of ResultSet interface returns true if records exist. If all the records are over, the method returns false and the loop terminates.

The difference between getInt(1) and getString(1) is the first one returns the value of the column as an integer which can be used in the program directly for mathematical calculations and the second method returns a string. This string is useful just to print the value but cannot be used in the program for arithmetic operations.

getInt(), getString() and getDouble() methods of ResultSet interface returns an integer, a string and double value. Some more get() methods exist and are discussed in JDBC Programming Basic Steps

ResultSet Cursor Movement or how next() method executes

Let us see how the while loop returns all the records as it is required almost in each JDBC program.

          
while(res.next())
{
 System.out.println(res.getInt(1) + "\t" + res.getString(2) + "\t" + res.getDouble(3));
}

JDBC maintains a cursor over the records. By default the cursor lies before the first record where actually no record exists. The next() method moves the cursor to the first record. Now the cursor is pointing to the first record. I call the first record as the current record (the record which the cursor points). The getXXX() methods read the fields of the current record, that is first record.

After getXXX() methods read the current record data, the iteration continuous. Now the next() method moves the cursor over to the 2nd record. If the cursor points a record, the next() method returns a boolean value of true and the execution goes into the loop body. The getXXX() methods read the 2nd record data. Now the current record is 2nd record. That is, getXXX() methods read always the current record data. When the records get exhausted, the cursor comes after the last record where there is no record (other way, there is no current record). Now the next() method returns false and the loop terminates. It is similar to the Enumeration iteration.

Note 1: Before going into the program, it is advised to go through JDBC Programming Basic Steps where the meaning of Connection, Statement etc. are discussed.

Note 2: To know how to use try-catch-finally with the above code, example program is available at JDBC Example – Create Table.