JDBC - Join

Since there is only one table for business card management, we 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 method of the JoinTable.java file.

  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();
			}
		}
	}

}