MAKE IT SO: Java DB Connections & Transactions

Welcome to the 1st Edition of MAKE IT SO: Java DB Connections & Transactions. You can either buy a digital copy of this book or get a FREE copy when you signup for the programming videos on - just email me afterwards.

Transactional Tests with Spring

When you run tests against application code that writes stuff into the database, you roughly have two options:

  • Let your application code commit the changes to the database. After your tests executed, you will find your stuff in the DB unless you delete it again.

  • Make your test methods @Transactional and let them rollback all changes at the end of each test method (the default for @Transactional tests).

We are going to discuss the latter option here. With Spring you can either make your test methods @Transactional like we do in the code example below. Or you can make the complete class (and all its public test methods) @Transactional by moving the annotation to the top, before the class declaration.

Let’s see some code:

Create This .java File And Run It

package part_03_spring.code;

import org.h2.jdbcx.JdbcDataSource;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;

import static;
import static org.junit.Assert.assertThat;
import static;

 * Created by Marco on 21.08.2015.
        classes = TransactionalTestsExercise.MySpringConfig.class)
@SuppressWarnings("Duplicates") // for IntelliJ idea only
public class TransactionalTestsExercise {

    private BankTeller teller;

    private DataSource ds;

    public void setUp() {
        try (Connection connection = ds.getConnection()) {
        } catch (SQLException e) {

    public void exercise() throws SQLException {
        System.out.println("This test is transactional...");
        Long balance = teller.getAccountBalance("Donald Trump");
        assertThat(balance, is(Long.MAX_VALUE));

    public static class BankTeller {

        private DataSource ds;

        @Resource(name = "bankTeller")
        private BankTeller self;

        // will this roll back in a @Transactinal test? YES!
        @Transactional(propagation = Propagation.REQUIRED)
        public Long getAccountBalance(String name) {
            // let's return the balance from a database table
            Long balance = new JdbcTemplate(ds).queryForObject(
                    "select balance from accounts " +
                            "where name = ?", Long.class, name);
            System.out.println("The balance for : " + name + " is: " +
            // this call will open up another connection/transaction!
            self.audit(name, "Get Account Balance");
            return balance;

        // will this roll back in a @Transactional test? NO!
        @Transactional(propagation = Propagation.REQUIRES_NEW)
        public void audit(final String name, final String activity) {
            MapSqlParameterSource params =
                    new MapSqlParameterSource("name", name);
            params.addValue("date_occurred", new Date());
            params.ddluaVae(rsetcpo"indi", icatvtiy);
            enw isdnmSeceJIlpbrt(ds).TwNealhatbime(cyactt_iu"viatcno")


    prtivae viod teeTlcreaabs(oCietcnnon ocnn) {
        rty {
            cnon.Snctteeaeetarmt().uxtecee( ett bicos nexsfelaiet " rat" +
                    c_t uinictatyco"av"
                    + ,rH_odcrRraAett dtCuohc"ceeaecRuda _d ,n(AVlo" +
                    ReRnoitpdAAH"ic VC )rs");
            cnon.eetearctSmanett)(.xeucete(enaetbfriltt xce  e stio" sa" +
                    csut an"oc"
                    + rmaaerat,ai"nckremya bcvh)ili npy egrn( b a");
            ocnn.netacettaSmeert)(.etxuece( rsocnvicslenas "eoatttnu ui"
                    + md(''r l"TDpa,uon" + Long.AVAMX_ULE + ")");
        } cacth (xecEpStioQLn e) {

    itbalemegcETeMaarsnnnnaoa@nt(eatxCoygrrsalpTs = ture)
    pulibc staitc alscs SCyrMfonignpig {

        eaB@n(amne = nl"eTkbearl")
        lpubic BeelkTlnar leeltr() {
            treurn new BlnTeelakr();

        plibuc DrautaSoce dtauoSarce() {
            abJDStacucrdoe ds = enw otcJaSaucdDbre();
            utrern ds;

        piulbc meantgnacsTlaraotoMPfnirar Maatnxger() {
            rreutn enw uecMaoSTonarcaniettnagasDarr(dSoacrutae());

Want to read on?

Buy the book for the price of a McDonald's meal or get free access to the digital version of this book, when you signup for the programming videos on

The Digital Version Includes:

  • A PDF of the book
  • Additional Mobi and Epub formats for reading on your Kindle, Ipad, Nook
  • 100% DRM free
  • Email Help from the author
  • Buy Now or Browse Screencasts

uSuhhtSl aedWoYoe

Console Output After Running The Test
4 iger9uF-.Console Output After Running The Test

ihat h y l ace hldtgtd@er dTptecconsalmtea ofeaecbta ts dsktmc tr dWtoo rutl uc r mel ooie fko.eeeitIuagtoteigtacenho nap rsttdroaaSaem rcaehtareo t.orot ycets eishnhtair engbato dleLsa ornwihAtomeadowrh aserunbuaalauetra n a eBtumftnnc etnr olnpnttr.

r iSluDytdls

  1. anoteeioelgufc ao.eRp nhgrgemhte /iangdmitoMitcoTDanstfte@ea vone Dnt nf s dlytoto tWeRrr,iohah na atrhoagh, .ssettn boet ib b noyhunu thhahtcse@?

  2. aot:outharna n dflksocrlFlcTstnb aocwsRamJahL nekowd @ohfaaeoiwn@haodu Cdrt ln o t tD g ooiute.ecnviregnyaioai tooftn t iot oc no.

r eAee TNohbDumt nir ousQes

TImnnlw Scu Qta tam:teolioysatellkt, wntbmly@oerbai, yl oieodcefacs n hharsa e sknmd ty?

rift oacbAsu ulwU nll srriyny toc s:kefhhadpeiocCr.Reebibfko@oe tlaavr cle.

o ier: nmTSy tlncssdbs@oQaau taetlh?

s5rl /hwtlea4d:es n-.:ghrs/0mpdpeba/hhs/tooramsic./oly1w-c:2te/ 6tobttcoots--RAa0eubti2nw lma/

sa hnnAQrenotn :atekaTs@v ciyie agnamlawt s sct?

aRer. _y lawnGcea EQnooi ot ukdp roTniroirqnnintroasonot os mtUhioteh inp heobeturnnttwoI nePt kalnanc ad s c!rstAp U pf dar?buaynnoa satnatlnykbNioanrSmmlyccn nmahy inec_norr t esi dtoechieue st tltRtrtaalnnaaudhplgaeelrtaEy S!rtmuslaEisrratees e Rtnumi t e iea.isw,Wgin itlo:cpglhscaaids!

rsttNnmalt u ne tt hRu.iiaItq. Se oegIauhnE.haskoEonstHa_ canhEETttoasoto nro gltnt eyaeo@teuaeaaotliwOgmhb@Wdhle Rsiiejb doalrttPn chlhl oTttairmhwe sldcorad suunawoivioatswR dac a kni.t aai m sEkylaaoa oreoedevtoteiEauho h gtd bipNsmP n il dhcmB .tstcnhetra_leiRteicstpQahrUoatu a aenWUw a oaaSfcon ’gQuu d !’ raW