void insertAll(Connection conn, Stream<T> stream) throws JFleetException, SQLException;
public static void main(String[] args) throws JFleetException, IOException, SQLException { MySqlTestConnectionProvider connectionSuplier = new MySqlTestConnectionProvider(); try (Connection connection = connectionSuplier.get()) { createTable(connection); List<Stream<EmissionTrade>> emissions = new ArrayList<>(); Path path = Paths.get("/tmp/eu-ets.csv"); for (int i = 0; i < 10; i++) { emissions.add(Files.lines(path).skip(1).map(ConfigurationSample::parse)); } LoadDataConfiguration config = from(EmissionTrade.class) .batchSize(1024*1024) .autocommit(false) .build(); BulkInsert<EmissionTrade> bulkInsert = new LoadDataBulkInsert<>(config); bulkInsert.insertAll(connection, emissions.stream().flatMap(i -> i)); } }
public static void main(String[] args) throws IOException, SQLException { Supplier<Connection> connectionSuplier = new MySqlTestConnectionProvider(); try (Connection connection = connectionSuplier.get()) { TableHelper.createTable(connection); CitiBikeReader<TripFlatEntity> reader = new CitiBikeReader<>("/tmp", str -> new FlatTripParser(str)); BulkInsert<TripFlatEntity> bulkInsert = new LoadDataBulkInsert<>(TripFlatEntity.class); reader.forEachCsvInZip(tripsStream -> { try { List<TripFlatEntity> trips = tripsStream.collect(toList()); bulkInsert.insertAll(connection, trips); } catch (Exception e) { e.printStackTrace(); } }); } } }
public static void main(String[] args) throws IOException, SQLException { Supplier<Connection> connectionSuplier = new MySqlTestConnectionProvider(); try (Connection connection = connectionSuplier.get()) { TableHelper.createTable(connection); CitiBikeReader<TripEntity> reader = new CitiBikeReader<>("/tmp", str -> new TripEntityParser(str)); BulkInsert<TripEntity> bulkInsert = new LoadDataBulkInsert<>(TripEntity.class); reader.forEachCsvInZip(trips -> { try { bulkInsert.insertAll(connection, trips); } catch (JFleetException | SQLException e) { e.printStackTrace(); } }); } }
public static void main(String[] args) throws IOException, SQLException { Supplier<Connection> connectionSuplier = new MySqlTestConnectionProvider(); try (Connection connection = connectionSuplier.get()) { TableHelper.createTable(connection); CitiBikeReader<TripFlatEntity> reader = new CitiBikeReader<>("/tmp", str -> new FlatTripParser(str)); BulkInsert<TripFlatEntity> bulkInsert = new LoadDataBulkInsert<>(TripFlatEntity.class); reader.forEachCsvInZip((Stream<TripFlatEntity> trips) -> { try { bulkInsert.insertAll(connection, trips); } catch (JFleetException | SQLException e) { e.printStackTrace(); } }); } } }
public static void main(String[] args) throws IOException, SQLException { Supplier<Connection> connectionSuplier = new MySqlTestConnectionProvider(); try (Connection connection = connectionSuplier.get()){ TableHelper.createTable(connection); CitiBikeReader<TripFlatEntity> reader = new CitiBikeReader<>("/tmp", str -> new FlatTripParser(str)); JdbcConfiguration config = JdbcConfigurationBuilder.from(TripFlatEntity.class) .batchSize(100).build(); BulkInsert<TripFlatEntity> bulkInsert = new JdbcBulkInsert<>(config); reader.forEachCsvInZip(trips -> { try { bulkInsert.insertAll(connection, trips); } catch (JFleetException | SQLException e) { e.printStackTrace(); } }); } } }
@Test public void canPersistWithReservedWords() throws Exception { int times = 1000; BulkInsert<ReservedWordEntity> insert = new LoadDataBulkInsert<>(ReservedWordEntity.class); Stream<ReservedWordEntity> stream = IntStream.range(0, times) .mapToObj(i -> new ReservedWordEntity(i, "current_user_" + i)); try (Connection conn = new MySqlDatabase().getConnection()) { SqlUtil.createTableForEntity(conn, ReservedWordEntity.class); insert.insertAll(conn, stream); try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT id, `current_user` FROM `select` ORDER BY id ASC")) { for (int i = 0; i < times; i++) { assertTrue(rs.next()); assertEquals("current_user_" + i, rs.getString("current_user")); } } } } }
public void testWithString(Database database, String text) throws Exception { EnityWithStrings entity = new EnityWithStrings(); entity.setFoo("Some text"); entity.setBar(text); BulkInsert<EnityWithStrings> insert = database.getBulkInsert(EnityWithStrings.class); try (Connection conn = database.getConnection()) { SqlUtil.createTableForEntity(conn, EnityWithStrings.class); insert.insertAll(conn, Stream.of(entity)); try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT foo, bar FROM table_with_strings")) { assertTrue(rs.next()); assertEquals("Some text", rs.getString("foo")); assertEquals(text, rs.getString("bar")); } } } }
@TestAllDBs public void persistAllTypes(@WithDB Database database) throws Exception { List<EncodingUtf8Entity> values = fetchTestValues(); BulkInsert<EncodingUtf8Entity> insert = database.getBulkInsert(EncodingUtf8Entity.class); try (Connection conn = database.getConnection()) { SqlUtil.createTableForEntity(conn, EncodingUtf8Entity.class); insert.insertAll(conn, values); try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT * FROM table_utf8_encoding ORDER BY lang ASC")) { for (int i = 0; i < values.size(); i++) { EncodingUtf8Entity expected = values.get(i); assertTrue(rs.next()); assertEquals(expected.getLang(), rs.getString("lang")); assertEquals(expected.getText(), rs.getString("text")); } } } } }
@TestAllDBs public void canPersistWithAssignedId(@WithDB Database database) throws Exception { int times = 1000; BulkInsert<EntityWithAssignedId> insert = database.getBulkInsert(EntityWithAssignedId.class); Stream<EntityWithAssignedId> stream = LongStream.range(0, times) .mapToObj(i -> new EntityWithAssignedId(i, "name_" + i)); try (Connection conn = database.getConnection()) { SqlUtil.createTableForEntity(conn, EntityWithAssignedId.class); insert.insertAll(conn, stream); try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT id, name FROM simple_table ORDER BY id ASC")) { for (int i = 0; i < times; i++) { assertTrue(rs.next()); assertEquals(i, rs.getLong("id")); assertEquals("name_" + i, rs.getString("name")); } } } } }
@TestAllDBs public void canPersistWithIdentityId(@WithDB Database database) throws Exception { int times = 1000; BulkInsert<EntityWithIdentityId> insert = database.getBulkInsert(EntityWithIdentityId.class); Stream<EntityWithIdentityId> stream = LongStream.range(0, times) .mapToObj(i -> new EntityWithIdentityId(null, "name_" + i)); try (Connection conn = database.getConnection()) { SqlUtil.createTableForEntity(conn, EntityWithIdentityId.class); insert.insertAll(conn, stream); try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT id, name FROM simple_table ORDER BY id ASC")) { for (int i = 0; i < times; i++) { assertTrue(rs.next()); assertNotNull(rs.getLong("id")); assertEquals("name_" + i, rs.getString("name")); } } } } } }
@Test public void inLongTransactionWithDuplicatedIdCanBeRollbacked() throws Exception { try (Connection connection = database.getConnection()) { connection.setAutoCommit(false); LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(false) .errorOnMissingRow(true) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); try { bulkInsert.insertAll(connection, employeesWithUniqueError()); connection.commit(); } catch (JFleetException e) { connection.rollback(); } assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void inLongTransactionWithMissedForeignKeyCanBeSkipped() throws Exception { try (Connection connection = database.getConnection()) { connection.setAutoCommit(false); LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(false) .errorOnMissingRow(false) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); bulkInsert.insertAll(connection, employeesWithForeignKeyError()); assertEquals(6, numberOfRowsInEmployeeTable(connection)); connection.rollback(); assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void inLongTransactionWithDuplicatedIdCanBeSkipped() throws Exception { try (Connection connection = database.getConnection()) { connection.setAutoCommit(false); LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(false) .errorOnMissingRow(false) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); bulkInsert.insertAll(connection, employeesWithUniqueError()); assertEquals(4, numberOfRowsInEmployeeTable(connection)); connection.rollback(); assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void longTransactionExecuteMultipleLoadDataOperationsTransactionaly() throws Exception { try (Connection connection = database.getConnection()) { connection.setAutoCommit(false); PgCopyConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(false) .build(); BulkInsert<Employee> bulkInsert = new PgCopyBulkInsert<>(config); bulkInsert.insertAll(connection, employeesWithOutErrors()); // We don't know how many load data operations were executed, but with // low batch size, multiple load data are executed with few records. assertEquals(7, numberOfRowsInEmployeeTable(connection)); connection.rollback(); assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void longTransactionExecuteMultipleLoadDataOperationsTransactionaly() throws Exception { try (Connection connection = database.getConnection()) { connection.setAutoCommit(false); LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(false) .errorOnMissingRow(true) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); bulkInsert.insertAll(connection, employeesWithOutErrors()); // We don't know how many load data operations were executed, but with // low batch size, multiple load data are executed with few records. assertEquals(7, numberOfRowsInEmployeeTable(connection)); connection.rollback(); assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void multipleBatchOperationsExecuteMultipleLoadDataOperationsWithHisOwnTransaction() throws Exception { try (Connection connection = database.getConnection()) { PgCopyConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(true) .build(); BulkInsert<Employee> bulkInsert = new PgCopyBulkInsert<>(config); try { bulkInsert.insertAll(connection, employeesWithConstraintError()); } catch (SQLException e) { logger.info("Expected error on missed FK"); assertTrue(numberOfRowsInEmployeeTable(connection) > 0); return; } assertTrue(false, "Expected JFleetException exception"); } }
@TestDBs @ValueSource(strings = { "JdbcMySql", "JdbcPosgres" }) public void longTransactionExecuteMultipleLoadDataOperationsTransactionaly(@WithDB JdbcDatabase database) throws Exception { try (Connection connection = database.getConnection()) { setupDatabase(connection); connection.setAutoCommit(false); JdbcConfiguration config = JdbcConfigurationBuilder.from(Employee.class) .batchSize(TWO_ROW_BATCH_SIZE) .autocommit(false) .build(); BulkInsert<Employee> bulkInsert = database.getBulkInsert(config); bulkInsert.insertAll(connection, employeesWithOutErrors()); // We don't know how many load data operations were executed, but with // low batch size, multiple load data are executed with few records. assertEquals(7, numberOfRowsInEmployeeTable(connection)); connection.rollback(); assertEquals(0, numberOfRowsInEmployeeTable(connection)); } }
@Test public void multipleBatchOperationsExecuteMultipleLoadDataOperationsWithHisOwnTransaction() throws Exception { try (Connection connection = database.getConnection()) { LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(true) .errorOnMissingRow(true) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); try { bulkInsert.insertAll(connection, employeesWithForeignKeyError()); } catch (JFleetException e) { logger.info("Expected error on missed FK"); assertTrue(numberOfRowsInEmployeeTable(connection) > 0); return; } assertTrue(false, "Expected JFleetException exception"); } }
@Test public void multipleBatchOperationsCanMissRows() throws Exception { try (Connection connection = database.getConnection()) { LoadDataConfiguration config = from(Employee.class) .batchSize(VERY_LOW_SIZE_TO_FREQUENT_LOAD_DATA) .autocommit(true) .errorOnMissingRow(false) .build(); BulkInsert<Employee> bulkInsert = new LoadDataBulkInsert<>(config); bulkInsert.insertAll(connection, employeesWithMultipleConstraintsErrors()); assertEquals(6, numberOfRowsInEmployeeTable(connection)); } }