JDBC - Select

This chapter demonstrates the JDBC example of retrieving records from the NAMECARD table. Our goal is to run the following SQL using JDBC.

SELECT NO,NAME,MOBILE,EMAIL,COMPANY 
FROM NAMECARD
ORDER BY NO DESC

Complete the main() of NamecardSelect.java by referring the JDBC programming procedure below.

  1. Loading a JDBC Driver
  2. Getting a Connection
  3. Execute SQL
  4. [If the SQL statement is a select statement, use ResultSet to process the data.]
  5. Returning Resources
NamecardSelect.java
package net.java_school.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class NamecardSelect {
  static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
  static final String USER = "scott";
  static final String PASS = "tiger";
	
  public static void main(String[] args) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
		
    String sql = "SELECT NO,NAME,MOBILE,EMAIL,COMPANY " +
      "FROM NAMECARD " +
      "ORDER BY NO DESC";

    try {
      con = DriverManager.getConnection(URL, USER, PASS);
      stmt = con.createStatement();
      rs = stmt.executeQuery(sql);

      while (rs.next()) {
        int no = rs.getInt("no");
        String name = rs.getString("name");
        String mobile = rs.getString("mobile");
        String email = rs.getString("email");
        String company = rs.getString("company");
        System.out.println(no + "|" + name + "|" + mobile + "|" + email + "|" + company);
      }
			
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println(sql);
    } finally {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        stmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

}
ResultSet getXXX() method
It is not actually named getXXX(). XXX means a data type fo Java. If the data type of the first column in the result set is NUMBER, you can replace the XXX part with Int, Long, Double,.. one of the Java data types. If you pass an index of the column as an argument value like getInt(1), you will get the column value as a Java int type. You can pass the column name as an argument value like our example. Of course, performance is faster to pass indexes, but giving column names is better for maintenance.