Spring data - insert data depending on previous insert

I need to save data into 2 tables (an entity and an association table). I simply save my entity with the save() method from my entity repository. Then, for performances, I need to insert rows into an association table in native sql. The rows have a reference on the entity I saved before. The issue comes here : I get an integrity constraint exception concerning a Foreign Key. The entity saved first isn't known in this second query.

Here is my code :

The repo :

public interface DistributionRepository extends JpaRepository<Distribution, Long>, QueryDslPredicateExecutor<Distribution> {

    @Modifying
    @Query(value = "INSERT INTO DISTRIBUTION_PERIMETER(DISTRIBUTION_ID, SERVICE_ID) SELECT :distId, p.id FROM PERIMETER p "
        + "WHERE p.id in (:serviceIds) AND p.discriminator = 'SRV' ", nativeQuery = true)
    void insertDistributionPerimeter(@Param(value = "distId") Long distributionId, @Param(value = "serviceIds") Set<Long> servicesIds);
}

The service :

@Service
public class DistributionServiceImpl implements IDistributionService {

    @Inject
    private DistributionRepository distributionRepository;

    @Override
    @Transactional
    public DistributionResource distribute(final DistributionResource distribution) {

        // 1. Entity creation and saving
        Distribution created = new Distribution();
        final Date distributionDate = new Date();
        created.setStatus(EnumDistributionStatus.distributing);
        created.setDistributionDate(distributionDate);
        created.setDistributor(agentRepository.findOne(distribution.getDistributor().getMatricule()));
        created.setDocument(documentRepository.findOne(distribution.getDocument().getTechId()));
        created.setEntity(entityRepository.findOne(distribution.getEntity().getTechId()));
        created = distributionRepository.save(created);

        // 2. Association table 
        final Set<Long> serviceIds = new HashSet<Long>();
        for (final ServiceResource sr : distribution.getServices()) {
            serviceIds.add(sr.getTechId());
        }

        // EXCEPTION HERE
        distributionRepository.insertDistributionPerimeter(created.getId(), serviceIds);
    }
}

The 2 queries seem to be in different transactions whereas I set the @Transactionnal annotation. I also tried to execute my second query with an entityManager.createNativeQuery() and got the same result...

Solution

Invoke entityManager.flush() before you execute your native queries or use saveAndFlush instead.

I your specific case I would recommend to use

created = distributionRepository.saveAndFlush(created);

Important: your "native" queries must use the same transaction! (or you need a now transaction isolation level)

you also wrote:

I don't really understand why the flush action is not done by default

Flushing is handled by Hibernate (it can been configured, default is "auto"). This mean that hibernate will flush the data at any point in time. But always before you commit the transaction or execute an other SQL statement VIA HIBERNATE. - So normally this is no problem, but in your case, you bypass hibernate with your native query, so hibernate will not know about this statement and therefore it will not flush its data.

See also this answer of mine: https://stackoverflow.com/a/17889017/280244 about this topic