Example usage for org.hibernate SQLQuery setParameterList

List of usage examples for org.hibernate SQLQuery setParameterList

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

License:Open Source License

public List<CVTerm> getVariablesByType(final List<Integer> types) {
    final List<CVTerm> terms = new ArrayList<>();

    try {/*  w w w  .j a  v  a2s .  c  om*/
        final StringBuilder queryString = new StringBuilder()
                .append("SELECT variable.cvterm_id, variable.name, variable.definition FROM cvterm variable ")
                .append("INNER JOIN cvterm_relationship hasScale ON hasScale.subject_id = variable.cvterm_id AND hasScale.type_id = "
                        + TermId.HAS_SCALE.getId() + " ")
                .append("INNER JOIN cvterm_relationship numericScale ON hasScale.object_id = numericScale.subject_id  AND numericScale.type_id = "
                        + TermId.HAS_TYPE.getId() + " AND numericScale.object_id IN (:types)");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameterList("types", types);

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

        for (final Object[] row : list) {
            final Integer id = (Integer) row[0];
            final String name = (String) row[1];
            final String definition = (String) row[2];

            final CVTerm cvTerm = new CVTerm();
            cvTerm.setCvTermId(id);
            cvTerm.setName(name);
            cvTerm.setDefinition(definition);
            terms.add(cvTerm);
        }
    } catch (final HibernateException e) {
        this.logAndThrowException(
                "Error at getVariablesByType=" + types + " query on CVTermDao: " + e.getMessage(), e);
    }

    return terms;
}

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

License:Open Source License

public List<CategoricalTraitInfo> setCategoricalVariables(final List<CategoricalTraitInfo> traitInfoList) {
    final List<CategoricalTraitInfo> categoricalTraitInfoList = new ArrayList<>();

    // Get trait IDs
    final List<Integer> traitIds = new ArrayList<>();
    for (final CategoricalTraitInfo trait : traitInfoList) {
        traitIds.add(trait.getId());//  ww w . j a v a 2 s  . co m
    }

    try {
        SQLQuery query = this.getSession().createSQLQuery(
                "SELECT cvt_categorical.cvterm_id, cvt_categorical.name, cvt_categorical.definition, cvr_value.object_id, cvt_value.name "
                        + "FROM cvterm_relationship cvr_categorical  "
                        + "INNER JOIN cvterm cvt_categorical ON cvr_categorical.subject_id = cvt_categorical.cvterm_id "
                        + "INNER JOIN cvterm_relationship cvr_scale ON cvr_categorical.subject_id = cvr_scale.subject_id "
                        + "INNER JOIN cvterm_relationship cvr_scale_type ON cvr_scale.object_id = cvr_scale_type.subject_id "
                        + "INNER JOIN cvterm_relationship cvr_value ON cvr_scale.object_id = cvr_value.subject_id and cvr_value.type_id = 1190 "
                        + "INNER JOIN cvterm cvt_value ON cvr_value.object_id = cvt_value.cvterm_id "
                        + "WHERE cvr_scale.type_id = 1220 and cvr_scale_type.type_id = 1105 AND cvr_scale_type.object_id = 1130 "
                        + "    AND cvt_categorical.cvterm_id in (:traitIds) ");

        query.setParameterList("traitIds", traitIds);

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

        final Map<Integer, String> valueIdName = new HashMap<Integer, String>();
        for (final Object[] row : list) {
            final Integer variableId = (Integer) row[0];
            final String variableName = (String) row[1];
            final String variableDescription = (String) row[2];
            final Integer valueId = (Integer) row[3];
            final String valueName = (String) row[4];

            valueIdName.put(valueId, valueName);

            for (final CategoricalTraitInfo traitInfo : traitInfoList) {
                if (traitInfo.getId() == variableId) {
                    traitInfo.setName(variableName);
                    traitInfo.setDescription(variableDescription);
                    traitInfo.addValue(new CategoricalValue(valueId, valueName));
                    break;
                }
            }
        }

        // Remove non-categorical variable from the list
        for (final CategoricalTraitInfo traitInfo : traitInfoList) {
            if (traitInfo.getName() != null) {
                categoricalTraitInfoList.add(traitInfo);
            }
        }

        // This step was added since the valueName is not retrieved
        // correctly with the above query in Java.
        // Most probably because of the two cvterm id-name present in the
        // query.
        // The steps that follow will just retrieve the name of the
        // categorical values in each variable.

        final List<Integer> valueIds = new ArrayList<>();
        valueIds.addAll(valueIdName.keySet());

        if (valueIds != null && !valueIds.isEmpty()) {
            query = this.getSession().createSQLQuery(
                    "SELECT cvterm_id, cvterm.name " + "FROM cvterm " + "WHERE cvterm_id IN (:ids) ");
            query.setParameterList("ids", valueIds);

            list = query.list();
        }

        for (final Object[] row : list) {
            final Integer variableId = (Integer) row[0];
            final String variableName = (String) row[1];

            valueIdName.put(variableId, variableName);
        }

        for (final CategoricalTraitInfo traitInfo : categoricalTraitInfoList) {
            final List<CategoricalValue> values = traitInfo.getValues();
            for (final CategoricalValue value : values) {
                final String name = valueIdName.get(value.getId());
                value.setName(name);
            }
            traitInfo.setValues(values);
        }

    } catch (final HibernateException e) {
        this.logAndThrowException("Error at setCategoricalVariables() query on CVTermDao: " + e.getMessage(),
                e);
    }

    return categoricalTraitInfoList;
}

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

