성을 입력받아 같은 성을 가진 직원을 조회하는 쿼리를 XML 파일에 작성하여 불러와서 수행하는 방법.
1. XML 파일안에 쿼리문 작성
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<!-- 성으로 직원 조회용 쿼리 -->
<entry key="selectEmpByFamilyName">
SELECT
E.*
FROM EMPLOYEE E
WHERE E.EMP_NAME LIKE ? || '%'
</entry>
</properties>
2. employee-query.xml에서 쿼리문 가져와서 수행
Connection con = getConnection();
PreparedStatement pstmt = null;
ResultSet rset = null;
EmployeeDTO row = null;
List<EmployeeDTO> empList = null;
Scanner sc = new Scanner(System.in);
System.out.print("조회할 이름의 성을 입력하세요 : ");
String empName = sc.nextLine();
Properties prop = new Properties();
try {
prop.loadFromXML(new FileInputStream("파일경로/파일명.xml"));
String query = prop.getProperty("selectEmpByFamilyName");
System.out.println("query : " + query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, empName);
rset = pstmt.executeQuery();
empList = new ArrayList<>();
while(rset.next()) {
row = new EmployeeDTO();
row.setEmpId(rset.getString("EMP_ID"));
row.setEmpName(rset.getString("EMP_NAME"));
row.setEmpNo(rset.getString("EMP_NO"));
row.setEmail(rset.getString("EMAIL"));
row.setPhone(rset.getString("PHONE"));
row.setDeptCode(rset.getString("DEPT_CODE"));
row.setJobCode(rset.getString("JOB_CODE"));
row.setSalLevel(rset.getString("SAL_LEVEL"));
row.setSalary(rset.getInt("SALARY"));
row.setBonus(rset.getDouble("BONUS"));
row.setManagerId(rset.getString("MANAGER_ID"));
row.setHireDate(rset.getDate("HIRE_DATE"));
row.setEntDate(rset.getDate("ENT_DATE"));
row.setEntYn(rset.getString("ENT_YN"));
empList.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (InvalidPropertiesFormatException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
close(rset);
close(pstmt);
close(con);
}
for(EmployeeDTO emp : empList) {
System.out.println(emp);
}
}
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] Chap14 권한과 ROLL (0) | 2022.01.29 |
---|---|
[Oracle] Chap13 SYNONYM (0) | 2022.01.28 |
[Oracle] Chap12 INDEX (0) | 2022.01.28 |
[Oracle] Chap11 SEQUENCE (0) | 2022.01.27 |
[Oracle] Chap10 VIEW (0) | 2022.01.27 |