Example usage for org.hibernate SQLQuery uniqueResult

Introduction

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

Prototype

R uniqueResult();

Source Link

Document

Convenience method to return a single instance that matches the query, or null if the query returns no results.

Usage

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

License:Open Source License

/**
 * Detect the usage of the specified variable in any programs except for the specified programUUID.
 *
 * @param variableId    - The term id of the variable (e.g. 8190 to look for variable LOCATION_NAME_ID)
 * @param variableValue - The value of the variable (e.g. 101 which is the location name id of the location "India")
 * @param programUUID/*  w ww.j  ava  2s.  com*/
 * @return
 */
public boolean isVariableUsedInOtherPrograms(final String variableId, final String variableValue,
        final String programUUID) {
    Preconditions.checkNotNull(variableId);
    Preconditions.checkNotNull(variableValue);

    // Check if the variable is used in trial level and/or environment level of studies except for the specified programUUID.
    final SQLQuery query = this.getSession().createSQLQuery("SELECT CASE WHEN\n"
            + "            (EXISTS( SELECT project.* FROM\n" + "                    projectprop INNER JOIN\n"
            + "                    project ON project.project_id = projectprop.project_id WHERE\n"
            + "                    projectprop.variable_id = :variableId AND projectprop.value = :variableValue\n"
            + "                        AND project.program_uuid <> :programUUID AND project.deleted = 0)) = 1 "
            + "                  OR " + "            (EXISTS( SELECT \n"
            + "                    project.* FROM project\n" + "                        INNER JOIN\n"
            + "                    nd_experiment ON nd_experiment.project_id = project.project_id\n"
            + "                        INNER JOIN\n"
            + "                    nd_geolocationprop ON nd_experiment.nd_geolocation_id = nd_geolocationprop.nd_geolocation_id"
            + "                WHERE nd_geolocationprop.type_id = :variableId\n"
            + "                        AND nd_geolocationprop.value = :variableValue\n"
            + "                        AND project.program_uuid <> :programUUID AND project.deleted = 0)) = 1 THEN 1 ELSE 0\n"
            + "    END;");
    query.setParameter("variableId", variableId);
    query.setParameter("variableValue", variableValue);
    query.setParameter("programUUID", programUUID);

    return ((BigInteger) query.uniqueResult()).intValue() != 0;

}

---

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

License:Open Source License

/***
 * Count calculated traits in the speficified datasets.
 * @param projectIds// w w w.  j a  v  a  2  s.com
 * @return
 */
public int countCalculatedVariablesInDatasets(final Set<Integer> projectIds) {
    // Check if the variable is used in trial level and/or environment level of studies except for the specified programUUID.
    final SQLQuery query = this.getSession().createSQLQuery(COUNT_CALCULATED_VARIABLES_IN_DATASETS);
    query.setParameterList("projectIds", projectIds);
    return ((BigInteger) query.uniqueResult()).intValue();

}

---

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

License:Open Source License

