bulk insert from Java into Oracle

Question

I need to insert many small rows rapidly into Oracle. (5 fields).

With MySQL, I break the inserts into groups of 100, then use one insert statement for every group of 100 inserts.

But with Oracle, user feedback is that the mass inserts (anywhere from 1000-30000) are too slow.

Is there a similar trick I can use to speed up the programmatic inserts from Java into Oracle?

Answer 1

You can use Spring's DAO module to batch insert many rows.

An example that inserts a collection of Order objects into the database in one update:

public class OrderRepositoryImpl extends SimpleJdbcDaoSupport implements
        OrderRepository {

    private final String saveSql = "INSERT INTO orders(userid, username, coffee, coffeename, amount) "
            + "VALUES(?, ?, ?, ?, ?)";

    public void saveOrders(final Collection<Order> orders) {
        List<Object[]> ordersArgumentList = new ArrayList<Object[]>(orders
                .size());

        Object[] orderArguments;
        for (Order order : orders) {
            orderArguments = new Object[] { order.getUserId(),
                    order.getUserName(), order.getCoffe(),
                    order.getCoffeeName(), order.getAmount() };

            ordersArgumentList.add(orderArguments);
        }

        getSimpleJdbcTemplate().batchUpdate(saveSql, ordersArgumentList);
    }
}