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:models.BalanceSheetSummary.java

public Double GetSummary(String args) {
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;//w  w  w  .  j  a  v a2 s.c om
    String formula = Formula.main(args);
    Double xtotal = 0.00;
    try {
        tx = session.beginTransaction();
        String sql;
        SQLQuery query;
        Integer[] arg;
        switch (formula.toLowerCase().trim()) {
        case "sum":
            sql = "select " + formula + "(total) from balance_sheet_summaries"
                    + " where ref >= :arg1 and ref <= :arg2";
            query = session.createSQLQuery(sql);
            arg = Formula.ref(args);
            query.setParameter("arg1", arg[0].toString());
            query.setParameter("arg2", arg[1].toString());
            xtotal = (Double) query.uniqueResult();
            break;
        case "avg":
            sql = "select " + formula + "(total) from balance_sheet_summaries"
                    + " where ref >= :arg1 and ref <= :arg2";
            query = session.createSQLQuery(sql);
            arg = Formula.ref(args);
            query.setParameter("arg1", arg[0].toString());
            query.setParameter("arg2", arg[1].toString());
            xtotal = (Double) query.uniqueResult();
            break;
        default:
            arg = Formula.ref(args);
            Double subtotal = 0.00;
            if (arg.length > 0) {
                for (Integer i = 0; i < arg.length; i++) {
                    sql = "select total from balance_sheet_summaries" + " where ref = :arg ";
                    query = session.createSQLQuery(sql);
                    query.setParameter("arg", arg[i]);
                    if (!"+".equals(Formula.separator(args))) {
                        subtotal -= (Double) query.uniqueResult();
                    } else {
                        subtotal += (Double) query.uniqueResult();
                    }

                    xtotal = subtotal;
                }
            }
            break;

        }

        tx.commit();
    } catch (HibernateException e) {
        System.out.println(e.getMessage());
        if (tx != null) {
            tx.rollback();
        }

    } finally {
        session.close();
    }
    return xtotal;
}

From source file:models.BeginningBalance.java

public Double getSumBalance(String year, String accountNo, String calc) {
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;//from w w w .jav a2  s .  co m
    Double total = null;
    accountNo = accountNo.trim();
    String[] args;
    String calcSelect = "Debet - Credit".equals(calc) ? "debet - credit" : "credit - debet";
    try {
        tx = session.beginTransaction();
        if (accountNo.contains("to")) {
            args = Formula.args(accountNo.trim(), "to");
            String sql = "select sum(" + calcSelect + ") from beginning_balances bb "
                    + " where bb.year = :year and (bb.account_no >= :arg1 and bb.account_no<= :arg2) ";
            SQLQuery query = session.createSQLQuery(sql);
            query.setParameter("year", year);
            query.setParameter("arg1", args[0]);
            query.setParameter("arg2", args[1]);
            total = (Double) query.uniqueResult();
        } else {
            args = Formula.args(accountNo.trim(), "\\,");
            String sql = "select sum(" + calcSelect + ") from beginning_balances bb "
                    + " where bb.year = :year and bb.account_no in(:no) ";
            SQLQuery query = session.createSQLQuery(sql);
            query.setParameter("year", year);
            query.setParameterList("no", args);
            total = (Double) query.uniqueResult();
        }
        session.flush();
        tx.commit();
    } catch (HibernateException e) {
        System.out.println("Error :" + e.getMessage());
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }

    if (total == null) {
        total = 0.00;
    }
    return total;
}

From source file:models.JournalDetail.java

public JournalDetails getSumBalanceByUntilDate(String year, Date date, String accountNo) {
    JournalDetails jds = null;/*  w w  w.  j  a va 2 s.  c  o  m*/
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();
        String sql = " select sum(jd.debet) as debet,sum(jd.credit) as credit " + " from journal_details jd "
                + " inner join journals j on j.id = jd.journal_id "
                + " where j.date < :date and year(j.date) = :year and jd.account_no = :no ";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("date", date);
        query.setParameter("year", year);
        query.setParameter("no", accountNo);
        query.setResultTransformer(Transformers.aliasToBean(JournalDetails.class));
        session.flush();
        tx.commit();
        jds = (JournalDetails) query.uniqueResult();

    } catch (HibernateException e) {
        System.out.println(e.getMessage());
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }

    return jds;
}

From source file:models.JournalDetail.java

