java-school logo

Spring JDBC

자바 은행에서 사용한 JDBC 코드를 스프링 JDBC로 바꾸어 보자. 다음 의존성을 추가한다.

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${spring.version}</version>
</dependency>

ShinhanBankDao 클래스가 NamedParameterJdbcDaoSupport 클래스를 상속하도록 선언한다. 생성자와 getConnection(), close(rs,pstmt,con) 메서드는 제거한다.

ShinhanBankDao.java
package net.java_school.bank;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

public class ShinhanBankDao extends NamedParameterJdbcDaoSupport implements BankDao {

	//필드 URL, USER, PASSWORD 삭제
	//생성자와 getConnection(), close(rs,pstmt,con) 메서드 삭제
	
	//..중간 생략..

}
기존 insertAccount() 메서드
@Override
public void insertAccount(String accountNo, String name, String kind) {
	
	Connection con = null;
	PreparedStatement pstmt = null;
	
	String sql = "INSERT INTO bankaccount VALUES (?, ?, 0, ?)";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, accountNo);
		pstmt.setString(2, name);
		pstmt.setString(3, kind);
		pstmt.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(null, pstmt, con);
	}
	
}
수정 후 insertAccount() 메서드
private static final String INSERT_ACCOUNT = 
		"INSERT INTO " +
		"bankaccount (accountno, owner, kind) " +
		"VALUES (:accountNo, :name, :kind)";
   
@Override
public void insertAccount(String accountNo, String name, String kind) {
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("accountNo", accountNo);
	params.put("name", name);
	params.put("kind", kind);
	
	getNamedParameterJdbcTemplate().update(INSERT_ACCOUNT, params);		
}
기존 selectOneAccount() 메서드
@Override
public Account selectOneAccount(String accountNo) {
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	Account account = null;
	
	String sql = "SELECT accountNo,owner,balance,kind " +
			"FROM bankaccount " +
			"WHERE accountNo = ?";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, accountNo);
		rs = pstmt.executeQuery();
		
		if (rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getString("accountNo"));
			account.setName(rs.getString("owner"));
			account.setBalance(rs.getLong("balance"));
			account.setKind(rs.getString("kind"));
			
			return account;
			
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(rs, pstmt, con);
	}
	return null;
}
수정 후 selectOneAccount() 메서드
private static final String SELECT_ONE_ACCOUNT = 
		"SELECT accountno,owner,balance,kind " +
		"FROM bankaccount " +
		"WHERE accountno = :accountNo";

@Override
public Account selectOneAccount(String accountNo) {
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("accountNo", accountNo);
	
	return getNamedParameterJdbcTemplate().queryForObject(
		SELECT_ONE_ACCOUNT,
		params,
		new RowMapper<Account>() {
			public Account mapRow(ResultSet rs,int rowNum) throws SQLException {
				Account account = new Account();
				account.setAccountNo(rs.getString("accountNo"));
				account.setName(rs.getString("owner"));
				account.setBalance(rs.getLong("balance"));
				account.setKind(rs.getString("kind"));
				
				return account;
			}
		}
	);
}
기존 selectAccountsByName() 메서드
@Override
public List<Account> selectAccountsByName(String name) {
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	List<Account> matched = new ArrayList<Account>();
	Account account = null;
	
	String sql = "SELECT accountNo,owner,balance,kind " +
			"FROM bankaccount " +
			"WHERE owner = ? " +
			"ORDER By accountNo DESC";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, name);
		rs = pstmt.executeQuery();
		
		while (rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getString("accountNo"));
			account.setName(rs.getString("owner"));
			account.setBalance(rs.getLong("balance"));
			account.setKind(rs.getString("kind"));
			matched.add(account);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(rs, pstmt, con);
	}

	return matched;
}
수정 후 selectAccountsByName() 메서드
private static final String SELECT_ACCOUNTS_BY_NAME = 
		"SELECT accountno,owner,balance,kind " +
		"FROM bankaccount " +
		"WHERE owner = :name";

@Override
public List<Account> selectAccountsByName(String name) {
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("name", name);
	RowMapper<Account> rowMapper = new AccountRowMapper();
	
	return getNamedParameterJdbcTemplate().query(SELECT_ACCOUNTS_BY_NAME,params,rowMapper);
}

