Skip to content

Instantly share code, notes, and snippets.

@uklance
Last active March 7, 2023 15:04
Show Gist options
  • Save uklance/14fd8c27e34ffddbe504109009e4dcc3 to your computer and use it in GitHub Desktop.
Save uklance/14fd8c27e34ffddbe504109009e4dcc3 to your computer and use it in GitHub Desktop.
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);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment