JDBC 실습구조
Package 구조(MVC)
Model
Class 구조
JDBCExample3
package edu.kh.jdbc1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import edu.kh.jdbc1.model.vo.Emp;
public class JDBCExample3 {
public static void main (String[] args) {
// 부서명을 입력받아 같은 부서에 있는 사원의
// 사원명, 부서명, 급여 조회
Scanner sc = new Scanner(System.in);
// JDBC 객체 참조 변수 선언
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.println("부서명 입력 : ");
String input = sc.nextLine();
// JDBC 참조변수에 알맞은 객체 대입
Class.forName("oracle.jdbc.driver.OracleDriver");
String type = "jdbc:oracle:thin:@";
String ip = "localhost";
String port = ":1521";
String sid = ":XE";
String user = "kh";
String pw = "kh1234";
conn = DriverManager.getConnection(type+ip+port+sid, user, pw);
//jdbc:oracle:thin:@localhost:1521:XE == url
// SQL 작성
String sql = "SELECT EMP_NAME, NVL(DEPT_TITLE, '부서없음') AS DEPT_TITLE, SALARY"
+ " FROM EMPLOYEE"
+ " LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)"
+ " WHERE NVL(DEPT_TITLE, '부서없음') = '" + input + "'";
// Java 에서 작성되는 SQL에
// 문자열 변수 추가할 경우
// '' (DB 문자열 리터럴) 이 누락되지 않도록 신경써야한다!
// 만약 '' 미작성 시 String 값은 컬럼명으로 인식되어
// 부적합한 식별자 오류가 발생한다!
stmt = conn.createStatement(); // Statement 객체 생성
// Statement 객체를 이용해서
// SQL(SELECT)을 DB에 전달하여 실행한 후
// ResultSet을 반환받아 rs 변수에 대입
rs = stmt.executeQuery(sql);
// 조회 결과(rs) 를 List에 옮겨담기
List<Emp> list = new ArrayList<>();
while(rs.next()) { // 다음 행으로 이동해서 해당 행에 데이터가 있으면 true
// 현재 행에 존재하는 컬럼 값 얻어오기
String empName = rs.getString("EMP_NAME");
String deptTitle = rs.getString("DEPT_TITLE");
int salary = rs.getInt("SALARY");
// Emp 객체를 생성하여 컬럼값 담기
Emp emp = new Emp(empName, deptTitle, salary);
//생성된 Emp 객체를 List에 추가
list.add(emp);
}
// 만약 List에 추가된 Emp 객체가 없다면 "조회 결과가 없습니다"
// 있다면 순차적으로 출력
if(list.isEmpty()) { // List가 비어있을 경우
System.out.println("조회 결과가 없습니다");
} else {
// 향상된 for문
for(Emp emp : list) {
System.out.println(emp);
}
}
}catch(Exception e) {
e.printStackTrace();
} finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}JDBCExample4
package edu.kh.jdbc1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import edu.kh.jdbc1.model.vo.Employee;
public class JDBCExample4 {
// 직급명, 급여를 입력 받아
// 해당 직급에서 입력 받은 급여보다 많이 받는 사원의
// 이름, 직급명, 급여, 연봉을 조회하여 출력
// 단, 조회 결과가 없으면 "조회 결과 없음" 출력
// 조회 결과가 있으면 아래와 같이 출력
// 선동일 / 대표 / 8000000 / 96000000
// 송중기 / 부장 / 6000000 / 72000000
// ...
public static void main (String[] args) {
Scanner sc = new Scanner(System.in);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.print("직급명 입력 : ");
String inputJobName = sc.nextLine();
System.out.print("급여 입력 : ");
int inputSalary = sc.nextInt();
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:XE";
String user = "kh";
String pw = "kh1234";
conn = DriverManager.getConnection(url, user, pw);
String sql = "SELECT EMP_NAME, JOB_NAME, SALARY, SALARY * 12 ANNUAL_INCOME"
+ " FROM EMPLOYEE"
+ " JOIN JOB USING (JOB_CODE)"
+ " WHERE JOB_NAME = '" + inputJobName + "'"
+ " AND SALARY > " + inputSalary;
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
List<Employee> list = new ArrayList<>();
while(rs.next()) {
Employee emp = new Employee(); // 기본 생성자로 Employee 객체 생성
emp.setEmpName( rs.getString("이름") ); // 조회 시 컬럼명이 "이름"
emp.setJobName( rs.getString("직급명") );
emp.setSalary( rs.getInt("급여") );
emp.setAnnualIncome( rs.getInt("연봉") );
list.add(emp);
}
if(list.isEmpty()) {
System.out.println("조회 결과 없음");
} else {
for(Employee emp : list) {
System.out.println(emp);
}
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}JDBCExample5
package edu.kh.jdbc1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import edu.kh.jdbc1.model.vo.Employee;
public class JDBCExample5 {
public static void main(String[] args) {
// 입사일을 입력("2022-09-06") 받아
// 입력 받은 값 보다 먼저 입사한 사람의
// 이름, 입사일, 성별(M,F) 조회
Scanner sc = new Scanner(System.in);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
System.out.print("입사일 입력(YYYY-MM-DD) : ");
String input = sc.next();
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:XE";
String user = "kh";
String pw = "kh1234";
conn = DriverManager.getConnection(url, user, pw);
String sql = "SELECT EMP_NAME 이름, TO_CHAR(HIRE_DATE, 'YYYY\"년\" MM\"월\" DD\"일\"') 입사일,"
+ " DECODE ( SUBSTR(EMP_NO, 8, 1) , '1' , 'M', '2', 'F') 성별"
+ " FROM EMPLOYEE"
+ " WHERE HIRE_DATE < TO_DATE('" + input + "')";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
List<Employee> list = new ArrayList<Employee>();
while(rs.next()) {
Employee emp = new Employee(); // 기본 생성자로 Employee 객체 생성
emp.setEmpName( rs.getString("이름") ); // 조회 시 컬럼명이 "이름"
emp.setHireDate( rs.getString("입사일") );
emp.setGender( rs.getString("성별").charAt(0) );
// Java의 char : 문자 1개 의미
// DB의 CHAR : 고정길이 문자열(==String)
list.add(emp);
}
// 조회결과 없는 경우
if(list.isEmpty()) {
System.out.println("조회 결과 없음");
} else {
for(int i = 0; i<list.size(); i++) {
System.out.printf("%02d) %s / %s / %c \n",
i+1,
list.get(i).getEmpName(),
list.get(i).getHireDate(),
list.get(i).getGender() );
}
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}JDBCTemplate
package edu.kh.jdbc.common;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTemplate {
/* DB 연결 (Connection 생성), 자동 커밋 off
* 트랜잭션 제어, JDBC 객체 자원 반환(close)
*
* 이러한 JDBC에서 반복 사용되는 코드를 모아둔 클래스
*
* * 모든 필드, 메서드가 static *
* -> 별도 객체 생성 X
* -> 어디서든지 클래스명.필드명 / 클래스명.메서드명 호출 가능
*
* */
private static Connection conn = null;
/** DB 연결 정보를 담고있는 Connection 객체생성 및 반환 메서드
* @return
*/
public static Connection getConnection() {
try {
// 현재 커넥션 객체가 없을 경우 -> 새 커넥션 객체 생성
if( conn == null || conn.isClosed() ) {
// conn.isClosed() : 커넥션이 close 상태면 true 반환
Properties prop = new Properties();
// Map<String, String> 형태의 객체, XML 입출력 특화
// driver.xml 파일 읽어오기
prop.loadFromXML( new FileInputStream("driver.xml") );
// -> XML 파일에 작성된 내용이 Properties 객체에 모두 저장됨.
// XML에서 읽어온 값을 모두 변수에 저장
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String password = prop.getProperty("password");
// 커넥션 생성
Class.forName(driver); // Oracle JDBC Driver 객체 메모리 로드
// DriverManager를 이용해 Connection 객체 생성
conn = DriverManager.getConnection(url, user, password);
// 자동 커밋 비활성화
conn.setAutoCommit(false);
}
}catch(Exception e) {
System.out.println("[Connection 생성 중 예외 발생]");
e.printStackTrace();
}
return conn;
}
/** Connection 객체 자원 반환 메서드
* @param conn
*/
public static void close(Connection conn) {
try {
// 전달 받은 conn이
// 참조하는 Connection 객체가 있고
// 그 Connection 객체가 close 상태가 아니라면
if(conn != null && !conn.isClosed()) conn.close();
} catch(Exception e) {
e.printStackTrace();
}
}
/** Statement(부모), PreparedStatement(자식) 객체 자원 반환 메서드
* @param stmt
*/
public static void close(Statement stmt) {
try {
if(stmt != null && !stmt.isClosed()) stmt.close();
} catch(Exception e) {
e.printStackTrace();
}
}
/** ResultSet 객체 자원 반환 메서드
* @param rs
*/
public static void close(ResultSet rs) {
try {
if(rs != null && !rs.isClosed()) rs.close();
} catch(Exception e) {
e.printStackTrace();
}
}
/** 트랜잭션 Commit 메서드
* @param conn
*/
public static void commit(Connection conn) {
try {
if(conn != null && !conn.isClosed()) conn.commit();
} catch(Exception e) {
e.printStackTrace();
}
}
/** 트랜잭션 Rollback 메서드
* @param conn
*/
public static void rollback(Connection conn) {
try {
if(conn != null && !conn.isClosed()) conn.rollback();
} catch(Exception e) {
e.printStackTrace();
}
}
}CreateXMLFile
package edu.kh.jdbc.common;
import java.io.FileOutputStream;
import java.util.Properties;
import java.util.Scanner;
public class CreateXMLFile {
public static void main(String[] args) {
// XML(eXtensible Markup Language) : 단순화된 데이터 기술 형식
// XML에 저장되는 데이터 형식 Key : Value 형식(Map)이다.
// -> Key, Value 모두 String(문자열) 형식
// XML 파일을 읽고, 쓰기 위한 IO 관련 클래스 필요
// * Properties 컬렉션 객체 *
// - Map의 후손 클래스
// - Key, Value 모두 String(문자열 형식)
// - XML 파일을 읽고, 쓰는데 특화된 메서드 제공
try {
Scanner sc = new Scanner(System.in);
// Properties 객체 생성
Properties prop = new Properties();
System.out.print("생성할 파일 이름 : ");
String fileName = sc.nextLine();
// FileOutputStream 생성
FileOutputStream fos = new FileOutputStream(fileName + ".xml");
// Properties 객체를 이용해서 XML 파일 생성
prop.storeToXML(fos, fileName + ".xml file");
System.out.println(fileName + ".xml 파일 생성 완료");
}catch(Exception e) {
e.printStackTrace();
}
}
}LoadXMLFile
package edu.kh.jdbc.common;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class LoadXMLFile {
public static void main(String[] args) {
// XML 파일 읽어오기 (Properties, FileInputStream)
try {
Properties prop = new Properties();
// driver.xml 파일을 읽어오기 위한 InputStream 객체 생성
FileInputStream fis = new FileInputStream("driver.xml");
// 연결된 driver.xml 파일에 있는 내용을 모두 읽어와
// Properties 객체에 K:V 형식으로 저장
prop.loadFromXML(fis);
System.out.println(prop);
// Property : 속성(데이터)
// prop.getProperty("key"); : key가 일치하는 속성을 얻어옴
String driver = prop.getProperty("driver");
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String password = prop.getProperty("password");
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
/*
* 왜 XML 파일을 이용해서 DB 연결 정보를 읽어와야 할까?
*
* 1. 코드 중복 제거
* 2. 별도 관리 용도
* 3. 재 컴파일을 진행하지 않기 위해서
* - 코드가 길수록 컴파일에 소요되는 시간이 크다
* -> 코드 수정으로 인한 컴파일 소요시간을 없앰.
* (파일의 내용을 읽어오는 코드만 작성해두면
* Java 코드 수정 없이, 파일 내용만 수정하면
* 재 컴파일은 수행되지 않는다)
*
* 4. XML 파일에 작성된 문자열 형태를 그대로 읽어오기 때문에
* SQL 작성 시 좀 더 편리해진다.
*
*
* */
} catch(Exception e) {
e.printStackTrace();
}
}
}TestVO
package edu.kh.jdbc.model.vo;
public class TestVO {
private int testNo;
private String testTitle;
private String testContent;
// 기본생성자
public TestVO() {}
// 매개변수 생성자
public TestVO(int testNo, String testTitle, String testContent) {
super();
this.testNo = testNo;
this.testTitle = testTitle;
this.testContent = testContent;
}
// getter/setter
public int getTestNo() {
return testNo;
}
public void setTestNo(int testNo) {
this.testNo = testNo;
}
public String getTestTitle() {
return testTitle;
}
public void setTestTitle(String testTitle) {
this.testTitle = testTitle;
}
public String getTestContent() {
return testContent;
}
public void setTestContent(String testContent) {
this.testContent = testContent;
}
@Override
public String toString() {
return "TestVO [testNo=" + testNo + ", testTitle=" + testTitle + ", testContent=" + testContent + "]";
}
}TestService
package edu.kh.jdbc.model.service;
import java.sql.Connection;
import java.sql.SQLException;
// import static 구문
// -> static이 붙은 필드, 메서드를 호출할 때
// 클래스명을 생략할 수 있게해주는 구문
import static edu.kh.jdbc.common.JDBCTemplate.*;
import edu.kh.jdbc.model.dao.TestDAO;
import edu.kh.jdbc.model.vo.TestVO;
public class TestService {
// Service : 비즈니스 로직(데이터 가공, 트랜잭션 제어) 처리
//-> 실제 프로그램이 제공하는 기능을 모아놓은 클래스
// 하나의 Service 메서드에서 n개의 DAO 메서드를 호출하여
// 이를 하나의 트랜잭션 단위로 취급하여
// 한번에 commit, rollback 을 수행할 수 있다.
private TestDAO dao = new TestDAO();
public int insert(TestVO vo1) throws SQLException{
// 커넥션 생성
Connection conn = getConnection();
// DAO 메서드 호출하여 수행 후 결과 반환받기
// -> Service에서 생성한 Connection 객체를 반드시 같이 전달!
int result = dao.insert(conn, vo1);
// 트랜잭션 제어
if(result > 0) commit(conn);
else rollback(conn);
// 커넥션 반환
close(conn);
// 결과 반환
return result;
}
/** 3행 삽입 서비스
* @param vo1
* @param vo2
* @param vo3
* @return result
*/
public int insert(TestVO vo1, TestVO vo2, TestVO vo3) throws SQLException {
// 1. Connection 생성 (무조건 1번)
Connection conn = getConnection();
int result = 0; // insert 3회 모두 성공시 1, 아니면 0
try {
int res1 = dao.insert(conn, vo1);
int res2 = dao.insert(conn, vo2);
int res3 = dao.insert(conn, vo3);
if(res1 + res2 + res3 == 3) { // 모두 insert 성공한 경우
commit(conn);
result = 1;
} else {
rollback(conn);
}
} catch(Exception e) {
e.printStackTrace();
}
close(conn);
return result; // insert 3회 결과 반환
}
}TestDAO
package edu.kh.jdbc.model.dao;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import static edu.kh.jdbc.common.JDBCTemplate.*;
import edu.kh.jdbc.model.vo.TestVO;
public class TestDAO {
// DAO (Data Access Object) : 데이터가 저장된 DB에 접근하는 객체
// -> SQL 수행, 결과 반환 받는 기능을 수행
// JDBC 객체를 참조하기 위한 참조변수 선언
private Statement stmt;
private PreparedStatement pstmt;
private ResultSet rs;
// xml로 SQL을 다룰 것이다 -> Properties 객체 사용
private Properties prop;
// 기본 생성자
public TestDAO() {
// TestDAO 객체 생성 시
// test-query.xml 파일의 내용을 읽어와
// Properties 객체에 저장
try {
prop = new Properties();
prop.loadFromXML(new FileInputStream("test-query.xml"));
} catch(Exception e) {
e.printStackTrace();
}
}
public int insert(Connection conn, TestVO vo1) throws SQLException {
// 1. 결과 저장용 변수 선언
int result = 0;
try {
// 2. SQL 작성(test-query.xml에 작성된 SQL얻어오기)
String sql = prop.getProperty("insert");
// INSERT INTO TB_TEST
// VALUES(?, ?, ?)
// 3. PreparedStatement 객체 생성
pstmt = conn.prepareStatement(sql);
// 4. 위치 홀더(?)에 알맞은 값 세팅
pstmt.setInt(1, vo1.getTestNo());
pstmt.setString(2, vo1.getTestTitle());
pstmt.setString(3, vo1.getTestContent());
// 5. SQL(INSERT) 수행 후 결과 반환받기
result = pstmt.executeUpdate(); // -> DML 수행, 반영된 행의 개수(int) 반환
} finally {
// 6. 사용한 JDBC 객체 자원 반환
close(pstmt);
}
// 7. SQL 수행 결과 반환
return result;
}
}Run
package edu.kh.jdbc.run;
import java.sql.SQLException;
import edu.kh.jdbc.model.service.TestService;
import edu.kh.jdbc.model.vo.TestVO;
public class Run {
public static void main(String[] args) {
TestService service = new TestService();
// TB_TEST 테이블에 INSERT 수행
TestVO vo1 = new TestVO(1, "제목1", "내용1");
// TB_TEST 테이블에 insert를 수행하는 서비스 메서드를 호출 후
// 결과 반환받기
try {
int result = service.insert(vo1); // 1 / 0
if(result > 0) {
System.out.println("insert 성공");
} else {
System.out.println("insert 실패");
}
} catch(SQLException e) {
System.out.println("SQL 수행 중 오류 발생");
e.printStackTrace();
}
}
}Run2
package edu.kh.jdbc.run;
import edu.kh.jdbc.model.service.TestService;
import edu.kh.jdbc.model.vo.TestVO;
public class Run2 {
public static void main(String[] args) {
// TB_TEST 테이블에 한번에 3행 삽입
TestService service = new TestService();
TestVO vo1 = new TestVO(70, "제목70", "내용70");
TestVO vo2 = new TestVO(80, "제목80", "내용80");
TestVO vo3 = new TestVO(90, "제목90", "내용90");
try {
int result = service.insert(vo1, vo2, vo3);
if(result > 0 ) {
System.out.println("성공");
} else {
System.out.println("실패");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}driver.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>driver.xml file!!</comment>
<!-- 마크업 언어 주석 -->
<!-- ctrl + shift + / -->
<!-- entry : K:V 한 쌍-->
<entry key="driver">oracle.jdbc.driver.OracleDriver</entry>
<entry key="url">jdbc:oracle:thin:@localhost:1521:XE</entry>
<entry key="user">kh</entry>
<entry key="password">kh1234</entry>
</properties>test-query.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>test-query.xml file</comment>
<entry key="insert">
INSERT INTO TB_TEST
VALUES(?, ?, ?)
</entry>
</properties>DBeaber
SELECT EMP_NAME, NVL(DEPT_TITLE, '부서없음') AS DEPT_TITLE, SALARY
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE NVL(DEPT_TITLE, '부서없음') = '총무부';
SELECT EMP_NAME, JOB_NAME AS JOB_NAME, SALARY, SALARY * 12 ANNUAL_INCOME
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE JOB_NAME = '대표'
AND SALARY > 2000000;
SELECT EMP_NAME 이름, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') 입사일,
DECODE ( SUBSTR(EMP_NO, 8, 1) , '1' , 'M', '2', 'F') 성별
FROM EMPLOYEE
WHERE HIRE_DATE < TO_DATE('2000-01-01');
CREATE TABLE TB_TEST (
TEST_NO NUMBER,
TEST_TITLE VARCHAR2(30),
TEST_CONTENT VARCHAR2(100)
);
SELECT * FROM TB_TEST;
초보자