@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(); }
addScalar("materialType", new StringType()). addScalar("fingerprint", new StringType()). setParameterList("ids", CollectionUtil.map(relevantToLookedUpMap.keySet(), PipelineId.MAP_ID)). list();
public SQLQuery getSqlQueryByMap(SQLQuery sqlQuery,Map<String,Object> params){ if (params != null && !params.isEmpty()) { for (String key : params.keySet()) { Object obj = params.get(key); if (obj instanceof Collection<?>) sqlQuery.setParameterList(key, (Collection<?>) obj); else if (obj instanceof Object[]) sqlQuery.setParameterList(key, (Object[]) obj); else sqlQuery.setParameter(key, obj); } } return sqlQuery; }
String[] values= {"You say \"How I can make it? We' ...\""}; String sql = "SELECT file.name as `NAME` FROM film WHERE film.name IN (:values)"; SQLQuery query = session.createSQLQuery(sql); query.setParameterList("values", values);
@Override public NativeQuery setParameterList(String name, Collection values) { queryInfo.getParameters().put(name, values); query.setParameterList(name, values); return this; }
@Override public NativeQuery setParameterList(String name, Object[] values) { queryInfo.getParameters().put(name, values); query.setParameterList(name, values); return this; }
String[] values= {"a", "b", "c", "d"}; SQLQuery query = getSession().createSQLQuery("SELECT * FROM data WHERE value IN (:values)"); query.setParameterList("values", values); List<Object[]> data = query.list();
public Object doInHibernate(Session session) throws HibernateException, SQLException { String sql="select max(id) as m_id,job_id from zeus_job_history where job_id in (:idList) group by job_id"; SQLQuery query=session.createSQLQuery(sql); query.setParameterList("idList", jobIds); List<Object[]> list= query.list(); List<Long> ids=new ArrayList<Long>(); for(Object[] o:list){ ids.add(((Number)o[0]).longValue()); } return ids; } });
@Override public void markHavingFlows(final Collection<Integer> ids) { getHibernateTemplate().executeWithNativeSession(session -> session.createSQLQuery("update node set hasFlows = true where nodeid in (:ids)") .setParameterList("ids", ids) .executeUpdate()); }
protected SQLQuery getQuery(String queryString, Map<String, Object> where, int start, int limit, boolean returnMap) { Session session = this.getSession(); SQLQuery query = null; if (start > -1 && limit > 0) { query = session.createSQLQuery(queryString); query.setFirstResult(start); query.setMaxResults(limit); } else { query = session.createSQLQuery(queryString); } if (where != null) { for (Entry<String, Object> entry : where.entrySet()) { Object value = entry.getValue(); String key = entry.getKey(); if (value instanceof Collection) { query.setParameterList(key, (Collection) value); } else if (value instanceof Object[]) { query.setParameterList(key, (Object[]) value); } else { query.setParameter(key, value); } } } if (returnMap) { query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); } return query; }
public Object doInHibernate(Session session) throws HibernateException, SQLException { if(ids==null || ids.isEmpty()){ return Collections.emptyList(); } String sql="select id,job_id,start_time,end_time,execute_host,status,trigger_type,illustrate,operator,properties from zeus_job_history where id in (:ids)"; SQLQuery query=session.createSQLQuery(sql); query.setParameterList("ids", ids); List<Object[]> list= query.list(); List<JobHistory> result=new ArrayList<JobHistory>(); for(Object[] o:list){ JobHistoryPersistence p=new JobHistoryPersistence(); p.setId(((Number)o[0]).longValue()); p.setJobId(((Number)o[1]).longValue()); p.setStartTime((Date)o[2]); p.setEndTime((Date)o[3]); p.setExecuteHost((String)o[4]); p.setStatus((String)o[5]); p.setTriggerType((Integer)o[6]); p.setIllustrate((String)o[7]); p.setOperator((String)o[8]); p.setProperties((String)o[9]); result.add(PersistenceAndBeanConvert.convert(p)); } return result; } });
" and cn2.categoryid in (:columnCategories)" .setParameterList("rowCategories", rowCategories) .setParameterList("columnCategories", columnCategories) .setResultTransformer(new ResultTransformer() {