public JournalDetails getSumBalanceByDate(Date dateFrom, Date dateTo, String accountNo) {
    JournalDetails jds = null;/*w  ww.  ja va  2  s.c o  m*/
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;
    try {
        tx = session.beginTransaction();
        String sql = " select sum(jd.debet) as debet,sum(jd.credit) as credit " + " from journal_details jd "
                + " inner join journals j on j.id = jd.journal_id  "
                + " where (j.date>= :dateFrom and j.date <= :dateTo) and jd.account_no = :no ";
        SQLQuery query = session.createSQLQuery(sql);
        query.setParameter("dateFrom", dateFrom);
        query.setParameter("dateTo", dateFrom);
        query.setParameter("no", accountNo);
        query.setResultTransformer(Transformers.aliasToBean(JournalDetails.class));
        session.flush();
        tx.commit();
        jds = (JournalDetails) query.uniqueResult();

    } catch (HibernateException e) {
        System.out.println(e.getMessage());
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }

    return jds;
}

From source file:models.JournalDetail.java

public Double getBalanceSheetSummary(Date periode, String year, String accountNo, String calc) {
    Session session = DatabaseUtil.getSessionFactory().openSession();
    Transaction tx = null;/*from  w ww .j a v  a2  s.co  m*/
    Double total = null;
    String[] args;
    accountNo = accountNo.trim();
    String calcSelect = "Debet - Credit".equals(calc.trim()) ? "jd.debet - jd.credit" : "jd.credit - jd.debet";
    String sql;
    SQLQuery query;
    try {
        tx = session.beginTransaction();
        System.out.println("Account Data : " + accountNo);
        if (accountNo.contains("to")) {
            args = Formula.args(accountNo.trim(), "to");
            String arg1 = args[0].trim();
            String arg2 = args[1].trim();
            System.out.println("Data ke 0 : " + args[0]);
            System.out.println("Data ke 1 : " + arg1);

            sql = "select sum(" + calcSelect
                    + ") from journal_details jd inner join journals j on j.id = jd.journal_id  "
                    + " where j.date <= :date and year(j.date) = :year and jd.account_no BETWEEN :arg0 and :arg1 ";
            query = session.createSQLQuery(sql);
            query.setParameter("date", periode);
            query.setParameter("year", year);
            query.setParameter("arg0", arg1);
            query.setParameter("arg1", arg2);
            total = (Double) query.uniqueResult();

        } else {
            args = Formula.args(accountNo.trim(), "\\,");
            sql = "select sum(" + calcSelect
                    + ") from journal_details jd inner join journals j on j.id = jd.journal_id  "
                    + " where j.date <= :date and year(j.date) = :year and jd.account_no in(:no) ";
            query = session.createSQLQuery(sql);
            query.setParameter("date", periode);
            query.setParameter("year", year);
            query.setParameterList("no", args);
            total = (Double) query.uniqueResult();
        }

        session.flush();
        tx.commit();
    } catch (HibernateException e) {
        total = 0.00;
        System.out.println(e.getMessage());
        if (tx != null) {
            tx.rollback();
        }

    } finally {
        session.close();
    }

    if (total == null) {
        total = 0.00;
    }

    return total;
}

From source file:nc.noumea.mairie.annuairev2.saisie.dao.impl.GuestDao.java

License:Open Source License

@Override
public Guest findByIdentifiant(String identifiant) {

    SQLQuery query = getCurrentSession().createSQLQuery("select * from " + Guest.TABLENAME
            + " where ('G' || lpad(cast(id as varchar(4)), 4, '0')) = :identifiant");
    query.setParameter("identifiant", identifiant);
    query.addEntity(Guest.class);
    return (Guest) query.uniqueResult();
}

From source file:net.lc4ever.framework.activiti.engine.impl.AbstractNativeQueryHibernate.java

License:Open Source License

/**
 * @see org.activiti.engine.query.NativeQuery#count()
 *//*from ww  w  .  ja va2s.  co  m*/
@Override
public long count() {
    // TODO Any cute idea for count sql?
    return crudService.callback(new HibernateCallback<Long>() {
        @Override
        public Long doInHibernate(final Session session) throws HibernateException, SQLException {
            SQLQuery query = session.createSQLQuery(COUNT_PREFIX + selectClause + COUNT_SUFFIX);
            if (parameters != null) {
                for (Map.Entry<String, Object> entry : parameters.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }
            }
            if (resultTransformer != null)
                query.setResultTransformer(resultTransformer);
            return ((Number) query.uniqueResult()).longValue();
        }
    });
}

