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

Source Link

Document

Declare a scalar query result.

Usage

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

License:Open Source License

public List<MapInfo> getMapInfoByMarkersAndMap(final List<Integer> markers, final Integer mapId) {
    final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MARKERS_AND_MAP);
    query.setParameterList("markerIdList", markers);
    query.setInteger("mapId", mapId);

    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 {// ww w  . java  2s  . c  om
        @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 getMapInfoByMarkersAndMap() query: " + e.getMessage(),
                e);
    }

    return mapInfoList;
}

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

License:Open Source License

public String getMapNameById(final Integer mapId) {
    try {/*from www.ja v a 2s.  c o m*/
        final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_NAME_BY_ID);
        query.setParameter("mapId", mapId);
        return (String) query.addScalar("map_name", StringType.class.newInstance()).uniqueResult();

    } catch (final HibernateException e) {
        MapDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getMapNameById(" + mapId + ") in MapDAO: " + e.getMessage(), e);
    } catch (final Exception e) {
        MapDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getMapNameById(" + mapId + ") in MapDAO: " + e.getMessage(), e);
    }
}

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

License:Open Source License

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

    List<Object> results = new ArrayList<>();
    try {/* w w w .  ja v a  2s. c  o  m*/
        if (gids != null) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(MappingPopValuesDAO.GET_UNIQUE_MAPPOP_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("map_char_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 getUniqueMapPopAllelesByGidsAndMids(gids=" + gids
                + ") query from MappingPopValuesDAO " + e.getMessage(), e);
    }
    return results;

}

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

License:Open Source License

public List<Object> getMarkersOnMapByMarkerIdsAndMapId(List<Integer> markerIds, Integer mapID) {

    List<Object> result = new ArrayList<>();

    String strQuerry = "SELECT distinct gdms_markers_onmap.marker_id, gdms_map.map_name, gdms_markers_onmap.start_position, gdms_markers_onmap.linkage_group, gdms_map.map_unit FROM "
            + "gdms_map join gdms_markers_onmap on gdms_map.map_id=gdms_markers_onmap.map_id where gdms_markers_onmap.marker_id in (:markerIds) "
            + "and gdms_map.map_id=(:mapID) "
            + "order BY gdms_map.map_name, gdms_markers_onmap.linkage_group, gdms_markers_onmap.start_position asc";

    SQLQuery query = this.getSession().createSQLQuery(strQuerry);
    query.setParameterList("markerIds", markerIds);
    query.setParameter("mapID", mapID);

    query.addScalar("marker_id", Hibernate.INTEGER);
    query.addScalar("map_name", Hibernate.STRING);
    query.addScalar("start_position", Hibernate.DOUBLE);
    query.addScalar("linkage_group", Hibernate.STRING);
    query.addScalar("map_unit", Hibernate.STRING);
    result = query.list();/* w w  w.j  a  va2s  .  c  o m*/

    return result;

}

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

License:Open Source License

public List<Object[]> getAllListMetadata(final List<Integer> listIdsFromGermplasmList) {

    if (listIdsFromGermplasmList.isEmpty()) {
        return Collections.emptyList();
    }//w w w .  jav  a  2 s  .com

    final StringBuilder sql = new StringBuilder(
            "SELECT ln.listid as listId, COUNT(ld.listid) as count, ln.listuid as ownerId ")
                    .append(" FROM listnms ln ").append("   INNER JOIN listdata ld ON ln.listid = ld.listid ")
                    .append(" WHERE ln.listid in (:listids) AND").append(" ln.listtype != 'FOLDER' ")
                    .append(" GROUP BY ln.listid;");

    final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
    query.addScalar("listId", new IntegerType());
    query.addScalar("count", new IntegerType());
    query.addScalar("ownerId", new IntegerType());
    query.setParameterList("listids", listIdsFromGermplasmList);

    @SuppressWarnings("unchecked")
    final List<Object[]> queryResults = query.list();
    return queryResults;
}

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

License:Open Source License

/**
 * @param folderIds// ww  w.  j  a va2 s . c  o  m
 *            a group of folder ids for which we want to return children
 * @return the resultant map which contains the folder meta data
 */
