본문 바로가기
JDBC

JDBC모델링 DAO-Class.forName , dataSource

by 임혁진 2024. 1. 22.

Class.forName 사용

package com.jdbc.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;

public class Model {
	
	private String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private String uid = "hr";
	private String upw = "hr"; 
	
	public Model() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");	
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//select할 내용작성
	public void selectOne() {
		String sql = "SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID >= ?";
		//모든 jdbc코드는 try~catch구문에서 작성이 들어가야 합니다. (throws를 던지고 있기 때문에)
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			//1. JDBC드라이버 준비
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			//2. conn객체생성
			conn = DriverManager.getConnection(url, uid, upw);
			
			//3. conn으로부터 statement객체 생성 - sql상태를 지정하기 위한 객체
			pstmt = conn.prepareStatement(sql);
			//?에 개수에 맞추어 값을 채웁니다. 
			//setString(순서 , 문자열)
			//setInt(순서, 숫자)
			//setDouble(순서, 실수)
			pstmt.setString(1, "120");
			
			//4. 실행
			//executeQuery - select문에 사용합니다.
			//executeUpdate - insert, update, delete문에 사용합니다.
			rs = pstmt.executeQuery();
			
			while( rs.next() ) { //다음이 있다면 true, 다음이 없다면 false
				
				//rs.getString(컬럼명) - 문자열반환
				//rs.getInt(컬럼명) - 정수반환
				//rs.getDouble(컬럼명) - 실수형반환
				//rs.getDate(컬럼명) - 날짜형반환
				int emp_id = rs.getInt("EMPLOYEE_ID");
				String first_name = rs.getString("FIRST_NAME");
				String phone_number = rs.getString("phone_number");
				//String hire_date = rs.getString("hire_date");
				Timestamp hire_date = rs.getTimestamp("hire_date"); 
				int salary = rs.getInt("salary");
				
				System.out.println("----------------------------");
				System.out.println("아이디:" + emp_id);
				System.out.println("이름:" + first_name);
				System.out.println("전화번호:" + phone_number);
				System.out.println("입사일:" + hire_date);
				System.out.println("급여:" + salary);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
				rs.close();
			} catch (Exception e2) {
			}
		}
		
		
		
		
		
	}
	
	//insert할 내용작성
	public void insertOne(int id, String name, String mId, String lId) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		//resultSet은 insert에서 필요가 없습니다.
		
		String sql = "INSERT INTO DEPTS VALUES(?,?,?,?)";
		
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//1. conn생성
			conn = DriverManager.getConnection(url, upw, uid);
			//2. pstmt생성
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, name);
			pstmt.setString(3, mId);
			pstmt.setString(4, lId);
			//3. sql실행
			int result = pstmt.executeUpdate(); // 성공시 1 or 실패시 0
			
			if(result == 1) {
				System.out.println("인서트 성공");
			} else {
				System.out.println("인서트 실패");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (Exception e2) {
			}
		}
		
		
	}
	
	//update할 내용작성(실습)
	public void updateOne(String deptName, String managerId, String deptId) {

		//Main에서 부서아이디, 부서명, 매니저아이디를 받아서, 해당부서의 부서명과 매니저아이디를 수정해주세요.
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "UPDATE DEPTS SET DEPARTMENT_NAME = ?, MANAGER_ID = ? WHERE DEPARTMENT_ID = ?";
		
		try {
			//커넥션		
			conn = DriverManager.getConnection(url, uid, upw);
			//스테이트먼트 - sql상태
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, deptName);
			pstmt.setString(2, managerId);
			pstmt.setString(3, deptId);
			//sql실행
			int result = pstmt.executeUpdate(); //0 or 1
			//결과받음
			if(result == 1) {
				System.out.println("성공");
			} else {
				System.out.println("실패");
			}
						
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				pstmt.close();
			} catch (Exception e2) {
				
			}
		}
		
		
		
		
	}
	
	//delete할 내용작성(실습)
	public void deleteOne(String employeeId) {
		//Main employee_id를 받아서 emps테이블에서 해당 아이디를 삭제해주세요.
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "DELETE FROM EMPS WHERE EMPLOYEE_ID = ?";
		
		try {
			
			conn = DriverManager.getConnection(url, uid, upw);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, employeeId);
			
			int result = pstmt.executeUpdate();
			
			System.out.println(result);
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//조인을 통한 select(실습)
	public ArrayList<EmployeeVO> selectTwo() {
		
		//값을 담을 ArrayList
		ArrayList<EmployeeVO> list = new ArrayList<>();
		
		
		
		//사원번호, 이름, 부서명, 급여 - 급여순으로 정렬을해서 10~20번에 속해있는 데이터. 출력
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT *\r\n"
					+ "FROM (\r\n"
					+ "    SELECT ROWNUM RN,\r\n"
					+ "           A.*\r\n"
					+ "    FROM (\r\n"
					+ "        SELECT E.EMPLOYEE_ID,\r\n"
					+ "               E.FIRST_NAME,\r\n"
					+ "               D.DEPARTMENT_NAME,\r\n"
					+ "               E.SALARY\r\n"
					+ "        FROM EMPLOYEES E\r\n"
					+ "        LEFT JOIN DEPARTMENTS D\r\n"
					+ "        ON E.DEPARTMENT_ID = D.DEPARTMENT_ID\r\n"
					+ "        ORDER BY SALARY DESC\r\n"
					+ "    ) A\r\n"
					+ ")\r\n"
					+ "WHERE RN > 10 AND RN <= 20";
			
		try {
			
			conn = DriverManager.getConnection(url, uid, upw);
			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				
				//1행에 대한 처리
				int employeeId = rs.getInt("employee_id");
				String firstName = rs.getString("first_name");
				String departmentName = rs.getString("department_name");
				int salary = rs.getInt("salary");
				
				System.out.println("--------------------------------------------");
				System.out.println(employeeId);
				System.out.println(firstName);
				System.out.println(departmentName);
				System.out.println(salary);
				
				EmployeeVO vo = new EmployeeVO(employeeId, firstName, salary, departmentName);
				list.add(vo);
				
			}
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
		
		
		
		return list;
	}
	
	
	
	
	
}

 

 

dataSource 사용

private static UserDAO instance = new UserDAO();
	//2.생성자 private
	
	private UserDAO() {
	
		try {
			InitialContext init = new InitialContext();
			
			dataSource = (DataSource)init.lookup("java:comp/env/jdbc/oracle1");
			
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
    private DataSource dataSource;

 

 

DataSource 타입을 받아주고 "java:comp/env/jdbc/oracle1"은 was에 들어간 server- context.xml에 jdbc 내역을 적어주면된다 (jdbc/oracle1은 이름)

<Resource
		auth="Container"
		driverClassName = "oracle.jdbc.driver.OracleDriver"
		url = "jdbc:oracle:thin:@172.30.1.12:1521:xe"
		username = "hr"
		password = "hr"
		name = "jdbc/oracle"
		type = "javax.sql.DataSource"
		maxActive = "300"
		maxWait = "1000" 
/>
<Resource
		auth="Container"
		driverClassName = "oracle.jdbc.driver.OracleDriver"
		url = "jdbc:oracle:thin:@172.30.1.12:1521:xe"
		username = "JSP"
		password = "JSP"
		name = "jdbc/oracle1"
		type = "javax.sql.DataSource"
		maxActive = "300"
		maxWait = "1000" 
/>

를 context 사이에 넣어주면 된다. 

'JDBC' 카테고리의 다른 글

VO(자바빈)  (0) 2024.01.22