JDBC - Join

Since there is only one table for name card example, Let's practice the example of joining EMP and DEPT.

Our goal is to execute the following SQL:

SELECT ENAME, JOB, D.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.LOC = 'DALLAS'

Refer to the JDBC programming procedure below and complete the main() of the JoinTable.java.

1. Loading a JDBC Driver
2. Getting a Connection
3. Execute SQL
4. [If the SQL statement is a select statement, use the returned ResultSet to process data.]
5. Returning Resources

JoinTable.java

package net.java_school.jdbc.test;

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

public class JoinTable {
  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) {
      e.printStackTrace();
    }
		
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
		
    String sql = "SELECT ENAME,JOB,D.DEPTNO,DNAME " +
        "FROM EMP E, DEPT D " +
        "WHERE E.DEPTNO = D.DEPTNO " +
        "AND D.LOC = 'DALLAS'";
		
    try {
      con = DriverManager.getConnection(URL, USER, PASS);
      pstmt = con.prepareStatement(sql);
      rs = pstmt.executeQuery();
			
      while (rs.next()) {
        String ename = rs.getString("ename");
        String job = rs.getString("job");
        String deptno = rs.getString("deptno");
        String dname = rs.getString("dname");
        System.out.println(ename + " " + job + " " + deptno + " " + dname);
      }
			
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println(sql);
    } finally {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

}