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

Example 21 with NativeQuery

use of org.hibernate.query.NativeQuery in project ice by JBEI.

the class MessageDAO method retrieveNewMessageCount.

public int retrieveNewMessageCount(Account account) {
    try {
        StringBuilder builder = new StringBuilder();
        builder.append("select count(id) from message m where m.is_read=false AND (m.id in ").append("(select message_id from message_destination_accounts where account_id = ").append(account.getId()).append(")");
        if (!account.getGroups().isEmpty()) {
            builder.append(" OR m.id in (select message_id from message_destination_groups where group_id in (");
            int i = 0;
            for (Group group : account.getGroups()) {
                if (i > 0)
                    builder.append(", ");
                builder.append(group.getId());
                i += 1;
            }
            builder.append("))");
        }
        builder.append(")");
        NativeQuery query = currentSession().createNativeQuery(builder.toString());
        Number number = (Number) query.uniqueResult();
        return number.intValue();
    } catch (HibernateException he) {
        Logger.error(he);
        throw new DAOException(he);
    }
}

Example 22 with NativeQuery

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

the class ObjectRetriever method getObjectInternal.

public <T extends ObjectType> PrismObject<T> getObjectInternal(Session session, Class<T> type, String oid, Collection<SelectorOptions<GetOperationOptions>> options, boolean lockForUpdate) throws ObjectNotFoundException, SchemaException, DtoTranslationException {
    boolean lockedForUpdateViaHibernate = false;
    boolean lockedForUpdateViaSql = false;
    LockOptions lockOptions = new LockOptions();
    // todo fix lock for update!!!!!
    if (lockForUpdate) {
        if (getConfiguration().isLockForUpdateViaHibernate()) {
            lockOptions.setLockMode(LockMode.PESSIMISTIC_WRITE);
            lockedForUpdateViaHibernate = true;
        } else if (getConfiguration().isLockForUpdateViaSql()) {
            LOGGER.trace("Trying to lock object {} for update (via SQL)", oid);
            long time = System.currentTimeMillis();
            NativeQuery q = session.createNativeQuery("select oid from m_object where oid = ? for update");
            q.setParameter(1, oid);
            Object result = q.uniqueResult();
            if (result == null) {
                return throwObjectNotFoundException(type, oid);
            }
            if (LOGGER.isTraceEnabled()) {
                LOGGER.trace("Locked via SQL (in {} ms)", System.currentTimeMillis() - time);
            }
            lockedForUpdateViaSql = true;
        }
    }
    if (LOGGER.isTraceEnabled()) {
        if (lockedForUpdateViaHibernate) {
            LOGGER.trace("Getting object {} with locking for update (via hibernate)", oid);
        } else if (lockedForUpdateViaSql) {
            LOGGER.trace("Getting object {}, already locked for update (via SQL)", oid);
        } else {
            LOGGER.trace("Getting object {} without locking for update", oid);
        }
    }
    GetObjectResult fullObject = null;
    if (!lockForUpdate) {
        Query<?> query = session.getNamedQuery("get.object");
        query.setParameter("oid", oid);
        query.setResultTransformer(GetObjectResult.RESULT_STYLE.getResultTransformer());
        query.setLockOptions(lockOptions);
        fullObject = (GetObjectResult) query.uniqueResult();
    } else {
        // we're doing update after this get, therefore we load full object right now
        // (it would be loaded during merge anyway)
        // this just loads object to hibernate session, probably will be removed later. Merge after this get
        // will be faster. Read and use object only from fullObject column.
        // todo remove this later [lazyman]
        Class<?> clazz = ClassMapper.getHQLTypeClass(type);
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<?> cq = cb.createQuery(clazz);
        cq.where(cb.equal(cq.from(clazz).get("oid"), oid));
        Query<?> query = session.createQuery(cq);
        query.setLockOptions(lockOptions);
        RObject obj = (RObject) query.uniqueResult();
        if (obj != null) {
            fullObject = new GetObjectResult(obj.getOid(), obj.getFullObject());
        }
    }
    LOGGER.trace("Got it.");
    if (fullObject == null) {
        throwObjectNotFoundException(type, oid);
    }
    LOGGER.trace("Transforming data to JAXB type.");
    PrismObject<T> prismObject = updateLoadedObject(fullObject, type, oid, options, null, session);
    return prismObject;
}

Example 23 with NativeQuery

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

the class OrgClosureManager method computeDeltaTable.

