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

License:Open Source License

@Override
public Germplasm getById(final Integer gid) {
    try {//from  w w  w .j  a  v  a2  s .  c  o m
        final StringBuilder queryString = new StringBuilder();
        queryString.append("SELECT g.* FROM germplsm g WHERE g.deleted = 0 AND gid=:gid LIMIT 1");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameter("gid", gid);
        query.addEntity("g", Germplasm.class);

        return (Germplasm) query.uniqueResult();

    } catch (final HibernateException e) {
        final String errorMessage = "Error with getById(gid=" + gid + GermplasmDAO.QUERY_FROM_GERMPLASM
                + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
}

---

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

License:Open Source License

public long countByNamePermutations(final String name, final Operation operation) {

    // Converting supplied value to combination of names that can exists in names
    final List<String> names = GermplasmDataManagerUtil.createNamePermutations(name);

    if (names == null || names.isEmpty()) {
        return 0;
    }//from   w  w w.  j ava  2  s  .c o  m

    try {
        final String originalName = names.get(0);
        final String standardizedName = names.get(1);
        final String noSpaceName = names.get(2);

        // Count using = by default
        SQLQuery query = this.getSession().createSQLQuery(Germplasm.COUNT_BY_NAME_ALL_MODES_USING_EQUAL);
        if (operation == Operation.LIKE) {
            query = this.getSession().createSQLQuery(Germplasm.COUNT_BY_NAME_ALL_MODES_USING_LIKE);
        }

        // Set the parameters
        query.setParameter("name", originalName);
        query.setParameter("noSpaceName", noSpaceName);
        query.setParameter("standardizedName", standardizedName);

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

    } catch (final HibernateException e) {
        final String errorMessage = "Error with countByName(names=" + names + GermplasmDAO.QUERY_FROM_GERMPLASM
                + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
}

---

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

License:Open Source License

public long countManagementNeighbors(final Integer gid) {
    try {//from   w  w w  .  j  a v  a  2  s  .  c o m
        if (gid != null) {
            final SQLQuery query = this.getSession().createSQLQuery(Germplasm.COUNT_MANAGEMENT_NEIGHBORS);
            query.setParameter("gid", gid);
            final BigInteger count = (BigInteger) query.uniqueResult();
            return count.longValue();
        }
    } catch (final HibernateException e) {
        final String errorMessage = "Error with countManagementNeighbors(gid=" + gid
                + GermplasmDAO.QUERY_FROM_GERMPLASM + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
    return 0;
}

---

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

License:Open Source License

public long countGroupRelatives(final Integer gid) {
    try {//  w w w  . j  av  a  2s.  com
        if (gid != null) {
            final SQLQuery query = this.getSession().createSQLQuery(Germplasm.COUNT_GROUP_RELATIVES);
            query.setParameter("gid", gid);
            final BigInteger count = (BigInteger) query.uniqueResult();
            return count.longValue();
        }
    } catch (final HibernateException e) {
        final String errorMessage = "Error with countGroupRelatives(gid=" + gid
                + GermplasmDAO.QUERY_FROM_GERMPLASM + e.getMessage();
        GermplasmDAO.LOG.error(errorMessage, e);
        throw new MiddlewareQueryException(errorMessage, e);
    }
    return 0;
}

---

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

License:Open Source License

public String getNextSequenceNumberForCrossName(String prefix) {
    String nextInSequence = "1";

    if (!prefix.isEmpty()) {
        try {//ww  w .  j  a  va2s  . c  o m
            prefix = prefix.trim();
            final StringBuilder sb = new StringBuilder();
            sb.append("SELECT CONVERT(REPLACE(UPPER(nval), :prefix, ''), SIGNED)+1 as next_number ");

            // We used LIKE when matching names by prefix
            sb.append(" FROM ( " + "    SELECT  distinct nval " + "      FROM names "
                    + "      WHERE names.nval LIKE :prefixLike "
                    + "      AND NOT EXISTS (select 1 from germplsm g where g.gid = names.gid and g.deleted = 1)"
                    + " ) matches ");
            sb.append(" ORDER BY next_number desc LIMIT 1");

            final SQLQuery query = this.getSession().createSQLQuery(sb.toString());
            query.setParameter("prefix", prefix.toUpperCase());
            query.setParameter("prefixLike", prefix + "%");

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

            if (nextNumberInSequence != null) {
                nextInSequence = String.valueOf(nextNumberInSequence);
            }

        } catch (final HibernateException e) {
            final String message = "Error with getNextSequenceNumberForCrossName(prefix=" + prefix + ") "
                    + "query : " + e.getMessage();
            GermplasmDAO.LOG.error(message, e);
            throw new MiddlewareQueryException(message, e);
        }
    }

    return nextInSequence;
}

---

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

License:Open Source License

public Germplasm getByLGid(final Integer lgid) {
    try {/*from ww  w. j  a  va  2s .  c om*/
        final StringBuilder queryString = new StringBuilder();
        queryString.append("SELECT g.* FROM germplsm g WHERE g.deleted = 0 AND lgid=:lgid LIMIT 1");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameter("lgid", lgid);
        query.addEntity("g", Germplasm.class);

        return (Germplasm) query.uniqueResult();

    } catch (final HibernateException e) {
        final String message = "Error with getByLGid(lgid=" + lgid + GermplasmDAO.QUERY_FROM_GERMPLASM
                + e.getMessage();
        GermplasmDAO.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }
}

---

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

License:Open Source License

public long countGermplasmDTOs(final GermplasmSearchRequestDto germplasmSearchRequestDTO) {

    String queryString = "SELECT COUNT(1) " + "  FROM germplsm g " //
            + "  LEFT JOIN reflinks reference ON reference.brefid = g.gref WHERE g.deleted = 0 AND g.grplce = 0"; //

    if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) {
        queryString = queryString + "      AND (SELECT n.nval" //
                + "                    FROM names n" //
                + "                    WHERE n.nstat = 1 AND n.gid = g.gid" //
                + "                    LIMIT 1) like :likeCondition "; //
    }//from www  .j a  va 2  s. c  om

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) {
        queryString = queryString + " AND EXISTS (SELECT 1" //
                + "         FROM names n" //
                + "                  INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'ACCNO' AND u.fldno = n.ntype)" //
                + "         WHERE n.gid = g.gid AND n.nval IN (:accessionNumbers)) "; //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) {
        queryString = queryString + " AND EXISTS (SELECT 1" //
                + "         FROM atributs a" //
                + "                  INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'CROPNM' AND u.fldno = a.atype)" //
                + "         WHERE a.gid = g.gid AND a.aval IN (:commonCropNames)) "; //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) {
        queryString = queryString + " AND g.gid IN (:germplasmDbIds) ";
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) {
        queryString = queryString + " AND EXISTS (SELECT 1" //
                + "         FROM names n" //
                + "                  INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'GENUS' AND u.fldno = n.ntype)" //
                + "         WHERE n.gid = g.gid AND n.nval IN (:germplasmGenus)) "; //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) {
        queryString = queryString + " AND EXISTS (SELECT 1" //
                + "         FROM names n" //
                + "         WHERE n.gid = g.gid AND n.nval IN (:germplasmNames)) "; //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) {
        queryString = queryString + " AND reference.btable IN (:germplasmPUIs) "; //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) {
        queryString = queryString + " AND EXISTS (SELECT 1" //
                + "         FROM atributs a" //
                + "                  INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPNAM' AND u.fldno = a.atype)" //
                + "         WHERE a.gid = g.gid AND a.aval IN (:germplasmSpecies)) "; //
    }

    final SQLQuery sqlQuery = this.getSession().createSQLQuery(queryString);

    if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) {
        sqlQuery.setParameter("likeCondition", "%" + germplasmSearchRequestDTO.getPreferredName() + "%"); //
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) {
        sqlQuery.setParameterList("accessionNumbers", germplasmSearchRequestDTO.getAccessionNumbers());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) {
        sqlQuery.setParameterList("commonCropNames", germplasmSearchRequestDTO.getCommonCropNames());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) {
        sqlQuery.setParameterList("germplasmDbIds", germplasmSearchRequestDTO.getGermplasmDbIds());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) {
        sqlQuery.setParameterList("germplasmGenus", germplasmSearchRequestDTO.getGermplasmGenus());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) {
        sqlQuery.setParameterList("germplasmNames", germplasmSearchRequestDTO.getGermplasmNames());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) {
        sqlQuery.setParameterList("germplasmPUIs", germplasmSearchRequestDTO.getGermplasmPUIs());
    }

    if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) {
        sqlQuery.setParameterList("germplasmSpecies", germplasmSearchRequestDTO.getGermplasmSpecies());
    }

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

}

