Example usage for org.hibernate SQLQuery setParameterList

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameterList(String name, Object[] values);

Source Link

Usage

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

License:Open Source License

public long countByGids(List<Integer> gIds) throws MiddlewareQueryException {
    try {/*from   w ww.j ava 2  s.  com*/
        if (gIds != null && gIds.get(0) != null) {
            SQLQuery query = this.getSession().createSQLQuery(MappingPopValuesDAO.COUNT_BY_GIDS);
            query.setParameterList("gIdList", gIds);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                return result.longValue();
            }
        }
    } catch (HibernateException e) {
        this.logAndThrowException(
                "Error with countByGids(gIds=" + gIds + ") query from MappingPopValues: " + e.getMessage(), e);
    }
    return 0;
}

---

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

License:Open Source License

@SuppressWarnings("rawtypes")
public List<MarkerSampleId> getMarkerSampleIdsByGids(List<Integer> gIds) throws MiddlewareQueryException {
    List<MarkerSampleId> toReturn = new ArrayList<MarkerSampleId>();
    try {/*from w ww . j  a  v  a2 s  .  c  om*/
        if (gIds != null && !gIds.isEmpty()) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(MappingPopValuesDAO.GET_MARKER_SAMPLE_IDS_BY_GIDS);
            query.setParameterList("gids", gIds);

            List results = query.list();
            for (Object o : results) {
                Object[] result = (Object[]) o;
                if (result != null) {
                    Integer markerId = (Integer) result[0];
                    Integer markerSampleId = (Integer) result[1];
                    MarkerSampleId dataElement = new MarkerSampleId(markerId, markerSampleId);
                    toReturn.add(dataElement);
                }
            }
        }
    } catch (HibernateException e) {
        this.logAndThrowException("Error with getMarkerSampleIdsByGids(gIds=" + gIds
                + ") query from MappingPopValuesDAO: " + e.getMessage(), e);
    }
    return toReturn;
}

---

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 {/*from 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.MarkerDAO.java

License:Open Source License

/**
 * Gets the ids by names.//from   w  w w.java  2 s.c o  m
 *
 * @param names     the names
 * @param start     the start
 * @param numOfRows the num of rows
 * @return the ids by names
 * @ the MiddlewareQueryException
 */
public List<Integer> getIdsByNames(final List<String> names, final int start, final int numOfRows) {

    if (names == null || names.isEmpty()) {
        return new ArrayList<>();
    }

    try {
        final SQLQuery query = this.getSession().createSQLQuery(MarkerDAO.GET_IDS_BY_NAMES);
        query.setParameterList("markerNameList", names);
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);
        return query.list();
    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getIdsByNames(names=" + names + ") query from Marker: " + e.getMessage(), e);
    }
}

---

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

License:Open Source License

/**
 * Gets the marker_id of the first occurence of the marker_name
 *
 * @param names/*from   w w  w . ja v a 2 s.c  o m*/
 * @return Map of markerId-markerName pairs
 * @
 */
public Map<Integer, String> getFirstMarkerIdByMarkerName(final List<String> names) {
    final Map<Integer, String> toReturn = new HashMap<>();
    if (names == null || names.isEmpty()) {
        return toReturn;
    }

    try {
        final SQLQuery query = this.getSession().createSQLQuery(MarkerDAO.GET_ID_AND_NAME_BY_NAMES);
        query.setParameterList("markerNameList", names);
        final List<Object> results = query.list();

        for (final Object o : results) {
            final Object[] result = (Object[]) o;
            if (result != null) {
                final Integer id = (Integer) result[0];
                final String name = (String) result[1];

                // Add to map if it doesn't contain the name yet. Ignore the case.
                if (!toReturn.containsValue(name.toUpperCase())) {
                    toReturn.put(id, name.toUpperCase());
                }
            }
        }

    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getIdAndNameByNames(names=" + names + ") query from Marker: " + e.getMessage(), e);
    }
    return toReturn;

}

---

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

License:Open Source License

/**
 * Gets the marker type by marker ids./*from w w  w. j av  a2s. c  om*/
 *
 * @param markerIds the marker ids
 * @return the marker type by marker ids
 * @ the MiddlewareQueryException
 */
public List<String> getMarkerTypeByMarkerIds(final List<Integer> markerIds) {

    if (markerIds == null || markerIds.isEmpty()) {
        return new ArrayList<>();
    }

    try {

        final SQLQuery query = this.getSession().createSQLQuery(MarkerDAO.GET_MARKER_TYPE_BY_MARKER_IDS);
        query.setParameterList("markerIdList", markerIds);
        return query.list();

    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException("Error with getMarkerTypeByMarkerIds(markerIds=" + markerIds
                + ") query from Marker: " + e.getMessage(), e);
    }
}