License:Open Source License

public List<TraitInfo> getTraitInfo(final List<Integer> traitIds) {
    final List<TraitInfo> traits = new ArrayList<>();

    try {/*from   ww  w. j av  a2s.co  m*/

        final StringBuilder sql = new StringBuilder().append(
                "SELECT cvt.cvterm_id, cvt.name, cvt.definition,  c_scale.scaleName, cr_type.object_id ")
                .append("FROM cvterm cvt ")
                .append("   INNER JOIN cvterm_relationship cr_scale ON cvt.cvterm_id = cr_scale.subject_id ")
                .append("   INNER JOIN (SELECT cvterm_id, name AS scaleName FROM cvterm) c_scale ON c_scale.cvterm_id = cr_scale.object_id ")
                .append("        AND cr_scale.type_id = ").append(TermId.HAS_SCALE.getId()).append(" ")
                .append("   INNER JOIN cvterm_relationship cr_type ON cr_type.subject_id = cr_scale.subject_id ")
                .append("      AND cr_type.type_id = ").append(TermId.HAS_TYPE.getId()).append(" ")
                .append("WHERE cvt.cvterm_id in (:traitIds) ");

        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameterList("traitIds", traitIds);

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

        for (final Object[] row : list) {
            final Integer id = (Integer) row[0];
            final String name = (String) row[1];
            final String description = (String) row[2];
            final String scaleName = (String) row[3];
            final Integer typeId = (Integer) row[4];

            traits.add(new TraitInfo(id, name, description, scaleName, typeId));

        }

    } catch (final HibernateException e) {
        this.logAndThrowException("Error at getTraitInfo() query on CVTermDao: " + e.getMessage(), e);
    }
    return traits;
}

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

License:Open Source License

public List<Integer> getStandardVariableIdsByPhenotypicType(final PhenotypicType type) {
    try {/*from   w w w  . j av a 2 s  .c  o  m*/
        // Standard variable has the combination of property-scale-method
        final StringBuilder queryString = new StringBuilder();
        queryString.append("SELECT DISTINCT cvr.subject_id ");
        queryString.append("FROM cvterm_relationship cvr ");
        queryString.append(
                "INNER JOIN cvterm_relationship cvrp ON cvr.subject_id = cvrp.subject_id AND cvrp.type_id = 1200 ");
        queryString.append(
                "INNER JOIN cvterm_relationship cvrs ON cvr.subject_id = cvrs.subject_id AND cvrs.type_id = 1220 ");
        queryString.append(
                "INNER JOIN cvterm_relationship cvrm ON cvr.subject_id = cvrm.subject_id AND cvrm.type_id = 1210 ");
        queryString.append(
                "INNER JOIN cvterm_relationship storedIn ON cvr.subject_id = storedIn.subject_id AND storedIn.type_id = 1044 ");
        queryString.append("INNER JOIN cvterm term ON cvr.subject_id = term.cvterm_id ");
        queryString.append("WHERE storedIn.object_id IN (:type) ORDER BY term.name");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameterList("type", type.getTypeStorages());

        return query.list();

    } catch (final HibernateException e) {
        this.logAndThrowException("Error at getStandardVariableIdsByPhenotypicType :" + e.getMessage(), e);
    }
    return new ArrayList<>();
}

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

