Examples with SQLQuery org.hibernate.SQLQuery used on opensource projects

Example 11 with SQLQuery

use of org.hibernate.SQLQuery in project midpoint by Evolveum.

the class ObjectUpdater method nonOverwriteAddObjectAttempt.

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());
        SQLQuery query = session.createSQLQuery("select count(*) from " + RUtil.getTableName(hqlType) + " where oid=:oid");
        query.setString("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;
}

Example 12 with SQLQuery

use of org.hibernate.SQLQuery in project midpoint by Evolveum.

the class SqlAuditServiceImpl method selectRecordsByNumberToKeep.

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.buildLimitHandler(selectString, rowSelection);
    selectString = limitHandler.getProcessedSql();
    selectString = selectString.replace("?", String.valueOf(recordsToDelete));
    String queryString = "insert into " + tempTable + " " + selectString;
    LOGGER.trace("Query string = {}", queryString);
    SQLQuery query = session.createSQLQuery(queryString);
    return query.executeUpdate();
}

Example 13 with SQLQuery

use of org.hibernate.SQLQuery in project midpoint by Evolveum.

the class SqlAuditServiceImpl method selectRecordsByMaxAge.

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.buildLimitHandler(selectString, rowSelection);
    selectString = limitHandler.getProcessedSql();
    // 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);
    SQLQuery query = session.createSQLQuery(queryString);
    query.setParameter(0, new Timestamp(minValue.getTime()));
    return query.executeUpdate();
}

Example 14 with SQLQuery

use of org.hibernate.SQLQuery in project gocd by gocd.

the class PipelineRepository method updateNaturalOrderForPipeline.

public static int updateNaturalOrderForPipeline(Session session, Long pipelineId, double naturalOrder) {
    String sql = "UPDATE pipelines SET naturalOrder = :naturalOrder WHERE id = :pipelineId";
    SQLQuery query = session.createSQLQuery(sql);
    query.setLong("pipelineId", pipelineId);
    query.setDouble("naturalOrder", naturalOrder);
    return query.executeUpdate();
}

Example 15 with SQLQuery

use of org.hibernate.SQLQuery in project gocd by gocd.

the class PipelineRepository method updatePipelineTimeline.

@SuppressWarnings({ "unchecked" })
public void updatePipelineTimeline(final PipelineTimeline pipelineTimeline, final List<PipelineTimelineEntry> tempEntriesForRollback) {
    getHibernateTemplate().execute(new HibernateCallback() {

        private static final int PIPELINE_NAME = 0;

        private static final int ID = 1;

        private static final int COUNTER = 2;

        private static final int MODIFIED_TIME = 3;

        private static final int FINGERPRINT = 4;

        private static final int NATURAL_ORDER = 5;

        private static final int REVISION = 6;

        private static final int FOLDER = 7;

        private static final int MOD_ID = 8;

        private static final int PMR_ID = 9;

        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            LOGGER.info("Start updating pipeline timeline");
            List<Object[]> matches = retrieveTimeline(session, pipelineTimeline);
            List<PipelineTimelineEntry> newPipelines = populateFrom(matches);
            addEntriesToPipelineTimeline(newPipelines, pipelineTimeline, tempEntriesForRollback);
            updateNaturalOrdering(session, newPipelines);
            LOGGER.info("Pipeline timeline updated");
            return null;
        }

        private void updateNaturalOrdering(Session session, List<PipelineTimelineEntry> pipelines) {
            for (PipelineTimelineEntry pipeline : pipelines) {
                if (pipeline.hasBeenUpdated()) {
                    updateNaturalOrderForPipeline(session, pipeline.getId(), pipeline.naturalOrder());
                }
            }
        }

        private List<Object[]> loadTimeline(SQLQuery query) {
            long startedAt = System.currentTimeMillis();
            List<Object[]> matches = (List<Object[]>) query.list();
            long duration = System.currentTimeMillis() - startedAt;
            if (duration > 1000) {
                LOGGER.warn("updating in memory pipeline-timeline took: " + duration + " ms");
            }
            return matches;
        }

        private List<Object[]> retrieveTimeline(Session session, PipelineTimeline pipelineTimeline) {
            SQLQuery query = session.createSQLQuery(queryExtensions.retrievePipelineTimeline());
            query.setLong(0, pipelineTimeline.maximumId());
            List<Object[]> matches = loadTimeline(query);
            sortTimeLineByPidAndPmrId(matches);
            return matches;
        }

        private void sortTimeLineByPidAndPmrId(List<Object[]> matches) {
            Collections.sort(matches, new Comparator<Object[]>() {

                @Override
                public int compare(Object[] m1, Object[] m2) {
                    long id1 = id(m1);
                    long id2 = id(m2);
                    if (id1 == id2) {
                        return (int) (pmrId(m1) - pmrId(m2));
                    }
                    return (int) (id1 - id2);
                }
            });
        }

        private List<PipelineTimelineEntry> populateFrom(List<Object[]> matches) {
            ArrayList<PipelineTimelineEntry> newPipelines = new ArrayList<>();
            if (matches.isEmpty()) {
                return newPipelines;
            }
            Map<String, List<PipelineTimelineEntry.Revision>> revisions = new HashMap<>();
            String name = null;
            long curId = -1;
            Integer counter = null;
            double naturalOrder = 0.0;
            PipelineTimelineEntry entry = null;
            for (int i = 0; i < matches.size(); i++) {
                Object[] row = matches.get(i);
                long id = id(row);
                if (curId != id) {
                    name = pipelineName(row);
                    curId = id;
                    counter = counter(row);
                    revisions = new HashMap<>();
                    naturalOrder = naturalOrder(row);
                }
                String fingerprint = fingerprint(row);
                if (!revisions.containsKey(fingerprint)) {
                    revisions.put(fingerprint, new ArrayList<>());
                }
                revisions.get(fingerprint).add(rev(row));
                int nextI = i + 1;
                if ((//new pipeline instance starts in next record, so capture this one
                (nextI < matches.size() && id(matches.get(nextI)) != curId) || nextI == matches.size())) {
                    //this is the last record, so capture it
                    entry = new PipelineTimelineEntry(name, curId, counter, revisions, naturalOrder);
                    newPipelines.add(entry);
                }
            }
            return newPipelines;
        }

        private String folder(Object[] row) {
            return (String) row[FOLDER];
        }

        private PipelineTimelineEntry.Revision rev(Object[] row) {
            return new PipelineTimelineEntry.Revision(modifiedTime(row), stringRevision(row), folder(row), modId(row));
        }

        private long pmrId(Object[] row) {
            return ((BigInteger) row[PMR_ID]).longValue();
        }

        private long modId(Object[] row) {
            return ((BigInteger) row[MOD_ID]).longValue();
        }

        private double naturalOrder(Object[] row) {
            return (Double) row[NATURAL_ORDER];
        }

        private Date modifiedTime(Object[] row) {
            return (Date) row[MODIFIED_TIME];
        }

        private String stringRevision(Object[] row) {
            return (String) row[REVISION];
        }

        private String fingerprint(Object[] row) {
            return String.valueOf(row[FINGERPRINT]);
        }

        private String pipelineName(Object[] row) {
            return (String) row[PIPELINE_NAME];
        }

        private int counter(Object[] row) {
            return row[COUNTER] == null ? -1 : ((BigInteger) row[COUNTER]).intValue();
        }

        private long id(Object[] first) {
            return ((BigInteger) first[ID]).longValue();
        }
    });
}