HsqlDbTest.java

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;

import static org.assertj.core.api.Assertions.assertThat;

public class HsqlDbTest {
    private Connection connection;

    @BeforeEach
    public void beforeEach() throws Exception {
        connection = DriverManager.getConnection("jdbc:hsqldb:mem:test;sql.syntax_ora=true", "test", "test");
        connection.createStatement().execute(
                "CREATE TABLE SAMPLE (\n" +
                "   ID NUMERIC(12,0) PRIMARY KEY,\n" +
                "   CODE VARCHAR2(50)," +
                "   LAST_UPDATED TIMESTAMP WITH TIME ZONE,\n" +
                ")"
        );
    }
    
    @AfterEach
    public void afterEach() throws Exception {
        connection.createStatement().execute("DROP TABLE SAMPLE");
        connection.close();
    }    

    @Test
    public void testInsert() throws Exception {
        insertSample(1, "C1", OffsetDateTime.now());
        insertSample(2, "C2", OffsetDateTime.now());
    }

    @Test
    public void testUpdate() throws Exception {
        insertSample(1, "C1A", OffsetDateTime.now());
        insertSample(2, "C2A", OffsetDateTime.now());
        updateSample(1, "C1B", OffsetDateTime.now());
        updateSample(2, "C2B", OffsetDateTime.now());
    }

    @Test
    public void testMerge() throws Exception {
        merge1Sample(1, "C1A", OffsetDateTime.now());
        merge1Sample(1, "C1B", OffsetDateTime.now());
        merge2Sample(2, "C2A", OffsetDateTime.now()); // this fails
    }


    private void insertSample(long id, String code, OffsetDateTime createdDate) throws SQLException  {
        try (PreparedStatement ps = connection.prepareStatement("INSERT INTO SAMPLE (ID, CODE, LAST_UPDATED) VALUES (?, ?, ?)")) {
            ps.setLong(1, id);
            ps.setString(2, code);
            ps.setObject(3, createdDate);
            assertThat(ps.executeUpdate()).isEqualTo(1);
        }
    }

    private void updateSample(long id, String code, OffsetDateTime createdDate) throws SQLException  {
        try (PreparedStatement ps = connection.prepareStatement("UPDATE SAMPLE SET CODE = ?, LAST_UPDATED = ? WHERE ID = ?")) {
            ps.setString(1, code);
            ps.setObject(2, createdDate);
            ps.setLong(3, id);
            assertThat(ps.executeUpdate()).isEqualTo(1);
        }
    }

    // this works since it uses PreparedStatement.setString(...)
    private void merge1Sample(long id, String code, OffsetDateTime createdDate) throws SQLException  {
        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSxxxxx");
        String sql =
                "MERGE INTO SAMPLE t " +
                "USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM DUAL) val " +
                "ON (t.ID = val.ID) " +
                "WHEN MATCHED THEN UPDATE SET t.CODE = val.CODE, t.LAST_UPDATED = val.LAST_UPDATED " +
                "WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setLong(1, id);
            ps.setString(2, code);
            ps.setString(3, dateFormatter.format(createdDate));
            assertThat(ps.executeUpdate()).isEqualTo(1);
        }
    }

    // this fails since it calls PreparedStatement.setObject(...) with an OffsetDateTime
    // exception is java.sql.SQLDataException: data exception: invalid datetime format
    private void merge2Sample(long id, String code, OffsetDateTime createdDate) throws SQLException  {
        String sql =
                "MERGE INTO SAMPLE t " +
                "USING (SELECT ? AS ID, ? AS CODE, ? AS LAST_UPDATED FROM DUAL) val " +
                "ON (t.ID = val.ID) " +
                "WHEN MATCHED THEN UPDATE SET t.CODE = val.CODE, t.LAST_UPDATED = val.LAST_UPDATED " +
                "WHEN NOT MATCHED THEN INSERT (ID, CODE, LAST_UPDATED) VALUES (val.ID, val.CODE, val.LAST_UPDATED)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setLong(1, id);
            ps.setString(2, code);
            ps.setObject(3, createdDate);
            assertThat(ps.executeUpdate()).isEqualTo(1);
        }
    }
}