본문 바로가기
Programming/JDBC

JDBC(2) 트랜잭션

by yoon9i 2024. 4. 4.

 

7. JDBC 트랜잭션 처리 ( Service 클래스에서 반드시 처리함. )
``
@Override
public int insertDelete(DeptDTO dto, int deptno) throws DuplicatedDeptnoException {
int n = 0;
Connection con = null;
try {
con = DriverManager.getConnection(url, userid, passwd);
// DeptDAO 연동
DeptDAO dao = new DeptDAO();
con.setAutoCommit(false); // 반드시 commit & rollback 해줘야함.
dao.insert(con, dto);
dao.delete(con, deptno);
con.commit();
} catch (SQLException e) {

try {
con.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

}

``

package com.jdbc4_DML3_트랜잭션;

import java.util.List;
import java.util.Scanner;

public class DeptMain {

	public static void main(String[] args) {

		Scanner scan = new Scanner(System.in);

		while (true) {

			System.out.println("1. 전체목록");
			System.out.println("2. 부서저장");
			System.out.println("3. 부서삭제");
			System.out.println("4. 트랜잭션");
			System.out.println("0. 종료");
			System.out.println("-------------------");
			String num = scan.next();
			if ("0".equals(num)) {
				System.out.println("프로그램 종료됨.");
				System.exit(0);
			} else if ("1".equals(num)) {
				// DeptService 연동
				DeptService service = new DeptServiceImpl();
				List<DeptDTO> list = service.select();
				for (DeptDTO dto : list) {
					System.out.println(dto);
				}
			} else if ("2".equals(num)) {
				System.out.println("부서번호를 입력하세요.");
				String deptno = scan.next();
				System.out.println("부서명를 입력하세요.");
				String dname = scan.next();
				System.out.println("부서위치를 입력하세요.");
				String loc = scan.next();

				// DTO 저장해서 DAO로 전달
				DeptDTO dto = new DeptDTO(Integer.parseInt(deptno), dname, loc);

				// DeptService 연동
				DeptService service = new DeptServiceImpl();
				int n = 0;
				try {
					n = service.insert(dto);
					System.out.println(n + " 개가 저장됨.");
				} catch (DuplicatedDeptnoException e) {
					System.out.println(e.getMessage());
				}

			} else if ("3".equals(num)) {
				System.out.println("삭제할 부서번호를 입력하세요.");
				String deptno = scan.next();
				// DeptService 연동
				DeptService service = new DeptServiceImpl();
				int n = service.delete(Integer.parseInt(deptno));
				System.out.println(n + " 개가 삭제됨.");
			} else if ("4".equals(num)) {
				
				// 저장할 데이터
				DeptDTO dto = new DeptDTO(1,"a","b");
				int deptno = 2;
				
				DeptService service = new DeptServiceImpl();
				try {
					service.insertDelete(dto, deptno);
				} catch (DuplicatedDeptnoException e) {
					e.printStackTrace();
				}
				
			} else {
			
				System.out.println("번호를 잘못 입력했습니다.");
			}

		} // end while

	}// end main
}// end class
package com.jdbc4_DML3_트랜잭션;

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

// 역할: MySQL DB 연동
// DAO 패턴: Data Access Object
public class DeptDAO {

	// 부서 삭제
	public int delete(Connection con, int deptno) {
		int n = 0;
		PreparedStatement pstmt = null;
		try {
			String sql = "delete from dept where deptno=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, deptno);
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return n;
	}

	// 부서 저장
	public int insert(Connection con, DeptDTO dto) throws DuplicatedDeptnoException {
		int n = 0;
		PreparedStatement pstmt = null;
		try {
			String sql = "insert into dept ( deptno, dname, loc ) " + " values (?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, dto.getDeptno());
			pstmt.setString(2, dto.getDname());
			pstmt.setString(3, dto.getLoc());
			n = pstmt.executeUpdate();
		} catch (SQLException e) {

			// 중복예외처리 클래스를 만들고 명시적으로 생성한다.
			throw new DuplicatedDeptnoException(dto.getDeptno() + " 값이 중복");

		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return n;
	}// end insert

	// 목록보기
	public List<DeptDTO> select(Connection con) {

		// DTO 누적하는 용도로 ArrayList를 사용한다.
		List<DeptDTO> list = new ArrayList<>();

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "select deptno as no, dname, loc from dept";

			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int deptno = rs.getInt("no"); // 컬럼헤더값
				String dname = rs.getString(2); // 컬럼헤더 위치값
				String loc = rs.getString("loc"); // 컬럼헤더값
				// DTO 저장하고 누적해야 된다.
				DeptDTO dto = new DeptDTO(deptno, dname, loc);
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally

		return list;
	}// end select
}
package com.jdbc4_DML3_트랜잭션;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

//역할: -비즈니즈로직코드(가공), -트랜잭션처리(*****)
// Connection con 까지만 얻으면 됨.
public class DeptServiceImpl implements DeptService {

	String driver = "com.mysql.cj.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/testdb";
	String userid = "root";
	String passwd = "1234";

	// 생성자
	public DeptServiceImpl() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 메서드
	@Override
	public List<DeptDTO> select() {
		List<DeptDTO> list = null;
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			// DeptDAO 연동
			DeptDAO dao = new DeptDAO();
			list = dao.select(con);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return list;
	}// end select

	@Override
	public int insert(DeptDTO dto) throws DuplicatedDeptnoException {
		int n = 0;
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			// DeptDAO 연동
			DeptDAO dao = new DeptDAO();
			n = dao.insert(con, dto);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return n;
	}

	@Override
	public int delete(int deptno) {
		int n = 0;
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			// DeptDAO 연동
			DeptDAO dao = new DeptDAO();
			n = dao.delete(con, deptno);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return n;
	}

	@Override
	public int insertDelete(DeptDTO dto, int deptno) throws DuplicatedDeptnoException {
		int n = 0;
		Connection con = null;
		try {
			con = DriverManager.getConnection(url, userid, passwd);
			// DeptDAO 연동
			DeptDAO dao = new DeptDAO();
			con.setAutoCommit(false); // 반드시 commit & rollback 해줘야함.
			dao.delete(con, deptno);
			dao.insert(con, dto);
			con.commit();
		} catch (SQLException e) {
			
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} // end finally
		return n;
	}

}
package com.jdbc4_DML3_트랜잭션;

// 역할: dept 테이블의 레코드 저장
public class DeptDTO {
	
	// 컬럼에 대응하는 변수
	int deptno;
	String dname;
	String loc;
	
	public DeptDTO() {}

	public DeptDTO(int deptno, String dname, String loc) {
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int 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;
	}

	@Override
	public String toString() {
		return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
	}
	
    
}
package com.jdbc4_DML3_트랜잭션;

import java.util.List;

//역할: - 메서드 강제, loosely coupling, 어떤 기능인지 쉽게 알 수 있다.
public interface DeptService {
	
	public List<DeptDTO> select();
	
	public int insert(DeptDTO dto) throws DuplicatedDeptnoException;
	
	public int delete(int deptno);
	
	// 트랜잭션처리
	public int insertDelete(DeptDTO dto, int deptno) throws DuplicatedDeptnoException;
}
package com.jdbc4_DML3_트랜잭션;

public class DuplicatedDeptnoException extends Exception {

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

}

'Programming > JDBC' 카테고리의 다른 글

MyBatis(3) 동적sql 처리  (0) 2024.04.05
MyBatis(2) MyBatis 사용  (0) 2024.04.04
MyBatis(1) 설치 및 설정  (0) 2024.04.04
JDBC(1) 기본 + 응용 + 예외처리  (1) 2024.04.03
JDBC 설정하는방법  (1) 2024.04.03

관련글

티스토리툴바