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.dms.PhenotypeDao.java

License:Open Source License

public void updateOutOfSyncPhenotypesByGeolocation(final int geoLocationId,
        final Set<Integer> targetVariableIds) {
    final String sql = "UPDATE nd_experiment experiment\n"
            + "LEFT JOIN nd_experiment experimentParent ON experimentParent.nd_experiment_id = experiment.parent_id\n"
            + "INNER JOIN phenotype pheno ON  pheno.nd_experiment_id = experimentParent.nd_experiment_id OR pheno.nd_experiment_id = experiment.nd_experiment_id\n"
            + "SET pheno.status = :status \n"
            + "WHERE experiment.nd_geolocation_id = :geoLocationId  AND pheno.observable_id in (:variableIds) ;";

    final SQLQuery statement = this.getSession().createSQLQuery(sql);
    statement.setParameter("status", Phenotype.ValueStatus.OUT_OF_SYNC.getName());
    statement.setParameter("geoLocationId", geoLocationId);
    statement.setParameterList("variableIds", targetVariableIds);
    statement.executeUpdate();/*w ww. j  a  v a  2s.  c  o m*/
}

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.FactorDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public Set<Integer> getGIDSByObservationUnitIds(Set<Integer> ounitIds, int start, int numOfRows)
        throws MiddlewareQueryException {
    Set<Integer> results = new HashSet<Integer>();
    try {//  w ww. j ava2s . c om
        SQLQuery levelNQuery = getSession()
                .createSQLQuery(Factor.GET_GID_FROM_NUMERIC_LEVELS_GIVEN_OBSERVATION_UNIT_IDS);
        levelNQuery.setParameterList("ounitids", ounitIds);
        levelNQuery.setFirstResult(start);
        levelNQuery.setMaxResults(numOfRows);

        List<Double> gids1 = levelNQuery.list();
        for (Double gid : gids1) {
            results.add(gid.intValue());
        }

        SQLQuery levelCQuery = getSession()
                .createSQLQuery(Factor.GET_GID_FROM_CHARACTER_LEVELS_GIVEN_OBSERVATION_UNIT_IDS);
        levelCQuery.setParameterList("ounitids", ounitIds);
        levelCQuery.setFirstResult(start);
        levelCQuery.setMaxResults(numOfRows);

        List<String> gids2 = levelCQuery.list();
        for (String gid : gids2) {
            results.add(Integer.parseInt(gid));
        }

        return results;
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getGIDSByObservationUnitIds(ounitIds=" + ounitIds
                + ") query from Factor: " + e.getMessage(), e);
    }
}

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

License:Open Source License

@SuppressWarnings("unchecked")
public List<Integer> getDatasetIdsByGermplasmIds(List<Integer> gIds) throws MiddlewareQueryException {
    try {//from  w w w.  j a v  a  2  s .c o  m
        if (gIds != null && !gIds.isEmpty()) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(AccMetadataSetDAO.GET_DATASET_IDS_BY_GERMPLASM_IDS);
            query.setParameterList("gIdList", gIds);
            return query.list();
        }
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getDatasetIdsByGermplasmIds(" + gIds
                + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
    return new ArrayList<Integer>();
}

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

License:Open Source License

@SuppressWarnings("unchecked")
//FIXME/* ww w  .jav  a  2  s.  c om*/
public Set<Integer> getNIdsByMarkerIdsAndDatasetIdsAndNotGIds(List<Integer> datasetIds, List<Integer> markerIds,
        List<Integer> gIds, int start, int numOfRows) throws MiddlewareQueryException {
    try {

        if (datasetIds != null && !datasetIds.isEmpty()) {
            StringBuilder queryString = new StringBuilder(
                    AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_AND_MARKER_IDS_AND_NOT_GIDS_SELECT);
            queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_AND_MARKER_IDS_AND_NOT_GIDS_FROM);

            if (markerIds != null && !markerIds.isEmpty()) {
                queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_FILTER_BY_MARKER_IDS);
            }

            if (gIds != null && !gIds.isEmpty()) {
                queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_FILTER_NOT_BY_GIDS);
            }

            queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_ORDER);

            SQLQuery query;
            query = this.getSession().createSQLQuery(queryString.toString());
            query.setParameterList("represnos", datasetIds);
            if (markerIds != null && !markerIds.isEmpty()) {
                query.setParameterList("markerids", markerIds);
            }
            if (gIds != null && !gIds.isEmpty()) {
                query.setParameterList("gids", gIds);
            }
            query.setFirstResult(start);
            query.setMaxResults(numOfRows);
            return new TreeSet<Integer>(query.list());
        }

    } catch (HibernateException e) {
        throw new MiddlewareQueryException(
                "Error with getNIDsByDatasetIdsAndMarkerIdsAndNotGIDs(datasetIds=" + datasetIds + ", markerIds="
                        + markerIds + ", gIds=" + gIds + ") query from AccMetadataSet: " + e.getMessage(),
                e);
    }
    return new TreeSet<Integer>();
}

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