From source file:net.lc4ever.framework.activiti.engine.impl.AbstractNativeQueryHibernate.java

License:Open Source License

/**
 * @see org.activiti.engine.query.NativeQuery#singleResult()
 *///from w  w w  .j av  a 2  s. c o  m
@Override
public U singleResult() {
    return crudService.callback(new HibernateCallback<U>() {
        @SuppressWarnings("unchecked")
        @Override
        public U doInHibernate(final Session session) throws HibernateException, SQLException {
            SQLQuery query = session.createSQLQuery(selectClause);
            if (parameters != null) {
                for (Map.Entry<String, Object> entry : parameters.entrySet()) {
                    query.setParameter(entry.getKey(), entry.getValue());
                }
            }
            if (resultTransformer != null)
                query.setResultTransformer(resultTransformer);
            return (U) query.uniqueResult();
        }
    });
}

From source file:nl.strohalm.cyclos.dao.members.ReferenceDAOImpl.java

License:Open Source License

public List<PaymentAwaitingFeedbackDTO> searchPaymentsAwaitingFeedback(
        final PaymentsAwaitingFeedbackQuery query) {

    final ResultType resultType = query.getResultType();
    final PageParameters pageParameters = query.getPageParameters();
    final boolean countOnly = resultType == ResultType.PAGE && pageParameters != null
            && pageParameters.getMaxResults() == 0;

    // There are 2 tables which contains payments that can have feedback: transfers and scheduled payments
    // As we need an union, we need a native SQL

    final Member member = query.getMember();
    Boolean expired = query.getExpired();

    final StringBuilder sql = new StringBuilder();
    sql.append(" select ");
    if (countOnly) {
        sql.append(" count(*) as row_count");
    } else {//from  w  w  w  .j  a va  2s  . c o m
        sql.append(" * ");
    }
    sql.append(" from ( ");
    {
        sql.append(
                " select t.id, t.type_id as transferTypeId, false as scheduled, t.date, t.amount, tm.id as memberId, tm.name as memberName, ta.owner_name as memberUsername");
        sql.append(
                " from transfers t inner join transfer_types tt on t.type_id = tt.id inner join accounts ta on t.to_account_id = ta.id inner join members tm on ta.member_id = tm.id");
        if (member != null) {
            sql.append(" inner join accounts a on t.from_account_id = a.id");
        }
        sql.append(" left join refs tf on tf.transfer_id = t.id");
        sql.append(" where tt.requires_feedback = true");
        sql.append(" and t.date >= tt.feedback_enabled_since");
        sql.append(" and t.parent_id is null");
        sql.append(" and t.chargeback_of_id is null");
        sql.append(" and t.scheduled_payment_id is null");
        sql.append(" and t.process_date is not null");
        if (expired != null) {
            sql.append(" and t.feedback_deadline " + (expired ? "<" : ">=") + " now()");
        }
        sql.append(" and tf.id is null");
        if (member != null) {
            sql.append(" and a.member_id = :memberId");
        }

        sql.append(" union ");

        sql.append(" select sp.id, sp.type_id, true, sp.date, sp.amount, tm.id, tm.name, ta.owner_name");
        sql.append(
                " from scheduled_payments sp inner join transfer_types tt on sp.type_id = tt.id inner join accounts ta on sp.to_account_id = ta.id inner join members tm on ta.member_id = tm.id");
        if (member != null) {
            sql.append(" inner join accounts a on sp.from_account_id = a.id");
        }
        sql.append(" left join refs tf on tf.scheduled_payment_id = sp.id");
        sql.append(" where tt.requires_feedback = true");
        if (expired != null) {
            sql.append(" and sp.feedback_deadline " + (expired ? "<" : ">=") + " now()");
        }
        sql.append(" and sp.date >= tt.feedback_enabled_since");
        sql.append(" and tf.id is null");
        if (member != null) {
            sql.append(" and a.member_id = :memberId");
        }
    }
    sql.append(") as awaiting ");
    if (!countOnly) {
        sql.append("order by date");
    }

    SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString());
    if (member != null) {
        sqlQuery.setLong("memberId", member.getId());
    }
    if (countOnly) {
        // Handle the special case for count only
        sqlQuery.addScalar("row_count", StandardBasicTypes.INTEGER);
        int count = ((Number) sqlQuery.uniqueResult()).intValue();
        return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, count,
                Collections.<PaymentAwaitingFeedbackDTO>emptyList());
    } else {
        // Execute the search
        sqlQuery.addScalar("id", StandardBasicTypes.LONG);
        sqlQuery.addScalar("transferTypeId", StandardBasicTypes.LONG);
        sqlQuery.addScalar("scheduled", StandardBasicTypes.BOOLEAN);
        sqlQuery.addScalar("date", StandardBasicTypes.CALENDAR);
        sqlQuery.addScalar("amount", StandardBasicTypes.BIG_DECIMAL);
        sqlQuery.addScalar("memberId", StandardBasicTypes.LONG);
        sqlQuery.addScalar("memberName", StandardBasicTypes.STRING);
        sqlQuery.addScalar("memberUsername", StandardBasicTypes.STRING);
        getHibernateQueryHandler().applyPageParameters(pageParameters, sqlQuery);

        // We'll always use an iterator, even if it is for later adding it to a list
        Iterator<PaymentAwaitingFeedbackDTO> iterator = new ScrollableResultsIterator<PaymentAwaitingFeedbackDTO>(
                sqlQuery, new Transformer<Object[], PaymentAwaitingFeedbackDTO>() {
                    public PaymentAwaitingFeedbackDTO transform(final Object[] input) {
                        PaymentAwaitingFeedbackDTO dto = new PaymentAwaitingFeedbackDTO();
                        dto.setId((Long) input[0]);
                        dto.setTransferTypeId((Long) input[1]);
                        dto.setScheduled(Boolean.TRUE.equals(input[2]));
                        dto.setDate((Calendar) input[3]);
                        dto.setAmount((BigDecimal) input[4]);
                        dto.setMemberId((Long) input[5]);
                        dto.setMemberName((String) input[6]);
                        dto.setMemberUsername((String) input[7]);

                        TransferType transferType = (TransferType) getSession().load(TransferType.class,
                                dto.getTransferTypeId());
                        dto.setCurrency(getFetchDao().fetch(transferType.getCurrency()));

                        return dto;
                    }
                });
        if (resultType == ResultType.ITERATOR) {
            return new IteratorListImpl<PaymentAwaitingFeedbackDTO>(iterator);
        } else {
            List<PaymentAwaitingFeedbackDTO> list = new ArrayList<PaymentAwaitingFeedbackDTO>();
            CollectionUtils.addAll(list, iterator);
            DataIteratorHelper.close(iterator);

            if (resultType == ResultType.PAGE) {
                // For page, we need another search for the total count
                query.setPageForCount();
                int totalCount = PageHelper.getTotalCount(searchPaymentsAwaitingFeedback(query));

                return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, totalCount, list);
            } else {
                return list;
            }
        }
    }
}

