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

Example 26 with NativeQuery

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

the class OrgClosureManager method removeIndependentEdgesInternal.

private void removeIndependentEdgesInternal(List<Edge> edges, Context context, Session session) {
    String deltaTempTableName = computeDeltaTable(edges, context, session);
    try {
        int count;
        String deleteFromClosureQueryText, updateInClosureQueryText;
        // Can/must be unified with PG after H2 > 1.4.200 if no other issues emerge.
        if (isH2()) {
            // delete with join is not supported by H2
            // and the "postgresql/oracle version" does not work for some reasons
            deleteFromClosureQueryText = "delete from " + CLOSURE_TABLE_NAME + " cl " + "where exists (" + "select 0 from " + deltaTempTableName + " delta " + "where cl.descendant_oid = delta.descendant_oid and cl.ancestor_oid = delta.ancestor_oid and cl.val = delta.val)";
            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() || isOracle()) {
            deleteFromClosureQueryText = "delete from " + CLOSURE_TABLE_NAME + " " + "where (descendant_oid, ancestor_oid, val) in " + "(select descendant_oid, ancestor_oid, val from " + deltaTempTableName + ")";
            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 (isSQLServer()) {
            deleteFromClosureQueryText = "delete " + CLOSURE_TABLE_NAME + " from " + CLOSURE_TABLE_NAME + " " + "inner join " + deltaTempTableName + " td on " + "td.descendant_oid = " + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid = " + CLOSURE_TABLE_NAME + ".ancestor_oid and " + "td.val = " + CLOSURE_TABLE_NAME + ".val";
            updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME + " " + "set " + CLOSURE_TABLE_NAME + ".val = " + CLOSURE_TABLE_NAME + ".val - td.val " + "from " + CLOSURE_TABLE_NAME + " " + "inner join " + deltaTempTableName + " td " + "on td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and " + "td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid";
        } else {
            throw new UnsupportedOperationException("Org. closure manager - unsupported database operation");
        }
        long startDelete = System.currentTimeMillis();
        NativeQuery deleteFromClosureQuery = session.createNativeQuery(deleteFromClosureQueryText);
        count = deleteFromClosureQuery.executeUpdate();
        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Deleted {} records from closure table in {} ms", count, System.currentTimeMillis() - startDelete);
        }
        if (DUMP_TABLES) {
            dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
        }
        long startUpdate = System.currentTimeMillis();
        NativeQuery updateInClosureQuery = session.createNativeQuery(updateInClosureQueryText);
        count = updateInClosureQuery.executeUpdate();
        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Updated {} records in closure table in {} ms", count, System.currentTimeMillis() - startUpdate);
        }
        if (DUMP_TABLES) {
            dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
        }
    } finally {
        dropDeltaTableIfNecessary(session, deltaTempTableName);
    }
}

Example 27 with NativeQuery

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

the class OrgClosureManager method quickCheck.

private int quickCheck(Session session) {
    NativeQuery q = session.createNativeQuery("select count(m_org.oid) as problems from m_org left join m_org_closure cl " + "on cl.descendant_oid = m_org.oid and cl.ancestor_oid = m_org.oid " + "where cl.descendant_oid is null").addScalar("problems", IntegerType.INSTANCE);
    List problemsList = q.list();
    if (problemsList == null || problemsList.size() != 1) {
        throw new IllegalStateException("Unexpected return value from the closure check query: " + problemsList + " (a 1-item list of Integer expected)");
    }
    return (int) problemsList.get(0);
}