Example usage for org.hibernate SQLQuery addScalar

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.caisi.dao.BedProgramDao.java

License:Open Source License

public String[] getProgramInfo(int programId) {
    String[] result = new String[3];

    SQLQuery query = getSession()
            .createSQLQuery("SELECT name,address,phone,fax from program where id=" + programId);
    query.addScalar("name", Hibernate.STRING);
    query.addScalar("address", Hibernate.STRING);
    query.addScalar("phone", Hibernate.STRING);
    query.addScalar("fax", Hibernate.STRING);
    Object[] o = (Object[]) query.uniqueResult();
    if (o != null) {
        result[0] = new String(o[0] + "\n" + o[1]);
        result[1] = (String) o[2];
        result[2] = (String) o[3];
    }/*  w  w w  . jav a  2s.c  om*/
    return result;
}

---

From source file:org.conventionsframework.dao.impl.BaseHibernateDaoImpl.java

License:Apache License

/**
 *
 * @param nativeQuery//from  ww w . j  a v a 2  s  .co  m
 * @param params
 * @param class entity to be queried, if no entity is passed persistentClass
 * will be used
 * @param result transformer
 * @param Scalar
 * @return
 */
@Override
public List findByNativeQuery(String nativeQuery, Map params, Class entity, ResultTransformer rt,
        ScalarReturn scalar) {
    SQLQuery query = getSession().createSQLQuery(nativeQuery);
    if (scalar != null) {
        query.addScalar(scalar.getColumnAlias(), scalar.getType());
    }
    if (rt != null) {
        query.setResultTransformer(rt);
    }
    if (entity != null) {
        query.addEntity(entity);
    } else {
        query.addEntity(getPersistentClass());
    }

    Set<Map.Entry> rawParameters = params.entrySet();
    for (Map.Entry entry : rawParameters) {
        query.setParameter(entry.getKey().toString(), entry.getValue());
    }
    return query.list();
}

---

From source file:org.ednovo.gooru.infrastructure.persistence.hibernate.index.ContentIdexDaoImpl.java

License:Open Source License

@Override
public Long getSubscriptionCountByContentId(long contentId) {
    SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(GET_SUBSCRIPTION_BY_CONTENT_ID);
    query.addScalar("subscriberCount", StandardBasicTypes.LONG);
    query.setLong(CONTENT_ID, contentId);
    List<Long> list = query.list();
    return (list.size() > 0 ? list.get(0) : 0);
}

---

From source file:org.ednovo.gooru.infrastructure.persistence.hibernate.partner.CustomFieldRepositoryHibernate.java

License:Open Source License

@Override
public List<Object[]> getSearchAliasByOrganization(String accountUId) {
    String sql = "SELECT search_alias_name, data_column_name,add_to_search,add_to_search_index,add_to_filters FROM custom_fields WHERE show_in_response=? AND account_uid =? AND add_to_search=? ";
    SQLQuery query = getSession().createSQLQuery(sql);
    query.setParameter(0, 1, StandardBasicTypes.INTEGER);

    if (accountUId != null) {
        query.setParameter(1, accountUId, StandardBasicTypes.STRING);
    }/*  www. j a  v a  2  s.c  o  m*/
    query.setParameter(2, 1, StandardBasicTypes.INTEGER);

    query.addScalar("search_alias_name", StandardBasicTypes.STRING);
    query.addScalar("data_column_name", StandardBasicTypes.STRING);
    query.addScalar("add_to_search", StandardBasicTypes.INTEGER);
    query.addScalar("add_to_search_index", StandardBasicTypes.INTEGER);
    query.addScalar("add_to_filters", StandardBasicTypes.INTEGER);
    return query.list();
}

---

