UPDATE: Thank you to @M.Deinum for informing me how to deal with the @ManyToOne cascade issue that I was previously stuck on by using EntityManager getReference or JpaRepository getOne function. I am now able to batch save with basic JpaRepsitorymethods as follows:
@Transactional
public void insertCommands(List<CommandDto> dtos) {
final List<Command> commands = new ArrayList<>();
for (CommandDto dto : dtos) {
ZonedDateTime now = ZonedDateTime.now();
final Request request = requestRepository.getOne(dto.getRequestId());
String commandId = UUID.randomUUID().toString();
final Command command = new Command();
command.setId(commandId);
command.setCreatedBy(SYSTEM);
command.setCreatedTimestamp(now);
command.setStatus(dto.getStatus());
command.setContent(dto.getContent());
command.setSendOnDate(dto.getSendOnDate());
command.setRequest(request);
commands.add(command);
}
commandRepository.saveAll(commands);
}
Original post content as seen below:
I need to insert multiple rows to my application's database using the batch_size property set in my properties:
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_size=10
I am looking for a way to insert them using nativequery in JpaRepository or CrudRepository with a syntax along the lines of the following, but allowing for multiple rows:
@Modifying
@Transactional
@Query(nativeQuery = true, value = "INSERT into command (id, created_by, created_timestamp, " +
" updated_by, updated_timestamp, status, content, send_on_date, request_id) " +
"VALUES (:id, :createdBy, :createdTimestamp, :updatedBy, :updatedTimestamp, " +
" :status, :content, :sendOnDate, :requestId) ")
int batchInsertCommandDto( @Param("commands") List<CommandDto> commandDtos);
How can I perform this sort of query with a list?
NOTE: Before you bring up EntityManger, I must note that have not had any luck with saveall functionality because:
- I am translating the data from it's JSON-friendly class (CommandDto) to its data entity class(Command)
- The class for the entity (Command) has a @ManyToOne annotation for one or more Object fields whereas the JSON object (CommandDto) simply has the id of these fields. For example, java class for command entity has "request" field:
@ManyToOne(optional = false, fetch = FetchType.LAZY, cascade = CascadeType.MERGE)
@JoinColumn(name = "request_id", foreignKey = @ForeignKey(name="fk_rcommand_request_id"))
private Request request;
Whereas CommandDto object simply has field "requestId". That means that if I simply try creating a request object with only the requestId, entityManager will fail to save because the Request object is not fully formed and therefore not recognized. It would be grossly inefficient to retrieve the Request object for each command being saved, so I am looking to do the mapping as seen in the nativequery above.