728x90
반응형
SQL
ERD
K_EMPLOYEE TABLE
DEPARTMENT TABLE
empno가 1에 해당하는 ename, sal, deptno, dname, loc, tel을 조회하여 출력
SELECT e.ename, e.sal, d.deptno, d.dname, d.loc, d.tel
FROM k_employee e, department d
WHERE e.deptno = d.deptno and e.empno = 1;
Java JDBC
UML Diagram
TestDAO class (main class)
TestJoin
기능 1. findEmpAndDeptInfo 메서드 (input - string empno /output - Employee e)
k_employee table과 department 테이블을 조인(결합)해서
empno에 해당하는 사원번호를 가진 사원의 사원정보와 부서정보를 조회하고자 한다
TestJoin2
기능 2. getEmpAndDeptInfoList 메서드 (input - X / output - ArrayList<Employee>)
사원 정보와 사원이 속한 부서 정보가 출력
/TestJoin.java
package test;
import java.sql.SQLException;
import model.Employee;
import model.EmployeeDAO;
public class TestJoin {
public static void main (String[] args) {
EmployeeDAO dao;
try {
dao = new EmployeeDAO();
/*
* k_employee table과 department 테이블을 조인(결합)해서
* 1번 사원번호를 가진 사원의 사원정보와 부서정보를 조회하고자 한다
* (이 형태는 빈번하게 사용되고,
* 때로는 사원정보만 조회해서 화면제공하고,
* 때로는 부서정보만 조회해서 화면이 제공되기도 한다.)
* -> UML (Class Diagram)을 정의해보자
*/
String empno = "1";
//empno에 해당하는 ename, sal, deptno, dname, loc, tel을 조회하여 출력
Employee e = dao.findEmpAndDeptInfo(empno);
System.out.println("사원번호 : "+empno);
System.out.println("사원명 : "+e.getEname());
System.out.println("사원월급 : "+e.getSalary());
System.out.println("부서번호 : "+e.getDepartment().getDeptno());
System.out.println("부서명 : "+e.getDepartment().getDname());
System.out.println("지역 : "+e.getDepartment().getLoc());
System.out.println("부서 전화번호 : "+e.getDepartment().getTel());
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
/TestJoin2.java
package test;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Employee;
import model.EmployeeDAO;
public class TestJoin2 {
public static void main (String[] args) {
EmployeeDAO dao;
try {
dao = new EmployeeDAO();
ArrayList<Employee> list = dao.getEmpAndDeptInfoList();
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
DAO ( Data Access Object ) class
/EmployeeDAO.java
package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import common.DbInfo;
public class EmployeeDAO {
// 생성자 - Driver Loading
public EmployeeDAO() throws ClassNotFoundException {
Class.forName(DbInfo.DRIVER_NAME);
}
// closeAll 1
public void closeAll(PreparedStatement pstmt, Connection con) throws SQLException {
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
}//closeAll
// closeAll 2
public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) throws SQLException {
if (rs != null)
rs.close();
closeAll(pstmt, con);
}//closeAll
// findEmpAndDeptInfo method
public Employee findEmpAndDeptInfo(String empno) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Employee e = null;
try {
con = DriverManager.getConnection(DbInfo.URL, DbInfo.USER, DbInfo.PASS);
StringBuilder sb = new StringBuilder();
sb.append("SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc, d.tel ");
sb.append("FROM k_employee e, department d ");
sb.append("WHERE e.deptno = d.deptno and e.empno = ?");
pstmt = con.prepareStatement(sb.toString());
pstmt.setString(1, empno);
rs = pstmt.executeQuery();
if(rs.next()) {
Department d = new Department();
d.setDeptno(rs.getString("deptno"));
d.setDname(rs.getString("dname"));
d.setLoc(rs.getString("loc"));
d.setTel(rs.getString("tel"));
e = new Employee();
e.setEmpno(rs.getString("empno"));
e.setEbame(rs.getString("ename"));
e.setSalary(rs.getInt("sal"));
e.setDepartment(d);
}
} finally {
closeAll(rs, pstmt, con);
}
return e;
}
// getEmpAndDeptInfoList method
public ArrayList<Employee> getEmpAndDeptInfoList() throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<Employee> list = new ArrayList<Employee>();
try {
con = DriverManager.getConnection(DbInfo.URL, DbInfo.USER, DbInfo.PASS);
StringBuilder sql = new StringBuilder();
sql.append("SELECT e.*, d.* ");
sql.append("FROM k_employee e, department d ");
sql.append("WHERE e.deptno = d.deptno");
pstmt = con.prepareStatement(sql.toString());
rs = pstmt.executeQuery();
while(rs.next()) {
Department d = new Department();
d.setDeptno(rs.getString("deptno"));
d.setDname(rs.getString("dname"));
d.setLoc(rs.getString("loc"));
d.setTel(rs.getString("tel"));
Employee e = new Employee(rs.getString("empno"),
rs.getString("ename"),
rs.getInt("sal"),
d);
list.add(e);
}
} finally {
closeAll(rs, pstmt, con);
}
return list;
}
}
VO class
/Department.java
package model;
//DepartmentVO 대신 그냥 Department로 써주어도 된다
public class Department {
private String deptno;
private String dname;
private String loc;
private String tel;
// 기본 생성자
public Department() {
super();
}
// 생성자
public Department(String deptno, String dname, String loc, String tel) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
this.tel = tel;
}
@Override
public String toString() {
return "Department [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + ", tel=" + tel + "]";
}
public String getDeptno() {
return deptno;
}
public void setDeptno(String deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
}
/Employee.java
package model;
public class Employee {
private String empno;
private String ename;
private int salary;
private Department department;//has a, aggregation relationship
// 기본 생성자
public Employee() {
super();
}
// 부서 정보 제외한 사원 정보 생성자
public Employee(String empno, String ename, int salary) {
super();
this.empno = empno;
this.ename = ename;
this.salary = salary;
}
// 모든 정보 생성자
public Employee(String empno, String ename, int salary, Department department) {
super();
this.empno = empno;
this.ename = ename;
this.salary = salary;
this.department = department;
}
@Override
public String toString() {
return "Employee [empno=" + empno + ", name=" + ename + ", salary=" + salary + "] | " + department;
}
public String getEmpno() {
return empno;
}
public void setEmpno(String empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEbame(String name) {
this.ename = name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
}
728x90
반응형
