Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias);

Source Link

Document

Declare a scalar query result.

Usage

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();
}