Acorn academy 12/15

JDBC 자동 커밋 autocommit => connection이 관리

• tx 명시적 처리 순서 (트랜잭션)
  1) con.setAutoCommit(false)
  2) dml sql executeUpdate
  3) 정상실행 con.commit()
  4) 문제 발생 con.rollback();
  5) con.AutoCommit(true)
  

EmpTest : EmpBiz클래스의 메소드를 호출

import java.util.ArrayList;
import java.util.HashMap;

import com.biz.EmpBiz;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;

public class EmpTest {
	public static void main(String[] args) {
		EmpBiz empBiz = new EmpBiz();
		
		
		
		
//		empBiz.empInsert(new EmpDTO(30, "kim", "sales", 7902, null, 0.0, 0.1, 10));
		
		try {
			empBiz.empDelete("30");
		} catch (DataNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		try {
//			empBiz.empUpdate(new EmpDTO(30, "kim", "aa", 7902, null, 100.0, 0.1, 10));
//		} catch (DataNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		
//		HashMap<String, String> map = new HashMap<String, String>();
//		map.put("job", "SALESMAN");
//		map.put("deptno", "30");
//		ArrayList<EmpDTO> list = empBiz.selectDetailEmp2(map);
		
//		try {
//			System.out.println(empBiz.selectDetailEmp("7369"));
//		} catch (DataNotFoundException e) {
//			e.printStackTrace();
//		}
//		System.out.println("==================");
		
		
		ArrayList<EmpDTO> list = empBiz.selectAllEmp();
//		System.out.println(list);
		for (EmpDTO dto : list) {
			System.out.println(dto);
		}
		System.out.println("=================");
	}
}

EmpBiz :

package com.biz;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;

import com.common.util.JdbcTemplate;
import com.dao.EmpDAO;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;

public class EmpBiz {
	EmpDAO dao;
	JdbcTemplate template;
	
	public EmpBiz() {
		super();
		dao = new EmpDAO();
		template = new JdbcTemplate();
	}

	public ArrayList<EmpDTO> selectAllEmp() {
		Connection con = JdbcTemplate.getConnection();
		ArrayList<EmpDTO> list = dao.selectAllEmp(con);
		JdbcTemplate.close(con);
		return list;
	}

	public EmpDTO selectDetailEmp(String eno) throws DataNotFoundException {
		Connection con = JdbcTemplate.getConnection();
		EmpDTO dto = dao.selectDetailEmp(con, eno);
		JdbcTemplate.close(con);
		return dto;
	}

	public ArrayList<EmpDTO> selectDetailEmp2(HashMap<String, String> map) {
		Connection con = JdbcTemplate.getConnection();
		ArrayList<EmpDTO> list = dao.selectDetailEmp2(con, map);
		JdbcTemplate.close(con);
		return list;
	}

	public void empInsert(EmpDTO empDTO) {
		Connection con = JdbcTemplate.getConnection();
		dao.Insert(con, empDTO);
		JdbcTemplate.commit(con);
		JdbcTemplate.close(con);
	}

	public void empUpdate(EmpDTO empDTO) throws DataNotFoundException {
		Connection con = JdbcTemplate.getConnection();
		dao.update(con, empDTO);
		JdbcTemplate.commit(con);
		JdbcTemplate.close(con);
	}

	public void empDelete(String string) throws DataNotFoundException {
		Connection con = JdbcTemplate.getConnection();
		dao.delete(con, string);
		JdbcTemplate.commit(con);
		JdbcTemplate.close(con);
		
	}
	
}

JdbcTemplate

package com.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.net.aso.l;

public class JdbcTemplate {
	
	public static String driver = "oracle.jdbc.driver.OracleDriver"; //드라이버 클래스명
	public static String url = "jdbc:oracle:thin:@localhost:1521:xe"; //접속정보 및 sid
	public static String userid = "scott"; // 계정정보
	public static String passwd = "tiger";
	
	public JdbcTemplate() {
		super();
		try {
			Class.forName(driver);
			System.out.println("드라이버 로딩 성공");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	// 접속얻기
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, userid, passwd);
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			System.out.println("[JdbcTemplate.getConnection] : " + e.getMessage());
			e.printStackTrace();
		}
		return conn;
	}
	
	// 접속확인
	public static boolean isConnected(Connection conn) {
		boolean validConnection = true;
		try {
			if(conn ==null || conn.isClosed()) {
				validConnection = false;
			}
		} catch (SQLException e) {
			validConnection = false;
			e.printStackTrace();
		}
		return validConnection;
	}
	
	// 접속 닫기
	public static void close(Connection conn) {
		if(isConnected(conn)) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	// stmt 닫기 (pstmt부모가 stmt라서 다형성 적용)
	public static void close(Statement stmt) {
		try {
			if(stmt!=null)stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// rs 닫기
	public static void close(ResultSet rset) {
		try {
			if(rset!=null)rset.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 커밋
	public static void commit(Connection conn) {
		try {
			if(isConnected(conn))conn.commit();
			System.out.println("[JdbcTemplate.commit] : DB Successfully Committed");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 롤백
	public static void rollback(Connection conn) {
		try {
			if(isConnected(conn))conn.rollback();
			System.out.println("[JdbcTemplate.rollback] : DB Successfully rollbacked");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

EmpDAO

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.common.util.JdbcTemplate;
import com.entity.EmpDTO;
import com.exception.DataNotFoundException;

public class EmpDAO {
	
	public ArrayList<EmpDTO> selectAllEmp(Connection con){
		ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select empno, ename, job, mgr, to_char(hiredate, 'YYYY-MM-DD') "
				+ "hiredate, sal, comm, deptno from emp "
				+ "order by empno desc";
		
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int empno = rs.getInt("empno");
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				int mgr = rs.getInt("mgr");
				String hiredate = rs.getString("hiredate");
				Double sal = rs.getDouble("sal");
				Double comm = rs.getDouble("comm");
				int deptno = rs.getInt("deptno");
				EmpDTO notice = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
				list.add(notice);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(rs);
			JdbcTemplate.close(pstmt);
		}
		return list;
		
	}

	public EmpDTO selectDetailEmp(Connection con, String eno) throws DataNotFoundException {
		EmpDTO result = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select empno, ename, job, mgr, to_char(hiredate, 'YYYY-MM-DD') "
				+ "hiredate, sal, comm, deptno from emp "
				+ "where empno=?"
				+ "order by empno desc";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(eno));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int empno = rs.getInt("empno");
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				int mgr = rs.getInt("mgr");
				String hiredate = rs.getString("hiredate");
				Double sal = rs.getDouble("sal");
				Double comm = rs.getDouble("comm");
				int deptno = rs.getInt("deptno");
				result = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
			} else {
				throw new DataNotFoundException(eno + "에 해당하는 사원정보가 없습니다. 확인 후 다시 조회하세요.");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(rs);
			JdbcTemplate.close(pstmt);
		}
		return result;
		
	}

	public ArrayList<EmpDTO> selectDetailEmp2(Connection con, HashMap<String, String> map) {
		ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select empno, ename, job, mgr, to_char(hiredate, 'YYYY-MM-DD') "
				+ "hiredate, sal, comm, deptno from emp "
				+ "where job=? and deptno=? "
				+ "order by empno desc";
		
		try {
//			System.out.println(map.get("deptno"));
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, map.get("job"));
			pstmt.setInt(2, Integer.parseInt(map.get("deptno")));
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int empno = rs.getInt("empno");
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				int mgr = rs.getInt("mgr");
				String hiredate = rs.getString("hiredate");
				Double sal = rs.getDouble("sal");
				Double comm = rs.getDouble("comm");
				int deptno = rs.getInt("deptno");
				EmpDTO notice = new EmpDTO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
				list.add(notice);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(rs);
			JdbcTemplate.close(pstmt);
		}
		return list;
	}

	public void Insert(Connection con, EmpDTO empDTO) {
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO emp VALUES (?, ?, ?, ?, sysdate, ?, ?, ?)";
		int num = 0;
		
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setInt(1, empDTO.getEmpno());
			pstmt.setString(2, empDTO.getEname());
			pstmt.setString(3, empDTO.getJob());
			pstmt.setInt(4, empDTO.getMgr());
//			pstmt.setString(5, "sysdate");
			pstmt.setDouble(5, empDTO.getSal());
			pstmt.setDouble(6, empDTO.getComm());
			pstmt.setInt(7, empDTO.getDeptno());
			
			num = pstmt.executeUpdate();
			System.out.println("insert : 된 갯수 " + num);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}
	}

	public void update(Connection con, EmpDTO empDTO) throws DataNotFoundException {
		PreparedStatement pstmt = null;
		
		String sql = "update emp set job=?, sal=? where empno=?";
		int num = 0;
		
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, empDTO.getJob());
			pstmt.setDouble(2, empDTO.getSal());
			pstmt.setInt(3, empDTO.getEmpno());
			
			num = pstmt.executeUpdate();
			System.out.println("update : 된 갯수 " + num);
			if (num==0) {
				throw new DataNotFoundException(empDTO.getEmpno() + "에 해당하는 사원정보가 없습니다. 확인 후 다시 조회하세요.");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}
		
	}

	public void delete(Connection con, String string) throws DataNotFoundException {
PreparedStatement pstmt = null;
		
		String sql = "delete from emp where empno=?";
		int num = 0;
		
		try {
			pstmt = con.prepareStatement(sql);
			
			pstmt.setInt(1, Integer.parseInt(string));
			
			num = pstmt.executeUpdate();
			System.out.println("delete : 된 갯수 " + num);
			if (num==0) {
				throw new DataNotFoundException(string + "에 해당하는 사원정보가 없습니다. 확인 후 다시 조회하세요.");
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcTemplate.close(pstmt);
		}
		
	}
}

EmpDTO

package com.entity;

public class EmpDTO {
	private int empno;
	private String ename;
	private String job;
	private int mgr;
	private String hiredate;
	private double sal;
	private double comm;
	private int deptno;
	
	public EmpDTO() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public EmpDTO(int empno, String ename, String job, int mgr, String hiredate, double sal, double comm, int deptno) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public int getMgr() {
		return mgr;
	}

	public void setMgr(int mgr) {
		this.mgr = mgr;
	}

	public String getHiredate() {
		return hiredate;
	}

	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}

	public double getSal() {
		return sal;
	}

	public void setSal(double sal) {
		this.sal = sal;
	}

	public double getComm() {
		return comm;
	}

	public void setComm(double comm) {
		this.comm = comm;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "EmpDTO [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
	
	
}

DataNotFoundException

package com.exception;

public class DataNotFoundException extends Exception{

	public DataNotFoundException(String message) {
		super(message);
	}
	
}

OralceTXMain2 프로젝트 실습

자바 JDBC 워크샵
문제 1
문제 2-1
문제 2-2
문제 2-3,4,5