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; }
@SuppressWarnings({"unchecked"}) public List<Modification> getModificationsForPipelineRange(final String pipelineName, final Integer fromCounter, final Integer toCounter) { return (List<Modification>) getHibernateTemplate().execute((HibernateCallback) session -> { final List<Long> fromInclusiveModificationList = fromInclusiveModificationsForPipelineRange(session, pipelineName, fromCounter, toCounter); final Set<Long> fromModifications = new TreeSet<>(fromInclusiveModificationsForPipelineRange(session, pipelineName, fromCounter, fromCounter)); final Set<Long> fromExclusiveModificationList = new HashSet<>(); for (Long modification : fromInclusiveModificationList) { if (fromModifications.contains(modification)) { fromModifications.remove(modification); } else { fromExclusiveModificationList.add(modification); } } SQLQuery query = session.createSQLQuery("SELECT * FROM modifications WHERE id IN (:ids) ORDER BY materialId ASC, id DESC"); query.addEntity(Modification.class); query.setParameterList("ids", fromExclusiveModificationList.isEmpty() ? fromInclusiveModificationList : fromExclusiveModificationList); return query.list(); }); }
private List<Long> fromInclusiveModificationsForPipelineRange(Session session, String pipelineName, Integer fromCounter, Integer toCounter) { String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName, fromCounter, toCounter); SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); final List ids = pipelineIdsQuery.list(); if (ids.isEmpty()) { return new ArrayList<>(); } String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max" + " FROM modifications mods1 " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId " + " WHERE pmr.pipelineId IN (:ids) " + " GROUP BY mods1.materialId"; SQLQuery query = session.createSQLQuery("SELECT mods.id " + " FROM modifications mods" + " INNER JOIN (" + minMaxQuery + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max" + " ORDER BY mods.materialId ASC, mods.id DESC"); query.addScalar("id", new LongType()); query.setParameterList("ids", ids); return query.list(); }
@Test @RequiresDialect(MySQL5Dialect.class) public void testEscapeColonInSQL() throws QueryException { Session s = openSession(); Transaction t = s.beginTransaction(); SQLQuery query = s.createSQLQuery( "SELECT @row \\:= 1" ); List list = query.list(); assertTrue( list.get( 0 ).toString().equals( "1" ) ); t.commit(); s.close(); }
@SuppressWarnings("unchecked") public List<MatchedRevision> findRevisionsMatching(final MaterialConfig materialConfig, final String searchString) { return (List<MatchedRevision>) getHibernateTemplate().execute((HibernateCallback) session -> { String sql = "SELECT m.*" + " FROM modifications AS m" + " INNER JOIN materials mat ON mat.id = m.materialId" + " WHERE mat.fingerprint = :finger_print" + " AND (m.revision || ' ' || COALESCE(m.username, '') || ' ' || COALESCE(m.comment, '') LIKE :search_string OR m.pipelineLabel LIKE :search_string)" + " ORDER BY m.id DESC" + " LIMIT 5"; SQLQuery query = session.createSQLQuery(sql); query.addEntity("m", Modification.class); Material material = materialConfigConverter.toMaterial(materialConfig); query.setString("finger_print", material.getFingerprint()); query.setString("search_string", "%" + searchString + "%"); final List<MatchedRevision> list = new ArrayList<>(); for (Modification mod : (List<Modification>) query.list()) { list.add(material.createMatchedRevision(mod, searchString)); } return list; }); }
private Map<PipelineId, Set<Long>> relevantToLookedUpDependencyMap(Session session, List<Long> pipelineIds) { final int LOOKED_UP_PIPELINE_ID = 2; final int RELEVANT_PIPELINE_ID = 0; final int RELEVANT_PIPELINE_NAME = 1; String pipelineIdsSql = queryExtensions.queryRelevantToLookedUpDependencyMap(pipelineIds); SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); pipelineIdsQuery.addScalar("id", new LongType()); pipelineIdsQuery.addScalar("name", new StringType()); pipelineIdsQuery.addScalar("lookedUpId", new LongType()); final List<Object[]> ids = pipelineIdsQuery.list(); Map<Long, List<PipelineId>> lookedUpToParentMap = new HashMap<>(); CollectionUtil.CollectionValueMap<Long, PipelineId> lookedUpToRelevantMap = CollectionUtil.collectionValMap(lookedUpToParentMap, new CollectionUtil.ArrayList<>()); for (Object[] relevantAndLookedUpId : ids) { lookedUpToRelevantMap.put((Long) relevantAndLookedUpId[LOOKED_UP_PIPELINE_ID], new PipelineId((String) relevantAndLookedUpId[RELEVANT_PIPELINE_NAME], (Long) relevantAndLookedUpId[RELEVANT_PIPELINE_ID])); } return CollectionUtil.reverse(lookedUpToParentMap); }
query.setLong(2, modificationId); query.setString(3, pipelineName); if (!query.list().isEmpty()) { match++; goCache.put(key, Boolean.TRUE);
List list = query.list(); assertTrue(list.size()==1); list = query.list(); assertTrue(list.size()==1); .addEntity( "sing", Single.class ); query.setString(0, "my id"); list = query.list(); assertTrue(list.size()==1); .addEntity( "sing", Single.class ); query.setString(0, "my id"); list = query.list();
@Test // @Ignore( "Support for locking on native-sql queries not yet implemented" ) public void testNativeSql() { Session session = openSession(); session.beginTransaction(); SQLQuery qry = session.createSQLQuery( "select * from door" ); qry.addRoot( "door", Door.class ); qry.getLockOptions().setLockMode( LockMode.PESSIMISTIC_WRITE ); qry.setFirstResult( 2 ); qry.setMaxResults( 2 ); @SuppressWarnings("unchecked") List results = qry.list(); assertEquals( 2, results.size() ); for ( Object door : results ) { assertEquals( LockMode.PESSIMISTIC_WRITE, session.getCurrentLockMode( door ) ); } session.getTransaction().commit(); session.close(); }
private void componentTest(String sql) throws SQLException { Componentizable c = setupComponentData(); Session session = openSession(); session.beginTransaction(); SQLQuery q = session.createSQLQuery( sql ).addEntity( "comp", Componentizable.class ); List list = q.list(); assertEquals( list.size(), 1 ); Componentizable co = (Componentizable) list.get(0); assertEquals( c.getNickName(), co.getNickName() ); assertEquals( c.getComponent().getName(), co.getComponent().getName() ); assertEquals( c.getComponent().getSubComponent().getSubName(), co.getComponent().getSubComponent().getSubName() ); session.delete( co ); session.getTransaction().commit(); session.close(); }
@Test public void testNativeQueryWithFormulaAttribute() { SQLFunction dateFunction = getDialect().getFunctions().get( "current_date" ); String dateFunctionRendered = dateFunction.render( null, java.util.Collections.EMPTY_LIST, sessionFactory() ); String sql = String.format( "select t.TABLE_NAME as {t.tableName}, %s as {t.daysOld} from ALL_TABLES t where t.TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered ); String sql2 = String.format( "select TABLE_NAME as t_name, %s as t_time from ALL_TABLES where TABLE_NAME = 'AUDIT_ACTIONS' ", dateFunctionRendered ); Session s = openSession(); s.beginTransaction(); s.createSQLQuery( sql ).addEntity( "t", AllTables.class ).list(); s.createSQLQuery( sql2 ).setResultSetMapping( "all" ).list(); SQLQuery q = s.createSQLQuery( sql2 ); q.addRoot( "t", AllTables.class ).addProperty( "tableName", "t_name" ).addProperty( "daysOld", "t_time" ); q.list(); s.getTransaction().commit(); s.close(); }
@Override public <E> List<E> getColumnsById(String schemaName, String joinTableName, String joinColumnName, String inverseJoinColumnName, Object parentId, Class columnJavaType) { StringBuffer sqlQuery = new StringBuffer(); sqlQuery.append("SELECT ").append(inverseJoinColumnName).append(" FROM ") .append(getFromClause(schemaName, joinTableName)).append(" WHERE ").append(joinColumnName).append("='") .append(parentId).append("'"); Session s = getSession(); SQLQuery query = s.createSQLQuery(sqlQuery.toString()); List<E> foreignKeys = new ArrayList<E>(); foreignKeys = query.list(); return foreignKeys; }
@SuppressWarnings("unchecked") public <T> T getBySql(String sql) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); List<T> ls = sqlQuery.list(); if (ls != null && ls.size() > 0) { return ls.get(0); } return null; }
@SuppressWarnings("unchecked") public <T> T getBySql(String sql, Map<String, Object> params) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); if (params != null && !params.isEmpty()) { for (String key : params.keySet()) { sqlQuery.setParameter(key, params.get(key)); } } List<T> ls = sqlQuery.list(); if (ls != null && ls.size() > 0) { return ls.get(0); } return null; }
@SuppressWarnings("unchecked") public <T> List<T> findBySql(String sql, int page, int rows, Class<T> clazz) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery.addEntity(clazz); return sqlQuery.list(); }
@SuppressWarnings("unchecked") public <T> List<T> findBySql(String sql, Class<T> clazz) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery.addEntity(clazz); return sqlQuery.list(); }
@SuppressWarnings("unchecked") public <T> List<T> findBySql(String sql, Map<String, Object> params, Class<T> clazz) { SQLQuery sqlQuery = this.getCurrentSession().createSQLQuery(sql); sqlQuery=getSqlQueryByMap(sqlQuery,params); sqlQuery.addEntity(clazz); return sqlQuery.list(); }
@Override public <T> List<T> find(String sql, Map<String,Object> params, Class<T> clazz) { SQLQuery query = this.getCurrentSession().createSQLQuery(sql); query=getSqlQueryByMap(query,params); query.setResultTransformer(Transformers.aliasToBean(clazz)); return query.list(); }
@Override public List findMapBySql(String sql, Object[] params, Type[] types, Class clazz) { SQLQuery query = this.getCurrentSession().createSQLQuery(sql); if (clazz != null) { query.setResultTransformer(Transformers.aliasToBean(clazz)); } else { query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); } query.setParameters(params, types); return query.list(); }