License:Open Source License

/**
 * Returns standard variables associated to the given list of trait names or
 * synonyms// w  ww  .j a va 2s.  com
 *
 * @param propertyNameOrSynonyms
 * @return Map of name-(standard variable ids-variable type) of the given
 *         trait name or synonyms
 */
public Map<String, Map<Integer, VariableType>> getStandardVariableIdsWithTypeByProperties(
        final List<String> propertyNameOrSynonyms) {
    final Map<String, Map<Integer, VariableType>> stdVarMap = new HashMap<String, Map<Integer, VariableType>>();

    // Store the names in the map in uppercase
    for (int i = 0, size = propertyNameOrSynonyms.size(); i < size; i++) {
        propertyNameOrSynonyms.set(i, propertyNameOrSynonyms.get(i).toUpperCase());
    }

    try {
        if (!propertyNameOrSynonyms.isEmpty()) {

            final StringBuilder sqlString = new StringBuilder()
                    .append("SELECT DISTINCT cvtr.name, syn.synonym, cvt.cvterm_id ")
                    .append("FROM cvterm_relationship cvr ")
                    .append("INNER JOIN cvterm cvtr ON cvr.object_id = cvtr.cvterm_id AND cvr.type_id = 1200 AND cvtr.is_obsolete = 0 ")
                    .append("INNER JOIN cvterm cvt ON cvr.subject_id = cvt.cvterm_id AND cvt.cv_id = 1040 AND cvt.is_obsolete = 0  ")
                    .append(", cvtermsynonym syn ")
                    .append("WHERE (cvtr.cvterm_id = syn.cvterm_id AND syn.synonym IN (:propertyNameOrSynonyms) ")
                    .append("OR cvtr.name IN (:propertyNameOrSynonyms)) ");

            final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
            query.setParameterList("propertyNameOrSynonyms", propertyNameOrSynonyms);

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

            for (final Object[] row : results) {
                final String cvtermName = ((String) row[0]).trim().toUpperCase();
                final String cvtermSynonym = ((String) row[1]).trim().toUpperCase();
                final Integer cvtermId = (Integer) row[2];

                Map<Integer, VariableType> stdVarIdsWithType = new HashMap<Integer, VariableType>();
                if (propertyNameOrSynonyms.contains(cvtermName)) {
                    if (stdVarMap.containsKey(cvtermName)) {
                        stdVarIdsWithType = stdVarMap.get(cvtermName);
                    }
                    stdVarIdsWithType.put(cvtermId, this.getDefaultVariableType(cvtermId));
                    stdVarMap.put(cvtermName, stdVarIdsWithType);

                }
                if (propertyNameOrSynonyms.contains(cvtermSynonym)) {
                    if (stdVarMap.containsKey(cvtermSynonym)) {
                        stdVarIdsWithType = stdVarMap.get(cvtermSynonym);
                    }
                    stdVarIdsWithType.put(cvtermId, this.getDefaultVariableType(cvtermId));
                    stdVarMap.put(cvtermSynonym, stdVarIdsWithType);
                }

            }

        }

    } catch (final HibernateException e) {
        this.logAndThrowException("Error in getStandardVariableIdsWithTypeByProperties="
                + propertyNameOrSynonyms + " in CVTermDao: " + e.getMessage(), e);
    }

    return stdVarMap;

}

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

License:Open Source License

/**
 * Retrieves the properties of Trait Classes
 *//*from   w  w  w . j  av a2  s.  c  om*/
