[Java] 스프링(Spring) JDBC(Java Database Connectivity)와 트랜잭션(Transaction)

추석 연휴가 제법 기네요~

코로나19로 가족이 모이지 않는

경우도 있겠지만 각자 즐거운 한가위

보내시기 바랍니다

지난 시간에는 폼(Form) 데이터와

Validator를 이용한 데이터 검증에

대해서 알아보았습니다

안보셨다면 아래의 링크↓↓

2020/09/29 - [Java] - [Java] 스프링(Spring) 폼(Form) 데이터와 Validator 검증

[Java] 스프링(Spring) 폼(Form) 데이터와 Validator 검증

오늘은 JDBC(Java Database Connectivity)로

마리아(Maria) DB를 연결하는 법과

트랜잭션(Transaction)에 대해 알아보겠습니다

먼저 JDBC는 자바에서 데이터베이스에

접속할 수 있도록 하는 자바 API입니다

설정을 하기 위해선 먼저 pom.xml에

dependency를 추가해줘야합니다

1. pom.xml

<!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
<dependency>
	<groupId>org.mariadb.jdbc</groupId>
	<artifactId>mariadb-java-client</artifactId>
	<version>2.7.0</version>
</dependency>
		
<!-- JDBC Template -->
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>4.1.4.RELEASE</version>
</dependency>

위의 코드를 pom.xml에 입력해주세요

추가한 dependency 정보는

아래의 링크에서 확인 가능합니다^^

https://mvnrepository.com 

Maven Repository: Search/Browse/Explore

위의 JDBC는 마리아(Maria) DB를 위한 것이고

밑에는 이 JDBC정보를 가지고

select, insert, update, delete 등을

보다 쉽게 하기 위한 JDBC Template 입니다

JDBC Template이 하는 일은

아래의 그림과 같습니다

2. servlet-context.xml

<beans:bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<beans:property name="driverClassName" value="org.mariadb.jdbc.Driver" />
	<beans:property name="url" value="jdbc:mariadb://localhost:3306:bananameta" />
	<beans:property name="username" value="gurusa" />
	<beans:property name="password" value="1234" />
</beans:bean>
	
<beans:bean name="template" class="org.springframework.jdbc.core.JdbcTemplate">
	<beans:property name="dataSource" ref="dataSource" />
</beans:bean>
	
<beans:bean name="dao" class="com.example.demo.dao.TicketDao" >
	<beans:property name="template" ref="template" />
</beans:bean>

이제 servlet-context.xml 파일에

위의 코드를 넣어주는데 맨 아래의 class는

각자 생성한데로 설정해주세요~

이제 마리아 DB에 생성한

데이터베이스에 접근 가능하게 되었습니다

마리아 DB에 대한 내용은

여기서 담기엔 내용이 많아 생략할께요!

3. TicketDao.java

package com.example.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.example.demo.dto.TicketDto;

public class TicketDao {

	JdbcTemplate template;
	PlatformTransactionManager transactionManager;
	
	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}
	
	public void setTransactionManager( PlatformTransactionManager transactionManager) {
		this.transactionManager = transactionManager;
	}
	
	public TicketDao() {
		System.out.println(template);
	}
	
	public void buyTicket(final TicketDto dto) {
		System.out.println("buyTicket()");
		System.out.println("dto.getConsumerId() : " + dto.getConsumerId());
		System.out.println("dto.getAmount() : " + dto.getAmount());
		
		TransactionDefinition definition = new DefaultTransactionDefinition();
		TransactionStatus status = transactionManager.getTransaction(definition);
		
		try {
			template.update(new PreparedStatementCreator() {
				
				@Override
				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					String query = "insert into bananameta.card (consumerId, amount) values (?, ?)";
					PreparedStatement pstmt = con.prepareStatement(query);
					pstmt.setString(1, dto.getConsumerId());
					pstmt.setString(2, dto.getAmount());
					
					return pstmt;
				}
			});
			
			template.update(new PreparedStatementCreator() {
				
				@Override
				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					String query = "insert into bananameta.ticket (consumerId, countnum) values (?, ?)";
					PreparedStatement pstmt = con.prepareStatement(query);
					pstmt.setString(1, dto.getConsumerId());
					pstmt.setString(2, dto.getAmount());
					
					return pstmt;
				}
			});
			
			transactionManager.commit(status);
			
		} catch (Exception e) {
			e.printStackTrace();
			
			transactionManager.rollback(status);
		}
	}

	
}

4. TicketDto.java

package com.example.demo.dto;

public class TicketDto {

	private String consumerId;
	private String amount;
	
	public String getConsumerId() {
		return consumerId;
	}
	public void setConsumerId(String consumerId) {
		this.consumerId = consumerId;
	}
	public String getAmount() {
		return amount;
	}
	public void setAmount(String amount) {
		this.amount = amount;
	}
	
}

5. HomeController.java

package com.example.demo;

import java.text.DateFormat;
import java.util.Date;
import java.util.Locale;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.example.demo.dao.TicketDao;
import com.example.demo.dto.TicketDto;

