Examples with NativeQuery org.hibernate.query.NativeQuery used on opensource projects

Example 11 with NativeQuery

use of org.hibernate.query.NativeQuery in project midpoint by Evolveum.

the class ObjectRetriever method countObjectsAttempt.

public <T extends ObjectType> int countObjectsAttempt(Class<T> type, ObjectQuery query, Collection<SelectorOptions<GetOperationOptions>> options, OperationResult result) {
    LOGGER_PERFORMANCE.debug("> count objects {}", type.getSimpleName());
    int count = 0;
    Session session = null;
    try {
        Class<? extends RObject> hqlType = ClassMapper.getHQLTypeClass(type);
        session = baseHelper.beginReadOnlyTransaction();
        Number longCount;
        query = refineAssignmentHolderQuery(type, query);
        if (query == null || query.getFilter() == null) {
            // this is 5x faster than count with 3 inner joins, it can probably improved also for queries which
            // filters uses only properties from concrete entities like RUser, RRole by improving interpreter [lazyman]
            // note: distinct can be ignored here, as there is no filter, so no joins
            NativeQuery sqlQuery = session.createNativeQuery("SELECT COUNT(*) FROM " + RUtil.getTableName(hqlType, session));
            longCount = (Number) sqlQuery.uniqueResult();
        } else {
            RQuery rQuery;
            QueryEngine engine = new QueryEngine(getConfiguration(), extItemDictionary, prismContext, relationRegistry);
            rQuery = engine.interpret(query, type, options, true, session);
            longCount = rQuery.uniqueResult();
        }
        LOGGER.trace("Found {} objects.", longCount);
        count = longCount != null ? longCount.intValue() : 0;
        session.getTransaction().commit();
    } catch (QueryException | RuntimeException ex) {
        baseHelper.handleGeneralException(ex, session, result);
    } finally {
        baseHelper.cleanupSessionAndResult(session, result);
    }
    return count;
}

Example 12 with NativeQuery

use of org.hibernate.query.NativeQuery in project midpoint by Evolveum.

the class OrgClosureManager method addIndependentEdgesInternal.

private void addIndependentEdgesInternal(List<Edge> edges, Context context, Session session) {
    checkForCycles(edges, session);
    String deltaTempTableName = computeDeltaTable(edges, context, session);
    try {
        int count;
        if (isOracle() || isSQLServer()) {
            long startUpsert = System.currentTimeMillis();
            String upsertQueryText;
            if (isSQLServer()) {
                // TODO try if this one (without prefixes in INSERT clause does not work for Oracle)
                upsertQueryText = "merge into " + CLOSURE_TABLE_NAME + " closure " + "using (select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ") delta " + "on (closure.descendant_oid = delta.descendant_oid and closure.ancestor_oid = delta.ancestor_oid) " + "when matched then update set closure.val = closure.val + delta.val " + "when not matched then insert (descendant_oid, ancestor_oid, val) " + "values (delta.descendant_oid, delta.ancestor_oid, delta.val);";
            } else {
                // Oracle
                upsertQueryText = "merge into " + CLOSURE_TABLE_NAME + " closure " + "using (select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ") delta " + "on (closure.descendant_oid = delta.descendant_oid and closure.ancestor_oid = delta.ancestor_oid) " + "when matched then update set closure.val = closure.val + delta.val " + "when not matched then insert (closure.descendant_oid, closure.ancestor_oid, closure.val) " + "values (delta.descendant_oid, delta.ancestor_oid, delta.val)";
            }
            NativeQuery<?> upsertQuery = session.createNativeQuery(upsertQueryText);
            int countUpsert = upsertQuery.executeUpdate();
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Added/updated {} records to closure table ({} ms)", countUpsert, System.currentTimeMillis() - startUpsert);
            }
            if (DUMP_TABLES) {
                dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
            }
        } else {
            // separate update and insert
            long startUpdate = System.currentTimeMillis();
            String updateInClosureQueryText;
            // Can/must be unified with PG after H2 > 1.4.200 if no other issues emerge.
            if (isH2()) {
                updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val + (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select (descendant_oid, ancestor_oid) from " + deltaTempTableName + ")";
            } else if (isPostgreSQL()) {
                updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set val = val + (select val from " + deltaTempTableName + " td " + "where td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid) " + "where (descendant_oid, ancestor_oid) in (select descendant_oid, ancestor_oid from " + deltaTempTableName + ")";
            } else {
                throw new UnsupportedOperationException("Org. closure manager - unsupported database operation");
            }
            NativeQuery updateInClosureQuery = session.createNativeQuery(updateInClosureQueryText);
            int countUpdate = updateInClosureQuery.executeUpdate();
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Updated {} records to closure table ({} ms)", countUpdate, System.currentTimeMillis() - startUpdate);
            }
            if (DUMP_TABLES) {
                dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
            }
            long startAdd = System.currentTimeMillis();
            String addQuery = "insert into " + CLOSURE_TABLE_NAME + " (descendant_oid, ancestor_oid, val) " + "select descendant_oid, ancestor_oid, val from " + deltaTempTableName + " delta ";
            if (countUpdate > 0) {
                // Can/must be unified with PG after H2 > 1.4.200 if no other issues emerge.
                if (isH2()) {
                    addQuery += " where (descendant_oid, ancestor_oid) not in (select (descendant_oid, ancestor_oid) from " + CLOSURE_TABLE_NAME + ")";
                } else if (isPostgreSQL()) {
                    addQuery += " where not exists (select 1 from " + CLOSURE_TABLE_NAME + " cl where cl.descendant_oid=delta.descendant_oid and cl.ancestor_oid=delta.ancestor_oid)";
                } else {
                    throw new UnsupportedOperationException("Org. closure manager - unsupported database operation");
                }
            }
            NativeQuery addToClosureQuery = session.createNativeQuery(addQuery);
            count = addToClosureQuery.executeUpdate();
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Added {} records to closure table ({} ms)", count, System.currentTimeMillis() - startAdd);
            }
            if (DUMP_TABLES) {
                dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
            }
        }
    } finally {
        dropDeltaTableIfNecessary(session, deltaTempTableName);
    }
}