License:Open Source License

public long countNIdsByMarkerIdsAndDatasetIdsAndNotGIds(List<Integer> datasetIds, List<Integer> markerIds,
        List<Integer> gIds) throws MiddlewareQueryException {
    try {/*  ww  w  . j  a v a 2 s  . c  o  m*/

        if (datasetIds != null && !datasetIds.isEmpty()) {
            StringBuilder queryString = new StringBuilder(
                    AccMetadataSetDAO.COUNT_NIDS_BY_DATASET_IDS_AND_MARKER_IDS_AND_NOT_GIDS_SELECT);
            queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_AND_MARKER_IDS_AND_NOT_GIDS_FROM);

            if (markerIds != null && !markerIds.isEmpty()) {
                queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_FILTER_BY_MARKER_IDS);
            }

            if (gIds != null && !gIds.isEmpty()) {
                queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_FILTER_NOT_BY_GIDS);
            }

            queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_ORDER);

            SQLQuery query;
            query = this.getSession().createSQLQuery(queryString.toString());
            query.setParameterList("represnos", datasetIds);
            if (markerIds != null && !markerIds.isEmpty()) {
                query.setParameterList("markerids", markerIds);
            }
            if (gIds != null && !gIds.isEmpty()) {
                query.setParameterList("gids", gIds);
            }

            return ((BigInteger) query.uniqueResult()).intValue();
        }

    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with countNIDsByDatasetIdsAndMarkerIdsAndNotGIDs(datasetIds="
                + datasetIds + ", markerIds=" + markerIds + ", gIds=" + gIds + ") query from AccMetadataSet: "
                + e.getMessage(), e);
    }
    return 0;
}

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

License:Open Source License

@SuppressWarnings("unchecked")
//FIXME//from  ww w . ja va 2s  .  c  o  m
public Set<Integer> getNIdsByMarkerIdsAndDatasetIds(List<Integer> datasetIds, List<Integer> markerIds)
        throws MiddlewareQueryException {
    try {

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

            StringBuilder queryString = new StringBuilder(
                    AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_AND_MARKER_IDS);

            if (markerIds != null && !markerIds.isEmpty()) {
                queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_FILTER_BY_MARKER_IDS);
            }
            queryString.append(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS_ORDER);

            SQLQuery query;
            query = this.getSession().createSQLQuery(queryString.toString());
            query.setParameterList("represnos", datasetIds);
            if (markerIds != null && !markerIds.isEmpty()) {
                query.setParameterList("markerids", markerIds);
            }
            return new TreeSet<Integer>(query.list());
        }

    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getNIdsByMarkerIdsAndDatasetIds(datasetIds=" + datasetIds
                + ", markerIds=" + markerIds + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
    return new TreeSet<Integer>();
}

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

License:Open Source License

public long countAccMetadataSetsByGids(List<Integer> gids) throws MiddlewareQueryException {
    long count = 0;
    try {/*from ww w.j a va2 s.  c o  m*/
        if (gids != null && !gids.isEmpty()) {
            SQLQuery query = this.getSession().createSQLQuery(AccMetadataSetDAO.COUNT_ACC_METADATASETS_BY_GIDS);
            query.setParameterList("gids", gids);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                count = result.longValue();
            }
        }
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with countAccMetadataSetByGids(gids=" + gids
                + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
    return count;
}

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

License:Open Source License

public List<Integer> getNidsByDatasetIds(List<Integer> datasetIds) throws MiddlewareQueryException {
    List<Integer> results = new ArrayList<>();
    try {//from   ww w  . j  av  a 2 s. c  o m
        SQLQuery query = this.getSession().createSQLQuery(AccMetadataSetDAO.GET_NIDS_BY_DATASET_IDS);
        query.setParameterList("datasetIds", datasetIds);
        results = query.list();

    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getNidsByDatasetIds=" + datasetIds
                + ") query from AccMetadataSet: " + e.getMessage(), e);
    }
    return results;
}

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

License:Open Source License

public long countSampleIdsByDatasetIds(List<Integer> datasetIds) throws MiddlewareQueryException {
    long count = 0;
    try {/*w ww .  ja va 2  s. c  o  m*/
        if (datasetIds != null && !datasetIds.isEmpty()) {
            SQLQuery query = this.getSession()
                    .createSQLQuery(AccMetadataSetDAO.COUNT_SAMPLE_IDS_BY_DATASET_IDS);
            query.setParameterList("datasetIds", datasetIds);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                count = result.longValue();
            }
        }

    } catch (HibernateException e) {
        throw new MiddlewareQueryException(
                "Error with countSampleIdsByDatasetIds=" + datasetIds + ") query from AccMetadataSet", e);
    }
    return count;
}