From source file:org.ednovo.gooru.infrastructure.persistence.hibernate.resource.ResourceRepositoryHibernate.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override/* ww  w  .  j a va 2s  .  c  o m*/
public List<Long> findResources(Map<String, String> filters) {
    String sql = "select r.content_id from resource r WHERE r.type_name not in (" + EXCLUDE_FOR_RESOURCES_STRING
            + ",'assessment-question')";
    int pageSize = Integer.parseInt(filters.get("pageSize"));
    int pageNo = Integer.parseInt(filters.get("pageNo"));
    String batchId = filters.get("batchId");
    if (batchId != null) {
        sql += " WHERE";
        sql += " r.batch_id= " + batchId;
    }
    sql += " LIMIT " + (pageSize * (pageNo - 1)) + " , " + pageSize;
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addScalar("content_id", StandardBasicTypes.LONG);
    List<Long> results = query.list();
    return results;
}

---

From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java

License:Open Source License

private void findandUpdateDuplicates() {
    // for payment cheques instrumentNo,debit,accountId combination should be unique else mark it duplicate
    try {//w w w.  j  a  v a  2  s  . co m
        String duplicates = "select instrumentNo,debit,accountId from " + TABLENAME
                + " where accountId=:accountId" + " and debit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED
                + "'  group by  instrumentNo,debit,accountId having count(*)>1";
        final SQLQuery paymentDuplicateChequesQuery = persistenceService.getSession()
                .createSQLQuery(duplicates);
        paymentDuplicateChequesQuery.addScalar("instrumentNo").addScalar("debit")
                .addScalar("accountId", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));
        // paymentDuplicateChequesQuery.setParameter("accountId", Long.class);
        paymentDuplicateChequesQuery.setLong("accountId", accountId);
        final List<AutoReconcileBean> duplicatePaymentCheques = paymentDuplicateChequesQuery.list();

        final String backUpdateDuplicatePaymentquery = "update " + TABLENAME + " set action='"
                + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'," + " errorMessage='"
                + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT
                + "' where debit=:debit and accountid=:accountId and instrumentNo=:instrumentNo "
                + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'";

        final SQLQuery paymentDuplicateUpdate = persistenceService.getSession()
                .createSQLQuery(backUpdateDuplicatePaymentquery);
        for (final AutoReconcileBean bean : duplicatePaymentCheques) {

            paymentDuplicateUpdate.setLong("accountId", bean.getAccountId());
            paymentDuplicateUpdate.setBigDecimal("debit", bean.getDebit());
            paymentDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo());
            paymentDuplicateUpdate.executeUpdate();

        }
        // this portion is for receipts instrumentNo,credit,accountId combination should be unique else mark it duplicate
        duplicates = "select instrumentNo,credit,accountId from " + TABLENAME + " where accountid=:accountId"
                + " and  credit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED
                + "' group by  instrumentNo,credit,accountId having count(*)>1";
        final SQLQuery receiptsDuplicateChequesQuery = persistenceService.getSession()
                .createSQLQuery(duplicates);
        receiptsDuplicateChequesQuery.addScalar("instrumentNo").addScalar("credit")
                .addScalar("accountId", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));
        receiptsDuplicateChequesQuery.setLong("accountId", accountId);
        final List<AutoReconcileBean> duplicateReceiptsCheques = receiptsDuplicateChequesQuery.list();

        final String backUpdateDuplicateReceiptsQuery = "update " + TABLENAME + " set action='"
                + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'" + " ,errorMessage='"
                + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT
                + "' where credit=:credit and accountid=:accountId and instrumentNo=:instrumentNo "
                + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'";
        final SQLQuery receiptDuplicateUpdate = persistenceService.getSession()
                .createSQLQuery(backUpdateDuplicateReceiptsQuery);

        for (final AutoReconcileBean bean : duplicateReceiptsCheques) {
            receiptDuplicateUpdate.setLong("accountId", bean.getAccountId());
            receiptDuplicateUpdate.setBigDecimal("credit", bean.getCredit());
            receiptDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo());
            receiptDuplicateUpdate.executeUpdate();
        }
    } catch (final HibernateException e) {
        throw new ApplicationRuntimeException("Failed while processing autoreconciliation ");
    }

}

---

From source file:org.egov.egf.web.actions.brs.ManualReconcileHelper.java

