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, Type type);

Source Link

Document

Declare a scalar query result.

Usage

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<FieldMapInfo> getAllFieldMapsInBlockByTrialInstanceId(final int datasetId, final int geolocationId,
        final Integer blockId) {
    List<FieldMapInfo> fieldmaps = new ArrayList<>();

    try {//from  w  ww . j a  v  a2 s  .c o m
        final String order = geolocationId > 0 ? "ASC" : "DESC";
        final StringBuilder sql = new StringBuilder().append(" SELECT ").append(" p.project_id AS datasetId ")
                .append(" , p.name AS datasetName ").append(" , st.name AS studyName ")
                .append(" , e.nd_geolocation_id AS geolocationId ").append(" , site.value AS siteName ")
                .append(" , siteId.value AS siteId").append(" , e.nd_experiment_id AS experimentId ")
                .append(" , s.uniqueName AS entryNumber ").append(" , s.name AS germplasmName ")
                .append(" , epropRep.value AS rep ").append(" , epropPlot.value AS plotNo ")
                .append(" , row.value AS row ").append(" , col.value AS col ")
                .append(" , blk.value AS blockId ").append(" , st.project_id AS studyId ")
                .append(" , geo.description AS trialInstance ").append(" , s.dbxref_id AS gid ")
                .append(" , st.start_date as startDate ").append(" , gpSeason.value as season ")
                .append(" , epropBlock.value AS blockNo ").append(" , e.obs_unit_id as obsUnitId ")
                .append(" FROM ").append("  nd_geolocationprop blk ")
                .append("  INNER JOIN nd_experiment e ON e.nd_geolocation_id = blk.nd_geolocation_id ")
                .append("  INNER JOIN nd_geolocation geo ON geo.nd_geolocation_id = e.nd_geolocation_id ")
                .append("  INNER JOIN project p ON p.project_id = e.project_id ")
                .append("  INNER JOIN project st ON st.project_id = p.study_id ")
                .append("  INNER JOIN stock s ON e.stock_id = s.stock_id ")
                .append("  LEFT JOIN nd_experimentprop epropRep ON epropRep.nd_experiment_id = e.nd_experiment_id ")
                .append("    AND epropRep.type_id = ").append(TermId.REP_NO.getId())
                .append(" AND epropRep.value <> '' ")
                .append("  LEFT JOIN nd_experimentprop epropBlock ON epropBlock.nd_experiment_id = e.nd_experiment_id ")
                .append("    AND epropBlock.type_id = ").append(TermId.BLOCK_NO.getId())
                .append(" AND epropBlock.value <> '' ")
                .append("  INNER JOIN nd_experimentprop epropPlot ON epropPlot.nd_experiment_id = e.nd_experiment_id ")
                .append("    AND epropPlot.type_id IN (").append(TermId.PLOT_NO.getId()).append(", ")
                .append(TermId.PLOT_NNO.getId()).append(") ").append(" AND epropPlot.value <> '' ")
                .append("  LEFT JOIN nd_geolocationprop site ON site.nd_geolocation_id = e.nd_geolocation_id ")
                .append("    AND site.type_id = ").append(TermId.TRIAL_LOCATION.getId())
                .append("  LEFT JOIN nd_geolocationprop siteId ON siteId.nd_geolocation_id = e.nd_geolocation_id ")
                .append("    AND siteId.type_id = ").append(TermId.LOCATION_ID.getId())
                .append("  LEFT JOIN nd_experimentprop row ON row.nd_experiment_id = e.nd_experiment_id ")
                .append("    AND row.type_id = ").append(TermId.RANGE_NO.getId())
                .append("  LEFT JOIN nd_experimentprop col ON col.nd_experiment_id = e.nd_experiment_id ")
                .append("    AND col.type_id = ").append(TermId.COLUMN_NO.getId())
                .append("  LEFT JOIN nd_geolocationprop gpSeason ON geo.nd_geolocation_id = gpSeason.nd_geolocation_id ")
                .append("     AND gpSeason.type_id =  ").append(TermId.SEASON_VAR.getId()).append(" ") // -- 8371 (2452)
                .append(" WHERE blk.type_id = ").append(TermId.BLOCK_ID.getId());

        if (blockId != null) {
            sql.append(" AND blk.value = :blockId ");
        } else {
            sql.append(" AND blk.value IN (SELECT DISTINCT bval.value FROM nd_geolocationprop bval ").append(
                    " INNER JOIN nd_experiment bexp ON bexp.nd_geolocation_id = bval.nd_geolocation_id ")
                    .append(" AND bexp.nd_geolocation_id = :geolocationId ")
                    .append(" AND bexp.project_id = :datasetId ").append(" WHERE bval.type_id = ")
                    .append(TermId.BLOCK_ID.getId()).append(")");
        }
        sql.append(" ORDER BY e.nd_experiment_id ").append(order);

        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.addScalar("datasetId").addScalar("datasetName").addScalar("studyName").addScalar("geolocationId")
                .addScalar("siteName").addScalar("siteId").addScalar("experimentId").addScalar("entryNumber")
                .addScalar("germplasmName").addScalar("rep").addScalar("plotNo").addScalar("row")
                .addScalar("col").addScalar("blockId").addScalar("studyId").addScalar("trialInstance")
                .addScalar("gid").addScalar("startDate").addScalar("season").addScalar("blockNo")
                .addScalar("obsUnitId", Hibernate.STRING);

        if (blockId != null) {
            query.setParameter("blockId", blockId);
        } else {
            query.setParameter("datasetId", datasetId);
            query.setParameter("geolocationId", geolocationId);
        }

        final List<Object[]> list = query.list();

        if (list != null && !list.isEmpty()) {
            fieldmaps = this.createFieldMapLabels(list);
        }

    } catch (final HibernateException e) {
        final String message = "Error at getAllFieldMapsInBlockByTrialInstanceId(" + geolocationId
                + ") at ExperimentPropertyDao: " + e.getMessage();
        ExperimentPropertyDao.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }

    return fieldmaps;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<TrialEnvironment> getTrialEnvironmentDetails(final Set<Integer> environmentIds) {
    final List<TrialEnvironment> environmentDetails = new ArrayList<>();

    if (environmentIds.isEmpty()) {
        return environmentDetails;
    }/* w  w w.j  a v a 2s  .  c om*/

    try {

        // Get location name, study id and study name
        final String sql = "SELECT DISTINCT e.nd_geolocation_id, l.lname, gp.value, p.project_id, p.name, p.description, prov.lname as provinceName, c.isoabbr "
                + "FROM nd_experiment e "
                + "   LEFT JOIN nd_geolocationprop gp ON e.nd_geolocation_id = gp.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 l.cntryid = c.cntryid "
                + " INNER JOIN project ds ON ds.project_id = e.project_id "
                + "   INNER JOIN project p ON p.project_id = ds.study_id "
                + " WHERE e.nd_geolocation_id IN (:locationIds) ";

        final SQLQuery query = this.getSession().createSQLQuery(sql);
        query.setParameterList("locationIds", environmentIds);
        query.addScalar("nd_geolocation_id", Hibernate.INTEGER);
        query.addScalar("lname", Hibernate.STRING);
        query.addScalar("value", Hibernate.INTEGER);
        query.addScalar(GeolocationDao.PROJECT_ID, Hibernate.INTEGER);
        query.addScalar("name", Hibernate.STRING);
        query.addScalar(GeolocationDao.DESCRIPTION, Hibernate.STRING);
        query.addScalar(GeolocationDao.PROVINCE_NAME, Hibernate.STRING);
        query.addScalar(GeolocationDao.ISOABBR, Hibernate.STRING);
        final List<Integer> locIds = new ArrayList<>();

        final List<Object[]> result = query.list();

        for (final Object[] row : result) {
            final Integer environmentId = (Integer) row[0];
            final String locationName = (String) row[1];
            final Integer locId = (Integer) row[2];
            final Integer studyId = (Integer) row[3];
            final String studyName = (String) row[4];
            final String studyDescription = (String) row[5];
            final String provinceName = (String) row[6];
            final String countryName = (String) row[7];

            environmentDetails.add(new TrialEnvironment(environmentId,
                    new LocationDto(locId, locationName, provinceName, countryName),
                    new StudyReference(studyId, studyName, studyDescription)));
            locIds.add(locId);
        }

    } catch (final HibernateException e) {
        final String errorMessage = "Error at getTrialEnvironmentDetails=" + environmentIds
                + GeolocationDao.AT_GEOLOCATION_DAO + e.getMessage();
        GeolocationDao.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }

    return environmentDetails;
}

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

License:Open Source License

public List<InstanceMetadata> getInstanceMetadata(final int studyId, final List<Integer> locationIds) {

    final String queryString = "select \n" + "    geoloc.nd_geolocation_id as instanceDBId, \n"
            + "    geoloc.description as instanceNumber, \n" + "    pmain.project_id trialDbId, \n"
            + "    pmain.name as trialName, \n" + "    proj.name as instanceDatasetName, \n"
            + "    pmain.program_uuid as programDbId, \n"
            + "    max(if(geoprop.type_id = 8190, loc.lname, null)) as LOCATION_NAME, \n"
            + "    max(if(geoprop.type_id = 8190, geoprop.value, null)) as LOCATION_ID, \n"
            + "    max(if(geoprop.type_id = 8189, geoprop.value, null)) as LOCATION_ABBR, \n"
            + "    max(if(geoprop.type_id = 8370, geoprop.value, null)) as CROP_SEASON \n" + " from  \n"
            + " nd_geolocation geoloc \n"
            + "    inner join nd_experiment nde on nde.nd_geolocation_id = geoloc.nd_geolocation_id \n"
            + "    inner join project proj on proj.project_id = nde.project_id \n"
            + "    inner join project pmain on pmain.project_id = proj.study_id \n"
            + "    left outer join nd_geolocationprop geoprop on geoprop.nd_geolocation_id = geoloc.nd_geolocation_id \n"
            + "      left outer join location loc on geoprop.value = loc.locid and geoprop.type_id = 8190 \n"
            + " where nde.type_id = 1020 and pmain.project_id = :studyId \n";

    final StringBuilder strBuilder = new StringBuilder(queryString);
    final boolean locationFilterSpecified = !CollectionUtils.isEmpty(locationIds);
    if (locationFilterSpecified) {
        strBuilder.append("    and geoprop.value in (:locationIds) ");
    }/*from w w  w  .  ja  v  a 2 s. c o  m*/
    strBuilder.append("    group by geoloc.nd_geolocation_id ");
    strBuilder.append("    order by geoloc.nd_geolocation_id asc \n");
    final SQLQuery query = this.getSession().createSQLQuery(strBuilder.toString());

    query.setParameter("studyId", studyId);
    if (locationFilterSpecified) {
        query.setParameterList("locationIds", locationIds);
    }
    query.addScalar("instanceDBId", new IntegerType());
    query.addScalar("instanceNumber");
    query.addScalar("trialDbId", new IntegerType());
    query.addScalar("trialName");
    query.addScalar("instanceDatasetName");
    query.addScalar("programDbId");
    query.addScalar("LOCATION_NAME");
    query.addScalar("LOCATION_ID", new IntegerType());
    query.addScalar("LOCATION_ABBR");
    query.addScalar("CROP_SEASON");

    @SuppressWarnings("rawtypes")
    final List results = query.list();

    final List<InstanceMetadata> tiMetadata = new ArrayList<>();
    for (final Object result : results) {
        final Object[] row = (Object[]) result;

        final InstanceMetadata metadata = new InstanceMetadata();
        metadata.setInstanceDbId((Integer) row[0]);
        metadata.setInstanceNumber(String.valueOf(row[1]));
        metadata.setTrialDbId((Integer) row[2]);
        metadata.setTrialName(String.valueOf(row[3]));
        metadata.setInstanceDatasetName(String.valueOf(row[4]));
        metadata.setProgramDbId(String.valueOf(row[5]));
        metadata.setLocationName(String.valueOf(row[6]));
        metadata.setLocationDbId((Integer) row[7]);
        metadata.setLocationAbbreviation(String.valueOf(row[8]));
        metadata.setSeason(String.valueOf(row[9]));
        tiMetadata.add(metadata);
    }
    return tiMetadata;
}

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

License:Open Source License

public List<TraitObservation> getObservationsForTrait(final int traitId, final List<Integer> environmentIds) {
    final List<TraitObservation> traitObservationList = new ArrayList<>();

    try {/*from   w ww.  java  2 s.  c  o  m*/
        final StringBuilder queryString = new StringBuilder();
        queryString.append(
                "SELECT p.observable_id, p.value, s.dbxref_id, e.nd_experiment_id, l.lname, gp.value as locationId ");
        queryString.append("FROM phenotype p ");
        queryString.append("INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id ");
        queryString.append(
                "INNER JOIN nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id AND gp.type_id = "
                        + TermId.LOCATION_ID.getId() + " ");
        queryString.append(" LEFT JOIN location l ON l.locid = gp.value ");
        queryString.append("INNER JOIN stock s ON s.stock_id = e.stock_id ");
        queryString.append("WHERE p.observable_id = :traitId AND e.nd_geolocation_id IN ( :environmentIds ) ");
        queryString.append("ORDER BY s.dbxref_id ");

        PhenotypeDao.LOG.debug(queryString.toString());

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameter("traitId", traitId).setParameterList("environmentIds", environmentIds);
        query.addScalar("observable_id", Hibernate.INTEGER);
        query.addScalar("value", Hibernate.STRING);
        query.addScalar("dbxref_id", Hibernate.INTEGER);
        query.addScalar("nd_experiment_id", Hibernate.INTEGER);
        query.addScalar("lname", Hibernate.STRING);
        query.addScalar("locationId", Hibernate.INTEGER);

        final List<Object[]> list = query.list();

        for (final Object[] row : list) {
            final Integer id = (Integer) row[0];
            final String value = (String) row[1];
            final Integer gid = (Integer) row[2];
            final Integer observationId = (Integer) row[3];
            final String locationName = (String) row[4];
            final Integer locationId = (Integer) row[5];

            traitObservationList
                    .add(new TraitObservation(id, value, gid, observationId, locationName, locationId));
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at getObservationsForTrait() query on PhenotypeDao: " + e.getMessage(), e);
    }

    return traitObservationList;
}

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

License:Open Source License

public List<PhenotypeSearchDTO> searchPhenotypes(final Integer pageSize, final Integer pageNumber,
        final PhenotypeSearchRequestDTO requestDTO) {
    final StringBuilder queryString = new StringBuilder(PhenotypeQuery.PHENOTYPE_SEARCH);

    addPhenotypeSearchFilter(requestDTO, queryString);

    final SQLQuery sqlQuery = this.getSession().createSQLQuery(queryString.toString());

    if (pageNumber != null && pageSize != null) {
        sqlQuery.setFirstResult(pageSize * (pageNumber - 1));
        sqlQuery.setMaxResults(pageSize);
    }/* ww w .j a  va  2 s .  c  om*/

    addPhenotypeSearchQueryParams(requestDTO, sqlQuery);

    sqlQuery.addScalar("nd_experiment_id").addScalar("observationUnitDbId", new StringType())
            .addScalar("observationUnitName").addScalar("observationLevel")
            .addScalar("plantNumber", new IntegerType()).addScalar("germplasmDbId", new StringType())
            .addScalar("germplasmName").addScalar("studyDbId", new StringType()).addScalar("studyName")
            .addScalar("programName").addScalar("FieldMapRow").addScalar("FieldMapCol")
            .addScalar("plotNumber", new StringType()).addScalar("blockNumber", new StringType())
            .addScalar("replicate", new StringType()).addScalar("COL").addScalar("ROW")
            .addScalar("studyLocationDbId", new StringType()).addScalar("studyLocation", new StringType())
            .addScalar("entryType").addScalar("entryNumber", new StringType());

    // TODO get map with AliasToEntityMapResultTransformer.INSTANCE
    final List<Object[]> results = sqlQuery.list();

    final Map<Integer, PhenotypeSearchDTO> observationUnitsByNdExpId = new LinkedHashMap<>();

    if (results != null && !results.isEmpty()) {

        // Process ObservationUnits (Measurement row)
        for (final Object[] row : results) {
            final PhenotypeSearchDTO observationUnit = new PhenotypeSearchDTO();

            final Integer ndExperimentId = (Integer) row[0];
            observationUnit.setObservationUnitDbId((String) row[1]); // OBS_UNIT_ID
            observationUnit.setObservationUnitName((String) row[2]);
            observationUnit.setObservationLevel((String) row[3]);
            observationUnit.setObservationLevels("1");
            observationUnit.setPlantNumber((String) row[4]);
            observationUnit.setGermplasmDbId((String) row[5]);
            observationUnit.setGermplasmName((String) row[6]);
            observationUnit.setStudyDbId((String) row[7]);
            observationUnit.setStudyName((String) row[8]);
            observationUnit.setProgramName((String) row[9]);
            String x = (String) row[15]; // ROW
            String y = (String) row[16]; // COL
            if (StringUtils.isBlank(x) || StringUtils.isBlank(y)) {
                x = (String) row[10]; // fieldMapRow
                y = (String) row[11]; // fieldMapCol
            }
            observationUnit.setX(x);
            observationUnit.setY(y);
            observationUnit.setPlotNumber((String) row[12]);
            observationUnit.setBlockNumber((String) row[13]);
            observationUnit.setReplicate((String) row[14]);
            observationUnit.setStudyLocationDbId((String) row[17]);
            observationUnit.setStudyLocation((String) row[18]);
            observationUnit.setEntryType((String) row[19]);
            observationUnit.setEntryNumber((String) row[20]);

            observationUnitsByNdExpId.put(ndExperimentId, observationUnit);
        }

        // Get observations (Traits)
        final SQLQuery observationsQuery = this.getSession()
                .createSQLQuery(PhenotypeQuery.PHENOTYPE_SEARCH_OBSERVATIONS);
        observationsQuery.setParameterList("ndExperimentIds", observationUnitsByNdExpId.keySet());
        observationsQuery.addScalar("expid").addScalar("phen_id").addScalar("cvterm_id")
                .addScalar("cvterm_name", new StringType()).addScalar("value", new StringType())
                .addScalar("crop_ontology_id", new StringType()).addScalar("updated_date");
        final List<Object[]> observationResults = observationsQuery.list();

        for (final Object[] result : observationResults) {
            final Integer ndExperimentId = (Integer) result[0];

            final PhenotypeSearchObservationDTO observation = new PhenotypeSearchObservationDTO();
            final String variableId = (result[5] != null && !((String) result[5]).isEmpty())
                    ? (String) result[5]
                    : String.valueOf(result[2]);
            observation.setObservationVariableDbId(variableId);
            observation.setObservationVariableName((String) result[3]);
            observation.setObservationDbId((Integer) result[1]);
            observation.setValue((String) result[4]);
            observation.setObservationTimeStamp(
                    Util.formatDateAsStringValue((Date) result[6], Util.FRONTEND_TIMESTAMP_FORMAT));
            // TODO
            observation.setSeason(StringUtils.EMPTY);
            observation.setCollector(StringUtils.EMPTY);

            final PhenotypeSearchDTO observationUnit = observationUnitsByNdExpId.get(ndExperimentId);
            observationUnit.getObservations().add(observation);
        }
    }

    return new ArrayList<>(observationUnitsByNdExpId.values());
}

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

License:Open Source License

@SuppressWarnings("unchecked")
//FIXME//from w  ww. j  a  v  a  2s  .co m
public List<Object> getUniqueAccMetaDatsetByGids(List<Integer> gids) throws MiddlewareQueryException {
    try {
        SQLQuery query = this.getSession().createSQLQuery(AccMetadataSetDAO.GET_UNIQUE_ACC_METADATASET_BY_GIDS);
        query.setParameterList("gids", gids);
        query.addScalar("gid", Hibernate.INTEGER);
        query.addScalar("nid", Hibernate.INTEGER);
        query.addScalar("acc_sample_id", Hibernate.INTEGER);
        return query.list();
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getUniqueAccMetaDatasetByGids(" + gids
                + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
}

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

License:Open Source License

@SuppressWarnings({ "deprecation", "unchecked" })
public List<Object> getUniqueAllelesByGidsAndMids(List<Integer> gids, List<Integer> mids) {

    List<Object> results = new ArrayList<>();
    try {//w  w w .j  av a 2  s .c o m
        if (gids != null) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(AlleleValuesDAO.GET_UNIQUE_ALLELIC_VALUES_BY_GIDS_AND_MIDS);
            query.setParameterList("gids", gids);
            query.setParameterList("mids", mids);
            query.addScalar("gid", Hibernate.INTEGER);
            query.addScalar("marker_id", Hibernate.INTEGER);
            query.addScalar("allele_bin_value", Hibernate.STRING);
            query.addScalar("acc_sample_id", Hibernate.INTEGER);
            query.addScalar("marker_sample_id", Hibernate.INTEGER);
            results = query.list();

        }
    } catch (HibernateException e) {
        this.logAndThrowException("Error with getUniqueAllelesByGidsAndMids(gids=" + gids
                + ") query from AlleleValuesDAO " + e.getMessage(), e);
    }
    return results;

}

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

License:Open Source License

@SuppressWarnings({ "deprecation", "unchecked" })
public List<Object> getUniqueCharAllelesByGidsAndMids(final List<Integer> gids, final List<Integer> mids) {

    List<Object> results = new ArrayList<>();
    try {/*from   w  ww.j  av a  2s  . com*/
        if (gids != null) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(CharValuesDAO.GET_UNIQUE_ALLELIC_VALUES_BY_GIDS_AND_MIDS);
            query.setParameterList("gids", gids);
            query.setParameterList("mids", mids);
            query.addScalar("gid", Hibernate.INTEGER);
            query.addScalar("marker_id", Hibernate.INTEGER);
            query.addScalar("char_value", Hibernate.STRING);
            query.addScalar("acc_sample_id", Hibernate.INTEGER);
            query.addScalar("marker_sample_id", Hibernate.INTEGER);
            results = query.list();

        }
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getUniqueAllelesByGidsAndMids(gids=" + gids
                + ") query from CharValuesDAO " + e.getMessage(), e);
    }
    return results;

}

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

License:Open Source License

public List<MapInfo> getMapInfoByMapAndChromosome(final Integer mapId, final String chromosome) {
    final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_AND_CHROMOSOME);
    query.setInteger("mapId", mapId);
    query.setString("chromosome", chromosome);

    query.addScalar("marker_id", new IntegerType());
    query.addScalar("marker_name", new StringType());
    query.addScalar("map_name", new StringType());
    query.addScalar("map_type", new StringType());
    query.addScalar("start_position", new FloatType());
    query.addScalar("linkage_group", new StringType());
    query.addScalar("map_unit", new StringType());

    final List<MapInfo> mapInfoList = new ArrayList<MapInfo>();

    try {//from w  w w. j a va 2  s.  c  o  m
        @SuppressWarnings("rawtypes")
        final List results = query.list();

        for (final Object o : results) {
            final Object[] result = (Object[]) o;

            if (result != null) {
                final Integer markerId = (Integer) result[0];
                final String markerName = (String) result[1];
                final String mapName = (String) result[2];
                final String mapType = (String) result[3];
                final Float startPosition = (Float) result[4];
                final String linkageGroup = (String) result[5];
                final String mapUnit = (String) result[6];

                final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup,
                        startPosition, mapType, mapUnit);
                mapInfoList.add(mapInfo);
            }
        }
    } catch (final HibernateException e) {
        MapDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException("Error with getMapInfoByMapAndChromosome() query: " + e.getMessage(),
                e);
    }

    return mapInfoList;
}

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