public Map<Integer, List<PropertyReference>> getPropertiesOfTraitClasses(final List<Integer> traitClassIds) {

    final Map<Integer, List<PropertyReference>> propertiesOfTraitClasses = new HashMap<>();

    if (traitClassIds.isEmpty()) {
        return propertiesOfTraitClasses;
    }

    Collections.sort(traitClassIds);

    try {

        final StringBuilder sqlString = new StringBuilder()
                .append("SELECT cvterm_id, name, definition, cvr.object_id ")
                .append("FROM cvterm cvt JOIN cvterm_relationship cvr ")
                .append("ON cvt.cvterm_id = cvr.subject_id AND cvr.type_id = ").append(TermId.IS_A.getId())
                .append(" ").append(" AND cvr.object_id  IN (:traitClassIds) ").append("WHERE cv_id =  ")
                .append(CvId.PROPERTIES.getId()).append(" ").append("ORDER BY cvr.object_id ");

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
        query.setParameterList("traitClassIds", traitClassIds);

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

        List<PropertyReference> properties = new ArrayList<>();
        Integer prevTraitClassId = traitClassIds.get(0);

        for (final Object[] row : list) {
            final Integer cvtermId = (Integer) row[0];
            final String cvtermName = (String) row[1];
            final String cvtermDefinition = (String) row[2];
            final Integer traitClassId = (Integer) row[3];

            if (!prevTraitClassId.equals(traitClassId)) {
                propertiesOfTraitClasses.put(prevTraitClassId, properties);
                properties = new ArrayList<>();
                prevTraitClassId = traitClassId;
            }
            properties.add(new PropertyReference(cvtermId, cvtermName, cvtermDefinition));
        }

        propertiesOfTraitClasses.put(prevTraitClassId, properties);

    } catch (final HibernateException e) {
        this.logAndThrowException("Error at getTraitClassProperties() query on CVTermDao: " + e.getMessage(),
                e);
    }

    return propertiesOfTraitClasses;
}

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

License:Open Source License

/**
 * Retrieves the standard variables of trait properties
 *///from www . j  a v  a  2s  .  c  o  m
public Map<Integer, List<StandardVariableReference>> getStandardVariablesOfProperties(
        final List<Integer> propertyIds) {
    final Map<Integer, List<StandardVariableReference>> variablesOfProperties = new HashMap<>();

    if (propertyIds.isEmpty()) {
        return variablesOfProperties;
    }

    Collections.sort(propertyIds);

    try {
        final StringBuilder sqlString = new StringBuilder()
                .append("SELECT cvterm_id, name, definition, cvr.object_id ")
                .append("FROM cvterm cvt JOIN cvterm_relationship cvr ")
                .append("ON cvt.cvterm_id = cvr.subject_id AND cvr.type_id = ")
                .append(TermId.HAS_PROPERTY.getId()).append(" AND cvr.object_id  IN (:propertyIds) ")
                .append("ORDER BY cvr.object_id ");

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
        query.setParameterList("propertyIds", propertyIds);

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

        List<StandardVariableReference> variables = new ArrayList<>();
        Integer prevPropertyId = propertyIds.get(0);

        for (final Object[] row : list) {
            final Integer cvtermId = (Integer) row[0];
            final String cvtermName = (String) row[1];
            final String cvtermDefinition = (String) row[2];
            final Integer traitClassId = (Integer) row[3];

            if (!prevPropertyId.equals(traitClassId)) {
                variablesOfProperties.put(prevPropertyId, variables);
                variables = new ArrayList<>();
                prevPropertyId = traitClassId;
            }
            variables.add(new StandardVariableReference(cvtermId, cvtermName, cvtermDefinition));
        }

        variablesOfProperties.put(prevPropertyId, variables);

    } catch (final HibernateException e) {
        this.logAndThrowException(
                "Error at getStandardVariablesOfProperties() query on CVTermDao: " + e.getMessage(), e);
    }

    return variablesOfProperties;
}

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

License:Open Source License

public boolean hasPossibleTreatmentPairs(final int cvTermId, final int propertyId,
        final List<Integer> hiddenFields) {
    try {//from   w w  w . j  a va 2s  .co m
        final StringBuilder sqlString = new StringBuilder().append("SELECT count(c.cvterm_id) ")
                .append(" FROM cvterm c ").append(" INNER JOIN cvterm_relationship pr ON pr.type_id = ")
                .append(TermId.HAS_PROPERTY.getId()).append("   AND pr.subject_id = c.cvterm_id ")
                .append("   AND pr.object_id = ").append(propertyId)
                .append(" INNER JOIN cvterm_relationship sr ON sr.type_id = ").append(TermId.HAS_SCALE.getId())
                .append("   AND sr.subject_id = c.cvterm_id ")
                .append(" INNER JOIN cvterm_relationship mr ON mr.type_id = ").append(TermId.HAS_METHOD.getId())
                .append("   AND mr.subject_id = c.cvterm_id ")
                .append(" INNER JOIN cvtermprop cvprop ON cvprop.type_id = ")
                .append(TermId.VARIABLE_TYPE.getId())
                .append("   AND cvprop.cvterm_id = c.cvterm_id AND cvprop.value = '")
                .append(VariableType.TREATMENT_FACTOR.getName()).append("' WHERE c.cvterm_id <> ")
                .append(cvTermId).append("   AND c.cvterm_id NOT IN (:hiddenFields) ");

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString());
        query.setParameterList("hiddenFields", hiddenFields);
        final long count = ((BigInteger) query.uniqueResult()).longValue();
        return count > 0;

    } catch (final HibernateException e) {
        this.logAndThrowException("Error in getAllPossibleTreatmentPairs in CVTermDao: " + e.getMessage(), e);
    }
    return false;
}

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

