Java 类org.hibernate.query.NativeQuery 实例源码

项目：flow-platform 文件：JobDaoImpl.java

@Override
public List<Job> latestByPath(List<String> paths) {
    return execute((Session session) -> {
        final StringBuilder query = new StringBuilder(JOB_QUERY);

        if (!CollectionUtil.isNullOrEmpty(paths)) {
            query.append(" where id in (select max(id) from job where node_path in (:paths) group by node_path)");
        } else {
            query.append(" where id in (select max(id) from job group by node_path)");
        }

        NativeQuery nativeQuery = session
            .createNativeQuery(query.toString())
            .setResultSetMapping("MappingJobResult");

        if (!CollectionUtil.isNullOrEmpty(paths)) {
            nativeQuery.setParameterList("paths", paths);
        }

        List<Object[]> objects = nativeQuery.list();
        return JobConvertUtil.convert(objects);
    });
}

项目：flow-platform 文件：JobDaoImpl.java

@Override
public List<Job> listByPath(final List<String> paths) {
    return execute((Session session) -> {
        final StringBuilder query = new StringBuilder(JOB_QUERY);

        if (!CollectionUtil.isNullOrEmpty(paths)) {
            query.append(" where job.node_path in (:paths) order by job.created_at desc ");
        }

        NativeQuery nativeQuery = session.createNativeQuery(query.toString())
            .setResultSetMapping("MappingJobResult");

        if (!CollectionUtil.isNullOrEmpty(paths)) {
            nativeQuery.setParameterList("paths", paths);
        }

        List<Object[]> objects = nativeQuery.list();
        return JobConvertUtil.convert(objects);
    });
}

项目：midpoint 文件：OrgClosureManager.java

private Context onBeginTransaction(Session session) {
    // table locking
    if (isH2() || isOracle() || isSQLServer()) {
        lockClosureTable(session);
    }
    // other
    Context ctx = new Context();
    if (isH2()) {
        ctx.temporaryTableName = generateDeltaTempTableName();
        String createTableQueryText = "create temporary table " + ctx.temporaryTableName + " (\n" +
                "  descendant_oid VARCHAR(36) NOT NULL,\n" +
                "  ancestor_oid   VARCHAR(36) NOT NULL,\n" +
                "  val            INTEGER     NOT NULL,\n" +
                "  PRIMARY KEY (descendant_oid, ancestor_oid)\n" +
                ")";
        long start = System.currentTimeMillis();
        NativeQuery q = session.createNativeQuery(createTableQueryText);
        q.executeUpdate();
        LOGGER.trace("Temporary table {} created in {} ms", ctx.temporaryTableName, System.currentTimeMillis()-start);
    }
    return ctx;
}

项目：midpoint 文件：OrgClosureManager.java

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;
        }
    }
}

项目：midpoint 文件：OrgClosureManager.java

private void addEdgeSimple(String oid, String parent, Session session) {
    if (parent != null) {
        long start = System.currentTimeMillis();
        NativeQuery addToClosureQuery = session.createNativeQuery(
                "insert into "+ CLOSURE_TABLE_NAME +" (descendant_oid, ancestor_oid, val) " +
                        "select :oid as descendant_oid, CL.ancestor_oid as ancestor_oid, CL.val as val " +
                        "from "+ CLOSURE_TABLE_NAME +" CL " +
                        "where CL.descendant_oid = :parent");
        addToClosureQuery.setParameter("oid", oid);
        addToClosureQuery.setParameter("parent", parent);
        int count = addToClosureQuery.executeUpdate();
        if (LOGGER.isTraceEnabled())
            LOGGER.trace("addEdges simplified: Added {} records to closure table ({} ms).", count,
                    System.currentTimeMillis() - start);
    }
    session.flush();
    session.clear();
}

项目：midpoint 文件：OrgClosureManager.java

private void handleDelete(String oid, Context context, Session session) {

        List<String> livingChildren = getChildren(oid, session);
        if (livingChildren.isEmpty()) {
            handleDeleteLeaf(oid, session);
            return;
        }

        // delete all edges "<child> -> OID" from the closure
        removeChildrenEdges(oid, livingChildren, context, session);
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'child' links.", livingChildren.size());

        // delete all edges "OID -> <parent>" from the closure
        List<String> livingParents = retainExistingOids(getParents(oid, session), session);
        removeParentEdges(oid, livingParents, context, session);
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'parent' links.", livingParents.size());

        // delete (OID, OID) record
        NativeQuery deleteSelfQuery = session.createNativeQuery("delete from "+ CLOSURE_TABLE_NAME +" " +
                "where descendant_oid=:oid and ancestor_oid=:oid");
        deleteSelfQuery.setParameter("oid", oid);
        int count = deleteSelfQuery.executeUpdate();
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Removed {} self-record from closure table.", count);
    }

项目：midpoint 文件：OrgClosureManager.java

private void initializeOracleTemporaryTable() {
    Session session = baseHelper.getSessionFactory().openSession();
    NativeQuery qCheck = session.createNativeQuery("select table_name from user_tables where table_name = upper('" + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + "')");
    if (qCheck.list().isEmpty()) {
        LOGGER.info("Creating temporary table {}", TEMP_DELTA_TABLE_NAME_FOR_ORACLE);
        session.beginTransaction();
        NativeQuery qCreate = session.createNativeQuery("CREATE GLOBAL TEMPORARY TABLE " + TEMP_DELTA_TABLE_NAME_FOR_ORACLE +
                "    (descendant_oid VARCHAR2(36 CHAR), " +
                "     ancestor_oid VARCHAR2(36 CHAR), " +
                "     val NUMBER (10, 0), " +
                "     PRIMARY KEY (descendant_oid, ancestor_oid)) " +
                "  ON COMMIT DELETE ROWS");
        try {
            qCreate.executeUpdate();
            session.getTransaction().commit();
        } catch (RuntimeException e) {
            String m = "Couldn't create temporary table " + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + ". Please create the table manually.";
            LoggingUtils.logException(LOGGER, m, e);
            throw new SystemException(m, e);
        }
    }
    session.close();
}

项目：midpoint 文件：OrgClosureManager.java

private Context onBeginTransaction(Session session) {
    // table locking
    if (isH2() || isOracle() || isSQLServer()) {
        lockClosureTable(session);
    }
    // other
    Context ctx = new Context();
    if (isH2()) {
        ctx.temporaryTableName = generateDeltaTempTableName();
        String createTableQueryText = "create temporary table " + ctx.temporaryTableName + " (\n" +
                "  descendant_oid VARCHAR(36) NOT NULL,\n" +
                "  ancestor_oid   VARCHAR(36) NOT NULL,\n" +
                "  val            INTEGER     NOT NULL,\n" +
                "  PRIMARY KEY (descendant_oid, ancestor_oid)\n" +
                ")";
        long start = System.currentTimeMillis();
        NativeQuery q = session.createNativeQuery(createTableQueryText);
        q.executeUpdate();
        LOGGER.trace("Temporary table {} created in {} ms", ctx.temporaryTableName, System.currentTimeMillis()-start);
    }
    return ctx;
}

项目：midpoint 文件：OrgClosureManager.java

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;
        }
    }
}

项目：midpoint 文件：OrgClosureManager.java

private void addEdgeSimple(String oid, String parent, Session session) {
    if (parent != null) {
        long start = System.currentTimeMillis();
        NativeQuery addToClosureQuery = session.createNativeQuery(
                "insert into "+ CLOSURE_TABLE_NAME +" (descendant_oid, ancestor_oid, val) " +
                        "select :oid as descendant_oid, CL.ancestor_oid as ancestor_oid, CL.val as val " +
                        "from "+ CLOSURE_TABLE_NAME +" CL " +
                        "where CL.descendant_oid = :parent");
        addToClosureQuery.setParameter("oid", oid);
        addToClosureQuery.setParameter("parent", parent);
        int count = addToClosureQuery.executeUpdate();
        if (LOGGER.isTraceEnabled())
            LOGGER.trace("addEdges simplified: Added {} records to closure table ({} ms).", count,
                    System.currentTimeMillis() - start);
    }
    session.flush();
    session.clear();
}

项目：midpoint 文件：OrgClosureManager.java

private void handleDelete(String oid, Context context, Session session) {

        List<String> livingChildren = getChildren(oid, session);
        if (livingChildren.isEmpty()) {
            handleDeleteLeaf(oid, session);
            return;
        }

        // delete all edges "<child> -> OID" from the closure
        removeChildrenEdges(oid, livingChildren, context, session);
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'child' links.", livingChildren.size());

        // delete all edges "OID -> <parent>" from the closure
        List<String> livingParents = retainExistingOids(getParents(oid, session), session);
        removeParentEdges(oid, livingParents, context, session);
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Deleted {} 'parent' links.", livingParents.size());

        // delete (OID, OID) record
        NativeQuery deleteSelfQuery = session.createNativeQuery("delete from "+ CLOSURE_TABLE_NAME +" " +
                "where descendant_oid=:oid and ancestor_oid=:oid");
        deleteSelfQuery.setParameter("oid", oid);
        int count = deleteSelfQuery.executeUpdate();
        if (LOGGER.isTraceEnabled()) LOGGER.trace("Removed {} self-record from closure table.", count);
    }

项目：midpoint 文件：OrgClosureManager.java

private void initializeOracleTemporaryTable() {
    Session session = baseHelper.getSessionFactory().openSession();
    NativeQuery qCheck = session.createNativeQuery("select table_name from user_tables where table_name = upper('" + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + "')");
    if (qCheck.list().isEmpty()) {
        LOGGER.info("Creating temporary table {}", TEMP_DELTA_TABLE_NAME_FOR_ORACLE);
        session.beginTransaction();
        NativeQuery qCreate = session.createNativeQuery("CREATE GLOBAL TEMPORARY TABLE " + TEMP_DELTA_TABLE_NAME_FOR_ORACLE +
                "    (descendant_oid VARCHAR2(36 CHAR), " +
                "     ancestor_oid VARCHAR2(36 CHAR), " +
                "     val NUMBER (10, 0), " +
                "     PRIMARY KEY (descendant_oid, ancestor_oid)) " +
                "  ON COMMIT DELETE ROWS");
        try {
            qCreate.executeUpdate();
            session.getTransaction().commit();
        } catch (RuntimeException e) {
            String m = "Couldn't create temporary table " + TEMP_DELTA_TABLE_NAME_FOR_ORACLE + ". Please create the table manually.";
            LoggingUtils.logException(LOGGER, m, e);
            throw new SystemException(m, e);
        }
    }
    session.close();
}

项目：flow-platform 文件：JobDaoImpl.java

@Override
public List<Job> list() {
    return execute((Session session) -> {
        NativeQuery nativeQuery = session.createNativeQuery(JOB_QUERY).setResultSetMapping("MappingJobResult");
        List<Object[]> objects = nativeQuery.list();
        return JobConvertUtil.convert(objects);
    });
}

项目：flow-platform 文件：JobDaoImpl.java

@Override
public List<Job> list(List<String> sessionIds, NodeStatus nodeStatus) {
    return execute((Session session) -> {
        NativeQuery nativeQuery = session.createNativeQuery(
            JOB_QUERY + " WHERE job.session_id IN (:sessionIds) AND nr.node_status=:status")
            .setParameter("sessionIds", sessionIds)
            .setParameter("status", nodeStatus.getName())
            .setResultSetMapping("MappingJobResult");

        List<Object[]> objects = nativeQuery.list();
        return JobConvertUtil.convert(objects);
    });
}

项目：flow-platform 文件：JobDaoImpl.java

@Override
public Job get(BigInteger key) {
    return execute((Session session) -> {
        NativeQuery nativeQuery = session.createNativeQuery(JOB_QUERY + " WHERE job.id=:id")
            .setParameter("id", key)
            .setResultSetMapping("MappingJobResult");

        Object[] objects = (Object[]) nativeQuery.uniqueResult();
        return JobConvertUtil.convert(objects);
    });
}

项目：flow-platform 文件：JobDaoImpl.java

@Override
public Job get(String path, Long number) {
    return execute((Session session) -> {
        NativeQuery nativeQuery = session.createNativeQuery(
            JOB_QUERY + " WHERE job.node_path=:name AND job.build_number=:build_number")
            .setParameter("name", path)
            .setParameter("build_number", number)
            .setResultSetMapping("MappingJobResult");
        Object[] objects = (Object[]) nativeQuery.uniqueResult();
        return JobConvertUtil.convert(objects);
    });
}

项目：jdblender 文件：ModelsDao.java

public void link2SpareFast(long modelId, long spareId) {
    try (Session s = sf.openSession()) {
        NativeQuery nq = s.createNativeQuery("INSERT INTO spare_to_model (spare_id, model_id) VALUES (?, ?)");
        nq.setParameter(1, spareId);
        nq.setParameter(2, modelId);
        Transaction tx = s.beginTransaction();
        nq.executeUpdate();
        tx.commit();
    }
}

项目：dhis2-core 文件：HibernateGenericStore.java

/**
 * Creates a SqlQuery.
 *
 * @param sql the sql query.
 * @return a SqlQuery instance.
 */
protected final NativeQuery getSqlQuery( String sql )
{
    NativeQuery query = getSession().createNativeQuery( sql );
    query.setHint( HibernateUtils.HIBERNATE_CACHEABLE_HINT, cacheable );
    return query;
}

项目：high-performance-java-persistence 文件：FetchEntitiesWithPaginationTest.java

@Test
public void testFetchAndPaginateUsingDenseRank() {
    List<Post> _posts = doInJPA(entityManager -> {
        String titlePattern = "High-Performance%";
        int maxResults = 3;
        List<Post> posts = entityManager.createNativeQuery(
            "select * " +
            "from (   " +
            "    select *, dense_rank() OVER (ORDER BY post_id) rank " +
            "    from (   " +
            "        select p.*, pc.* " +
            "        from post p  " +
            "        left join post_comment pc on p.id = pc.post_id  " +
            "        where p.title like :title " +
            "        order by p.id " +
            "    ) p_pc " +
            ") p_pc_r " +
            "where p_pc_r.rank <= :rank", Post.class)
        .setParameter("title", titlePattern)
        .setParameter("rank", maxResults)
        .unwrap( NativeQuery.class )
        .addEntity( "p", Post.class )
        .addEntity( "pc", PostComment.class )
        .setResultTransformer( DistinctPostResultTransformer.INSTANCE )
        .getResultList();

        assertEquals(maxResults, posts.size());
        return posts;
    });
    assertEquals(2, _posts.get(0).comments.size());
}

项目：midpoint 文件：ObjectUpdater.java

private <T extends ObjectType> String nonOverwriteAddObjectAttempt(PrismObject<T> object, RObject rObject,
                                                                   String originalOid, Session session, OrgClosureManager.Context closureContext)
        throws ObjectAlreadyExistsException, SchemaException, DtoTranslationException {

    // check name uniqueness (by type)
    if (StringUtils.isNotEmpty(originalOid)) {
        LOGGER.trace("Checking oid uniqueness.");
        //todo improve this table name bullshit
        Class hqlType = ClassMapper.getHQLTypeClass(object.getCompileTimeClass());
        NativeQuery query = session.createNativeQuery("select count(*) from "
                + RUtil.getTableName(hqlType, session) + " where oid=:oid");
        query.setParameter("oid", object.getOid());

        Number count = (Number) query.uniqueResult();
        if (count != null && count.longValue() > 0) {
            throw new ObjectAlreadyExistsException("Object '" + object.getCompileTimeClass().getSimpleName()
                    + "' with oid '" + object.getOid() + "' already exists.");
        }
    }

    updateFullObject(rObject, object);

    LOGGER.trace("Saving object (non overwrite).");
    String oid = (String) session.save(rObject);
    lookupTableHelper.addLookupTableRows(session, rObject, false);
    caseHelper.addCertificationCampaignCases(session, rObject, false);

    if (closureManager.isEnabled()) {
        Collection<ReferenceDelta> modifications = createAddParentRefDelta(object);
        closureManager.updateOrgClosure(null, modifications, session, oid, object.getCompileTimeClass(),
                OrgClosureManager.Operation.ADD, closureContext);
    }

    return oid;
}

项目：midpoint 文件：ObjectRetriever.java

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;
         if (query == null || query.getFilter() == null) {
            if (GetOperationOptions.isDistinct(SelectorOptions.findRootOptions(options))) {
                throw new UnsupportedOperationException("Distinct option is not supported here");   // TODO
    }
             // 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]
             NativeQuery sqlQuery = session.createNativeQuery("SELECT COUNT(*) FROM " + RUtil.getTableName(hqlType, session));
             longCount = (Number) sqlQuery.uniqueResult();
         } else {
             RQuery rQuery;
    QueryEngine2 engine = new QueryEngine2(getConfiguration(), prismContext);
    rQuery = engine.interpret(query, type, options, true, session);

             longCount = (Number) 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;
 }

项目：midpoint 文件：OrgClosureManager.java

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);
}

项目：midpoint 文件：OrgClosureManager.java

private void checkForCycles(List<Edge> edges, Session session) {
    String queryText = "select descendant_oid, ancestor_oid from " + CLOSURE_TABLE_NAME + " where " + getWhereClauseForCycleCheck(edges);
    NativeQuery query = session.createNativeQuery(queryText)
            .addScalar("descendant_oid", StringType.INSTANCE)
            .addScalar("ancestor_oid", StringType.INSTANCE);
    long start = System.currentTimeMillis();
    List list = query.list();
    LOGGER.trace("Cycles checked in {} ms, {} conflicts found", System.currentTimeMillis()-start, list.size());
    if (!list.isEmpty()) {
        throw new IllegalArgumentException("Modification couldn't be executed, because a cycle in org structure graph would be created. Cycle-creating edges being added: " + formatList(list));
    }
}

项目：midpoint 文件：OrgClosureManager.java

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);
}

项目：midpoint 文件：OrgClosureManager.java

private void dumpOrgClosureTypeTable(Session session, String tableName) {
    NativeQuery q = session.createNativeQuery("select descendant_oid, ancestor_oid, val from " + tableName)
            .addScalar("descendant_oid", StringType.INSTANCE)
            .addScalar("ancestor_oid", StringType.INSTANCE)
            .addScalar("val", IntegerType.INSTANCE);
    List<Object[]> list = q.list();
    LOGGER.trace("{} ({} rows):", tableName, list.size());
    for (Object[] row : list) {
        LOGGER.trace(" - [d={}, a={}, val={}]", row);
    }
}

项目：midpoint 文件：SqlAuditServiceImpl.java

private int selectRecordsByMaxAge(Session session, String tempTable, Date minValue, Dialect dialect) {

        // fill temporary table, we don't need to join task on object on
        // container, oid and id is already in task table
        StringBuilder selectSB = new StringBuilder();
        selectSB.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
        selectSB.append(" where a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" < ###TIME###");
        String selectString = selectSB.toString();

        // batch size
        RowSelection rowSelection = new RowSelection();
        rowSelection.setMaxRows(CLEANUP_AUDIT_BATCH_SIZE);
        LimitHandler limitHandler = dialect.getLimitHandler();
        selectString = limitHandler.processSql(selectString, rowSelection);

        // replace ? -> batch size, $ -> ?
        // Sorry for that .... I just don't know how to write this query in HQL,
        // nor I'm not sure if limiting max size in
        // compound insert into ... select ... query via query.setMaxSize()
        // would work - TODO write more nicely if anybody knows how)
        selectString = selectString.replace("?", String.valueOf(CLEANUP_AUDIT_BATCH_SIZE));
        selectString = selectString.replace("###TIME###", "?");

        String queryString = "insert into " + tempTable + " " + selectString;
        LOGGER.trace("Query string = {}", queryString);
        NativeQuery query = session.createNativeQuery(queryString);
        query.setParameter(1, new Timestamp(minValue.getTime()));

        return query.executeUpdate();
    }

项目：midpoint 文件：SqlAuditServiceImpl.java

private int selectRecordsByNumberToKeep(Session session, String tempTable, Integer recordsToKeep, Dialect dialect) {
       Number totalAuditRecords = (Number) session.createCriteria(RAuditEventRecord.class)
               .setProjection(Projections.rowCount())
               .uniqueResult();
       int recordsToDelete = totalAuditRecords.intValue() - recordsToKeep;
       if (recordsToDelete <= 0) {
           recordsToDelete = 0;
       } else if (recordsToDelete > CLEANUP_AUDIT_BATCH_SIZE) {
           recordsToDelete = CLEANUP_AUDIT_BATCH_SIZE;
       }
       LOGGER.debug("Total audit records: {}, records to keep: {} => records to delete in this batch: {}",
               totalAuditRecords, recordsToKeep, recordsToDelete);
       if (recordsToDelete == 0) {
        return 0;
       }

    StringBuilder selectSB = new StringBuilder();
    selectSB.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
    selectSB.append(" order by a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" asc");
    String selectString = selectSB.toString();

    // batch size
    RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(recordsToDelete);
    LimitHandler limitHandler = dialect.getLimitHandler();
    selectString = limitHandler.processSql(selectString, rowSelection);
    selectString = selectString.replace("?", String.valueOf(recordsToDelete));

    String queryString = "insert into " + tempTable + " " + selectString;
    LOGGER.trace("Query string = {}", queryString);
    NativeQuery query = session.createNativeQuery(queryString);
    return query.executeUpdate();
}

项目：midpoint 文件：ObjectUpdater.java

private <T extends ObjectType> String nonOverwriteAddObjectAttempt(PrismObject<T> object, RObject rObject,
                                                                   String originalOid, Session session, OrgClosureManager.Context closureContext)
        throws ObjectAlreadyExistsException, SchemaException, DtoTranslationException {

    // check name uniqueness (by type)
    if (StringUtils.isNotEmpty(originalOid)) {
        LOGGER.trace("Checking oid uniqueness.");
        //todo improve this table name bullshit
        Class hqlType = ClassMapper.getHQLTypeClass(object.getCompileTimeClass());
        NativeQuery query = session.createNativeQuery("select count(*) from "
                + RUtil.getTableName(hqlType, session) + " where oid=:oid");
        query.setParameter("oid", object.getOid());

        Number count = (Number) query.uniqueResult();
        if (count != null && count.longValue() > 0) {
            throw new ObjectAlreadyExistsException("Object '" + object.getCompileTimeClass().getSimpleName()
                    + "' with oid '" + object.getOid() + "' already exists.");
        }
    }

    updateFullObject(rObject, object);

    LOGGER.trace("Saving object (non overwrite).");
    String oid = (String) session.save(rObject);
    lookupTableHelper.addLookupTableRows(session, rObject, false);
    caseHelper.addCertificationCampaignCases(session, rObject, false);

    if (closureManager.isEnabled()) {
        Collection<ReferenceDelta> modifications = createAddParentRefDelta(object);
        closureManager.updateOrgClosure(null, modifications, session, oid, object.getCompileTimeClass(),
                OrgClosureManager.Operation.ADD, closureContext);
    }

    return oid;
}

项目：midpoint 文件：ObjectRetriever.java

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;
         if (query == null || query.getFilter() == null) {
            if (GetOperationOptions.isDistinct(SelectorOptions.findRootOptions(options))) {
                throw new UnsupportedOperationException("Distinct option is not supported here");   // TODO
    }
             // 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]
             NativeQuery sqlQuery = session.createNativeQuery("SELECT COUNT(*) FROM " + RUtil.getTableName(hqlType, session));
             longCount = (Number) sqlQuery.uniqueResult();
         } else {
             RQuery rQuery;
    QueryEngine2 engine = new QueryEngine2(getConfiguration(), prismContext);
    rQuery = engine.interpret(query, type, options, true, session);

             longCount = (Number) 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;
 }

项目：midpoint 文件：OrgClosureManager.java

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);
}

项目：midpoint 文件：OrgClosureManager.java

private void checkForCycles(List<Edge> edges, Session session) {
    String queryText = "select descendant_oid, ancestor_oid from " + CLOSURE_TABLE_NAME + " where " + getWhereClauseForCycleCheck(edges);
    NativeQuery query = session.createNativeQuery(queryText)
            .addScalar("descendant_oid", StringType.INSTANCE)
            .addScalar("ancestor_oid", StringType.INSTANCE);
    long start = System.currentTimeMillis();
    List list = query.list();
    LOGGER.trace("Cycles checked in {} ms, {} conflicts found", System.currentTimeMillis()-start, list.size());
    if (!list.isEmpty()) {
        throw new IllegalArgumentException("Modification couldn't be executed, because a cycle in org structure graph would be created. Cycle-creating edges being added: " + formatList(list));
    }
}

