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); }
List<Object[]> allModifications = query. addEntity("mods", Modification.class). addScalar("pmrPipelineId", new LongType()). addScalar("pmrPipelineName", new StringType()). addScalar("materialType", new StringType()). addScalar("fingerprint", new StringType()). setParameterList("ids", CollectionUtil.map(relevantToLookedUpMap.keySet(), PipelineId.MAP_ID)). 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(); }
sqlQuery.addScalar( "orgid", LongType.INSTANCE ) .addScalar( "name", StringType.INSTANCE ) .addScalar( "empid", LongType.INSTANCE ) .addScalar( "employee", LongType.INSTANCE ) .addScalar( "startDate", TimestampType.INSTANCE ) .addScalar( "endDate", TimestampType.INSTANCE ) .addScalar( "regionCode", StringType.INSTANCE ) .addScalar( "empId", LongType.INSTANCE ) .addScalar( "AMOUNT", FloatType.INSTANCE ) .addScalar( "CURRENCY", StringType.INSTANCE );
@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()); }
public Long latestModificationRunByPipeline(final CaseInsensitiveString pipelineName, final Material material) { final long materialId = findMaterialInstance(material).getId(); String key = cacheKeyForLatestPmrForPipelineKey(materialId, pipelineName.toLower()); Long modificationId = (Long) goCache.get(key); if (modificationId == null) { synchronized (key) { modificationId = (Long) goCache.get(key); if (modificationId == null) { modificationId = (Long) getHibernateTemplate().execute((HibernateCallback) session -> { SQLQuery sqlQuery = session.createSQLQuery("SELECT MAX(pmr.toRevisionId) toRevisionId " + "FROM (SELECT torevisionid, pipelineid FROM pipelineMaterialRevisions WHERE materialid = :material_id) AS pmr\n" + "INNER JOIN pipelines p ON ( p.name = :pipeline_name AND p.id = pmr.pipelineId)"); sqlQuery.setParameter("material_id", materialId); sqlQuery.setParameter("pipeline_name", pipelineName.toString()); sqlQuery.addScalar("toRevisionId", new LongType()); return sqlQuery.uniqueResult(); }); if (modificationId == null) { modificationId = -1L; } goCache.put(key, modificationId); } } } return modificationId; }
@Override public Map<Object, Object> getPatientProgramAttributeByAttributeName(List<Integer> patientIds, String attributeName) { Map<Object, Object> patientProgramAttributes = new HashMap<>(); if (patientIds.isEmpty() || attributeName == null) { return patientProgramAttributes; } String commaSeperatedPatientIds = StringUtils.join(patientIds, ","); List list = sessionFactory.getCurrentSession().createSQLQuery( "SELECT p.patient_id as person_id, " + " concat('{',group_concat(DISTINCT (coalesce(concat('\"',ppt.name,'\":\"', COALESCE (cn.name, ppa.value_reference),'\"'))) SEPARATOR ','),'}') AS patientProgramAttributeValue " + " from patient p " + " join patient_program pp on p.patient_id = pp.patient_id and p.patient_id in (" + commaSeperatedPatientIds + ")" + " join patient_program_attribute ppa on pp.patient_program_id = ppa.patient_program_id and ppa.voided=0" + " join program_attribute_type ppt on ppa.attribute_type_id = ppt.program_attribute_type_id and ppt.name ='" + attributeName + "' "+ " LEFT OUTER JOIN concept_name cn on ppa.value_reference = cn.concept_id and cn.concept_name_type= 'FULLY_SPECIFIED' and cn.voided=0 and ppt.datatype like '%ConceptDataType%'" + " group by p.patient_id") .addScalar("person_id", StandardBasicTypes.INTEGER) .addScalar("patientProgramAttributeValue", StandardBasicTypes.STRING) .list(); for (Object o : list) { Object[] arr = (Object[]) o; patientProgramAttributes.put(arr[0], arr[1]); } return patientProgramAttributes; } }
.addScalar("password", StandardBasicTypes.STRING).setInteger(0, candidateUser.getUserId()) .uniqueResult(); String saltOnRecord = (String) session.createSQLQuery("select salt from users where user_id = ?").addScalar( "salt", StandardBasicTypes.STRING).setInteger(0, candidateUser.getUserId()).uniqueResult();
ManyToOne.class))) q.addScalar(name != null ? name : r);
@SuppressWarnings("deprecation") private Query prepareQueryFind(SQLQuery query) { return query .addScalar("idAlias", Hibernate.INTEGER) .addScalar("nameAlias", Hibernate.STRING) .setResultTransformer(Transformers.aliasToBean(Pool.class)); }
String queryStr = "SELECT T.MDATE FROM MTABLE T"; Query query = em.createNativeQuery(queryStr); SQLQuery sqlQuery = (SQLQuery) ((HibernateQuery) query).getHibernateQuery(); sqlQuery.addScalar("MDATE", Hibernate.TIMESTAMP); List<Object[]> resultList = query.getResultList();
SQLQuery hibernateQuery = getSession().createSQLQuery(sqlQuery); Set<String> columns = columnDataTypes.keySet(); for (String column : columns) { if (columnDataTypes.get(column).equals(SqlType.DATE.name())) { hibernateQuery.addScalar(column, new TimestampType()); } else { hibernateQuery.addScalar(column); } }
protected void addScalarsFromDTO(SQLQuery query, Class<? extends BaseDTO> dto) { for (Field field : dto.getDeclaredFields()) { if (field.getAnnotation(STypeIndexed.class).returnColumn()) { if (BigDecimal.class.isAssignableFrom(field.getType())) { query.addScalar(field.getName(), StandardBasicTypes.BIG_DECIMAL); } else { query.addScalar(field.getName()); } } } }
@Override public void addScalar(Query query, String alias, Class<?> type) { if (query instanceof HibernateQuery) { org.hibernate.Query hibernateQuery = ((HibernateQuery) query).getHibernateQuery(); if (hibernateQuery instanceof SQLQuery) { ((SQLQuery) hibernateQuery).addScalar(alias); } } }
private void setReturnType(String builtQuery, SQLQuery sqlQuery) { if (isCountQuery(builtQuery)) { sqlQuery.addScalar("count", LongType.INSTANCE); } else { String hqlAlias = classAliasMappings.get(entityType.getName()); String sqlAlias = hqlToSqlAlias.containsKey(hqlAlias) ? hqlAlias.replace("user", "user_") : hqlAlias; Class<? extends PersistentObject> entityClass = interfaceToClassMapping.get(entityType.getName()); sqlQuery.addEntity(sqlAlias, entityClass.getName()); } }
String sql = "select count(*) as result from table"; BigDecimal count = (BigDecimal) ht.execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException { SQLQuery query = session.createSQLQuery(sql); // Add scalar to avoid bug in Hibernate query cache. query.addScalar("result", Hibernate.BIG_DECIMAL); return query.uniqueResult(); } });
public String computeGeomFromLongitudeLatitude() { Session session = getSession(); SQLQuery q = session .createSQLQuery( "select ST_GeometryFromText('POINT(10 10)',4326) as geom"); q.addScalar("geom", StringType.INSTANCE); String result = (String) q.list().get(0); closeSession(); return result; }
private SQLQuery createSqlUsingId(String sql, Object... params) { SQLQuery sqlQuery = getCurrentSession().createSQLQuery(sql).addScalar("id", LongType.INSTANCE); setQueryParameter(sqlQuery, params); return sqlQuery; }
hibernateTemplate.execute(new HibernateCallback<List>() { public String doInHibernate(Session s) throws HibernateException, SQLException { SQLQuery sql=s.createSQLQuery("select course_id from student_course where student_id=?"); sql.setParameter(0, adventureId); sql.addScalar(studentID); return sql.list(); } });