From source file:org.apache.usergrid.apm.service.NetworkMetricsDBServiceImpl.java

License:Apache License

@Override
public AggregatedNetworkData getAggregatedNetworkMetricsData(MetricsChartCriteria chartCriteria)
        throws HibernateException {

    String query = NetworkMetricsChartUtil.getQueryForAggregatedData(chartCriteria);

    Session session = null;//  w w  w.j  a  v  a 2  s.  c  om
    Transaction transaction = null;
    try {
        session = ServiceFactory.getAnalyticsHibernateSession();
        transaction = session.beginTransaction();
        SQLQuery sqlquery = session.createSQLQuery(query);
        //List<Long> results = (List<Long>) sqlquery.list();
        Object[] rawResult = (Object[]) sqlquery.uniqueResult();
        transaction.commit();
        log.info("there should be 3 columns returned " + rawResult.length);
        AggregatedNetworkData data = new AggregatedNetworkData();
        data.setAvgLatency(new Long(rawResult[0].toString()));
        data.setMaxLatency(new Long(rawResult[1].toString()));
        data.setTotalRequests(new Long(rawResult[2].toString()));
        data.setTotalErrors(new Long(rawResult[3].toString()));
        return data;

    } catch (HibernateException e) {
        transaction.rollback();
        throw new HibernateException("Cannot get compact network metrics. ", e);
    }

}