List of usage examples for org.hibernate SQLQuery addScalar
SQLQuery<T> addScalar(String columnAlias);
From source file:org.generationcp.middleware.dao.GermplasmDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public Map<Integer, String[]> getParentsInfoByGIDList(final List<Integer> gidList) { try {//from w w w .j a v a 2 s. com final Map<Integer, String[]> pedigreeMap = new HashMap<>(); final SQLQuery query = this.getSession() .createSQLQuery(Germplasm.GET_PREFERRED_NAME_AND_PARENT_FOR_A_GID_LIST); query.setParameterList("gidList", gidList); query.addScalar("gid"); query.addScalar("pedigree"); query.addScalar("nval"); final List<Object[]> results = query.list(); for (final Object[] result : results) { pedigreeMap.put((Integer) result[0], new String[] { (String) result[1], (String) result[2] }); } if (gidList.contains(0)) { pedigreeMap.put(0, new String[] { Name.UNKNOWN, Name.UNKNOWN }); } return pedigreeMap; } catch (final HibernateException e) { final String message = "Error with getPedigreeByGIDList(GIDS=" + gidList + ") : " + e.getMessage(); GermplasmDAO.LOG.error(message, e); throw new MiddlewareQueryException(message, e); } }
From source file:org.generationcp.middleware.dao.GermplasmDAO.java
License:Open Source License
public List<GermplasmDTO> getGermplasmDTOList(final GermplasmSearchRequestDto germplasmSearchRequestDTO, final Integer page, final Integer pageSize) { try {/*ww w .java 2 s .c o m*/ String queryString = "SELECT convert(g.gid, char) AS germplasmDbId, reference.btable AS germplasmPUI, " // + " (SELECT n.nval FROM names n " // + " INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'ACCNO' AND u.fldno = n.ntype)" // + " WHERE (n.gid = g.gid) LIMIT 1) AS accessionNumber, " // + " STR_TO_DATE (convert(g.gdate,char), '%Y%m%d') AS acquisitionDate," // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'ORI_COUN' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS countryOfOriginCode, " // + " (SELECT n.nval FROM names n WHERE n.nstat = 1 AND n.gid = g.gid LIMIT 1) AS germplasmName," // + " (SELECT n.nval FROM names n " // + " INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'GENUS' AND u.fldno = n.ntype)" // + " WHERE (n.gid = g.gid) LIMIT 1) AS genus," // + " (SELECT ld.source FROM listdata ld" // + " WHERE ld.gid = g.gid LIMIT 1) AS germplasmSeedSource, " // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPNAM' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS species, " // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPAUTH' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS speciesAuthority, " // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SUBTAX' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS subtaxa, " // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'STAUTH' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS subtaxaAuthority, " // + " (SELECT a.aval FROM atributs a " // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'PROGM' AND u.fldno = a.atype)" // + " WHERE (a.gid = g.gid) LIMIT 1) AS instituteCode, " // + " m.mname as breedingMethodDbId " // + " FROM germplsm g " // + " LEFT JOIN reflinks reference ON reference.brefid = g.gref " // + " LEFT join methods m ON g.methn = m.mid " // + " WHERE g.deleted = 0" // + " AND g.grplce = 0"; // if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) { queryString = queryString + " AND (SELECT n.nval" // + " FROM names n" // + " WHERE n.nstat = 1 AND n.gid = g.gid" // + " LIMIT 1) like :likeCondition "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) { queryString = queryString + " AND EXISTS (SELECT 1" // + " FROM names n" // + " INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'ACCNO' AND u.fldno = n.ntype)" // + " WHERE n.gid = g.gid AND n.nval IN (:accessionNumbers)) "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) { queryString = queryString + " AND EXISTS (SELECT 1" // + " FROM atributs a" // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'CROPNM' AND u.fldno = a.atype)" // + " WHERE a.gid = g.gid AND a.aval IN (:commonCropNames)) "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) { queryString = queryString + " AND g.gid IN (:germplasmDbIds) "; } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) { queryString = queryString + " AND EXISTS (SELECT 1" // + " FROM names n" // + " INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'GENUS' AND u.fldno = n.ntype)" // + " WHERE n.gid = g.gid AND n.nval IN (:germplasmGenus)) "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) { queryString = queryString + " AND EXISTS (SELECT 1" // + " FROM names n" // + " WHERE n.gid = g.gid AND n.nval IN (:germplasmNames)) "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) { queryString = queryString + " AND reference.btable IN (:germplasmPUIs) "; // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) { queryString = queryString + " AND EXISTS (SELECT 1" // + " FROM atributs a" // + " INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPNAM' AND u.fldno = a.atype)" // + " WHERE a.gid = g.gid AND a.aval IN (:germplasmSpecies)) "; // } final SQLQuery sqlQuery = this.getSession().createSQLQuery(queryString); sqlQuery.addScalar("germplasmDbId").addScalar("germplasmPUI").addScalar("accessionNumber") .addScalar("acquisitionDate").addScalar("countryOfOriginCode").addScalar("germplasmName") .addScalar("genus").addScalar("germplasmSeedSource").addScalar("species") .addScalar("speciesAuthority").addScalar("subtaxa").addScalar("subtaxaAuthority") .addScalar("instituteCode").addScalar("breedingMethodDbId") // .setResultTransformer(new AliasToBeanResultTransformer(GermplasmDTO.class)); if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) { sqlQuery.setParameter("likeCondition", "%" + germplasmSearchRequestDTO.getPreferredName() + "%"); // } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) { sqlQuery.setParameterList("accessionNumbers", germplasmSearchRequestDTO.getAccessionNumbers()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) { sqlQuery.setParameterList("commonCropNames", germplasmSearchRequestDTO.getCommonCropNames()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) { sqlQuery.setParameterList("germplasmDbIds", germplasmSearchRequestDTO.getGermplasmDbIds()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) { sqlQuery.setParameterList("germplasmGenus", germplasmSearchRequestDTO.getGermplasmGenus()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) { sqlQuery.setParameterList("germplasmNames", germplasmSearchRequestDTO.getGermplasmNames()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) { sqlQuery.setParameterList("germplasmPUIs", germplasmSearchRequestDTO.getGermplasmPUIs()); } if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) { sqlQuery.setParameterList("germplasmSpecies", germplasmSearchRequestDTO.getGermplasmSpecies()); } if (page != null && pageSize != null) { sqlQuery.setFirstResult(pageSize * page); sqlQuery.setMaxResults(pageSize); } final List<GermplasmDTO> germplasmDTOList = sqlQuery.list(); return germplasmDTOList; } catch (final HibernateException e) { final String message = "Error with getGermplasmDTOList" + e.getMessage(); GermplasmDAO.LOG.error(message, e); throw new MiddlewareQueryException(message, e); } }
From source file:org.generationcp.middleware.dao.GermplasmListDataDAO.java
License:Open Source License
/** * This will return all items of a cross list along with data of parents. * Note that we're getting the name of the parents from its preferred name which is indicated by name record with nstat = 1 *//*from www .j a v a2s. co m*/ public List<GermplasmListData> retrieveGermplasmListDataWithImmediateParents(final Integer listID) { Preconditions.checkNotNull(listID, "List id passed cannot be null."); final List<GermplasmListData> germplasmListData = new ArrayList<>(); try { final String queryStr = "select lp.lrecid as lrecid, lp.entryid as entryid, lp.desig as desig, lp.grpname as grpname, " + " if(g.gpid1 = 0, '" + Name.UNKNOWN + "', femaleParentName.nval) as fnval, g.gpid1 as fpgid, if(g.gpid2 = 0, '" + Name.UNKNOWN + "', maleParentName.nval) as mnval, g.gpid2 as mpgid, " + " g.gid as gid, lp.source as source, m.mname as mname, " + " if(g.gpid2 = 0, '" + Name.UNKNOWN + "', (select nMale.grpName from listdata nMale where nMale.gid = maleParentName.gid limit 1)) as malePedigree, " + " if(g.gpid1 = 0, '" + Name.UNKNOWN + "', (select nFemale.grpName from listdata nFemale where nFemale.gid = femaleParentName.gid limit 1)) as femalePedigree " + "from listdata lp inner join germplsm g on lp.gid = g.gid " + "left outer join names maleParentName on g.gpid2 = maleParentName.gid and maleParentName.nstat = :preferredNameNstat " + "left outer join names femaleParentName on g.gpid1 = femaleParentName.gid and femaleParentName.nstat = :preferredNameNstat " + "left outer join methods m on m.mid = g.methn " + "where lp.listid = :listId group by entryid"; final SQLQuery query = this.getSession().createSQLQuery(queryStr); query.setParameter("listId", listID); query.setParameter("preferredNameNstat", Name.NSTAT_PREFERRED_NAME); query.addScalar("lrecid"); query.addScalar("entryid"); query.addScalar("desig"); query.addScalar("grpname"); query.addScalar("fnval"); query.addScalar("fpgid"); query.addScalar("mnval"); query.addScalar("mpgid"); query.addScalar("gid"); query.addScalar("source"); query.addScalar("mname"); query.addScalar("malePedigree"); query.addScalar("femalePedigree"); this.createCrossListDataRows(germplasmListData, query); } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error in retrieveCrossListData=" + listID + " in GermplasmListDataDAO: " + e.getMessage(), e); } return germplasmListData; }
From source file:org.generationcp.middleware.dao.ProjectDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Project> getProjectsByUser(final WorkbenchUser user, final String cropName) { final List<Project> projects = new ArrayList<>(); try {/* ww w .jav a2 s . com*/ if (user != null) { final SQLQuery query = this.getSession().createSQLQuery(GET_PROJECTS_BY_USER_ID); query.setParameter("userId", user.getUserid()); query.setParameter("cropName", cropName); query.addScalar("project_id").addScalar("project_uuid").addScalar("project_name") .addScalar("start_date").addScalar("user_id").addScalar("crop_type") .addScalar("last_open_date"); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = query.list(); for (final Map<String, Object> result : results) { final Long project_id = Long.valueOf((Integer) result.get("project_id")); final String project_uuid = (String) result.get("project_uuid"); final String project_name = (String) result.get("project_name"); final Date start_date = (Date) result.get("start_date"); final Integer user_id = (Integer) result.get("user_id"); final CropType crop_type = new CropType((String) result.get("crop_type")); final Date last_open_date = (Date) result.get("last_open_date"); final Project u = new Project(project_id, project_uuid, project_name, start_date, user_id, crop_type, last_open_date); projects.add(u); } return projects; } } catch (final HibernateException e) { throw new MiddlewareQueryException("Error in getProjectsByUser(user=" + user + ") query from ProjectUserInfoDao: " + e.getMessage(), e); } return new ArrayList<>(); }
From source file:org.openmrs.module.muzima.api.db.hibernate.HibernateCoreDao.java
License:Open Source License
@Override @Transactional(readOnly = true)// www .ja v a2s . c o m public Number countPatients(final String cohortUuid, final Date syncDate) throws DAOException { String hqlQuery = " select count(p.patient_id) as total from patient p, cohort c, cohort_member m " + " where c.uuid = :uuid and p.patient_id = m.patient_id " + " and c.cohort_id = m.cohort_id " + " and c.voided = false and p.voided = false "; if (syncDate != null) { hqlQuery = hqlQuery + " and ( (c.date_created is not null and c.date_changed is null and c.date_voided is null and c.date_created >= :syncDate) or " + " (c.date_created is not null and c.date_changed is not null and c.date_voided is null and c.date_changed >= :syncDate) or " + " (c.date_created is not null and c.date_changed is not null and c.date_voided is not null and c.date_voided >= :syncDate) ) " + " and ( (p.date_created is not null and p.date_changed is null and p.date_voided is null and p.date_created >= :syncDate) or " + " (p.date_created is not null and p.date_changed is not null and p.date_voided is null and p.date_changed >= :syncDate) or " + " (p.date_created is not null and p.date_changed is not null and p.date_voided is not null and p.date_voided >= :syncDate) ) "; } SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(hqlQuery); query.addScalar("total"); query.setParameter("uuid", cohortUuid); if (syncDate != null) { query.setParameter("syncDate", syncDate); } return (Number) query.uniqueResult(); }
From source file:org.openmrs.module.nbs.datasource.HibernateLogicProviderDAO.java
License:Open Source License
public List<Integer> getAllProviders(Integer patientId, ArrayList<Integer> encounterList) { String encounterRestrictions = ""; if (encounterList != null && encounterList.size() == 0) { return null; }/* w ww. j a va2 s . co m*/ if (encounterList != null) { encounterRestrictions = " and encounter_id in (:encounterList)"; } String sql = "select distinct value_numeric as provider_id from obs where encounter_id in ( " + "select encounter_id from encounter where patient_id=?" + encounterRestrictions + ") " + "and concept_id=? and value_numeric is not null"; ConceptService conceptService = Context.getConceptService(); Concept providerUserIdConcept = conceptService.getConceptByName("PROVIDER_USER_ID"); if (providerUserIdConcept == null) { return null; } SQLQuery qry = this.sessionFactory.getCurrentSession().createSQLQuery(sql); qry.setInteger(0, patientId); qry.setInteger(1, providerUserIdConcept.getConceptId()); if (encounterList != null) { qry.setParameterList("encounterList", encounterList); } qry.addScalar("provider_id"); try { List<Double> tmpList = qry.list(); List<Integer> resultList = new ArrayList(); for (Double item : tmpList) { resultList.add(item.intValue()); } return resultList; } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:org.oscarehr.common.dao.DemographicDao.java
License:Open Source License
public Set getArchiveDemographicByProgramOptimized(int programId, Date dt, Date defdt) { Set<Demographic> archivedClients = new java.util.LinkedHashSet<Demographic>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String sqlQuery = "select distinct d.demographic_no,d.first_name,d.last_name,(select count(*) from admission a where client_id=d.demographic_no and admission_status='current' and program_id=" + programId + " and admission_date<='" + sdf.format(dt) + "') as is_active from admission a,demographic d where a.client_id=d.demographic_no and (d.patient_status='AC' or d.patient_status='' or d.patient_status=null) and program_id=" + programId//from w w w . j av a2s . co m + " and (d.anonymous is null or d.anonymous != 'one-time-anonymous') ORDER BY d.last_name,d.first_name"; SQLQuery q = this.getSession().createSQLQuery(sqlQuery); q.addScalar("d.demographic_no"); q.addScalar("d.first_name"); q.addScalar("d.last_name"); q.addScalar("is_active"); List results = q.list(); Iterator iter = results.iterator(); while (iter.hasNext()) { Object[] result = (Object[]) iter.next(); if (((BigInteger) result[3]).intValue() == 0) { Demographic d = new Demographic(); d.setDemographicNo((Integer) result[0]); d.setFirstName((String) result[1]); d.setLastName((String) result[2]); archivedClients.add(d); } } return archivedClients; }
From source file:org.yamj.core.api.model.builder.SqlScalars.java
License:Open Source License
/** * Add the scalars to the query/*from w ww . j av a 2 s. c om*/ * * @param query */ public void populateScalars(SQLQuery query) { if (scalars != null && !scalars.isEmpty()) { for (Map.Entry<String, BasicType> entry : scalars.entrySet()) { if (entry.getValue() == null) { // Use the default scalar for that entry query.addScalar(entry.getKey()); } else { // Use the passed scalar type query.addScalar(entry.getKey(), entry.getValue()); } } } }
From source file:org.yamj.core.api.model.SqlScalars.java
License:Open Source License
/** * Add the scalars to the query// w w w.j av a 2s .c om * * @param query */ public void populateScalars(SQLQuery query) { if (scalars != null && scalars.size() > 0) { for (Map.Entry<String, BasicType> entry : scalars.entrySet()) { if (entry.getValue() == null) { // Use the default scalar for that entry query.addScalar(entry.getKey()); } else { // Use the passed scalar type query.addScalar(entry.getKey(), entry.getValue()); } } } }
From source file:ubic.gemma.persistence.service.expression.designElement.CompositeSequenceDaoImpl.java
License:Apache License
@Override public Collection<Object[]> getRawSummary(Collection<CompositeSequence> compositeSequences) { if (compositeSequences == null || compositeSequences.size() == 0) return null; StringBuilder buf = new StringBuilder(); for (Iterator<CompositeSequence> it = compositeSequences.iterator(); it.hasNext();) { CompositeSequence compositeSequence = it.next(); if (compositeSequence == null || compositeSequence.getId() == null) { throw new IllegalArgumentException(); }// www.ja v a2 s .co m long id = compositeSequence.getId(); buf.append(id); if (it.hasNext()) buf.append(","); } // This uses the 'full' query, assuming that this list isn't too big. String nativeQueryString = CompositeSequenceDaoImpl.nativeBaseSummaryQueryString + " WHERE cs.ID IN (" + buf.toString() + ")"; org.hibernate.SQLQuery queryObject = this.getSessionFactory().getCurrentSession() .createSQLQuery(nativeQueryString); queryObject.addScalar("deID").addScalar("deName").addScalar("bsName").addScalar("bsdbacc") .addScalar("ssrid").addScalar("gpId").addScalar("gpName").addScalar("gpNcbi").addScalar("geneid") .addScalar("gId").addScalar("gSymbol").addScalar("gNcbi").addScalar("adShortName") .addScalar("adId"); queryObject.addScalar("chrom").addScalar("tgst").addScalar("tgend").addScalar("tgstarts").addScalar("bsId"); queryObject.addScalar("deDesc", StandardBasicTypes.TEXT); // must do this for CLOB or Hibernate is unhappy queryObject.addScalar("adName"); queryObject.setMaxResults(CompositeSequenceDaoImpl.MAX_CS_RECORDS); //noinspection unchecked return queryObject.list(); }