스프링 JDBC

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

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

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

MyBankDao.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 MyBankDao 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, balance, kind) " +
    "VALUES (:accountNo, :name, 0, :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.getDouble("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.getDouble("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.getDouble("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");
    double balance = rs.getDouble("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.getDouble("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, double 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, double 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, double 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, double 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.getDouble("amount"));
      transaction.setBalance(rs.getDouble("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");
    double amount = rs.getDouble("amount");
    double balance = rs.getDouble("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="myBank" />
  </bean>

  <bean id="myBank" class="net.java_school.bank.MyBank">
    <property name="dao" ref="myBankDao" />
  </bean>

  <bean id="myBankDao" class="net.java_school.bank.MyBankDao">
    <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 myBankDao() {
        MyBankDao bankDao = new MyBankDao();
        bankDao.setDataSource(dataSource());
        return bankDao;
    }

  @Bean
  public Bank myBank() {
    Bank bank = new MyBank();
    bank.setDao(myBankDao());
    return bank;
  }

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

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

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

최종 소스

https://github.com/kimjonghoon/jdbc