---

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

License:Open Source License

public Map<Integer, String> getMarkerTypeMapByIds(final List<Integer> markerIds) {
    final Map<Integer, String> markerTypes = new HashMap<>();

    if (markerIds == null || markerIds.isEmpty()) {
        return markerTypes;
    }/*w  ww.j a v  a2  s . c o  m*/

    try {

        final StringBuilder sql = new StringBuilder()
                .append("SELECT DISTINCT marker_id, CONCAT(marker_type, '') ").append("FROM gdms_marker ")
                .append("WHERE marker_id IN (:markerIdList)");
        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameterList("markerIdList", markerIds);

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

        for (final Object o : results) {
            final Object[] result = (Object[]) o;
            if (result != null) {
                final Integer id = (Integer) result[0];
                final String type = (String) result[1];
                markerTypes.put(id, type);
            }
        }

    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException("Error with getMarkerTypeByMarkerIds(markerIds=" + markerIds
                + ") query from Marker: " + e.getMessage(), e);
    }

    return markerTypes;
}

---

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

License:Open Source License

/**
 * Gets the marker names by gids./*from w w  w.j  a  v a  2s  . c  o m*/
 * <p/>
 * - Searches the allele_values, char_values, mapping_pop_values tables for the existence of gids. - Gets marker ids from allele_values,
 * char_values, mapping_pop_values by gids - Gets marker name from marker table by marker ids - Returns marker names matching the gids
 *
 * @param gIds the g ids
 * @return the marker names by g ids
 * @ the MiddlewareQueryException
 */
public List<MarkerNameElement> getMarkerNamesByGIds(final List<Integer> gIds) {
    final List<MarkerNameElement> dataValues = new ArrayList<>();

    if (gIds == null || gIds.isEmpty()) {
        return dataValues;
    }

    try {
        // Search the allele_values, char_values, mapping_pop_values tables for the existence of gids.
        // by getting alleleCount, charCount and mappingCount

        SQLQuery query = this.getSession().createSQLQuery(AlleleValuesDAO.GET_ALLELE_COUNT_BY_GID);
        query.setParameterList("gIdList", gIds);
        final BigInteger alleleCount = (BigInteger) query.uniqueResult();

        query = this.getSession().createSQLQuery(CharValuesDAO.GET_CHAR_COUNT_BY_GID);
        query.setParameterList("gIdList", gIds);
        final BigInteger charCount = (BigInteger) query.uniqueResult();

        query = this.getSession().createSQLQuery(MappingPopValuesDAO.GET_MAPPING_COUNT_BY_GID);
        query.setParameterList("gIdList", gIds);
        final BigInteger mappingCount = (BigInteger) query.uniqueResult();

        // Retrieves markers that are being genotyped
        if (alleleCount.intValue() > 0) {
            query = this.getSession().createSQLQuery(MarkerDAO.GET_ALLELE_MARKER_NAMES_BY_GID);
            query.setParameterList("gIdList", gIds);
            final List results = query.list();
            dataValues.addAll(this.createMarkerNameElementList(results));
        }

        if (charCount.intValue() > 0) {
            query = this.getSession().createSQLQuery(MarkerDAO.GET_CHAR_MARKER_NAMES_BY_GID);
            query.setParameterList("gIdList", gIds);
            final List results = query.list();
            dataValues.addAll(this.createMarkerNameElementList(results));
        }

        if (mappingCount.intValue() > 0) {
            query = this.getSession().createSQLQuery(MarkerDAO.GET_MAPPING_MARKER_NAMES_BY_GID);
            query.setParameterList("gIdList", gIds);
            final List results = query.list();
            dataValues.addAll(this.createMarkerNameElementList(results));
        }

        return dataValues;

    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(
                "Error with getMarkerNamesByGIds(gIds=" + gIds + ") query from Marker: " + e.getMessage(), e);
    }
}

---

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

License:Open Source License

/**
 * Gets the germplasm names by marker names.
 *
 * @param markerNames the marker names// w  ww  . j a  v  a2  s.  c  o m
 * @return the germplasm names by marker names
 * @ the MiddlewareQueryException
 */