项目：midpoint 文件：OrgClosureManager.java

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);
}

项目：midpoint 文件：OrgClosureManager.java

private void dumpOrgClosureTypeTable(Session session, String tableName) {
    NativeQuery q = session.createNativeQuery("select descendant_oid, ancestor_oid, val from " + tableName)
            .addScalar("descendant_oid", StringType.INSTANCE)
            .addScalar("ancestor_oid", StringType.INSTANCE)
            .addScalar("val", IntegerType.INSTANCE);
    List<Object[]> list = q.list();
    LOGGER.trace("{} ({} rows):", tableName, list.size());
    for (Object[] row : list) {
        LOGGER.trace(" - [d={}, a={}, val={}]", row);
    }
}

项目：midpoint 文件：SqlAuditServiceImpl.java

private int selectRecordsByMaxAge(Session session, String tempTable, Date minValue, Dialect dialect) {

        // fill temporary table, we don't need to join task on object on
        // container, oid and id is already in task table
        StringBuilder selectSB = new StringBuilder();
        selectSB.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
        selectSB.append(" where a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" < ###TIME###");
        String selectString = selectSB.toString();

        // batch size
        RowSelection rowSelection = new RowSelection();
        rowSelection.setMaxRows(CLEANUP_AUDIT_BATCH_SIZE);
        LimitHandler limitHandler = dialect.getLimitHandler();
        selectString = limitHandler.processSql(selectString, rowSelection);

        // replace ? -> batch size, $ -> ?
        // Sorry for that .... I just don't know how to write this query in HQL,
        // nor I'm not sure if limiting max size in
        // compound insert into ... select ... query via query.setMaxSize()
        // would work - TODO write more nicely if anybody knows how)
        selectString = selectString.replace("?", String.valueOf(CLEANUP_AUDIT_BATCH_SIZE));
        selectString = selectString.replace("###TIME###", "?");

        String queryString = "insert into " + tempTable + " " + selectString;
        LOGGER.trace("Query string = {}", queryString);
        NativeQuery query = session.createNativeQuery(queryString);
        query.setParameter(1, new Timestamp(minValue.getTime()));

        return query.executeUpdate();
    }

项目：midpoint 文件：SqlAuditServiceImpl.java

private int selectRecordsByNumberToKeep(Session session, String tempTable, Integer recordsToKeep, Dialect dialect) {
       Number totalAuditRecords = (Number) session.createCriteria(RAuditEventRecord.class)
               .setProjection(Projections.rowCount())
               .uniqueResult();
       int recordsToDelete = totalAuditRecords.intValue() - recordsToKeep;
       if (recordsToDelete <= 0) {
           recordsToDelete = 0;
       } else if (recordsToDelete > CLEANUP_AUDIT_BATCH_SIZE) {
           recordsToDelete = CLEANUP_AUDIT_BATCH_SIZE;
       }
       LOGGER.debug("Total audit records: {}, records to keep: {} => records to delete in this batch: {}",
               totalAuditRecords, recordsToKeep, recordsToDelete);
       if (recordsToDelete == 0) {
        return 0;
       }

    StringBuilder selectSB = new StringBuilder();
    selectSB.append("select a.id as id from ").append(RAuditEventRecord.TABLE_NAME).append(" a");
    selectSB.append(" order by a.").append(RAuditEventRecord.COLUMN_TIMESTAMP).append(" asc");
    String selectString = selectSB.toString();

    // batch size
    RowSelection rowSelection = new RowSelection();
    rowSelection.setMaxRows(recordsToDelete);
    LimitHandler limitHandler = dialect.getLimitHandler();
    selectString = limitHandler.processSql(selectString, rowSelection);
    selectString = selectString.replace("?", String.valueOf(recordsToDelete));

    String queryString = "insert into " + tempTable + " " + selectString;
    LOGGER.trace("Query string = {}", queryString);
    NativeQuery query = session.createNativeQuery(queryString);
    return query.executeUpdate();
}

项目：high-performance-java-persistence 文件：PostCommentScoreStoredProcedureTest.java

protected List<PostCommentScore> postCommentScoresCTEJoin(Long postId, int rank) {
    return doInJPA(entityManager -> {
        List<PostCommentScore> postCommentScores = entityManager.createNativeQuery(
            "SELECT id, parent_id, review, created_on, score " +
                "FROM ( " +
                "    SELECT " +
                "        id, parent_id, review, created_on, score, " +
                "        dense_rank() OVER (ORDER BY total_score DESC) rank " +
                "    FROM ( " +
                "       SELECT " +
                "           id, parent_id, review, created_on, score, " +
                "           SUM(score) OVER (PARTITION BY root_id) total_score " +
                "       FROM (" +
                "          WITH RECURSIVE post_comment_score(id, root_id, post_id, " +
                "              parent_id, review, created_on, score) AS (" +
                "              SELECT " +
                "                  id, id, post_id, parent_id, review, created_on, score" +
                "              FROM post_comment " +
                "              WHERE post_id = :postId AND parent_id IS NULL " +
                "              UNION ALL " +
                "              SELECT pc.id, pcs.root_id, pc.post_id, pc.parent_id, " +
                "                  pc.review, pc.created_on, pc.score " +
                 "              FROM post_comment pc " +
                "              INNER JOIN post_comment_score pcs " +
                "              ON pc.parent_id = pcs.id " +
                "              WHERE pc.parent_id = pcs.id " +
                "          ) " +
                "          SELECT id, parent_id, root_id, review, created_on, score " +
                "          FROM post_comment_score " +
                "       ) score_by_comment " +
                "    ) score_total " +
                "    ORDER BY total_score DESC, id ASC " +
                ") total_score_group " +
                "WHERE rank <= :rank", "PostCommentScore").unwrap(NativeQuery.class)
        .setParameter("postId", postId)
        .setParameter("rank", rank)
        .setResultTransformer(new PostCommentScoreResultTransformer())
        .list();
        return postCommentScores;
    });
}

项目：midpoint 文件：CertificationCaseHelper.java

protected List<Long> addOrDeleteCases(Session session, String campaignOid, Collection<? extends ItemDelta> modifications) throws SchemaException, DtoTranslationException {
        final ItemPath casePath = new ItemPath(AccessCertificationCampaignType.F_CASE);
        boolean replacePresent = false;
        List<Long> affectedIds = new ArrayList<>();
        for (ItemDelta delta : modifications) {
            ItemPath deltaPath = delta.getPath();
            if (!casePath.isSubPathOrEquivalent(deltaPath)) {
                throw new IllegalStateException("Wrong campaign delta sneaked into updateCampaignCases: class=" + delta.getClass() + ", path=" + deltaPath);
            }

            if (deltaPath.size() == 1) {
                if (delta.getValuesToDelete() != null) {
                    // todo do 'bulk' delete like delete from ... where oid=? and id in (...)
                    for (PrismContainerValue value : (Collection<PrismContainerValue>) delta.getValuesToDelete()) {
                        Long id = value.getId();
                        if (id == null) {
                            throw new SchemaException("Couldn't delete certification case with null id");
                        }
                        affectedIds.add(id);
                        // TODO couldn't this cascading be done by hibernate itself?
                        Integer integerCaseId = RUtil.toInteger(id);
//                        NativeQuery deleteCaseReferences = session.createNativeQuery("delete from " + RCertCaseReference.TABLE +
//                                " where owner_owner_oid=:oid and owner_id=:id");
//                        deleteCaseReferences.setParameter("oid", campaignOid);
//                        deleteCaseReferences.setParameter("id", integerCaseId);
//                        deleteCaseReferences.executeUpdate();
                        NativeQuery deleteWorkItemReferences = session.createNativeQuery("delete from " + RCertWorkItemReference.TABLE +
                                " where owner_owner_owner_oid=:oid and owner_owner_id=:id");
                        deleteWorkItemReferences.setParameter("oid", campaignOid);
                        deleteWorkItemReferences.setParameter("id", integerCaseId);
                        deleteWorkItemReferences.executeUpdate();
                        NativeQuery deleteCaseWorkItems = session.createNativeQuery("delete from " + RAccessCertificationWorkItem.TABLE +
                                " where owner_owner_oid=:oid and owner_id=:id");
                        deleteCaseWorkItems.setParameter("oid", campaignOid);
                        deleteCaseWorkItems.setParameter("id", integerCaseId);
                        deleteCaseWorkItems.executeUpdate();
                        Query deleteCase = session.getNamedQuery("delete.campaignCase");
                        deleteCase.setParameter("oid", campaignOid);
                        deleteCase.setParameter("id", integerCaseId);
                        deleteCase.executeUpdate();
                    }
                }
                // TODO generated IDs might conflict with client-provided ones
                // also, client-provided IDs might conflict with those that are already in the database
                // So it's safest not to provide any IDs by the client
                if (delta.getValuesToAdd() != null) {
                    int currentId = generalHelper.findLastIdInRepo(session, campaignOid, "get.campaignCaseLastId") + 1;
                    addCertificationCampaignCases(session, campaignOid, delta.getValuesToAdd(), currentId, affectedIds);
                }
                if (delta.getValuesToReplace() != null) {
                    deleteCertificationCampaignCases(session, campaignOid);
                    addCertificationCampaignCases(session, campaignOid, delta.getValuesToReplace(), 1, affectedIds);
                    replacePresent = true;
                }

            }
        }
        return replacePresent ? null : affectedIds;
    }

项目：midpoint 文件：OrgClosureManager.java

private void removeIndependentEdgesInternal(List<Edge> edges, Context context, Session session) {

        String deltaTempTableName = computeDeltaTable(edges, context, session);
        try {
            int count;

            String deleteFromClosureQueryText, updateInClosureQueryText;
            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()) {
                // delete is the same as for MySQL
                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";
                // update is also done via inner join (as in MySQL), but using slightly different syntax
                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 if (isMySqlCompatible()) {
                // http://stackoverflow.com/questions/652770/delete-with-join-in-mysql
                // TODO consider this for other databases as well
                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";
                // it is not possible to use temporary table twice in a query
                // TODO consider using this in postgresql as well...
                updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME +
                        " join " + deltaTempTableName + " td " +
                        "on td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid " +
                        "set "+ CLOSURE_TABLE_NAME +".val = "+ CLOSURE_TABLE_NAME +".val - td.val";
            } 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);
        }
    }

