Spring JDBC

The following article explains how to replace the JDBC code used by Java Bank with Spring JDBC.
Add the following to the pom.xml.

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

Declares that ShinhanBankDao inherits NamedParameterJdbcDaoSupport.
Remove constructors, getConnection(), and close(rs, pstmt, con) methods.

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 {

	//Remove URL, USER, PASSWORD fields.
	//Remove constructors, getConnection(), close(rs,pstmt,con) methods.
	
	//..omit..

}
Before
@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);
	}
	
}
After
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);		
}
Before
@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;
}
After
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;
			}
		}
	);
}
Before
@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;
}
After
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;
	}
}
Before
@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;

}
Atter
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);
}
Before
@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);
	}
	
}
After
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);
}
Before
@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);
	}
}
After
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);		
}
Before
@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;

}
After
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 configuration

Modify applicationContext.xml as shown below.

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>

Modify the main method of BankUi.java as follows.

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

It is assumed that 101 accounts exist but 505 accounts do not exist.
Try to transfer from account 101 to account 505.
After transfer, 101 accounts will be reduced by the transfer amount.
(This is covered in Spring Transaction)

JavaConfig configuration

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;
	}
	
}

First of all, comment out all settings in applicationContext.xml.
Modify the main method of BankUi.java as shown below.

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

Final Source : https://github.com/kimjonghoon/jdbc