License:Open Source License

public List<StandardVariable> getAllPossibleTreatmentPairs(final int cvTermId, final int propertyId,
        final List<Integer> hiddenFields) {

    final List<StandardVariable> list = new ArrayList<>();

    try {/*  w  w  w.  ja  v  a 2  s. c om*/
        final StringBuilder sqlString = new StringBuilder().append(
                "SELECT c.cvterm_id, c.name, c.definition, pr.object_id AS propertyId, sr.object_id AS scaleId, mr.object_id AS methodId ")
                .append(" FROM cvterm c ").append(" INNER JOIN cvterm_relationship pr ON pr.type_id = ")
                .append(TermId.HAS_PROPERTY.getId())
                .append("   AND pr.subject_id = c.cvterm_id and pr.object_id = ").append(propertyId)
                .append(" INNER JOIN cvterm_relationship sr ON sr.type_id = ").append(TermId.HAS_SCALE.getId())
                .append("   AND sr.subject_id = c.cvterm_id ")
                .append(" INNER JOIN cvterm_relationship mr ON mr.type_id = ").append(TermId.HAS_METHOD.getId())
                .append("   AND mr.subject_id = c.cvterm_id ")
                .append(" INNER JOIN cvtermprop cvprop ON cvprop.type_id = ")
                .append(TermId.VARIABLE_TYPE.getId())
                .append("   AND cvprop.cvterm_id = c.cvterm_id AND cvprop.value = '")
                .append(VariableType.TREATMENT_FACTOR.getName()).append("' WHERE c.cvterm_id <> ")
                .append(cvTermId).append("   AND c.cvterm_id NOT IN (:hiddenFields) ");

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString()).addScalar("cvterm_id")
                .addScalar("name").addScalar("definition").addScalar("propertyId").addScalar("scaleId")
                .addScalar("methodId");

        query.setParameterList("hiddenFields", hiddenFields);

        final List<Object[]> results = query.list();
        for (final Object[] row : results) {
            final StandardVariable variable = new StandardVariable();
            variable.setId((Integer) row[0]);
            variable.setName((String) row[1]);
            variable.setDescription((String) row[2]);
            variable.setProperty(new Term((Integer) row[3], null, null));
            variable.setScale(new Term((Integer) row[4], null, null));
            variable.setMethod(new Term((Integer) row[5], null, null));
            list.add(variable);
        }

    } catch (final HibernateException e) {
        this.logAndThrowException("Error in getAllPossibleTreatmentPairs in CVTermDao: " + e.getMessage(), e);
    }

    return list;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<UserDefinedField> getAttributeTypesByGIDList(final List<Integer> gidList) {
    List<UserDefinedField> returnList = new ArrayList<>();
    if (gidList != null && !gidList.isEmpty()) {
        try {/*from  w  ww .  j  a  va 2  s . c  o  m*/
            final String sql = "SELECT DISTINCT {u.*}" + " FROM atributs a" + " INNER JOIN udflds u"
                    + " WHERE a.atype=u.fldno" + " AND a.gid in (:gidList)" + " ORDER BY u.fname";
            final SQLQuery query = this.getSession().createSQLQuery(sql);
            query.addEntity("u", UserDefinedField.class);
            query.setParameterList("gidList", gidList);
            returnList = query.list();

        } catch (final HibernateException e) {
            throw new MiddlewareQueryException(
                    "Error with getAttributesByGIDList(gidList=" + gidList + "): " + e.getMessage(), e);
        }
    }
    return returnList;
}