CODEWITHSUNDEEP
javaspringhibernatejpa
Paul Fournel
Paul Fournel

Reputation: 11207

Native List Insert query in Jpa/hibernate + Spring

I want to insert a list of Objects in my db. In a special case I know that they primary key (not auto-generated) is not already there. Since I need to insert a big collection, the save(Iterable<Obj> objects) is to slow.

Therefore I consider using a native query. native insert query in hibernate + spring data

In the previous answer, it does not say how to insert a collection of objects. Is this possible?

@Query("insert into my_table (date, feature1, feature2, quantity) VALUES <I do not know what to add here>", nativeQuery = true)
void insert(List<Obj> objs);

Of course if you have a better solution overall, Its even better.

Upvotes: 12

Views: 8075

Answers (2)

carlokid
carlokid

Reputation: 227

I know that this is from 7 years ago but posting this just in case someone needed a SQL injection safe approach using JdbcTemplate but with same speed as the accepted answer.

public class MyBatchRepository {

    private static final int INSERT_BATCH_SIZE = 20000;
    private static final String INSERT_QUERY_BASE = "INSERT INTO table_name(column1, column2, column3) VALUES ";
    private static final String INSERT_PARAM_BASE = "(?,?,?)";
    private static final String DELIMITER = ",";

    private JdbcTemplate jdbcTemplate;

    public MyBatchRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void performBatchInsert(List<Object> objects) {
        ListUtils.partition(objects, INSERT_BATCH_SIZE).forEach(this::batchInsert);
    }

    private void batchInsert(List<Object> objects) {
        String insertQuery = buildInsertQuery(objects.size());
        Object[] insertParameters = buildParameters(objects);

        jdbcTemplate.update(insertQuery, insertParameters);
    }

    protected String buildInsertQuery(int size) {
        String parameters = String.join(DELIMITER, Collections.nCopies(size, INSERT_PARAM_BASE));
        return INSERT_QUERY_BASE + parameters;
    }

    protected Object[] buildParameters(List<Object> objects) {
        return objects.stream()
                .map(this::toObjectArray)
                .flatMap(Stream::of)
                .toArray(Object[]::new);
    }

    private Object[] toObjectArray(Object object) {
        return new Object[]{
            object.column1(),
            object.column2(),
            object.column3()
        };
    }
}

Upvotes: 3

Paul Fournel
Paul Fournel

Reputation: 11207

I ended up implementing my own repository. The performance of this is really good, 2s instead of 35s before to insert 50000 elements. The problem with this code is that it does not prevent sql injections.

I also tryed to build a query using setParameter(1, ...) but somehow JPA takes a long time to do that.

class ObjectRepositoryImpl implements DemandGroupSalesOfDayCustomRepository {

    private static final int INSERT_BATCH_SIZE = 50000;

    @Autowired
    private EntityManager entityManager;

    @Override
    public void blindInsert(List<SomeObject> objects) {
         partition(objects, INSERT_BATCH_SIZE).forEach(this::insertAll);
    }

    private void insertAll(List<SomeObject> objects) {
         String values = objects.stream().map(this::renderSqlForObj).collect(joining(","));
         String insertSQL = "INSERT INTO mytable (date, feature1, feature2, quantity) VALUES ";
         entityManager.createNativeQuery(insertSQL + values).executeUpdate();
         entityManager.flush();
         entityManager.clear();
    }

    private String renderSqlForObj(Object obj) {
        return "('" + obj.getDate() + "','" +
            obj.getFeature1() + "','" +
            obj.getFeature2() + "'," +
            obj.getQuantity() + ")";
    }
}

Upvotes: 8

Related Questions