---

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

License:Open Source License

public long countByListId(final Integer id) {

    // Make sure parameters are not null.
    Preconditions.checkNotNull(id, "List id passed in cannot be null.");

    final StringBuilder sql = new StringBuilder("select count(1) from listdata l, germplsm g");
    sql.append(" where l.gid = g.gid and l.lrstatus != ");
    sql.append(GermplasmListDataDAO.STATUS_DELETED);
    sql.append(" and  g.deleted = 0 ");
    sql.append(" and l.listid = :listId ");
    final Session session = this.getSession();
    final SQLQuery query = session.createSQLQuery(sql.toString());
    query.setParameter(GermplasmListDataDAO.GERMPLASM_LIST_DATA_LIST_ID_COLUMN, id);
    return ((BigInteger) query.uniqueResult()).longValue();
}

---

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

License:Open Source License

public Integer getStandadardVariableIdByPropertyScaleMethod(final Integer propertyId, final Integer scaleId,
        final Integer methodId, final String sortOrder) {
    try {/*from w  w  w.j a  va 2 s. co m*/
        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(
                "WHERE cvrp.object_id = :propertyId AND cvrs.object_id = :scaleId AND cvrm.object_id = :methodId ");
        queryString.append("ORDER BY cvr.subject_id ").append(sortOrder).append(" LIMIT 0,1");

        final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
        query.setParameter("propertyId", propertyId);
        query.setParameter("scaleId", scaleId);
        query.setParameter("methodId", methodId);

        return (Integer) query.uniqueResult();

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

}

---

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

License:Open Source License

public long countTermsByCvId(final CvId cvId) {

    try {//www . ja va  2 s  .  c o  m

        final SQLQuery query = this.getSession()
                .createSQLQuery("SELECT COUNT(cvterm_id) " + "FROM cvterm " + "WHERE cv_id = :cvId ");
        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;
}

---

• «
• 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
• »