protected class AccountRowMapper implements RowMapper<Account> {

	public Account mapRow(ResultSet rs,int rowNum) throws SQLException {

		String accountNo = rs.getString("accountNo");
		String name = rs.getString("owner");
		long balance = rs.getLong("balance");
		String kind = rs.getString("kind");
		
		Account account = new Account();
		account.setAccountNo(accountNo);
		account.setName(name);
		account.setBalance(balance);
		account.setKind(kind);
		
		return account;
	}
}
기존 selectAllAccounts() 메서드
@Override
public List<Account> selectAllAccounts() {
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	List<Account> all = new ArrayList<Account>();
	Account account = null;
	
	String sql = "SELECT accountNo,owner,balance,kind " +
			"FROM bankaccount " +
			"ORDER By accountNo DESC";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		while (rs.next()) {
			account = new Account();
			account.setAccountNo(rs.getString("accountNo"));
			account.setName(rs.getString("owner"));
			account.setBalance(rs.getLong("balance"));
			account.setKind(rs.getString("kind"));
			all.add(account);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(rs, pstmt, con);
	}

	return all;

}
수정 후 selectAllAccounts() 메서드
private static final String SELECT_ALL_ACCOUNTS = 
		"SELECT accountNo,owner,balance,kind " +
		"FROM bankaccount " +
		"ORDER BY accountNo DESC";

@Override
public List<Account> selectAllAccounts() {
	RowMapper<Account> rowMapper = new AccountRowMapper();
	return getJdbcTemplate().query(SELECT_ALL_ACCOUNTS,rowMapper);
}
기존 deposit() 메서드
@Override
public void deposit(String accountNo, long amount) {
	Connection con = null;
	PreparedStatement pstmt = null;
	
	String sql = "UPDATE bankaccount " +
			"SET balance = balance + ? " +
			"WHERE accountNo = ?";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setLong(1, amount);
		pstmt.setString(2, accountNo);
		pstmt.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(null, pstmt, con);
	}
	
}
수정 후 deposit() 메서드
private static final String DEPOSIT = 
		"UPDATE bankaccount " +
		"SET balance = balance + :amount " +
		"WHERE accountno = :accountNo";

@Override
public void deposit(String accountNo, long amount) {
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("amount", amount);
	params.put("accountNo", accountNo);
	
	getNamedParameterJdbcTemplate().update(DEPOSIT, params);
}
기존 withdraw() 메서드
@Override
public void withdraw(String accountNo, long amount) {
	Connection con = null;
	PreparedStatement pstmt = null;
	
	String sql = "UPDATE bankaccount " +
			"SET balance = balance - ? " +
			"WHERE accountNo = ?";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setLong(1, amount);
		pstmt.setString(2, accountNo);
		pstmt.executeUpdate();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(null, pstmt, con);
	}
}
수정 후 withdraw() 메서드
private static final String WITHDRAW = 
		"UPDATE bankaccount " +
		"SET balance = balance - :amount " +
		"WHERE accountno = :accountNo";

@Override
public void withdraw(String accountNo, long amount) {
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("amount", amount);
	params.put("accountNo", accountNo);
	
	getNamedParameterJdbcTemplate().update(WITHDRAW, params);		
}
기존 selectAllTransactions() 메서드
@Override
public List<Transaction> selectAllTransactions(String accountNo) {
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	List<Transaction> all = new ArrayList<Transaction>();
	Transaction transaction = null;
	
	String sql = "SELECT transactionDate,kind,amount,balance " +
			"FROM transaction " +
			"WHERE accountNo = ? " +
			"ORDER By transactionDate ASC";
	
	try {
		con = getConnection();
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1, accountNo);
		rs = pstmt.executeQuery();
		
		while (rs.next()) {
			transaction = new Transaction();
			String date = Account.DATE_FORMAT.format(rs.getTimestamp("transactionDate"));
			String time = Account.TIME_FORMAT.format(rs.getTimestamp("transactionDate"));
			transaction.setTransactionDate(date);
			transaction.setTransactionTime(time);
			transaction.setKind(rs.getString("kind"));
			transaction.setAmount(rs.getLong("amount"));
			transaction.setBalance(rs.getLong("balance"));
			all.add(transaction);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		close(rs, pstmt, con);
	}

	return all;

}
수정 후 selectAllTransactions() 메서드
private static final String SELECT_ALL_TRANSACTIONS = 
		"SELECT transactionDate,kind,amount,balance " +
		"FROM transaction " +
		"WHERE accountno = :accountNo " +
		"ORDER By transactionDate ASC";