Example 13 with NativeQuery

use of org.hibernate.query.NativeQuery in project midpoint by Evolveum.

the class OrgClosureManager method rebuild.

// endregion
// region Rebuilding or checking org closure
// we are already in the context of a transaction (and the org struct table is locked if possible)
// "check" here means "thorough check" (i.e. comparing with recomputed closure)
private void rebuild(boolean check, boolean rebuild, boolean stopOnFailure, final Context context, final Session session, OperationResult result) throws SchemaException {
    List existingEntries = null;
    if (check) {
        LOGGER.info("Reading from existing org closure table");
        NativeQuery selectQuery = session.createNativeQuery("SELECT descendant_oid, ancestor_oid, val from " + CLOSURE_TABLE_NAME).addScalar("descendant_oid", StringType.INSTANCE).addScalar("ancestor_oid", StringType.INSTANCE).addScalar("val", IntegerType.INSTANCE);
        existingEntries = selectQuery.list();
        LOGGER.info("{} entries read", existingEntries.size());
    }
    LOGGER.info("Computing org closure table from scratch");
    NativeQuery deleteQuery = session.createNativeQuery("delete from " + CLOSURE_TABLE_NAME);
    deleteQuery.executeUpdate();
    LOGGER.trace("Closure table content deleted");
    final int orgsTotal = repositoryService.countObjects(OrgType.class, null, null, result);
    final MutableInt orgsProcessed = new MutableInt(0);
    ResultHandler<OrgType> handler = new ResultHandler<OrgType>() {

        @Override
        public boolean handle(PrismObject<OrgType> object, OperationResult parentResult) {
            LOGGER.trace("Processing {}", object);
            handleAdd(object.getOid(), getParentOidsFromObject(object), context, session);
            orgsProcessed.add(1);
            int currentState = orgsProcessed.intValue();
            if (currentState % 100 == 0) {
                LOGGER.info("{} organizations processed (out of {})", currentState, orgsTotal);
            }
            return true;
        }
    };
    repositoryService.searchObjectsIterative(OrgType.class, null, handler, null, true, result);
    LOGGER.info("Org closure table was successfully recomputed (not committed yet); all {} organizations processed", orgsTotal);
    if (check) {
        LOGGER.info("Reading from recomputed org closure table");
        NativeQuery selectQuery = session.createNativeQuery("SELECT descendant_oid, ancestor_oid, val from " + CLOSURE_TABLE_NAME).addScalar("descendant_oid", StringType.INSTANCE).addScalar("ancestor_oid", StringType.INSTANCE).addScalar("val", IntegerType.INSTANCE);
        List recomputedEntries = selectQuery.list();
        LOGGER.info("{} entries read", recomputedEntries.size());
        compareOrgClosureTables(existingEntries, recomputedEntries, rebuild, result);
    } else {
        result.recordSuccess();
    }
}

Example 14 with NativeQuery

use of org.hibernate.query.NativeQuery in project midpoint by Evolveum.

the class OrgClosureManager method handleDeleteLeaf.

private void handleDeleteLeaf(String oid, Session session) {
    NativeQuery removeFromClosureQuery = session.createNativeQuery("delete from " + CLOSURE_TABLE_NAME + " " + "where descendant_oid = :oid");
    removeFromClosureQuery.setParameter("oid", oid);
    int count = removeFromClosureQuery.executeUpdate();
    if (LOGGER.isTraceEnabled()) {
        LOGGER.trace("DeleteLeaf: Removed {} records from closure table.", count);
    }
}

Example 15 with NativeQuery

use of org.hibernate.query.NativeQuery in project midpoint by Evolveum.

the class OrgClosureManager method cleanUpAfterOperation.

// may cause implicit commit!!! (in H2)
public void cleanUpAfterOperation(Context closureContext, Session session) {
    if (closureContext == null) {
        return;
    }
    if (closureContext.temporaryTableName == null) {
        return;
    }
    if (isH2()) {
        // beware, this does implicit commit!
        try {
            session.getTransaction().begin();
            NativeQuery dropQuery = session.createNativeQuery("drop table if exists " + closureContext.temporaryTableName);
            dropQuery.executeUpdate();
            closureContext.temporaryTableName = null;
        } catch (RuntimeException ex) {
            session.getTransaction().rollback();
            throw ex;
        }
    }
}