License:Open Source License

public List<ReconcileBean> getUnReconciledCheques(ReconcileBean reconBean) {
    List<ReconcileBean> list = new ArrayList<ReconcileBean>();
    String instrumentCondition = "";
    if (reconBean.getInstrumentNo() != null && !reconBean.getInstrumentNo().isEmpty()) {
        instrumentCondition = "and (ih.instrumentNumber='" + reconBean.getInstrumentNo()
                + "' or ih.transactionnumber='" + reconBean.getInstrumentNo() + "' )";
    }//from   w ww  .j  a  va  2 s.  c  o m
    try {
        String voucherExcludeStatuses = getExcludeStatuses();
        StringBuffer query = new StringBuffer().append(
                " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" ,ih.id as \"ihId\", case when ih.instrumentNumber is null then 'Direct' else ih.instrumentNumber  end as \"chequeNumber\", "
                        + " to_char(ih.instrumentdate,'dd/mm/yyyy') as \"chequeDate\" ,ih.instrumentAmount as \"chequeAmount\",rec.transactiontype as \"txnType\" , "
                        + " case when rec.transactionType='Cr' then  'Payment' else 'Receipt' end as \"type\" "
                        + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK,"
                        + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv   WHERE "
                        + "  ih.bankAccountId = BANK.ID AND bank.id =:bankAccId   AND IH.INSTRUMENTDATE <= :toDate  "
                        + " AND v.ID= iv.voucherheaderid  and v.STATUS not in  (" + voucherExcludeStatuses
                        + ")  " + instrumentCondition
                        + " AND ((ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited') and ih.ispaycheque='0') or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
                        + " AND rec.instrumentHeaderId=ih.id    and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.instrumentNumber is not null"
                        + " group by ih.id,rec.transactiontype "

                        + " union  "

                        + " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" , ih.id as \"ihId\", case when ih.transactionnumber is null then 'Direct' else ih.transactionnumber end as \"chequeNumber\", "
                        + " to_char(ih.transactiondate,'dd/mm/yyyy') as \"chequedate\" ,ih.instrumentAmount as \"chequeamount\",rec.transactiontype as \"txnType\", case when rec.transactionType= 'Cr' then 'Payment' else 'Receipt' end    as \"type\" "
                        + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK,"
                        + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv   WHERE   ih.bankAccountId = BANK.ID AND bank.id = :bankAccId "
                        + "   AND IH.INSTRUMENTDATE <= :toDate " + instrumentCondition
                        + " AND v.ID= iv.voucherheaderid and v.STATUS not in  (" + voucherExcludeStatuses
                        + ") AND ((ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited') and ih.ispaycheque='0')or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
                        + " AND rec.instrumentHeaderId=ih.id    and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.transactionnumber is not null"
                        + "   group by ih.id,rec.transactiontype order by 4 ");

        if (reconBean.getLimit() != null & reconBean.getLimit() != 0) {
            query.append(" limit " + reconBean.getLimit());
        }

        // if(LOGGER.isInfoEnabled())    
        LOGGER.info("  query  for getUnReconciledCheques: " + query);
        /*String query=" SELECT decode(rec.chequeNumber, null, 'Direct', rec.chequeNumber) as \"chequeNumber\",rec.chequedate as \"chequedate\" ,amount as \"chequeamount\",transactiontype as \"txnType\" ,rec.type as \"type\" from bankreconciliation rec, bankAccount bank, voucherheader vh "
           +" where  rec.bankAccountId = bank.id AND bank.id ="+bankAccId+" and  rec.isReversed = 0 AND (rec.reconciliationDate > to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') "
           +" OR (rec.isReconciled = 0)) AND vh.VOUCHERDATE <= to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and vh.id=rec.VOUCHERHEADERID and vh.STATUS<>4"
           +" union "
           +" select refno as \"chequeNumber\", txndate as \"chequedate\", txnamount as \"chequeamount\", decode(type,'R','Dr','Cr') as \"txnType\", "
           +" type as \"type\" from bankentries be,bankAccount bank where  be.bankAccountId = bank.id and bank.id ="+bankAccId+"  "
           +" and txndate<= to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and voucherheaderid is null ";
        */

        SQLQuery createSQLQuery = persistenceService.getSession().createSQLQuery(query.toString());
        createSQLQuery.setLong("bankAccId", reconBean.getAccountId());
        createSQLQuery.setDate("toDate", reconBean.getReconciliationDate());
        createSQLQuery.addScalar("voucherNumber", StringType.INSTANCE);
        createSQLQuery.addScalar("ihId", LongType.INSTANCE);
        createSQLQuery.addScalar("chequeDate", StringType.INSTANCE);
        createSQLQuery.addScalar("chequeNumber", StringType.INSTANCE);
        createSQLQuery.addScalar("chequeAmount", BigDecimalType.INSTANCE);
        createSQLQuery.addScalar("txnType", StringType.INSTANCE);
        createSQLQuery.addScalar("type", StringType.INSTANCE);
        createSQLQuery.setResultTransformer(Transformers.aliasToBean(ReconcileBean.class));
        list = (List<ReconcileBean>) createSQLQuery.list();

    } catch (Exception e) {
        LOGGER.error("Exp in getUnReconciledCheques:" + e.getMessage());
        throw new ApplicationRuntimeException(e.getMessage());
    }

    return list;
}

