Various DB's behavior of PreparedStatement#setNull() + Types.OTHER or NULL

SetNullTest.java

import static org.junit.jupiter.api.Assertions.*;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;

import net.harawata.jdbc.connection.JdbcConnection;

public class SetNullTest {

  @ParameterizedTest
  @ValueSource(strings = { "BIGINT", "NUMERIC", "BIT", "SMALLINT", "DECIMAL", "SMALLMONEY", "INT", "TINYINT", "MONEY",
    "FLOAT", "REAL", "DATE", "DATETIMEOFFSET", "DATETIME2", "SMALLDATETIME", "DATETIME", "TIME", "CHAR",
    "VARCHAR", "TEXT", "NCHAR", "NVARCHAR", "NTEXT", "BINARY", "VARBINARY", "IMAGE" })
  void testMssql(String dataType) throws Exception {
    runTest("mssql", dataType);
  }

  @ParameterizedTest
  @ValueSource(strings = { "bigint", "bigserial", "bit", "bit varying", "boolean", "box", "bytea", "character",
    "character varying", "cidr", "circle", "date", "double precision", "inet", "integer", "interval", "json", "jsonb",
    "line", "lseg", "macaddr", "macaddr8", "money", "numeric", "path", "pg_lsn", "point", "polygon", "real", "smallint",
    "smallserial", "serial", "text", "time", "time with time zone", "timestamp", "timestamp with time zone", "tsquery",
    "tsvector", "txid_snapshot", "uuid", "xml" })
  void testPostgres(String dataType) throws Exception {
    runTest("postgres", dataType);
  }

  @ParameterizedTest
  @ValueSource(strings = { "INTEGER", "SMALLINT", "DECIMAL", "NUMERIC", "FLOAT", "REAL", "DOUBLE PRECISION", "DATE",
    "TIME", "DATETIME", "TIMESTAMP", "YEAR", "CHAR", "VARCHAR(1)", "BINARY", "VARBINARY(1)", "BLOB",
    "TEXT", "ENUM('x')", "SET('x')",
    "JSON" })
  void testMysql(String dataType) throws Exception {
    runTest("mysql", dataType);
  }

  @ParameterizedTest
  @ValueSource(strings = { "CHAR(1)", "VARCHAR2(1)", "VARCHAR(1)", "NCHAR(1)", "NVARCHAR2(1)", "BLOB", "CLOB", "NCLOB",
    "BFILE", "RAW(1)", "LONG RAW", "NUMBER", "BINARY_FLOAT", "BINARY_DOUBLE", "DATE", "TIMESTAMP",
    "TIMESTAMP WITH TIME ZONE",
    "TIMESTAMP WITH LOCAL TIME ZONE", "ROWID", "UROWID" })
  void testOracle(String dataType) throws Exception {
    runTest("oracle18", dataType, Types.NULL);
  }

  @ParameterizedTest
  @ValueSource(strings = { "BIGINT", "SMALLINT", "INTEGER", "DOUBLE", "NUMERIC", "NUMERIC", "NUMERIC", "DATE", "REAL",
    "TIME", "TIMESTAMP", "CHAR", "VARCHAR(1)", "LONG VARCHAR", "CLOB", "GRAPHIC", "VARGRAPHIC(1)", "LONG VARGRAPHIC",
    "DBCLOB", "BLOB", })
  void testDb2(String dataType) throws Exception {
    runTest("db2", dataType);
  }

  @ParameterizedTest
  @ValueSource(strings = { "BIGINT" })
  void testSybase(String dataType) throws Exception {
    runTest("sybase", dataType);
  }

  protected void runTest(String db, String dataType) throws SQLException, ReflectiveOperationException, IOException {
    runTest(db, dataType, Types.OTHER);
  }

  protected void runTest(String db, String dataType, int nullType)
      throws SQLException, ReflectiveOperationException, IOException {
    // https://github.com/harawata/jdbc-connection
    try (Connection con = new JdbcConnection(db).getConnection()) {
      createTestTable(con, dataType);
      insertNull(con, nullType);
      verifyInsertedRow(con);
    }
  }

  protected void insertNull(Connection con, int nullType) throws SQLException {
    try (PreparedStatement stmt = con.prepareStatement("insert into test values (?)")) {
      stmt.setNull(1, nullType);
      assertFalse(stmt.execute());
    }
  }

  protected void verifyInsertedRow(Connection con) throws SQLException {
    try (Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select count(*) from test where c1 is null")) {
      assertTrue(rs.next());
      assertEquals(1, rs.getInt(1));
    }
  }

  protected void createTestTable(Connection con, String dataType) throws SQLException {
    try (Statement stmt = con.createStatement()) {
      try {
        stmt.execute("drop table test");
      } catch (Exception e) {
        // expected
      }
      stmt.execute("create table test (c1 " + dataType + ")");
    }
  }
}