public long countByVariable(final int variableId) {
    try {/*w ww. j  a va2 s.  c  o m*/
        final SQLQuery query = this.getSession().createSQLQuery(DmsProjectDao.COUNT_PROJECTS_WITH_VARIABLE);
        query.setParameter("variableId", variableId);

        return ((BigInteger) query.uniqueResult()).longValue();

    } catch (final HibernateException e) {
        final String errorMessage = "Error at countByVariable=" + variableId + " in DmsProjectDao: "
                + e.getMessage();
        DmsProjectDao.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
}

---

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

License:Open Source License

public boolean hasFieldmap(final int datasetId) {
    try {//from   www . j  av a 2s.  c o  m
        final String sql = "SELECT COUNT(eprop.value) " + " FROM nd_experiment ep "
                + " INNER JOIN nd_experimentprop eprop ON eprop.nd_experiment_id = ep.nd_experiment_id "
                + "    AND eprop.type_id = " + TermId.RANGE_NO.getId() + " AND eprop.value <> '' "
                + " WHERE ep.project_id = " + datasetId + "  LIMIT 1 ";
        final SQLQuery query = this.getSession().createSQLQuery(sql);
        final BigInteger count = (BigInteger) query.uniqueResult();
        return count != null && count.longValue() > 0;

    } catch (final HibernateException e) {
        final String message = "Error at hasFieldmap=" + datasetId + " query at ExperimentDao: "
                + e.getMessage();
        ExperimentDao.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }
}

---

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

License:Open Source License

public boolean checkIfAnyLocationIDsExistInExperiments(final int dataSetId, final List<Integer> locationIds) {

    try {/*  w w  w. ja  v a  2  s.  c  om*/
        final String sql = "SELECT count(*) FROM nd_experiment exp "
                + "WHERE exp.nd_geolocation_id in (:locationIds) " + "AND exp.project_id = :dataSetId ";

        final SQLQuery query = this.getSession().createSQLQuery(sql);
        query.setParameterList("locationIds", locationIds);
        query.setParameter("dataSetId", dataSetId);

        long count = 0L;
        final Object obj = query.uniqueResult();
        if (obj != null) {
            count = ((Number) obj).longValue();
        }

        return count != 0;

    } catch (final HibernateException e) {
        final String message = "Error at checkIfLocationIDsExistInExperiments=" + locationIds + "," + dataSetId
                + "," + " query at ExperimentDao: " + e.getMessage();
        ExperimentDao.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }

}

---

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

License:Open Source License

public int getExperimentIdByLocationIdStockId(final int projectId, final Integer locationId,
        final Integer stockId) {
    try {//from   w w  w . jav a 2s.c  o  m
        final String sql = "SELECT exp.nd_experiment_id " + "FROM nd_experiment exp "
                + " WHERE exp.project_id = " + projectId + " AND exp.nd_geolocation_id = " + locationId
                + " AND exp.type_id = 1170 " + " AND exp.stock_id = " + stockId;

        final SQLQuery statement = this.getSession().createSQLQuery(sql);
        final Integer returnVal = (Integer) statement.uniqueResult();

        if (returnVal == null) {
            return 0;
        } else {
            return returnVal;
        }

    } catch (final HibernateException e) {
        final String message = "Error in getExperimentIdByLocationIdStockId=" + projectId;
        ExperimentDao.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }
}

---

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

License:Open Source License

public long countStocksByDatasetId(final int datasetId) {

    final StringBuilder sql = new StringBuilder();
    sql.append("SELECT COUNT(DISTINCT e.stock_id) FROM nd_experiment e ")
            .append(" WHERE e.project_id = :datasetId");

    try {/* w w w .j a va  2s . c  om*/
        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameter("datasetId", datasetId);
        final BigInteger count = (BigInteger) query.uniqueResult();
        return count.longValue();

    } catch (final HibernateException e) {
        final String error = "Error at countStocksByDatasetId=" + datasetId + " query at ExperimentDao: "
                + e.getMessage();
        ExperimentDao.LOG.error(error);
        throw new MiddlewareQueryException(error, e);
    }
}

---

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

License:Open Source License

public boolean areAllInstancesExistInDataset(final int datasetId, final Set<Integer> instanceIds) {

    final StringBuilder sql = new StringBuilder();
    sql.append("SELECT COUNT(DISTINCT e.nd_geolocation_id) FROM nd_experiment e ")
            .append(" WHERE e.project_id = :datasetId and e.nd_geolocation_id in (:instanceIds)");

    try {/* ww  w .  ja  v a  2  s .c o  m*/

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

        final BigInteger count = (BigInteger) query.uniqueResult();
        return count.intValue() == instanceIds.size();

    } catch (final HibernateException e) {
        final String error = "Error at areAllInstancesExistInDataset=" + datasetId + "," + instanceIds
                + " query at ExperimentDao: " + e.getMessage();
        ExperimentDao.LOG.error(error);
        throw new MiddlewareQueryException(error, e);
    }
}

---

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

License:Open Source License

public long countByObservedVariable(final int variableId, final int variableTypeId)
        throws MiddlewareQueryException {
    try {/*from w w  w .  jav  a2 s.com*/
        String sql = null;
        if (VariableType.STUDY_DETAIL.getId() == variableTypeId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_PROJECTPROP;
        } else if (TermId.TRIAL_INSTANCE_FACTOR.getId() == variableId || TermId.LATITUDE.getId() == variableId
                || TermId.LONGITUDE.getId() == variableId || TermId.GEODETIC_DATUM.getId() == variableId
                || TermId.ALTITUDE.getId() == variableId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_GEOLOCATION;
        } else if (VariableType.ENVIRONMENT_DETAIL.getId() == variableTypeId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_GEOLOCATIONPROP;
        } else if (VariableType.EXPERIMENTAL_DESIGN.getId() == variableTypeId
                || VariableType.TREATMENT_FACTOR.getId() == variableTypeId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_EXPERIMENTPROP;
        } else if (TermId.ENTRY_NO.getId() == variableId || TermId.GID.getId() == variableId
                || TermId.DESIG.getId() == variableId || TermId.ENTRY_CODE.getId() == variableId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_STOCK;
        } else if (VariableType.GERMPLASM_DESCRIPTOR.getId() == variableTypeId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_STOCKPROP;
        } else if (VariableType.TRAIT.getId() == variableTypeId
                || VariableType.ANALYSIS.getId() == variableTypeId
                || VariableType.STUDY_CONDITION.getId() == variableTypeId
                || VariableType.SELECTION_METHOD.getId() == variableTypeId) {
            sql = ExperimentDao.COUNT_EXPERIMENT_BY_VARIABLE_IN_PHENOTYPE;
        }

        if (sql != null) {
            final SQLQuery query = this.getSession().createSQLQuery(sql);
            if (sql.indexOf(":variableId") > -1) {
                query.setParameter("variableId", variableId);
            }
            return ((BigInteger) query.uniqueResult()).longValue();
        }

    } catch (final HibernateException e) {
        final String message = "Error at countByObservationVariable query at ExperimentDao: " + e.getMessage();
        ExperimentDao.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }
    return 0;
}

---

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

License:Open Source License

public String getGeolocationPropValue(final int stdVarId, final int studyId) {
    try {//  ww  w .j  a va2 s.com
        final StringBuilder sql = new StringBuilder().append("SELECT value ").append("FROM nd_experiment e ")
                .append("INNER JOIN nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id ")
                .append("WHERE e.project_id = :projectId AND gp.type_id = :stdVarId ORDER BY e.nd_geolocation_id ");
        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameter("projectId", studyId);
        query.setParameter("stdVarId", stdVarId);
        return (String) query.uniqueResult();
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException("Error at getGeolocationPropValue=" + stdVarId
                + " query on GeolocationPropertyDao: " + 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
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• »