---

From source file:org.egov.egf.web.actions.budget.BudgetDetailAction.java

License:Open Source License

@SuppressWarnings("unchecked")
private void loadAjaxedFunctionAndBudgetGroup() {
    if (budgetDetail.getBudget() != null) {
        String sqlStr = "select distinct (f.name)  as name,f.id as id   from function f,egf_budgetdetail bd where  f.id=bd.function and bd.budget="
                + budgetDetail.getBudget().getId() + "  order  by f.name";
        SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);
        sqlQuery.addScalar("name").addScalar("id", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(CFunction.class));
        functionList = sqlQuery.list();/*from   www . j a va2s.c o  m*/
        sqlStr = "select  distinct (bg.name) as name ,bg.id  as id from egf_budgetgroup bg,egf_budgetdetail  bd where  bg.id=bd.budgetgroup and bd.budget="
                + budgetDetail.getBudget().getId() + "  order  by bg.name";
        sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);
        sqlQuery.addScalar("name").addScalar("id", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(BudgetGroup.class));
        budgetGroupList = sqlQuery.list();
    }

}

---

From source file:org.egov.egf.web.actions.budget.BudgetDetailAction.java

License:Open Source License

@Action(value = "/budget/budgetDetail-ajaxLoadFunctions")
public String ajaxLoadFunctions() {
    final Long id = (Long) request.get("id");
    final String sqlStr = "select distinct (f.name)  as name,f.id as id   from function f,egf_budgetdetail bd where  f.id=bd.function and bd.budget="
            + id + "  order  by f.name";
    final SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);
    sqlQuery.addScalar("name").addScalar("id", LongType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(CFunction.class));
    functionList = sqlQuery.list();/*w w w .j a  va2  s . c o m*/
    return "functions";
}

---

From source file:org.egov.egf.web.actions.budget.BudgetDetailAction.java

License:Open Source License

@Action(value = "/budget/budgetDetail-ajaxLoadBudgetGroups")
public String ajaxLoadBudgetGroups() {
    final Long id = (Long) request.get("id");
    final String sqlStr = "select  distinct (bg.name) as name ,bg.id  as id from egf_budgetgroup bg,egf_budgetdetail  bd where  bg.id=bd.budgetgroup and bd.budget="
            + id + "  order  by bg.name";
    final SQLQuery sqlQuery = persistenceService.getSession().createSQLQuery(sqlStr);
    sqlQuery.addScalar("name").addScalar("id", LongType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(BudgetGroup.class));
    budgetGroupList = sqlQuery.list();/*from  w w w . java2 s.co m*/
    return "budgetGroup";
}

---

• «
• 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
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• »