项目：midpoint 文件：CertificationCaseHelper.java

protected List<Long> addOrDeleteCases(Session session, String campaignOid, Collection<? extends ItemDelta> modifications) throws SchemaException, DtoTranslationException {
        final ItemPath casePath = new ItemPath(AccessCertificationCampaignType.F_CASE);
        boolean replacePresent = false;
        List<Long> affectedIds = new ArrayList<>();
        for (ItemDelta delta : modifications) {
            ItemPath deltaPath = delta.getPath();
            if (!casePath.isSubPathOrEquivalent(deltaPath)) {
                throw new IllegalStateException("Wrong campaign delta sneaked into updateCampaignCases: class=" + delta.getClass() + ", path=" + deltaPath);
            }

            if (deltaPath.size() == 1) {
                if (delta.getValuesToDelete() != null) {
                    // todo do 'bulk' delete like delete from ... where oid=? and id in (...)
                    for (PrismContainerValue value : (Collection<PrismContainerValue>) delta.getValuesToDelete()) {
                        Long id = value.getId();
                        if (id == null) {
                            throw new SchemaException("Couldn't delete certification case with null id");
                        }
                        affectedIds.add(id);
                        // TODO couldn't this cascading be done by hibernate itself?
                        Integer integerCaseId = RUtil.toInteger(id);
//                        NativeQuery deleteCaseReferences = session.createNativeQuery("delete from " + RCertCaseReference.TABLE +
//                                " where owner_owner_oid=:oid and owner_id=:id");
//                        deleteCaseReferences.setParameter("oid", campaignOid);
//                        deleteCaseReferences.setParameter("id", integerCaseId);
//                        deleteCaseReferences.executeUpdate();
                        NativeQuery deleteWorkItemReferences = session.createNativeQuery("delete from " + RCertWorkItemReference.TABLE +
                                " where owner_owner_owner_oid=:oid and owner_owner_id=:id");
                        deleteWorkItemReferences.setParameter("oid", campaignOid);
                        deleteWorkItemReferences.setParameter("id", integerCaseId);
                        deleteWorkItemReferences.executeUpdate();
                        NativeQuery deleteCaseWorkItems = session.createNativeQuery("delete from " + RAccessCertificationWorkItem.TABLE +
                                " where owner_owner_oid=:oid and owner_id=:id");
                        deleteCaseWorkItems.setParameter("oid", campaignOid);
                        deleteCaseWorkItems.setParameter("id", integerCaseId);
                        deleteCaseWorkItems.executeUpdate();
                        Query deleteCase = session.getNamedQuery("delete.campaignCase");
                        deleteCase.setParameter("oid", campaignOid);
                        deleteCase.setParameter("id", integerCaseId);
                        deleteCase.executeUpdate();
                    }
                }
                // TODO generated IDs might conflict with client-provided ones
                // also, client-provided IDs might conflict with those that are already in the database
                // So it's safest not to provide any IDs by the client
                if (delta.getValuesToAdd() != null) {
                    int currentId = generalHelper.findLastIdInRepo(session, campaignOid, "get.campaignCaseLastId") + 1;
                    addCertificationCampaignCases(session, campaignOid, delta.getValuesToAdd(), currentId, affectedIds);
                }
                if (delta.getValuesToReplace() != null) {
                    deleteCertificationCampaignCases(session, campaignOid);
                    addCertificationCampaignCases(session, campaignOid, delta.getValuesToReplace(), 1, affectedIds);
                    replacePresent = true;
                }

            }
        }
        return replacePresent ? null : affectedIds;
    }

项目：midpoint 文件：OrgClosureManager.java

private void removeIndependentEdgesInternal(List<Edge> edges, Context context, Session session) {

        String deltaTempTableName = computeDeltaTable(edges, context, session);
        try {
            int count;

            String deleteFromClosureQueryText, updateInClosureQueryText;
            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()) {
                // delete is the same as for MySQL
                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";
                // update is also done via inner join (as in MySQL), but using slightly different syntax
                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 if (isMySqlCompatible()) {
                // http://stackoverflow.com/questions/652770/delete-with-join-in-mysql
                // TODO consider this for other databases as well
                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";
                // it is not possible to use temporary table twice in a query
                // TODO consider using this in postgresql as well...
                updateInClosureQueryText = "update " + CLOSURE_TABLE_NAME +
                        " join " + deltaTempTableName + " td " +
                        "on td.descendant_oid=" + CLOSURE_TABLE_NAME + ".descendant_oid and td.ancestor_oid=" + CLOSURE_TABLE_NAME + ".ancestor_oid " +
                        "set "+ CLOSURE_TABLE_NAME +".val = "+ CLOSURE_TABLE_NAME +".val - td.val";
            } 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);
        }
    }