@Override
public List<Transaction> selectAllTransactions(String accountNo) {
	RowMapper<Transaction> rowMapper = new TransactionRowMapper();
	Map<String, Object> params = new HashMap<String, Object>();
	params.put("accountNo", accountNo);

	return getNamedParameterJdbcTemplate().query(SELECT_ALL_TRANSACTIONS,params,rowMapper);
}

protected class TransactionRowMapper implements RowMapper<Transaction> {

	public Transaction mapRow(ResultSet rs,int rowNum) throws SQLException {

		String date = Account.DATE_FORMAT.format(rs.getTimestamp("transactionDate"));
		String time = Account.TIME_FORMAT.format(rs.getTimestamp("transactionDate"));
		String kind = rs.getString("kind");
		long amount = rs.getLong("amount");
		long balance = rs.getLong("balance");

		Transaction transaction = new Transaction();
		transaction.setTransactionDate(date);
		transaction.setTransactionTime(time);
		transaction.setKind(kind);
		transaction.setAmount(amount);
		transaction.setBalance(balance);
		
		return transaction;
	}
}

XML 설정

applicationContext.xml에 강조된 부분을 추가한다.

applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:aop="http://www.springframework.org/schema/aop" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop 
        http://www.springframework.org/schema/aop/spring-aop.xsd">

	<aop:aspectj-autoproxy />

	<bean id="testLogger" class="net.java_school.commons.TestLogger" />

	<bean id="bankUi" class="net.java_school.bank.BankUi">
		<property name="stream" value="#{T(System).out}" />
		<property name="bank" ref="shinhanBank" />
	</bean>

	<bean id="shinhanBank" class="net.java_school.bank.ShinhanBank">
		<property name="dao" ref="shinhanBankDao" />
	</bean>

	<bean id="shinhanBankDao" class="net.java_school.bank.ShinhanBankDao">
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
		<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:XE" />
		<property name="username" value="scott" />
		<property name="password" value="tiger" />
	</bean>
	
</beans>

테스트

테스트하기 전에 BankUi.java의 메인 메서드를 아래를 참조해 수정한다.

ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); //XML
//AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(BankConfig.class); //JavaConfig

101계좌는 존재하나 505계좌는 존재하는 않는다고 가정한다. 101계좌에서 505계좌로 이체 테스트를 한다. 이체 후 101계좌는 이체 금액만큼 잔액이 줄어든다. (이 문제는 트랜잭션에서 다룬다)

JavaConfig 설정

BankConfig.java
package net.java_school.bank;

import javax.sql.DataSource;

import net.java_school.commons.TestLogger;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@EnableAspectJAutoProxy
public class BankConfig {

	@Bean
	public TestLogger testLogger() {
		return new TestLogger();
	}
	
	@Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:XE");
        dataSource.setUsername("scott");
        dataSource.setPassword("tiger");
        return dataSource;
    }
	
    @Bean
    public BankDao shinhanBankDao() {
        ShinhanBankDao bankDao = new ShinhanBankDao();
        bankDao.setDataSource(dataSource());
        return bankDao;
    }

	@Bean
	public Bank shinhanBank() {
		Bank bank = new ShinhanBank();
		bank.setDao(shinhanBankDao());
		return bank;
	}

	@Bean
	public BankUi bankUi() {
		BankUi ui = new BankUi();
		ui.setBank(shinhanBank());
		ui.setStream(System.out);
		return ui;
	}
	
}

테스트하기 전에, applicationContext.xml 설정을 모두 주석 처리한다. BankUi.java의 메인 메서드를 아래를 참조해 수정한다.

//ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); //XML
AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(BankConfig.class); //JavaConfig

최종 소스

예제 다운로드