Spring Boot, Spring JDBC and Spring Transaction Tutorial with Examples

package org.o7planning.sbjdbc.model;

public class BankAccountInfo {

    private Long id;
    private String fullName;
    private double balance;

    public BankAccountInfo(Long id, String fullName, double balance) {
        super();
        this.id = id;
        this.fullName = fullName;
        this.balance = balance;
    }

    public Long getId() {
        return id;
    }

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

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }
    
}

package org.o7planning.sbjdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.o7planning.sbjdbc.model.BankAccountInfo;
import org.springframework.jdbc.core.RowMapper;

public class BankAccountMapper implements RowMapper<BankAccountInfo> {

    public static final String BASE_SQL //
            = "Select ba.Id, ba.Full_Name, ba.Balance From Bank_Account ba ";

    @Override
    public BankAccountInfo mapRow(ResultSet rs, int rowNum) throws SQLException {

        Long id = rs.getLong("Id");
        String fullName = rs.getString("Full_Name");
        double balance = rs.getDouble("Balance");

        return new BankAccountInfo(id, fullName, balance);
    }

}

package org.o7planning.sbjdbc.dao;

import java.util.List;

import javax.sql.DataSource;

import org.o7planning.sbjdbc.exception.BankTransactionException;
import org.o7planning.sbjdbc.mapper.BankAccountMapper;
import org.o7planning.sbjdbc.model.BankAccountInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class BankAccountDAO extends JdbcDaoSupport {

	@Autowired
	public BankAccountDAO(DataSource dataSource) {
		this.setDataSource(dataSource);
	}

	public List<BankAccountInfo> getBankAccounts() {
		// Select ba.Id, ba.Full_Name, ba.Balance From Bank_Account ba
		String sql = BankAccountMapper.BASE_SQL;

		Object[] params = new Object[] {};
		BankAccountMapper mapper = new BankAccountMapper();
		List<BankAccountInfo> list = this.getJdbcTemplate().query(sql, params, mapper);

		return list;
	}

	public BankAccountInfo findBankAccount(Long id) {
		// Select ba.Id, ba.Full_Name, ba.Balance From Bank_Account ba
		// Where ba.Id = ?
		String sql = BankAccountMapper.BASE_SQL + " where ba.Id = ? ";

		Object[] params = new Object[] { id };
		BankAccountMapper mapper = new BankAccountMapper();
		try {
			BankAccountInfo bankAccount = this.getJdbcTemplate().queryForObject(sql, params, mapper);
			return bankAccount;
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	// MANDATORY: Transaction must be created before.
	@Transactional(propagation = Propagation.MANDATORY)
	public void addAmount(Long id, double amount) throws BankTransactionException {
		BankAccountInfo accountInfo = this.findBankAccount(id);
		if (accountInfo == null) {
			throw new BankTransactionException("Account not found " + id);
		}
		double newBalance = accountInfo.getBalance() + amount;
		if (accountInfo.getBalance() + amount < 0) {
			throw new BankTransactionException(
					"The money in the account '" + id + "' is not enough (" + accountInfo.getBalance() + ")");
		}
		accountInfo.setBalance(newBalance);
		// Update to DB
		String sqlUpdate = "Update Bank_Account set Balance = ? where Id = ?";
		this.getJdbcTemplate().update(sqlUpdate, accountInfo.getBalance(), accountInfo.getId());
	}

	// Do not catch BankTransactionException in this method.
	@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = BankTransactionException.class)
	public void sendMoney(Long fromAccountId, Long toAccountId, double amount) throws BankTransactionException {

		addAmount(toAccountId, amount);
		addAmount(fromAccountId, -amount);
	}
}

package org.o7planning.sbjdbc.exception;

public class BankTransactionException extends Exception {

    
    private static final long serialVersionUID = -3128681006635769411L;
    
    public BankTransactionException(String message) {
        super(message);
    }
    
}

package org.o7planning.sbjdbc.form;

public class SendMoneyForm {
    
    private Long fromAccountId;
    private Long toAccountId;
    private Double amount;
 
    public SendMoneyForm() {
 
    }
 
    public SendMoneyForm(Long fromAccountId, Long toAccountId, Double amount) {
        this.fromAccountId = fromAccountId;
        this.toAccountId = toAccountId;
        this.amount = amount;
    }
 
    public Long getFromAccountId() {
        return fromAccountId;
    }
 
    public void setFromAccountId(Long fromAccountId) {
        this.fromAccountId = fromAccountId;
    }
 
    public Long getToAccountId() {
        return toAccountId;
    }
 
    public void setToAccountId(Long toAccountId) {
        this.toAccountId = toAccountId;
    }
 
    public Double getAmount() {
        return amount;
    }
 
    public void setAmount(Double amount) {
        this.amount = amount;
    }
    
}

@Transactional(propagation = Propagation.REQUIRES_NEW,
                         rollbackFor = BankTransactionException.class)
public void sendMoney(Long fromAccountId, Long toAccountId,
                       double amount) throws BankTransactionException {

    addAmount(toAccountId, amount);
    addAmount(fromAccountId, -amount);
}

package org.o7planning.sbjdbc.controller;

import java.util.List;

import org.o7planning.sbjdbc.dao.BankAccountDAO;
import org.o7planning.sbjdbc.exception.BankTransactionException;
import org.o7planning.sbjdbc.form.SendMoneyForm;
import org.o7planning.sbjdbc.model.BankAccountInfo;
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;

@Controller
public class MainController {

    @Autowired
    private BankAccountDAO bankAccountDAO;

    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String showBankAccounts(Model model) {
        List<BankAccountInfo> list = bankAccountDAO.getBankAccounts();

        model.addAttribute("accountInfos", list);

        return "accountsPage";
    }

    @RequestMapping(value = "/sendMoney", method = RequestMethod.GET)
    public String viewSendMoneyPage(Model model) {

        SendMoneyForm form = new SendMoneyForm(1L, 2L, 700d);

        model.addAttribute("sendMoneyForm", form);

        return "sendMoneyPage";
    }

    @RequestMapping(value = "/sendMoney", method = RequestMethod.POST)
    public String processSendMoney(Model model, SendMoneyForm sendMoneyForm) {

        System.out.println("Send Money::" + sendMoneyForm.getAmount());

        try {
            bankAccountDAO.sendMoney(sendMoneyForm.getFromAccountId(), //
                    sendMoneyForm.getToAccountId(), //
                    sendMoneyForm.getAmount());
        } catch (BankTransactionException e) {
            model.addAttribute("errorMessage", "Error: " + e.getMessage());
            return "/sendMoneyPage";
        }
        return "redirect:/";
    }

}