Configuring H2 database for unit tests w/ spring

H2EmbeddedTest.java

package examples.database;

import com.google.common.collect.Lists;
import examples.database.dao.PeopleDAO;
import examples.database.model.PeopleInfo;
import org.apache.commons.lang.time.StopWatch;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.Date;
import java.util.List;
import java.util.UUID;

import static org.junit.Assert.assertNotNull;

@ContextConfiguration(locations = {"classpath:test-db-context.xml"})
@RunWith(SpringJUnit4ClassRunner.class)
public class H2EmbeddedTest {
    private static final Log LOGGER = LogFactory.getLog(H2EmbeddedTest.class);

    @Autowired
    private PeopleDAO peopleDAO;

    @Test
    public void testContext() {
        assertNotNull(peopleDAO);
    }

    public interface PeopleMaker {
        PeopleInfo makePerson(int num);
    }

    public interface UpsertOperation<T> {
        void doUpsert(List<T> data);
    }

    @Test
    public void testBatchUpsertNoTx() {
        doBatchUpsertTest("noTx", new UpsertOperation<PeopleInfo>() {
            @Override
            public void doUpsert(List<PeopleInfo> data) {
                peopleDAO.batchUpsert(data);
            }
        }, 100000, 5);
    }

//    @Test
//    public void testBatchUpsertWithTx() {
//        doBatchUpsertTest("withTx", new UpsertOperation<PeopleInfo>() {
//            @Override
//            public void doUpsert(List<PeopleInfo> data) {
//                peopleDAO.batchUpsertInTx(data);
//            }
//        }, 100000, 5);
//    }

    private void doBatchUpsertTest(String tag, UpsertOperation<PeopleInfo> upsertOperation, final int numItems, final int repeatCnt) {

        for (int i = 0; i < repeatCnt; i++) {
            final String uniqTag = String.valueOf(System.nanoTime());

            List<PeopleInfo> ppl = getPeopleList(numItems, new PeopleMaker() {
                @Override
                public PeopleInfo makePerson(int num) {
                    PeopleInfo info = new PeopleInfo();
                    info.setFirstName("firstname" + num + uniqTag);
                    info.setLastLogin(new Date());
                    info.setLastName("lastname" + num + uniqTag);
                    info.setSsn(UUID.randomUUID().toString().substring(0, 11));
                    return info;
                }
            });
            StopWatch sw = new StopWatch();
            sw.reset();
            sw.start();
            upsertOperation.doUpsert(ppl);
            sw.stop();
            LOGGER.info(String.format("UpsertTest[%s], iteration: [%d] -- Inserted [%d] people in: [%d] ms",
                    tag, i + 1, numItems, sw.getTime()));
        }

    }

    private void printPppl(List<PeopleInfo> peopleWithLoginInfo) {
        for (PeopleInfo info : peopleWithLoginInfo) {
            LOGGER.info(info);
        }
    }

    private List<PeopleInfo> getPeopleList(int numPeople, PeopleMaker maker) {
        final List<PeopleInfo> ppl = Lists.newArrayList();
        for (int i = 0; i < numPeople; i++) {
            ppl.add(maker.makePerson(i));
        }
        return ppl;
    }


}

jdbc-h2.properties

# an in-memory, anonymous database
#jdbc.url=jdbc:h2:mem
# a local, file-based database
jdbc.url=jdbc:h2:~/h2-testdb;MODE=MYSQL
jdbc.user=sa
jdbc.password=

PeopleDAO.java

package examples.database.dao;

import com.google.common.collect.Maps;
import examples.database.model.PeopleInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("peopleDao")
public class PeopleDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate;


    public List<PeopleInfo> getPeopleWithLoginInfo() {
        return this.namedJdbcTemplate.getJdbcOperations().query(
                "select * from people_with_logins",
                new RowMapper<PeopleInfo>() {
                    @Override
                    public PeopleInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                        PeopleInfo info = new PeopleInfo();
                        info.setFirstName(rs.getString("first_name"));
                        info.setLastName(rs.getString("last_name"));
                        info.setDob(rs.getDate("dob"));
                        info.setSsn(rs.getString("ssn"));
                        if (rs.getTimestamp("last_login") != null) {
                            info.setLastLogin(rs.getTimestamp("last_login"));
                        }
                        return info;
                    }
                });
    }

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }


    private static Map<String,Object> createBatchUpsertValuesMap(PeopleInfo info) {
        Map<String,Object> valMap = Maps.newHashMap();
        valMap.put("first", info.getFirstName());
        valMap.put("last", info.getLastName());
        valMap.put("ssn", info.getSsn());
        valMap.put("dob", info.getDob());
        return valMap;
    }

    public void batchUpsert(final List<PeopleInfo> pInfo) {
     doUpsert(pplListToSqlValuesMapArray(pInfo));
    }

    public void batchUpsertInTx(final List<PeopleInfo> pInfo) {
        doUpsertInTx(pplListToSqlValuesMapArray(pInfo));
    }



    //---
    // private utility methods
    //---
    private static Map<String, ?>[] pplListToSqlValuesMapArray(List<PeopleInfo> peopleInfos) {
        Map<String, ?>[] valuesMaps = new Map[peopleInfos.size()];
        for(int i=0; i<peopleInfos.size(); i++) {
            valuesMaps[i] = createBatchUpsertValuesMap(peopleInfos.get(i));
        }
        return valuesMaps;
    }

    private void doUpsert(Map<String,?>[] valuesMap) {
        int[] ids = this.namedJdbcTemplate.batchUpdate(
                "INSERT INTO people(first_name, last_name, dob, ssn) VALUES (:first, :last, :dob, :ssn)",
                valuesMap);
    }

    @Transactional
    private void doUpsertInTx(Map<String,?>[] valuesMap) {
        int[] ids = this.namedJdbcTemplate.batchUpdate(
                "INSERT INTO people(first_name, last_name, dob, ssn) VALUES (:first, :last, :dob, :ssn)",
                valuesMap);
    }



}

test-context.xml

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

    <!-- load H2 jdbc properties -->
    <context:property-placeholder location="classpath:jdbc-h2.properties"/>


    <!--
       The below config shows how to use a completely managed embedded version of H2
        Uncomment it if you do not wish to configure H2 database settings manually.
     -->

    <!--
    <jdbc:embedded-database id="dataSource" type="H2">
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:embedded-database>
    -->


    <bean id="dataSource"
          class="org.h2.jdbcx.JdbcDataSource">
        <property name="URL" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <jdbc:initialize-database>
        <jdbc:script location="classpath:schema.sql"/>
        <jdbc:script location="classpath:test-data.sql"/>
    </jdbc:initialize-database>


</beans>