License:Open Source License

public List<MapInfo> getMapInfoByMapChromosomeAndPosition(final Integer mapId, final String chromosome,
        final Float startPosition) {
    final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_CHROMOSOME_AND_POSITION);
    query.setInteger("mapId", mapId);
    query.setString("chromosome", chromosome);
    query.setFloat("startPosition", startPosition);

    query.addScalar("marker_id", new IntegerType());
    query.addScalar("marker_name", new StringType());
    query.addScalar("map_name", new StringType());
    query.addScalar("map_type", new StringType());
    query.addScalar("linkage_group", new StringType());
    query.addScalar("map_unit", new StringType());

    final List<MapInfo> mapInfoList = new ArrayList<MapInfo>();

    try {//from  w  w w . ja  va  2  s . com
        @SuppressWarnings("rawtypes")
        final List results = query.list();

        for (final Object o : results) {
            final Object[] result = (Object[]) o;

            if (result != null) {
                final Integer markerId = (Integer) result[0];
                final String markerName = (String) result[1];
                final String mapName = (String) result[2];
                final String mapType = (String) result[3];
                final String linkageGroup = (String) result[4];
                final String mapUnit = (String) result[5];

                final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup,
                        startPosition, mapType, mapUnit);
                mapInfoList.add(mapInfo);
            }
        }
    } catch (final HibernateException e) {
        MapDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getMapInfoByMapChromosomeAndPosition() query: " + e.getMessage(), e);
    }

    return mapInfoList;
}