/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {
	
private TicketDao dao;
	
	@Autowired
	public void setDao(TicketDao dao) {
		this.dao = dao;
	}
	
	private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
	
	/**
	 * Simply selects the home view to render by returning its name.
	 */
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home(Locale locale, Model model) {
		logger.info("Welcome home! The client locale is {}.", locale);
		
		Date date = new Date();
		DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);
		
		String formattedDate = dateFormat.format(date);
		
		model.addAttribute("serverTime", formattedDate );
		
		return "home";
	}
	
	@RequestMapping("/buy_ticket")
	public String buy_ticket() {
		return "buy_ticket";
		
	}
	
	@RequestMapping("/buy_ticket_card")
	public String buy_ticket_card(TicketDto ticketDto, Model model) {
		System.out.println( "buy_ticket_card" );
		System.out.println( "ticketDto : " + ticketDto.getConsumerId() );
		System.out.println( "ticketDto : " + ticketDto.getAmount() );
		
		dao.buyTicket(ticketDto);
		
		model.addAttribute("ticketInfo", ticketDto);
		
		return "buy_ticket_end";
	}
	
}

6. buy_ticket.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

<p>카드 결제</p>

<form action="buy_ticket_card">
	고객 아이디 : <input type="text" name="consumerId" > <br />
	티켓 구매수 : <input type="text" name="amount" > <br />
	<input type="submit" value="구매" > <br />
</form>

</body>
</html>

7. buy_ticket_end.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>

buy_ticket_end.jsp 입니다. <br />

${ticketInfo.consumerId } <br />
${ticketInfo.amount } <br />


</body>
</html>

위의 코드에서 중요한 부분은

4번 TicketDao 클래스입니다

이 클래스에서 앞서 dependency에 추가한

JDBC Template를 통해

간편하게 다른 테이블(card, ticket)에

각각 insert하고 있는데요

만약 card, ticket 테이블의 내용이

절대 달라지면 안되는 상황이고

card 테이블에만 insert되고 

이후에 에러가 발생한다면 어떨까요?

여기서 트랜잭션 관리가 필요해집니다

위의 코드에서는 PlatformTransactionManger를

이용하여 트랙잭션을 관리하고 있습니다

그런데 보면 commit과 rollback을

개발자가 직접 코드에

추가해야 하는 것을 볼 수 있습니다

이를 좀 더 쉽게 하기 위해

TransactionTemplate을 이용해보겠습니다

8. servlet-context.xml

<beans:bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<beans:property name="dataSource" ref="dataSource" />
</beans:bean>
	
<beans:bean name="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
	<beans:property name="transactionManager" ref="transactionManager"></beans:property>
</beans:bean>

<beans:bean name="dao" class="com.example.demo.dao.TicketDao" >
	<beans:property name="template" ref="template" />
	<beans:property name="transactionTemplate" ref="transactionTemplate" />
</beans:bean>

먼저 위의 코드를

servlet-context.xml에 추가한 후

TiketDao.java를 수정합니다

9. TiketDao.java

package com.example.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import com.example.demo.dto.TicketDto;

public class TicketDao {

	JdbcTemplate template;
	
	TransactionTemplate transactionTemplate;
	
	public void setTemplate(JdbcTemplate template) {
		this.template = template;
	}
	
	public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
		this.transactionTemplate = transactionTemplate;
	}
	
	public TicketDao() {
		System.out.println(template);
	}
	
	public void buyTicket(final TicketDto dto) {
		System.out.println("buyTicket()");
		System.out.println("dto.getConsumerId() : " + dto.getConsumerId());
		System.out.println("dto.getAmount() : " + dto.getAmount());
		
		transactionTemplate.execute(new TransactionCallbackWithoutResult() {
			
			@Override
			protected void doInTransactionWithoutResult(TransactionStatus arg0) {
				template.update(new PreparedStatementCreator() {
					
					@Override
					public PreparedStatement createPreparedStatement(Connection con)
							throws SQLException {
						String query = "insert into bananameta.card (consumerId, amount) values (?, ?)";
						PreparedStatement pstmt = con.prepareStatement(query);
						pstmt.setString(1, dto.getConsumerId());
						pstmt.setString(2, dto.getAmount());
						
						return pstmt;
					}
				});
				
				template.update(new PreparedStatementCreator() {
					
					@Override
					public PreparedStatement createPreparedStatement(Connection con)
							throws SQLException {
						String query = "insert into bananameta.ticket (consumerId, countnum) values (?, ?)";
						PreparedStatement pstmt = con.prepareStatement(query);
						pstmt.setString(1, dto.getConsumerId());
						pstmt.setString(2, dto.getAmount());
						
						return pstmt;
					}
				});
			}
		});
	}
	
}

또 2개 이상의 트랜잭션이 작동할 때

기존의 트랜잭션에 참여하는 방법을

결정하는 속성인 전파 속성이 있습니다

<beans:bean name="transactionTemplate1" class="org.springframework.transaction.support.TransactionTemplate">
	<beans:property name="transactionManager" ref="transactionManager" />
	<beans:property name="propagationBehavior" value="0"/>
</beans:bean>
	
<beans:bean name="transactionTemplate2" class="org.springframework.transaction.support.TransactionTemplate">
	<beans:property name="transactionManager" ref="transactionManager" />
	<beans:property name="propagationBehavior" value="0"/>
</beans:bean>
	
<beans:bean name="dao" class="com.example.demo.dao.TicketDao" >
	<beans:property name="template" ref="template" />
	<beans:property name="transactionTemplate1" ref="transactionTemplate1" />
</beans:bean>
	
<beans:bean name="ticketCommand" class="com.example.demo.command.TicketCommand" >
	<beans:property name="ticketDao" ref="dao" />
	<beans:property name="transactionTemplate2" ref="transactionTemplate2" />
</beans:bean>

위의 코드와 같이 세팅할 수 있으며

숫자가 가지는 의미는 표와 같습니다
이것으로 오늘의 학습을 마치겠습니다

그럼 이만-_-