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

Example 41 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class NativeSQLQueriesTest method testRegisteredNamedSQLQueryWithScalar.

@Test
public void testRegisteredNamedSQLQueryWithScalar() {
    final NamedSQLQueryDefinitionBuilder builder = new NamedSQLQueryDefinitionBuilder();
    builder.setName("namedQuery");
    builder.setQuery("select count(*) AS c from ORGANIZATION");
    builder.setQueryReturns(new NativeSQLQueryReturn[1]);
    sessionFactory().registerNamedSQLQueryDefinition("namedQuery", builder.createNamedQueryDefinition());
    final Session s = openSession();
    s.beginTransaction();
    final SQLQuery query = (SQLQuery) s.getNamedQuery("namedQuery");
    query.addScalar("c");
    final Number result = (Number) query.uniqueResult();
    s.getTransaction().commit();
    s.close();
    assertNotNull(result);
    assertTrue(0 == result.intValue());
}

Example 42 with SQLQuery

use of org.hibernate.SQLQuery in project hibernate-orm by hibernate.

the class NativeSqlAndQuotedIdentifiersTest method testPartialScalarDiscovery.

@Test
public void testPartialScalarDiscovery() {
    Session session = openSession();
    session.beginTransaction();
    SQLQuery query = (SQLQuery) session.getNamedQuery("query-person");
    query.setResultSetMapping("person-scalar");
    query.list();
    session.getTransaction().commit();
    session.close();
}

Example 43 with SQLQuery

use of org.hibernate.SQLQuery in project java-design-patterns by iluwatar.

the class QueryServiceImpl method getAuthorBooks.

@Override
public List<Book> getAuthorBooks(String username) {
    List<Book> bookDTos = null;
    try (Session session = sessionFactory.openSession()) {
        SQLQuery sqlQuery = session.createSQLQuery("SELECT b.title as \"title\", b.price as \"price\"" + " FROM Author a , Book b where b.author_id = a.id and a.username=:username");
        sqlQuery.setParameter("username", username);
        bookDTos = sqlQuery.setResultTransformer(Transformers.aliasToBean(Book.class)).list();
    }
    return bookDTos;
}

Example 44 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: {} ms", duration);
            }
            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();
        }
    });
}

Example 45 with SQLQuery

use of org.hibernate.SQLQuery in project dhis2-core by dhis2.

the class HibernateGenericStore method getSqlQuery.

/**
     * Creates a SqlQuery.
     *
     * @param sql the sql query.
     * @return a SqlQuery instance.
     */
protected final SQLQuery getSqlQuery(String sql) {
    SQLQuery query = getSession().createSQLQuery(sql);
    query.setCacheable(cacheable);
    return query;
}