public List<GermplasmMarkerElement> getGermplasmNamesByMarkerNames(final List<String> markerNames) {

    final ArrayList<GermplasmMarkerElement> dataValues = new ArrayList<>();

    if (markerNames == null || markerNames.isEmpty()) {
        return dataValues;
    }

    // Get marker_ids by marker_names
    final List<Integer> markerIds = this.getIdsByNames(markerNames, 0,
            Long.valueOf(this.countAll()).intValue());

    if (markerIds.isEmpty()) {
        return dataValues;
    }

    try {
        // Search the allele_values, char_values, mapping_pop_values tables for the existence of marker_ids.
        // by getting alleleCount, charCount and mappingCount

        SQLQuery query = this.getSession().createSQLQuery(AlleleValuesDAO.GET_ALLELE_COUNT_BY_MARKER_ID);
        query.setParameterList("markerIdList", markerIds);
        final BigInteger alleleCount = (BigInteger) query.uniqueResult();

        query = this.getSession().createSQLQuery(CharValuesDAO.GET_CHAR_COUNT_BY_MARKER_ID);
        query.setParameterList("markerIdList", markerIds);
        final BigInteger charCount = (BigInteger) query.uniqueResult();

        query = this.getSession().createSQLQuery(MappingPopValuesDAO.GET_MAPPING_COUNT_BY_MARKER_ID);
        query.setParameterList("markerIdList", markerIds);
        final BigInteger mappingCount = (BigInteger) query.uniqueResult();

        // Get marker name, germplasm name from allele_values given the marker names
        if (alleleCount.intValue() > 0) {
            query = this.getSession()
                    .createSQLQuery(AlleleValuesDAO.GET_ALLELE_GERMPLASM_NAME_AND_MARKER_NAME_BY_MARKER_NAMES);
            query.setParameterList("markerNameList", markerNames);
            final List results = query.list();
            dataValues.addAll(this.getGermplasmMarkerElementsFromList(results));
        }

        // Get marker name, germplasm name from char_values given the marker names
        if (charCount.intValue() > 0) {
            query = this.getSession()
                    .createSQLQuery(CharValuesDAO.GET_CHAR_GERMPLASM_NAME_AND_MARKER_NAME_BY_MARKER_NAMES);
            query.setParameterList("markerNameList", markerNames);
            final List results = query.list();
            dataValues.addAll(this.getGermplasmMarkerElementsFromList(results));
        }

        // Get marker name, germplasm name from mapping_pop_values given the marker names
        if (mappingCount.intValue() > 0) {
            query = this.getSession().createSQLQuery(
                    MappingPopValuesDAO.GET_MAPPING_GERMPLASM_NAME_AND_MARKER_NAME_BY_MARKER_NAMES);
            query.setParameterList("markerNameList", markerNames);
            final List results = query.list();
            dataValues.addAll(this.getGermplasmMarkerElementsFromList(results));
        }

        return dataValues;

    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException("Error with getGermplasmNamesByMarkerNames(markerNames="
                + markerNames + ") query from Marker: " + e.getMessage(), e);
    }
}

---

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

License:Open Source License

/**
 * Gets the allelic values by gids and marker ids.
 *
 * @param gids      the gids// w ww  . j a v  a 2  s  . co  m
 * @param markerIds the marker ids
 * @return the allelic values by gids and marker ids
 * @ the MiddlewareQueryException
 */
public List<AllelicValueElement> getAllelicValuesByGidsAndMarkerIds(final List<Integer> gids,
        final List<Integer> markerIds) {

    final List<AllelicValueElement> allelicValues = new ArrayList<>();

    if (gids == null || gids.isEmpty() || markerIds == null || markerIds.isEmpty()) {
        return allelicValues;
    }

    try {

        // retrieve allelic values from allele_values
        SQLQuery query = this.getSession()
                .createSQLQuery(AlleleValuesDAO.GET_ALLELIC_VALUES_BY_GIDS_AND_MARKER_IDS);
        query.setParameterList("gidList", gids);
        query.setParameterList("markerIdList", markerIds);
        List results = query.list();
        allelicValues.addAll(this.getAllelicValueElementsFromList(results));

        // retrieve allelic values from char_values
        query = this.getSession().createSQLQuery(CharValuesDAO.GET_ALLELIC_VALUES_BY_GIDS_AND_MARKER_IDS);
        query.setParameterList("gidList", gids);
        query.setParameterList("markerIdList", markerIds);
        results = query.list();
        allelicValues.addAll(this.getAllelicValueElementsFromList(results));

        // retrieve allelic values from mapping_pop_values
        query = this.getSession().createSQLQuery(MappingPopValuesDAO.GET_ALLELIC_VALUES_BY_GIDS_AND_MARKER_IDS);
        query.setParameterList("gidList", gids);
        query.setParameterList("markerIdList", markerIds);
        results = query.list();
        allelicValues.addAll(this.getAllelicValueElementsFromList(results));

        return allelicValues;
    } catch (final HibernateException e) {
        LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException("Error with getAllelicValuesByGidsAndMarkerIds(gIds=" + gids
                + ", markerIds=" + markerIds + ")  query from Marker: " + e.getMessage(), e);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »