Example usage for org.hibernate SQLQuery uniqueResult

List of usage examples 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:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1178] Gerar Relatrio de Acompanhamento dos Boletins de Medio
 * /*from  www. j  av a 2 s. c om*/
 * @author Diogo Peixoto
 * @date 28/07/2011
 * 
 * @param FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper
 * @param relatorioDefinitivo
 * @return Quantidade de OS Executadas para determinado boletim de medio
 * @throws ErroRepositorioException
 */
public Integer pesquisarQuantidadeOSExecutadas(FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper filtro)
        throws ErroRepositorioException {

    Session session = HibernateUtil.getSession();
    StringBuilder sb = new StringBuilder();
    Integer qtdeOSExecutadas = 0;

    try {
        sb.append("SELECT COUNT(execu.orse_id) AS quantidade ");
        sb.append("FROM cobranca.cobr_boletim_exec execu ");
        sb.append(
                "INNER JOIN cobranca.cobr_boletim_medicao med ON execu.cobm_id = med.cobm_id AND med.cobm_amreferencia = :amReferencia ");
        sb.append("AND med.cese_id = :idContrato ");

        String consulta = sb.toString();
        SQLQuery sqlQuery = session.createSQLQuery(consulta);
        sqlQuery = sqlQuery.addScalar("quantidade", Hibernate.INTEGER);

        sqlQuery.setInteger("idContrato", filtro.getIdContratoEmpresaServico());
        sqlQuery.setInteger("amReferencia", filtro.getMesAnoReferencia());

        qtdeOSExecutadas = (Integer) sqlQuery.uniqueResult();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }
    return qtdeOSExecutadas;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1178] Gerar Relatrio de Acompanhamento dos Boletins de Medio
 * /*from   w w w.ja  va  2 s  . com*/
 * @author Diogo Peixoto
 * @date 28/07/2011
 * 
 * @param FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper
 * @param relatorioDefinitivo
 * @return Quantidade de OS Penalizadas para determinado boletim de medio
 * @throws ErroRepositorioException
 */
public Integer pesquisarQuantidadeOSPenalizadas(FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper filtro)
        throws ErroRepositorioException {

    Session session = HibernateUtil.getSession();
    StringBuilder sb = new StringBuilder();
    Integer qtdeOSPenalizadas = 0;

    try {
        sb.append("SELECT COUNT(desco.orse_id) AS quantidade ");
        sb.append("FROM cobranca.cobr_boletim_desc desco ");
        sb.append(
                "INNER JOIN cobranca.cobr_boletim_medicao med ON med.cobm_id = desco.cobm_id AND med.cobm_amreferencia = :amReferencia ");
        sb.append("AND med.cese_id = :idContrato ");

        String consulta = sb.toString();
        SQLQuery sqlQuery = session.createSQLQuery(consulta);
        sqlQuery = sqlQuery.addScalar("quantidade", Hibernate.INTEGER);

        sqlQuery.setInteger("idContrato", filtro.getIdContratoEmpresaServico());
        sqlQuery.setInteger("amReferencia", filtro.getMesAnoReferencia());

        qtdeOSPenalizadas = (Integer) sqlQuery.uniqueResult();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }
    return qtdeOSPenalizadas;
}

From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java

License:Open Source License

/**
 * [UC1178] Gerar Relatrio de Acompanhamento dos Boletins de Medio
 * //from w w w .  j av a 2  s . c  o  m
 * @author Diogo Peixoto
 * @date 01/08/2011
 * 
 * @param FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper
 * @return Taxa de Sucesso do Boletim de Medio
 * 
 * @throws ErroRepositorioException
 */
public BigDecimal pesquisarTaxaSucessoBoletimMedicao(FiltrarRelatorioAcompanhamentoBoletimMedicaoHelper filtro)
        throws ErroRepositorioException {

    Session session = HibernateUtil.getSession();
    StringBuilder sb = new StringBuilder();
    BigDecimal taxaSucesso = new BigDecimal("0.00");

    try {
        sb.append("SELECT sum(cbsu.CBSU_VLTXSUCESSO) AS taxaSucesso ");
        sb.append("FROM cobranca.COBR_BOLETIM_SUCESSO cbsu ");
        sb.append(
                "INNER JOIN cobranca.cobr_boletim_medicao cobm on (cbsu.cobm_id = cobm.cobm_id and cobm.cobm_amreferencia = :amReferencia) ");
        sb.append(
                "INNER JOIN micromedicao.contrato_empresa_servico cese on (cobm.cese_id = cese.cese_id and cese.cese_id = :idContrato) ");

        String consulta = sb.toString();
        SQLQuery sqlQuery = session.createSQLQuery(consulta);
        sqlQuery = sqlQuery.addScalar("taxaSucesso", Hibernate.BIG_DECIMAL);

        sqlQuery.setInteger("amReferencia", filtro.getMesAnoReferencia());
        sqlQuery.setInteger("idContrato", filtro.getIdContratoEmpresaServico());

        taxaSucesso = (BigDecimal) sqlQuery.uniqueResult();

    } catch (HibernateException e) {
        throw new ErroRepositorioException(e, "Erro no Hibernate");
    } finally {
        HibernateUtil.closeSession(session);
    }
    return taxaSucesso;
}

From source file:gradebook.StudentHelper.java

public int getStudentID() {
    int studentID = 0;

    String sql = "select * from student where student_id = :id ORDER BY STUDENT_ID DESC LIMIT 1";

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

        //3 lines of code are always consistant 
        if (!this.session.getTransaction().isActive()) {
            session.beginTransaction();
        }

        SQLQuery q = session.createSQLQuery(sql);

        q.addEntity(Student.class);

        q.setParameter("id", studentID);

        studentID = (int) q.uniqueResult();

    } catch (Exception e) {
        e.printStackTrace();
    }
    return studentID;
}

From source file:gradebook.StudentHelper.java

public Student getStudentID(String fName, String lName) {

    Student student = null;/*from   w  ww  .  j a  v a  2s .c om*/
    // int studentID = 0;

    String sql = "SELECT * FROM STUDENT WHERE STUDENT_FNAME = :fname AND STUDENT_LNAME = :lname "
            + "ORDER BY STUDENT_ID DESC LIMIT 1";

    try {

        //3 lines of code are always consistant 
        if (!this.session.getTransaction().isActive()) {
            session.beginTransaction();
        }

        SQLQuery q = session.createSQLQuery(sql);

        q.addEntity(Student.class);

        q.setParameter("fname", fName);
        q.setParameter("lname", lName);

        student = (Student) q.uniqueResult();
        // studentID = student.getStudentId();

    } catch (Exception e) {
        e.printStackTrace();
    }
    return student;
}

From source file:is.merkor.statistics.hibernate.daos.RelationCountDAO.java

public int getCountSum() {
    SessionFactory factory = HibernateUtil.getSessionFactory();
    Session session = factory.openSession();
    try {/*  w  w w. j ava  2s. co m*/
        //Query query = session.getNamedQuery("relation.sumCount");
        SQLQuery query = session.createSQLQuery("select sum(count) from lexical_relation_new");
        BigDecimal count = (BigDecimal) query.uniqueResult();
        int result = count.intValue();
        return result;
    } finally {
        session.close();
    }
}

From source file:ispyb.server.biosaxs.services.core.analysis.Analysis3ServiceBean.java

License:Open Source License

@Override
public java.math.BigInteger getCountCompactAnalysisByExperimentId(Integer proposalId) {
    StringBuilder mySQLQuery = new StringBuilder(
            SQLQueryKeeper.getCountAnalysisCompactQueryByProposalId(proposalId));
    Session session = (Session) this.entityManager.getDelegate();
    SQLQuery query = session.createSQLQuery(mySQLQuery.toString());
    query.setParameter("proposalId", proposalId);
    return (java.math.BigInteger) query.uniqueResult();
}

From source file:it.av.youeat.service.impl.RistoranteServiceHibernate.java

License:Apache License

/**
 * {@inheritDoc}//from   ww  w  .  j  a v  a  2s  .c  o m
 */
@Override
public int countCityWithRistoByCountryAZ(Country country, String startLetter) {
    SQLQuery createSQLQuery = getHibernateSession().createSQLQuery(
            "select count (distinct ci) as count  from ristorante as ri inner join city as ci on (ri.city = ci.id) where ci.name like '"
                    + startLetter + "%' group by ci.name order by ci.name ");
    Object result = createSQLQuery.uniqueResult();
    return (result != null) ? ((BigInteger) result).intValue() : 0;
}

From source file:jp.go.nict.langrid.dao.hibernate.HibernateAccessRightDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public AccessRightSearchResult searchAccessRightsAccordingToDefaultAndOwner(int startIndex, int maxCount,
        String userGridId, String userId, String serviceAndOwnerGridId, String[] serviceIds, String ownerUserId,
        Order[] orders) throws DaoException {
    if (orders.length > 0) {
        orders = ArrayUtil.collect(orders, new Transformer<Order, Order>() {
            @Override//  w  w w.  j a  v a2  s .  c  o m
            public Order transform(Order value) throws TransformationException {
                String mapped = mappedFields.get(value.getFieldName());
                if (mapped != null) {
                    return new Order(mapped, value.getDirection());
                } else {
                    return value;
                }
            }
        });
    }
    String orderby = "order by lower(l.sn), lower(l.on)";
    if (orders.length > 0) {
        orderby = QueryUtil.buildOrderByQuery(capitalIgnoreFields, orders);
    }
    Session session = getSession();
    getContext().beginTransaction();
    try {
        if (getContext().getTransactionNestCount() > 1) {
            session.flush();
        }
        String servicesParameters = null;
        if (serviceAndOwnerGridId.length() > 0) {
            servicesParameters = " and s.gridId=:serviceGridId";
            servicesParameters += serviceIds.length > 0
                    ? " and s.serviceid in (" + StringUtil.repeatedString(new StringGenerator() {
                        public String generate() {
                            return ":service" + i++;
                        }

                        private int i = 0;
                    }, serviceIds.length, ", ") + ")"
                    : "";
        } else {
            servicesParameters = "";
        }
        String usersParameters = null;
        if (userGridId.length() > 0) {
            usersParameters = " and (u.gridId=:userGridId";
            usersParameters += userId.length() > 0 ? " and u.userid=:userId)" : " and u.userid<>'*')";
        } else {
            usersParameters = " and u.userid<>'*'";
        }
        String ownerExcludes = (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0)
                ? " and (u.gridId<>:ownerUserGridId or u.userid<>:ownerUserId)"
                : "";
        Query q = session.createSQLQuery(String.format(selectJoinedEntities, servicesParameters,
                usersParameters, ownerExcludes, orderby));
        q.setFirstResult(startIndex);
        q.setMaxResults(maxCount);
        if (serviceAndOwnerGridId.length() > 0) {
            q.setString("serviceGridId", serviceAndOwnerGridId);
        }
        for (int i = 0; i < serviceIds.length; i++) {
            q.setString("service" + i, serviceIds[i]);
        }
        if (userGridId.length() > 0) {
            q.setString("userGridId", userGridId);
        }
        if (userId.length() > 0) {
            q.setString("userId", userId);
        }
        if (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0) {
            q.setString("ownerUserGridId", serviceAndOwnerGridId);
            q.setString("ownerUserId", ownerUserId);
        }
        List<Object> list = q.list();
        List<AccessRight> elements = new ArrayList<AccessRight>();
        for (Object o : list) {
            Object[] values = (Object[]) o;
            String ugid = values[0].toString();
            String uid = values[1].toString();
            String sgid = values[2].toString();
            String sid = values[3].toString();
            Boolean permitted = (Boolean) values[4];
            Boolean gdPermitted = (Boolean) values[5];
            Boolean sdPermitted = (Boolean) values[6];
            boolean p = false;
            if (permitted != null) {
                p = permitted;
            } else if (gdPermitted != null) {
                p = gdPermitted;
            } else if (sdPermitted != null) {
                p = sdPermitted;
            }
            AccessRight ar = new AccessRight(ugid, uid, sgid, sid, p);
            Timestamp ct = ((Timestamp) values[7]);
            if (ct != null) {
                ar.setCreatedDateTime(CalendarUtil.createFromMillis(ct.getTime()));
            }
            Timestamp ut = ((Timestamp) values[8]);
            if (ut != null) {
                ar.setCreatedDateTime(CalendarUtil.createFromMillis(ut.getTime()));
            }
            elements.add(ar);
        }
        long totalCount = 0;
        if (elements.size() < maxCount) {
            totalCount = elements.size() + startIndex;
        } else {
            SQLQuery cq = session.createSQLQuery(
                    String.format(countJoinedEntities, servicesParameters, usersParameters, ownerExcludes));
            if (serviceAndOwnerGridId.length() > 0) {
                cq.setString("serviceGridId", serviceAndOwnerGridId);
            }
            for (int i = 0; i < serviceIds.length; i++) {
                cq.setString("service" + i, serviceIds[i]);
            }
            if (userGridId.length() > 0) {
                cq.setString("userGridId", userGridId);
            }
            if (userId.length() > 0) {
                cq.setString("userId", userId);
            }
            if (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0) {
                cq.setString("ownerUserGridId", serviceAndOwnerGridId);
                cq.setString("ownerUserId", ownerUserId);
            }
            totalCount = ((Number) cq.uniqueResult()).longValue();
        }

        /*         Criteria c = session.createCriteria(AccessRight.class);
                 addSearchAccessRightCriterionIgnoreDefault(c, userId, serviceIds);
                 List<AccessRight> elements = (List<AccessRight>)CriteriaUtil.getList(
                       c, startIndex, maxCount, orders);
                 int totalCount = 0;
                 if(elements.size() < maxCount){
                    totalCount = elements.size() + startIndex;
                 } else{
                    Criteria cr = session.createCriteria(AccessRight.class);
                    addSearchAccessRightCriterionIgnoreDefault(cr, userId, serviceIds);
                    totalCount = CriteriaUtil.getCount(cr);
                 }
        */ AccessRightSearchResult r = new AccessRightSearchResult(elements.toArray(new AccessRight[] {}),
                (int) totalCount, true);
        getContext().commitTransaction();
        return r;
    } catch (HibernateException e) {
        logAdditionalInfo(e);
        getContext().rollbackTransaction();
        throw new DaoException(e);
    } catch (RuntimeException e) {
        getContext().rollbackTransaction();
        throw new DaoException(e);
    } catch (Error e) {
        getContext().rollbackTransaction();
        throw new DaoException(e);
    }
}

From source file:kp.dao.data.MocDao.java

public void updateMocStatus(int cid, String status) {
    Transaction tx = null;//  ww w . j a  v a  2s. co  m
    Session session = null;
    gm = new GeneralMethods();
    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();
        MocInitMst mst = (MocInitMst) session.get(MocInitMst.class, cid);
        mst.setStatus(status);
        String unit = mst.getUnitId();
        mst.setUnitId(unit);
        // If moc in approved state
        if (status.equals("Approved")) {
            Date sysdate = gm.getSysDate(session);
            String year = gm.formatDate(sysdate, "YY");
            String sql = "SELECT COUNT (*)\n" + "  FROM moc_init_mst\n" + " WHERE unit_id = '" + unit
                    + "' AND SUBSTR (moc_no, 4, 2) = '" + year + "'";
            SQLQuery query = session.createSQLQuery(sql);
            int count = ((Number) query.uniqueResult()).intValue();
            String mocno = "MOC" + year + "/" + unit + "-" + String.format("%3s", ++count).replace(' ', '0');
            //Set moc no and app date
            mst.setMocNo(mocno);
            mst.setAppDate(sysdate);
        }
        session.update(mst);
        tx.commit();
    } catch (HibernateException ex) {
        if (tx != null) {
            tx.rollback();
        }
        Logger.getLogger(UserDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex);
        session.close();
    } catch (ParseException ex) {
        Logger.getLogger(MocDao.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        session.close();
    }
}