Example usage for org.hibernate SQLQuery uniqueResult

List of usage examples for org.hibernate SQLQuery uniqueResult

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

License:Open Source License

public long countIsAOfTermsByCvId(final CvId cvId) {

    try {/*from  w  w  w  . jav a 2 s.  c o  m*/

        final SQLQuery query = this.getSession()
                .createSQLQuery("SELECT COUNT(DISTINCT isA.cvterm_id) "
                        + "FROM cvterm isA, cvterm_relationship rel, cvterm subj " + "WHERE subj.cv_id = :cvId "
                        + "AND subj.cvterm_id = rel.subject_id " + "AND rel.object_id = isA.cvterm_id "
                        + "AND rel.type_id = " + TermId.IS_A.getId() + " ");
        query.setParameter("cvId", cvId.getId());

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

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

    return 0;
}

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

License:Open Source License

public Integer getStandardVariableIdByTermId(final int cvTermId, final TermId termId) {
    try {//from  w  w  w . ja v a  2 s . c om
        final StringBuilder queryString = new StringBuilder();
        queryString.append("SELECT DISTINCT cvr.subject_id ");
        queryString.append("FROM cvterm_relationship cvr ");
        queryString.append(
                "INNER JOIN cvterm_relationship cvrt ON cvr.subject_id = cvrt.subject_id AND cvrt.type_id = :typeId ");
        queryString.append("WHERE cvr.object_id = :cvTermId ");
        queryString.append("ORDER BY cvr.subject_id ").append(" LIMIT 0,1");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameter("typeId", termId.getId());
        query.setParameter("cvTermId", cvTermId);

        return (Integer) query.uniqueResult();

    } catch (final HibernateException e) {
        this.logAndThrowException("Error at getStandadardVariableIdByTermId :" + e.getMessage(), e);
    }
    return null;
}

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. ja va 2s  . c o 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 Scale getInventoryScaleByName(final String name) {
    Scale scale = new Scale();
    try {/*from   www.  ja  v a 2  s.  c o m*/
        final StringBuilder sql = this.buildQueryForInventoryScales();
        sql.append(" AND prs.name = :name");

        final SQLQuery query = this.getSession().createSQLQuery(sql.toString()).addScalar("id")
                .addScalar("scalename").addScalar("methodname").addScalar("name").addScalar("definition");
        query.setParameter("name", name);
        final Object[] result = (Object[]) query.uniqueResult();
        if (result != null) {
            final String displayName = result[1] + " - " + result[2];
            scale = new Scale(new Term((Integer) result[0], result[3].toString(), result[4].toString()));
            scale.setDisplayName(displayName);
            return scale;
        }

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

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

License:Open Source License

public Integer retrieveAnalysisDerivedVariableID(final Integer originalVariableID,
        final Integer analysisMethodTermID) {
    try {/*from  www.  j  av  a 2s.c  o  m*/
        final String sqlQuery = "select cr.object_id from cvterm_relationship cr WHERE cr.type_id = "
                + TermId.HAS_ANALYSIS_VARIABLE.getId() + " and cr.subject_id = :variableID AND EXISTS "
                + "(SELECT 1 FROM cvterm_relationship mr WHERE cr.object_id = mr.subject_id AND mr.type_id = "
                + TermId.HAS_METHOD.getId() + " AND mr.object_id = :methodID)";
        final SQLQuery query = this.getSession().createSQLQuery(sqlQuery);
        query.setParameter("variableID", originalVariableID);
        query.setParameter("methodID", analysisMethodTermID);

        final Object result = query.uniqueResult();
        if (result == null) {
            return null;
        } else {
            return (Integer) result;
        }
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException("Error with retrieveAnalysisDerivedVariableID=" + originalVariableID
                + ", " + analysisMethodTermID + " query from CVTermRelationship: " + e.getMessage(), e);
    }
}

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

License:Open Source License

public boolean isPersonExists(final String firstName, final String lastName) throws MiddlewareQueryException {
    try {/*from  w w w. j a v a2  s. c  o  m*/
        final StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM persons p ").append("WHERE UPPER(p.fname) = :firstName ")
                .append("AND UPPER(p.lname) = :lastName");

        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameter("firstName", firstName);
        query.setParameter("lastName", lastName);

        final BigInteger count = (BigInteger) query.uniqueResult();

        return count.longValue() > 0;
    } catch (final HibernateException e) {
        this.logAndThrowException("Error with isPersonExists(firstName=" + firstName + ", lastName=" + lastName
                + ") query from Person: " + e.getMessage(), e);
    }
    return false;
}

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

License:Open Source License

public boolean isPersonWithEmailExists(final String email) throws MiddlewareQueryException {
    try {//from w w w  . j av  a  2s.c  om
        final StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM persons p ").append("WHERE UPPER(p.pemail) = :email");
        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameter("email", email);

        final BigInteger count = (BigInteger) query.uniqueResult();

        return count.longValue() > 0;

    } catch (final HibernateException e) {
        this.logAndThrowException(
                "Error with isPersonWithEmailExists(email=" + email + ") query from Person: " + e.getMessage(),
                e);
    }

    return false;
}

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

License:Open Source License

public boolean isPersonWithUsernameAndEmailExists(final String username, final String email)
        throws MiddlewareQueryException {
    try {//from  w  ww . j av  a2 s .  c o m
        final StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT COUNT(1) FROM USERS users join PERSONS persons on users.personid = persons.personid ")
                .append("WHERE users.uname = :username and persons.pemail = :email");
        final SQLQuery query = this.getSession().createSQLQuery(sql.toString());
        query.setParameter("email", email);
        query.setParameter("username", username);

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

    } catch (final HibernateException e) {
        this.logAndThrowException(
                "Error with isPersonWithEmailExists(email=" + email + ") query from Person: " + e.getMessage(),
                e);
    }

    return false;
}

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

License:Open Source License

/**
 * Returns the number of {@link ProjectActivity} records by project id.
 *
 * @param projectId the project id/*from   w w w.j a va2 s  .  co m*/
 * @return the number of {@link ProjectActivity} records
 * @throws MiddlewareQueryException the MiddlewareQueryException
 */
public long countByProjectId(Long projectId) throws MiddlewareQueryException {
    try {
        if (projectId != null) {
            SQLQuery query = this.getSession().createSQLQuery(ProjectActivity.COUNT_ACTIVITIES_BY_PROJECT_ID);
            query.setParameter("projectId", projectId.intValue());
            BigInteger result = (BigInteger) query.uniqueResult();
            return result.longValue();
        }
    } catch (HibernateException e) {
        this.logAndThrowException("Error with countByProjectId(projectId=" + projectId
                + ") query from ProjectActivity " + e.getMessage(), e);
    }
    return 0;
}

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

License:Open Source License

/**
 * Returns the number of {@link Method} records by project id.
 *
 * @param projectId the project id/*w  ww  . ja  v a 2  s .  c  o  m*/
 * @return the number of {@link Method} records
 * @throws MiddlewareQueryException the MiddlewareQueryException
 */
public long countByProjectId(Long projectId) throws MiddlewareQueryException {
    try {
        SQLQuery query = getSession().createSQLQuery(ProjectMethod.COUNT_METHODS_BY_PROJECT_ID);
        query.setParameter("projectId", projectId.intValue());
        BigInteger result = (BigInteger) query.uniqueResult();
        return result.longValue();
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with countByProjectId(projectId=" + projectId
                + ") query from ProjectMethod: " + e.getMessage(), e);
    }
}