DB10 - 12/02(sql40, jdbc42~43)

40transaction.sql

transaction: 하나의 업무 단위

예를 들어, 돈을 송금한다고 하면, 1)하나의 계좌에서 돈을 출금하고 2)또 다른 계좌로 입금하는 과정이 필요

만약 1) 까지 실행하고 문제가 발생하여 2) 를 실행하지 못한다면, 다시 1) 도 실행하지 않은 상태로 되돌려야 함

즉, 1) 과 2) 는 묶어서 하나의 업무 단위이다

 

< 예시 코드 >

CREATE TABLE myBank (
	id INT PRIMARY KEY AUTO_INCREMENT,
	money INT NOT NULL
);

INSERT INTO myBank (money) VALUES (10000), (20000);

1번 계좌에서 2번 계좌로 5000원 송금한다면 다음과 같이 코드 작성

-- 1) 1번 계좌에서 출금
UPDATE mytBank SET money = 5000 WHERE id = 1;
-- 2) 2번 계좌에 입금
UPDATE myBank SET money = 25000 WHERE id = 2;

◆ 문제 상황

그런데 만약 1) 실행 후 문제가 발생한다면? 다시 되돌려야 함

현재 코드 실행 시 자동으로 반영되도록 설정되어있으므로 먼저 이를 해제해준다

-- auto COMMIT disable
SET autocommit = 0;

그리고 문제 발생 시 1) 을 되돌리기 위해 ROLLBACK

-- 1) 1번 계좌에서 출금
UPDATE myBank SET money = 5000 WHERE id = 1;
-- 문제 발생
ROLLBACK; -- 이전 상태로 원복
-- 2) 2번 계좌에 입금 -- 실행 안됨
-- UPDATE mytable29Bank SET money = 25000 WHERE id = 2;

◆ 정상 상황

업무를 완료하면 이를 반영하기 위해 COMMIT

-- 1) 1번 계좌에서 출금
UPDATE myBank SET money = 5000 WHERE id = 1;

-- 2) 2번 계좌에 입금
UPDATE myBank SET money = 25000 WHERE id = 2;

-- 3) 업무 완료 - table에 반영
COMMIT;

 

< auto commit 해제 시 사용하는 코드 > 

♠ ROLLBACK : 되돌리기

♠ COMMIT : 반영하기

→ COMMIT 후에는 ROLLBACK 할 수 없다

-- auto COMMIT 해제
SET autocommit = 0;

-- auto commit 활성화
SET autocommit = 1;

---

 

JDBC42Servlet

위의 transaction 예시 서블릿으로 구현해보기

jdbc11

JDBC42Servlet

doGet, doPost

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// v42로 forward
		String path = "/WEB-INF/view/jdbc11/v42.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 0. 사전작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		BankDao dao = new BankDao();

		// 2. request 분석/가공
		String moneyStr = request.getParameter("money");
		int money = Integer.parseInt(moneyStr);

		// 3. business logic
		Connection con = null;
		try {
			con = ds.getConnection();
			// 3.0 auto commit disabled
			con.setAutoCommit(false);
			
			// 3.1 1번 출금
			Bank bank1 = dao.getBankById(con, 1); // 1번 계좌 조회
			bank1.setMoney(bank1.getMoney() - money);

			dao.update(con, bank1); // 1번 계좌 update (출금)
			
			// 문제 발생!!!
			/*
			String a = "a";
			Integer.parseInt(a);
			*/
			
			// 3.2 2번 입금
			Bank bank2 = dao.getBankById(con, 2); // 2번 계좌 조회
			bank2.setMoney(bank2.getMoney() + money);

			dao.update(con, bank2); // 2번 계좌 update (입금)
			
			// 3.99 commit
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			
			if (con != null) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		// 4. add attribute
		// 5. forward/redirect
	}

doGet - v42.jsp 로 포워딩

doPost -

BankDao 객체 dao 생성

request.getParameter 로 money 받아와 저장

con.setAutocommit(false); 로 auto commit 해제

- 1번 출금

dao.getBankById(con, 1) 로 1번 계좌 조회, 반환값을 Bank 객체 bank1 에 저장

bank1.getMoney 에서 money 를 뺀 값을 bank1.setMoney 에 저장

그리고 dao.update(con, bank1) 으로 update 하는 sql 실행

- 2번 입금

dao.getBankById(con, 2) 로 2번 계좌 조회, 반환값을 Bank 객체 bank2 에 저장

bank2.getMoney 에서 money 를 더한 값을 bank2.setMoney 에 저장

그리고 dao.update(con, bank2) 으로 update 하는 sql 실행

- commit 

con.commit();

만약 문제 발생? catch 에서 con.rollback();

 

jdbc11.dao

BankDao

public class BankDao {

	public Bank getBankById(Connection con, int id) {
		String sql = "SELECT id, money FROM myBank WHERE id = ? ";
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, id);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				if (rs.next()) {
					Bank bank = new Bank();
					
					bank.setId(id);
					bank.setMoney(rs.getInt("money"));
					
					return bank;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void update(Connection con, Bank bank) {
		String sql = "UPDATE myBank "
				+ "SET money = ? "
				+ "WHERE id = ? ";
		
		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, bank.getMoney());
			pstmt.setInt(2, bank.getId());
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}
}

getBankById 메소드 - 매개변수로 con, id 받아와서 bank 반환

sql - SELECT id, money FROM myBank WHERE id = ?

Bank 객체 bank 생성, bank.setId, bank.setMoney 로 id, money 저장

bank 반환

update 메소드 - 매개변수로 con, bank 받으며 반환은 없음

sql - UPDATE myBank SET money = ? WHERE id = ?

bank.getMoney, bank.getId 로 bank 에 저장된 money, id 받아와서 ? 채움

sql 실행

 

jdbc1.bean

Bank

public class Bank {
	private int id;
	private int money;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public int getMoney() {
		return money;
	}

	public void setMoney(int money) {
		this.money = money;
	}
}

 

jdbc11

v42.jsp

<body>

<h1>1번 계좌에서 2번 계좌로 송금</h1>

<form action="" method="post">
  <input type="number" name="money" value="0"> 원 <input type="submit" value="송금">
</form>

</body>

얼마 송금할지 입력 - money 로 값을 전달

---

JDBC43Servlet

이번에는 2번 계좌에서 1번 계좌로 송금하도록 작성해보기

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String path = "/WEB-INF/view/jdbc11/v43.jsp";
		request.getRequestDispatcher(path).forward(request, response);
		
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 0. 
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		BankDao dao = new BankDao();
		
		// 2.
		int money = Integer.parseInt(request.getParameter("money"));
		
		// 3.
		Connection con = null;
		
		try {
			con = ds.getConnection();
			
			//   3.0 auto commit disabled (false)
			con.setAutoCommit(false);
			//   3.1 2번계좌 출금
			Bank bank2 = dao.getBankById(con, 2);
			bank2.setMoney(bank2.getMoney() - money);
			dao.update(con, bank2);
			
			//   3.1 1번계좌 입금
			Bank bank1 = dao.getBankById(con, 1);
			bank1.setMoney(bank1.getMoney() + money);
			dao.update(con, bank1);
			
			//   3.99 commit
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			//   3.exception rollback
			if (con != null) {
				try {
					con.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		// 4,5
	}

 

BankDao, Bank 위 42와 동일

 

v43.jsp 

<body>

<h1>2번 계좌에서 1번 계좌로 송금</h1>

<form action="" method="post">
  <input type="number" name="money" id="" value="0">원
  <input type="submit" value="송금">
</form>

</body>