Temporary tables keep their data after transaction is ended

[wies...@ckc.de]

Hello,

hopefully I'm having a minor problem, but I don't know what I'm doing wrong.

I have a Java Standalone application which serves as an interface and receives raw data (nothing special here). The planned flow was to insert the raw data into temporary table and transfering it via SQL statements to the final tables. One reason for using a temporary table is, that we don't want to take care of cleaning up the table after the transfer is complete (also it's an all or nothing usecase).

The project itself uses the several Spring dependecies (4.1.8-RELEASE) like spring-jdbc, spring-beans and spring-tx and the H2 database (embedded) for the JUnit tests (target DB is Oracle).

My problem is that the inserted data rows in the temporary table still exists after the transaction has ended. What did I forget or misunderstood?

dependencies form the pom.xml

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${spring.version}</version>
        <exclusions>
            <exclusion>
                <artifactId>commons-logging</artifactId>
                <groupId>commons-logging</groupId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>${spring.version}</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
         <version>1.4.190</version>
         <scope>test</scope>
    </dependency>

schema.sql

CREATE CACHED GLOBAL TEMPORARY TABLE IF NOT EXISTS temp_record (
  cardnumber         VARCHAR(17CHAR) NOT NULL PRIMARY KEY,
  recordtype         VARCHAR(4CHAR),
  name1              VARCHAR(40CHAR),
  name2              VARCHAR(40CHAR),
  street             VARCHAR(40CHAR),
  streetsupplement1  VARCHAR(40CHAR),
  streetsupplement2  VARCHAR(40CHAR),
  postalcode         VARCHAR(12CHAR),
  city               VARCHAR(40CHAR),
  country            VARCHAR(40CHAR),
  shippingtocustomer TIMESTAMP,
  countryisocode     VARCHAR(3CHAR)
) NOT PERSISTENT TRANSACTIONAL;

spring.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" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                        http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
                        http://www.springframework.org/schema/jdbc
                        http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
                        http://www.springframework.org/schema/context
                        http://www.springframework.org/schema/context/spring-context-4.1.xsd
                        http://www.springframework.org/schema/tx
                        http://www.springframework.org/schema/tx/spring-tx-4.1.xsd">

    <context:annotation-config />
    <context:component-scan base-package="com.company.module" />

    <tx:annotation-driven transaction-manager="transactionManager" />
    <!-- Enable Annotation based Declarative Transaction Management -->
    <!-- Creating TransactionManager Bean, since JDBC we are creating of type
        DataSourceTransactionManager -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <constructor-arg ref="dataSource" />
    </bean>


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

    <bean id="namedParamJdbcTemplate"
        class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>
</beans>

TestHandler.java (not all methods shown)

@Service
public class TestHandler {
    /**
     * The connection to the database.
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * The {@link NamedParameterJdbcTemplate} for data operations.
     */
    @Autowired
    private NamedParameterJdbcTemplate paramJdbcTemplate;

    /**
     * Instance for mapping a ResultSet to a {@link AbstractRecord}.
     */
    private static final RowMapper<AbstractRecord> CARD_ROW_MAPPER = new TempRecordRowMapper();

    /**
     * The business process to the test
     * {@link TestCase#testInsertTemp()}.
     */
    @Transactional
    public void testInsertTempBusinessProcess(
            final List<AbstractRecord> recordList,
            final int expectedInsertCount) {
        final int result = this.insertAllTemapRecords(recordList);
        Assert.assertEquals(expectedInsertCount, result);

        // Query all and count
        final List<AbstractUtaRecord> queryResult = this.dao
                .queryAllTmpUtaRecords();

        Assert.assertEquals(expectedInsertCount, queryResult.size());
    }

    /**
     * Iterates the record list and inserts each record in the temporary table.
     *
     * @param recordList
     *            the records to persist
     * @return number of elements inserted
     */
    public int insertAllTemapRecords(
            final List<AbstractRecord> recordList) {
        int result = 0;
        if (null != recordList) {
            for (AbstractRecord record : recordList) {
                result += this.insertTempEntry(record);
            }
        }
        return result;
    }

    /**
     * Inserts a single record in the temporary table.
     *
     * @param record
     *            the record to store
     * @return number of inserts
     */
    public Integer insertTempEntry(final AbstractRecord record) {
        final Map<String, Object> paramMap = this
                .createRecordParameterMap(record);
        return this.paramJdbcTemplate.execute(this.buildInsertString(paramMap),
                paramMap, new PreparedStatementCallback<Integer>() {

                    @Override
                    public Integer doInPreparedStatement(
                            final PreparedStatement ps)
                            throws SQLException, DataAccessException {

                        return ps.executeUpdate();
                    }
                });
    }

    /**
     * Queries all entries of the temporary table.
     *
     * @return List of all temporary records in the database.
     */
    public List<AbstractUtaRecord> queryAllTmpUtaRecords() {

        return new ArrayList<AbstractUtaRecord>(
                this.paramJdbcTemplate.query("SELECT * FROM TEMP_RECORD", CARD_ROW_MAPPER));
    }
}

TestCase.java

@ContextConfiguration(locations = { "classpath:context-test.xml" })
@RunWith(SpringJUnit4ClassRunner.class)
@DirtiesContext(classMode = ClassMode.AFTER_EACH_TEST_METHOD)
public class TestCase extends AbstractBaseTestCase {

    /**
     * Logger of the class.
     */
    private final static Logger LOG = Logger
            .getLogger(TestCase.class.getCanonicalName());

    /**
     * Handler to test the transaction management
     */
    @Autowired
    private TestHandler handler;  

    /**
     * Load a test file of the src/test/resources folder
     *
     * @param filename
     *            name of the file to load.
     * @return file as {@link InputStream}
     */
    public InputStream getTestFile(final String filename) {
        return Thread.currentThread().getContextClassLoader()
                .getResourceAsStream(filename);
    }

    /**
     * Tests inserting data from receipts to the temporary table and check if
     * the table is empty after ending the transaction.
     */
    @Test
    public void testInsertTemp() {
        final Long zero = new Long(0);
        // assure that the table is empty
        final Long initialResult = this.jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM TEMP_RECORD", Long.class);
        Assert.assertEquals(zero, initialResult);
        InputStream inStream = null;
        try {
            inStream = this.getTestFile(TESTFILE_TEN_ENTRIES);
            final List<AbstractRecord> recordList = MyFileParser
                    .parseFile(inStream);

            Assert.assertEquals(10, recordList.size());

            this.testHandler.testInsertTempBusinessProcess(recordList,
                    10);

        } catch (final UnknownFileException e) {
            // This shouldn't happen in this test case
            Assert.assertTrue(false);
        } catch (final ParserException e) {
            // This shouldn't happen in this test case
            Assert.assertTrue(false);
        } catch (final DataAccessException dae) {
            // SQLException
            Assert.assertTrue(false);
        } finally {
            // clean up.
            if (null != inStream) {
                try {
                    inStream.close();
                } catch (final IOException ioe) {
                    // do nothing
                }
            }
        }
       

        // after the transaction the table must be empty
        final Long controlResult = this.jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM TEMP_RECORD", Long.class);
        Assert.assertEquals(initialResult, controlResult);

    }

[Noel Grandin]

http://h2database.com/html/grammar.html#create_table