Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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.dms.ExperimentDao.java

License:Open Source License

private void addScalarForTraits(final List<MeasurementVariableDto> selectionMethodsAndTraits,
        final SQLQuery createSQLQuery, final Boolean addStatus) {
    for (final MeasurementVariableDto measurementVariable : selectionMethodsAndTraits) {
        createSQLQuery.addScalar(measurementVariable.getName()); // Value
        createSQLQuery.addScalar(measurementVariable.getName() + "_PhenotypeId", new IntegerType());
        if (addStatus) {
            createSQLQuery.addScalar(measurementVariable.getName() + "_Status");
        }//from ww w.ja  va 2  s .  com
        createSQLQuery.addScalar(measurementVariable.getName() + "_CvalueId", new IntegerType());
        createSQLQuery.addScalar(measurementVariable.getName() + "_DraftValue");
        createSQLQuery.addScalar(measurementVariable.getName() + "_DraftCvalueId", new IntegerType());
    }
}

From source file:org.generationcp.middleware.dao.dms.GeolocationDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<TrialEnvironment> getAllTrialEnvironments() {
    final List<TrialEnvironment> environments = new ArrayList<>();
    try {//from w w w.jav a2  s .c o m
        final SQLQuery query = this.getSession().createSQLQuery(GeolocationDao.GET_ALL_ENVIRONMENTS_QUERY);
        query.addScalar(GeolocationDao.ENVT_ID);
        query.addScalar(GeolocationDao.LOCATION_NAME);
        query.addScalar(GeolocationDao.PROVINCE_NAME);
        query.addScalar(GeolocationDao.ISOABBR);
        query.addScalar(GeolocationDao.PROJECT_ID);
        query.addScalar("name");
        query.addScalar(GeolocationDao.LOCATION_ID);
        query.addScalar(GeolocationDao.DESCRIPTION);
        final List<Object[]> list = query.list();
        for (final Object[] row : list) {
            // otherwise it's invalid data and should not be included
            if (NumberUtils.isNumber((String) row[6])) {
                environments.add(new TrialEnvironment((Integer) row[0],
                        new LocationDto(Integer.valueOf(row[6].toString()), (String) row[1], (String) row[2],
                                (String) row[3]),
                        new StudyReference((Integer) row[4], (String) row[5], (String) row[7])));
            }
        }

    } catch (final HibernateException e) {
        final String errorMessage = "Error at getAllTrialEnvironments at GeolocationDao: " + e.getMessage();
        GeolocationDao.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
    return environments;
}

From source file:org.generationcp.middleware.dao.dms.GeolocationDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public TrialEnvironments getEnvironmentsForTraits(final List<Integer> traitIds, final String programUUID) {
    final TrialEnvironments environments = new TrialEnvironments();
    try {/*ww  w  . j a  va2  s  . c  o  m*/
        final String sql = "SELECT DISTINCT gp.nd_geolocation_id as envtId, l.lname as locationName, prov.lname as provinceName, c.isoabbr, p.project_id, p.name, gp.value as locationId"
                + " FROM nd_experiment e " + " INNER JOIN project ds ON ds.project_id = e.project_id "
                + " INNER JOIN project p ON p.project_id = ds.study_id "
                + " INNER JOIN phenotype ph ON ph.nd_experiment_id = e.nd_experiment_id"
                + " INNER JOIN nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id AND gp.type_id = "
                + TermId.LOCATION_ID.getId() + " LEFT JOIN location l ON l.locid = gp.value"
                + " LEFT JOIN location prov ON prov.locid = l.snl1id"
                + " LEFT JOIN cntry c ON c.cntryid = l.cntryid"
                + " WHERE ph.observable_id IN (:traitIds) AND p.program_uuid = :programUUID ;";
        final SQLQuery query = this.getSession().createSQLQuery(sql);
        query.addScalar(GeolocationDao.ENVT_ID);
        query.addScalar(GeolocationDao.LOCATION_NAME);
        query.addScalar(GeolocationDao.PROVINCE_NAME);
        query.addScalar(GeolocationDao.ISOABBR);
        query.addScalar(GeolocationDao.PROJECT_ID);
        query.addScalar("name");
        query.addScalar(GeolocationDao.LOCATION_ID);
        query.setParameterList("traitIds", traitIds);
        query.setParameter("programUUID", programUUID);
        final List<Object[]> list = query.list();
        for (final Object[] row : list) {
            // otherwise it's invalid data and should not be included
            if (NumberUtils.isNumber((String) row[6])) {
                environments.add(new TrialEnvironment(
                        (Integer) row[0], new LocationDto(Integer.valueOf(row[6].toString()), (String) row[1],
                                (String) row[2], (String) row[3]),
                        new StudyReference((Integer) row[4], (String) row[5])));
            }
        }

    } catch (final HibernateException e) {
        final String errorMessage = "Error at getEnvironmentForTraits at GeolocationDao: " + e.getMessage();
        GeolocationDao.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
    return environments;
}

From source file:org.generationcp.middleware.dao.dms.GeolocationPropertyDao.java

License:Open Source License

public Map<Integer, String> getGeoLocationPropertyByVariableId(final Integer datasetId,
        final Integer instanceDbId) {
    Preconditions.checkNotNull(datasetId);
    final String sql = "SELECT " + "    gp.type_id as variableId, " + "      gp.value as value " + "FROM "
            + "    nd_experiment e " + "        INNER JOIN "
            + "    nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id " + "WHERE "
            + "      e.project_id = :datasetId " + "      and e.nd_geolocation_id = :instanceDbId";

    final SQLQuery query = this.getSession().createSQLQuery(sql);
    query.addScalar("variableId").addScalar("value").setParameter("datasetId", datasetId)
            .setParameter("instanceDbId", instanceDbId);
    query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

    final List<Map<String, Object>> results = query.list();
    final Map<Integer, String> geoProperties = new HashMap<>();
    for (final Map<String, Object> result : results) {
        final Integer variableId = (Integer) result.get("variableId");
        final String value = (String) result.get("value");
        geoProperties.put(variableId, value);
    }/*w  w  w. j a v a  2s  .  c  o m*/
    return geoProperties;
}

From source file:org.generationcp.middleware.dao.dms.ProjectPropertyDao.java

License:Open Source License

private List<String> findPlotDatasetVariablesByTypesForStudy(final int studyIdentifier,
        final List<Integer> variableTypeIds) {
    final String variablesQuery = " SELECT cvt.name" + " FROM  projectprop pp "
            + " INNER JOIN project ds ON ds.project_id = pp.project_ID AND ds.dataset_type_id = "
            + DatasetTypeEnum.PLOT_DATA.getId() + " INNER JOIN cvterm cvt ON cvt.cvterm_id = pp.variable_id "
            + " WHERE pp.type_id IN (:variableTypeIds)" + " AND ds.study_id = :studyId";
    final SQLQuery sqlQuery = this.getSession().createSQLQuery(variablesQuery);
    sqlQuery.addScalar("name");
    sqlQuery.setParameter("studyId", studyIdentifier);
    sqlQuery.setParameterList("variableTypeIds", variableTypeIds);
    return sqlQuery.list();
}

From source file:org.generationcp.middleware.dao.dms.StockDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<StudyReference> getStudiesByGid(final int gid) {
    final List<StudyReference> studyReferences = new ArrayList<>();
    try {//from w  w w. java 2 s. c  o  m
        final SQLQuery query = this.getSession()
                .createSQLQuery("select distinct p.project_id, p.name, p.description, "
                        + "st.study_type_id, st.label, st.name as studyTypeName, st.visible, st.cvterm_id, p.program_uuid, p.locked, "
                        + "p.created_by " + "FROM stock s "
                        + "LEFT JOIN nd_experiment e on e.stock_id = s.stock_id "
                        + "LEFT JOIN project ds ON ds.project_id = e.project_id "
                        + "LEFT JOIN project p ON ds.study_id = p.project_id "
                        + "INNER JOIN study_type st ON p.study_type_id = st.study_type_id "
                        + " WHERE s.dbxref_id = " + gid + " AND p.deleted = 0");
        query.addScalar("project_id").addScalar("name").addScalar("description").addScalar("study_type_id")
                .addScalar("label").addScalar("studyTypeName").addScalar("visible").addScalar("cvterm_id")
                .addScalar("program_uuid").addScalar("locked").addScalar("created_by");

        final List<Object[]> results = query.list();
        for (final Object[] row : results) {
            if (row[0] == null) {
                continue;
            }
            final Integer studyTypeId = (Integer) row[3];
            final String label = (String) row[4];
            final String studyTypeName = (String) row[5];
            final boolean visible = ((Byte) row[6]) == 1;
            final Integer cvtermId = (Integer) row[7];
            final String programUUID = (String) row[8];
            final Boolean isLocked = (Boolean) row[9];
            final String ownerId = (String) row[10];

            final StudyTypeDto studyTypeDto = new StudyTypeDto(studyTypeId, label, studyTypeName, cvtermId,
                    visible);
            studyReferences.add(new StudyReference((Integer) row[0], (String) row[1], (String) row[2],
                    programUUID, studyTypeDto, isLocked, Integer.valueOf(ownerId)));
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error in getStudiesByGid=" + gid + StockDao.IN_STOCK_DAO + e.getMessage(), e);
    }
    return studyReferences;
}

From source file:org.generationcp.middleware.dao.gdms.AccMetadataSetDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Object> getUniqueAccMetaDatsetByDatasetId(String datasetId) throws MiddlewareQueryException {
    try {//w  w  w .  j  ava 2  s . c o  m
        SQLQuery query = this.getSession().createSQLQuery(AccMetadataSetDAO.GET_SAMPLE_ID_BY_DATASET);
        query.setParameter("datasetId", datasetId);
        query.addScalar("sample_id");
        query.addScalar("acc_sample_id");
        return query.list();
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getUniqueAccMetaDatasetByDatasetId(" + datasetId
                + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.dao.gdms.DatasetDAO.java

License:Open Source License

public List<Name> getGermplasmNamesByMarkerId(final Integer markerId) {
    try {//from  ww  w.j  ava  2  s.  c o  m
        if (markerId != null) {
            SQLQuery query = this.getSession().createSQLQuery(DatasetDAO.GET_GERMPLASM_NAMES_BY_MARKER_ID);
            query.addScalar("nid");
            query.addScalar("germplasmId");
            query.addScalar("typeId");
            query.addScalar("nstat");
            query.addScalar("userId");
            query.addScalar("nval");
            query.addScalar("locationId");
            query.addScalar("ndate");
            query.addScalar("referenceId");
            query.setParameter("markerId", markerId);
            query.setResultTransformer(Transformers.aliasToBean(Name.class));
            return query.list();
        }
        return new ArrayList<>();
    } catch (HibernateException e) {
        DatasetDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.dao.GermplasmDAO.java

License:Open Source License

public List<Germplasm> getProgenitorsByGIDWithPrefName(final Integer gid) {
    Preconditions.checkNotNull(gid);//  w w w .j  ava2 s  .  com
    try {
        final List<Germplasm> progenitors = new ArrayList<>();

        final SQLQuery query = this.getSession()
                .createSQLQuery(Germplasm.GET_PROGENITORS_BY_GIDS_WITH_PREF_NAME);
        query.addScalar("gid");
        query.addEntity("g", Germplasm.class);
        query.addEntity("n", Name.class);
        query.addScalar("malePedigree");
        query.setParameterList("gidList", Lists.newArrayList(gid));
        final List<Object[]> results = query.list();
        for (final Object[] result : results) {
            final Germplasm germplasm = (Germplasm) result[1];
            final Name prefName = (Name) result[2];
            germplasm.setPreferredName(prefName);
            progenitors.add(germplasm);
        }

        return progenitors;
    } catch (final HibernateException e) {
        final String errorMessage = "Error with getProgenitorsByGIDWithPrefName(gid=" + gid
                + GermplasmDAO.QUERY_FROM_GERMPLASM + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
}

From source file:org.generationcp.middleware.dao.GermplasmDAO.java

License:Open Source License

public Map<Integer, List<GermplasmParent>> getParentsFromProgenitorsForGIDsMap(final List<Integer> gids) {
    Preconditions.checkNotNull(gids);/*  www . j a  v  a 2s . c o  m*/
    Preconditions.checkArgument(!gids.isEmpty());

    final Map<Integer, List<GermplasmParent>> map = new HashMap<>();
    try {
        final SQLQuery query = this.getSession()
                .createSQLQuery(Germplasm.GET_PROGENITORS_BY_GIDS_WITH_PREF_NAME);
        query.addScalar("gid");
        query.addEntity("g", Germplasm.class);
        query.addEntity("n", Name.class);
        query.addScalar("malePedigree");
        query.setParameterList("gidList", gids);
        final List<Object[]> results = query.list();

        List<GermplasmParent> progenitors = new ArrayList<>();
        Integer lastGid = 0;
        for (final Object[] result : results) {
            final Integer crossGid = (Integer) result[0];
            if (lastGid == 0) {
                lastGid = crossGid;
            }
            if (!crossGid.equals(lastGid)) {
                map.put(lastGid, progenitors);
                lastGid = crossGid;
                progenitors = new ArrayList<>();
            }
            final Germplasm germplasm = (Germplasm) result[1];
            final Name prefName = (Name) result[2];
            final String pedigree = (String) result[3];
            germplasm.setPreferredName(prefName);
            progenitors.add(new GermplasmParent(germplasm.getGid(), prefName.getNval(), pedigree));
        }
        // Set last cross GID to map
        map.put(lastGid, progenitors);

        return map;
    } catch (final HibernateException e) {
        final String errorMessage = "Error with getProgenitorsForGIDsMap(gids=" + gids
                + GermplasmDAO.QUERY_FROM_GERMPLASM + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
}