// returns table name
private String computeDeltaTable(List<Edge> edges, Context context, Session session) {
    if (edges.isEmpty()) {
        throw new IllegalArgumentException("No edges to add/remove");
    }
    String deltaTempTableName;
    if (context.temporaryTableName != null) {
        // table was created on the beginning of transaction
        deltaTempTableName = context.temporaryTableName;
    } else if (isOracle()) {
        // table definition is global
        deltaTempTableName = TEMP_DELTA_TABLE_NAME_FOR_ORACLE;
    } else {
        // table will be created now
        deltaTempTableName = generateDeltaTempTableName();
    }
    if (COUNT_CLOSURE_RECORDS && LOGGER.isTraceEnabled()) {
        NativeQuery q = session.createNativeQuery("select count(*) from " + CLOSURE_TABLE_NAME);
        List list = q.list();
        LOGGER.trace("OrgClosure has {} rows", list.toString());
    }
    long start;
    int count;
    String selectClause = "select t1.descendant_oid as descendant_oid, t2.ancestor_oid as ancestor_oid, " + "sum(t1.val*t2.val) as val " + "from " + CLOSURE_TABLE_NAME + " t1, " + CLOSURE_TABLE_NAME + " t2 " + "where " + getWhereClause(edges) + " " + "group by t1.descendant_oid, t2.ancestor_oid";
    if (isSQLServer()) {
        // we create the table manually, because we want to have an index on it, and
        // with serializable transactions it is not possible to create index within the transaction (after inserting data)
        start = System.currentTimeMillis();
        final String createTableSql = "create table " + deltaTempTableName + " (" + "descendant_oid NVARCHAR(36) COLLATE database_default, " + "ancestor_oid NVARCHAR(36) COLLATE database_default, " + "val INT, " + "PRIMARY KEY (descendant_oid, ancestor_oid))";
        // NativeQuery createTableQuery = session.createNativeQuery(createTableSql);
        // createTableQuery.executeUpdate();  <--- this does not work because the temporary table gets deleted when the command terminates (preparedStatement issue - maybe something like this: https://support.microsoft.com/en-us/kb/280134 ?)
        session.doWork(connection -> RUtil.executeStatement(connection, createTableSql));
        LOGGER.trace("Empty delta table created in {} ms", System.currentTimeMillis() - start);
        NativeQuery insertQuery = session.createNativeQuery("insert into " + deltaTempTableName + " " + selectClause);
        start = System.currentTimeMillis();
        count = insertQuery.executeUpdate();
    } else {
        String createTablePrefix;
        if (isPostgreSQL()) {
            createTablePrefix = "create local temporary table " + deltaTempTableName + " on commit drop as ";
        } else if (isH2()) {
            // todo skip if this is first in this transaction
            NativeQuery q = session.createNativeQuery("delete from " + deltaTempTableName);
            int c = q.executeUpdate();
            LOGGER.trace("Deleted {} rows from temporary table {}", c, deltaTempTableName);
            createTablePrefix = "insert into " + deltaTempTableName + " ";
        } else if (isOracle()) {
            // todo skip if this is first in this transaction
            NativeQuery q = session.createNativeQuery("delete from " + deltaTempTableName);
            int c = q.executeUpdate();
            LOGGER.trace("Deleted {} rows from temporary table {}", c, deltaTempTableName);
            createTablePrefix = "insert into " + deltaTempTableName + " ";
        } else {
            throw new UnsupportedOperationException("Org. closure manager - unsupported database operation");
        }
        NativeQuery query1 = session.createNativeQuery(createTablePrefix + selectClause);
        start = System.currentTimeMillis();
        count = query1.executeUpdate();
    }
    LOGGER.trace("Added {} records to temporary delta table {} ({} ms).", count, deltaTempTableName, System.currentTimeMillis() - start);
    if (isPostgreSQL()) {
        start = System.currentTimeMillis();
        NativeQuery qIndex = session.createNativeQuery("CREATE INDEX " + deltaTempTableName + "_idx " + "  ON " + deltaTempTableName + "  USING btree " + "  (descendant_oid, ancestor_oid)");
        qIndex.executeUpdate();
        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Index created in {} ms", System.currentTimeMillis() - start);
        }
    }
    if (DUMP_TABLES) {
        dumpOrgClosureTypeTable(session, CLOSURE_TABLE_NAME);
    }
    if (DUMP_TABLES) {
        dumpOrgClosureTypeTable(session, deltaTempTableName);
    }
    return deltaTempTableName;
}

Example 24 with NativeQuery

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

the class OrgClosureManager method addEdgeSimple.

// we expect that the link oid->parent does not exist yet and the parent exists
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();
}

Example 25 with NativeQuery

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

the class OrgClosureManager method onBeginTransaction.

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