Java Heap space with jpa native Query

Question

I am having an OOME problem when trying to run a native SQL query through the jpa / hibernate EM. The treatment serves to make millions of insertion per pack of 50.

Here is code of my algorithm:

private void createNewJetonsForIndividus(boolean isGlobal, List<String> entreprises, List<String> services,
                                         String user, Timestamp dateDB) {

    LocalDateTime timer = LocalDateTime.now();

    List<Object[]> MinMaxId = getMinMaxIdDroitsIndividusActifsForCreation(
            isGlobal, entreprises, services);

    if (null != MinMaxId.get(0)[0]) {

        int idStart =  ((BigInteger) MinMaxId.get(0)[0]).intValue();
        int idEnd = idStart + PAS;
        int idMax = ((BigInteger) MinMaxId.get(0)[1]).intValue();
        int nbRowsTotal = 0;
        Logger.debug("Droits Individus : ID Min {}  -  ID Max {}", idStart, idMax);


        do {
            int finalIdStart = idStart;
            int finalIdEnd = idEnd;
            callTransaction(() -> create(false, true,isGlobal, entreprises, services, finalIdStart,
                    finalIdEnd, user, dateDB));
            idStart = idEnd + 1;
            idEnd = idEnd + PAS;
        }
        while (idMax > idEnd);

    }
}

the method is used to calculate the id min and max of the records that interest my treatment. Subsequently, I use the create method whose code is below :

int nbRowsFind;

    List<Object[]> listeDroitsIndividusActifsForCreation = getDroitsIndividusActifsForCreation(
            isGlobal, entreprises, services, idStart, idEnd);

    if (ValidationUtils.isNotEmpty(listeDroitsIndividusActifsForCreation)) {

        nbRowsFind = listeDroitsIndividusActifsForCreation.size();

        StringBuilder sbJeton = new StringBuilder();
        sbJeton.append("INSERT INTO sigs_nv_jeton VALUES ");


        StringBuilder sbDroitHasJeton = new StringBuilder();

        if (isCreateForIndiv) {
            sbDroitHasJeton.append("INSERT INTO sigs_droits_individu_has_nv_jeton VALUES ");
        }

        listeDroitsIndividusActifsForCreation.stream().forEach(object -> {
            sbJeton.append("(");
            sbDroitHasJeton.append("(");

            BigInteger idDroit = (BigInteger) object[0];

            String jetonGenerated = IdJetonGenerator.codeGenerator(idDroit.toString(), DateUtils.now());
            sbJeton.append("'").append(jetonGenerated).append("', ");

            appendDate(sbJeton, object[1]);
            appendDate(sbJeton, object[2]);
            sbJeton.append(0).append(", ");
            sbJeton.append(0).append(", ");
            sbJeton.append("'").append(dateDB).append("', ");
            sbJeton.append("'").append(user).append("'");

            sbDroitHasJeton.append(idDroit).append(",'").append(jetonGenerated).append("'");

            sbJeton.append("),");
            sbDroitHasJeton.append("),");
        });


        String requestJeton = sbJeton.toString();
        sbJeton.delete(33, sbJeton.length());
        requestJeton = requestJeton.substring(0, requestJeton.length() - 1);
        jpaApi.em().createNativeQuery(requestJeton).executeUpdate();

        String requestDroitHasJeton = sbDroitHasJeton.toString();
        sbDroitHasJeton.delete(54, sbDroitHasJeton.length());
        requestDroitHasJeton = requestDroitHasJeton.substring(0, requestDroitHasJeton.length() - 1);
        **jpaApi.em().createNativeQuery(requestDroitHasJeton).executeUpdate();**


        **jpaApi.em().flush();
        jpaApi.em().clear();**
    }

When I analyze the Heap Dump, I notice that despite the flush and clear, queries are still referenced in the SessionFactory, is this normal? enter image description here

Answer 1

flush is executing changes made in the unit of work and clear removes entities from the persistence context.

Both have nothing to do with native SQL queries. I assume that the native queries are cached anywhere else (Hibernate, JDBC...)

I would suggest that you use a prepared statement instead of your dynamic insert statement.