private CopyManager getCopyManager(Connection conn) throws SQLException { PgConnection unwrapped = conn.unwrap(PgConnection.class); return unwrapped.getCopyAPI(); }
@Test public void canNotInsertIntoSerialColumn() throws SQLException, IOException { try (Connection conn = database.getConnection()) { try (Statement stmt = conn.createStatement()) { PgConnection unwrapped = conn.unwrap(PgConnection.class); stmt.execute("DROP TABLE IF EXISTS simple_table"); stmt.execute("CREATE TABLE simple_table (id SERIAL, name VARCHAR(255), PRIMARY KEY (id))"); CopyManager copyManager = unwrapped.getCopyAPI(); String sql = "COPY simple_table (id, name) FROM STDIN WITH (" + "ENCODING 'UTF-8', DELIMITER '\t', HEADER false" + ")"; String row1 = "\\N\tJohn\n"; String row2 = "\\N\tSmith\n"; Reader reader = new StringBuilderReader(new StringBuilder(row1).append(row2)); assertThrows(PSQLException.class, () -> copyManager.copyIn(sql, reader)); } } }
@Test public void canExecuteCopy() throws SQLException, IOException { int someValue = 12345; String otherValue = "foobar"; try (Connection conn = database.getConnection()) { try (Statement stmt = conn.createStatement()) { PgConnection unwrapped = conn.unwrap(PgConnection.class); stmt.execute("DROP TABLE IF EXISTS simple_table"); stmt.execute("CREATE TABLE simple_table (some_column INTEGER, other_column VARCHAR(255))"); CopyManager copyManager = unwrapped.getCopyAPI(); String sql = "COPY simple_table (some_column, other_column) FROM STDIN WITH (" + "ENCODING 'UTF-8', DELIMITER '\t', HEADER false" + ")"; String someData = someValue + "\t" + otherValue + "\n"; StringBuilder sb = new StringBuilder(someData); Reader reader = new StringBuilderReader(sb); copyManager.copyIn(sql, reader); try (ResultSet rs = stmt.executeQuery("SELECT some_column, other_column FROM simple_table")) { assertTrue(rs.next()); assertEquals(rs.getInt(1), someValue); assertEquals(rs.getString(2), otherValue); } } } }
@Test public void canInsertIntoSerialColumn() throws SQLException, IOException { try (Connection conn = database.getConnection()) { try (Statement stmt = conn.createStatement()) { PgConnection unwrapped = conn.unwrap(PgConnection.class); stmt.execute("DROP TABLE IF EXISTS simple_table"); stmt.execute("CREATE TABLE simple_table (id SERIAL, name VARCHAR(255), PRIMARY KEY (id))"); CopyManager copyManager = unwrapped.getCopyAPI(); String sql = "COPY simple_table (id, name) FROM STDIN WITH (" + "ENCODING 'UTF-8', DELIMITER '\t', HEADER false" + ")"; String row1 = "1\tJohn\n"; String row2 = "2\tSmith\n"; Reader reader = new StringBuilderReader(new StringBuilder(row1).append(row2)); copyManager.copyIn(sql, reader); try (ResultSet rs = stmt.executeQuery("SELECT id, name FROM simple_table ORDER BY id")) { assertTrue(rs.next()); assertEquals(1, rs.getInt("id")); assertEquals("John", rs.getString("name")); assertTrue(rs.next()); assertEquals(2, rs.getInt("id")); assertEquals("Smith", rs.getString("name")); } } } }
@Test public void canExecuteLoadDataWithNullValues() throws SQLException, IOException { int someValue = 12345; String otherValue = "foobar"; try (Connection conn = database.getConnection()) { try (Statement stmt = conn.createStatement()) { PgConnection unwrapped = conn.unwrap(PgConnection.class); stmt.execute("DROP TABLE IF EXISTS simple_table"); stmt.execute("CREATE TABLE simple_table (some_column INTEGER, other_column VARCHAR(255))"); CopyManager copyManager = unwrapped.getCopyAPI(); String sql = "COPY simple_table (some_column, other_column) FROM STDIN WITH (" + "ENCODING 'UTF-8', DELIMITER '\t', HEADER false" + ")"; // Empty string, without "" identify a null value String row1 = someValue + "\t\\N\n"; String row2 = "\\N\t" + otherValue + "\n"; StringBuilder sb = new StringBuilder(row1).append(row2); Reader reader = new StringBuilderReader(sb); copyManager.copyIn(sql, reader); try (ResultSet rs = stmt.executeQuery("SELECT some_column, other_column FROM simple_table")) { assertTrue(rs.next()); assertEquals(someValue, rs.getInt(1)); assertEquals(null, rs.getString(2)); assertTrue(rs.next()); assertEquals(0, rs.getInt(1)); assertTrue(rs.wasNull()); assertEquals(otherValue, rs.getString(2)); } } } }