public Map<Integer, ListMetadata> getGermplasmFolderMetadata(final List<Integer> folderIds) {

    if (folderIds.isEmpty()) {
        return Collections.<Integer, ListMetadata>emptyMap();
    }

    final String folderMetaDataQuery = "SELECT parent.listid AS listId, COUNT(child.listid) AS numberOfChildren FROM listnms parent "
            + "LEFT OUTER JOIN listnms child ON child.lhierarchy = parent.listid "
            + "WHERE parent.listid IN (:folderIds) GROUP BY parent.listid";
    final SQLQuery setResultTransformer = this.getSession().createSQLQuery(folderMetaDataQuery);
    setResultTransformer.setParameterList("folderIds", folderIds);
    setResultTransformer.addScalar("listId", new IntegerType());
    setResultTransformer.addScalar("numberOfChildren", new IntegerType());
    setResultTransformer.setResultTransformer(Transformers.aliasToBean(ListMetadata.class));
    final List<ListMetadata> list = setResultTransformer.list();
    return Maps.uniqueIndex(list, new Function<ListMetadata, Integer>() {
        @Override
        public Integer apply(final ListMetadata folderMetaData) {
            return folderMetaData.getListId();
        }
    });
}

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

License:Open Source License

@SuppressWarnings("deprecation")
public Long countAllBreedingLocations() {
    try {/*from  ww w .j  a va 2  s  . c o  m*/
        final Session session = this.getSession();
        final SQLQuery query = session.createSQLQuery(Location.COUNT_ALL_BREEDING_LOCATIONS);
        return (Long) query.addScalar("count", Hibernate.LONG).uniqueResult();
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("countAllBreedingLocations", "", null,
                e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
}

From source file:org.generationcp.middleware.dao.oms.CVTermRelationshipDao.java

License:Open Source License

@SuppressWarnings("unchecked")
protected List<String> getScaleCategoriesUsedInObservations(final int scaleId) {
    final SQLQuery query = this.getSession()
            .createSQLQuery("SELECT v.name category " + " FROM cvterm_relationship scale_values "
                    + " INNER JOIN cvterm v ON v.cvterm_id = scale_values.object_id "
                    + " WHERE scale_values.subject_id = :scaleId AND scale_values.type_id = "
                    + TermId.HAS_VALUE.getId() + " AND EXISTS ( " + "     SELECT 1        "
                    + "     FROM phenotype p "
                    + "     INNER JOIN nd_experiment ep on ep.nd_experiment_id = p.nd_experiment_id "
                    + "     INNER JOIN project pr ON pr.project_id = ep.project_id and pr.deleted = 0 "
                    + "     WHERE cvalue_id = v.cvterm_id)" + "");
    query.setParameter("scaleId", scaleId);
    query.addScalar("category", CVTermRelationshipDao.STRING);
    return query.list();
}

From source file:org.generationcp.middleware.dao.oms.CVTermRelationshipDao.java

License:Open Source License

@SuppressWarnings("unchecked")
protected List<String> getScaleCategoriesUsedAsConditions(final int scaleId) {
    final SQLQuery query = this.getSession().createSQLQuery("SELECT categ.name category "
            + " FROM cvterm_relationship scale_values "
            + " INNER JOIN cvterm categ ON categ.cvterm_id = scale_values.object_id "
            + " INNER JOIN cvterm_relationship var ON var.object_id = scale_values.subject_id and var.type_id = "
            + TermId.HAS_SCALE.getId() + " WHERE scale_values.subject_id = :scaleId AND scale_values.type_id = "
            + TermId.HAS_VALUE.getId() + " AND EXISTS ( " + "     SELECT 1        "
            + "     FROM projectprop pp "
            + "     INNER JOIN project pr ON pr.project_id =pp.project_id and pr.deleted = 0 "
            + "      WHERE pp.variable_id = var.subject_id and pp.value = categ.cvterm_id)");
    query.setParameter("scaleId", scaleId);
    query.addScalar("category", CVTermRelationshipDao.STRING);
    return query.list();
}

From source file:org.generationcp.middleware.dao.oms.CVTermRelationshipDao.java

License:Open Source License

@SuppressWarnings("unchecked")
protected List<String> getScaleCategoriesUsedAsGermplasmDescriptors(final int scaleId) {
    final SQLQuery query = this.getSession().createSQLQuery("SELECT categ.name category "
            + " FROM cvterm_relationship scale_values "
            + " INNER JOIN cvterm categ ON categ.cvterm_id = scale_values.object_id "
            + " INNER JOIN cvterm_relationship var ON var.object_id = scale_values.subject_id and var.type_id = "
            + TermId.HAS_SCALE.getId() + " WHERE scale_values.subject_id = :scaleId AND scale_values.type_id = "
            + TermId.HAS_VALUE.getId() + " AND EXISTS ( " + "      SELECT 1 " + "      FROM stockprop sp "
            + "      INNER JOIN nd_experiment ep on ep.stock_id = sp.stock_id "
            + "      INNER JOIN project pr ON pr.project_id =ep.project_id and pr.deleted = 0 "
            + "      WHERE sp.type_id = var.subject_id and sp.value = categ.cvterm_id)");
    query.setParameter("scaleId", scaleId);
    query.addScalar("category", CVTermRelationshipDao.STRING);
    return query.list();
}