Example usage for org.hibernate SQLQuery setResultTransformer

List of usage examples for org.hibernate SQLQuery setResultTransformer

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. setResultTransformer

Introduction

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

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

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

License:Open Source License

@SuppressWarnings("rawtypes")
@Override//from ww  w. j ava 2s  . c o  m
public List getResourceRatingSubscription(long contentId) {
    String sql = "select * from v_rating_data where content_id=" + contentId;
    SQLQuery query = getSession().createSQLQuery(sql);
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    return query.list();
}

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

License:Open Source License

@SuppressWarnings("rawtypes")
@Override/*  w  ww.  j  a v  a2s . c o  m*/
public List getResourceFieldValueById(String fields, String contentIds) {
    String sql = "SELECT content_id," + fields + " FROM resource WHERE content_id IN(" + contentIds + ")";
    Session session = getSessionFactory().getCurrentSession();
    SQLQuery query = session.createSQLQuery(sql);
    query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    return query.list();
}

From source file:org.egov.collection.service.CollectionReportService.java

License:Open Source License

public SQLQuery getOnlinePaymentReportData(final String districtName, final String ulbName,
        final String fromDate, final String toDate, final String transactionId) {
    final SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
    final StringBuilder queryStr = new StringBuilder(500);
    queryStr.append("select * from ").append(environmentSettings.statewideSchemaName())
            .append(".onlinepayment_view opv where 1=1");

    if (StringUtils.isNotBlank(districtName))
        queryStr.append(" and opv.districtName=:districtName ");
    if (StringUtils.isNotBlank(ulbName))
        queryStr.append(" and opv.ulbName=:ulbName ");
    if (StringUtils.isNotBlank(fromDate))
        queryStr.append(" and opv.transactiondate>=:fromDate ");
    if (StringUtils.isNotBlank(toDate))
        queryStr.append(" and opv.transactiondate<=:toDate ");
    if (StringUtils.isNotBlank(transactionId))
        queryStr.append(" and opv.transactionnumber like :transactionnumber ");
    queryStr.append(" order by receiptdate desc ");

    final SQLQuery query = getCurrentSession().createSQLQuery(queryStr.toString());

    if (StringUtils.isNotBlank(districtName))
        query.setString("districtName", districtName);
    if (StringUtils.isNotBlank(ulbName))
        query.setString("ulbName", ulbName);
    try {// www  .  j a v a  2s.c om
        if (StringUtils.isNotBlank(fromDate))
            query.setDate("fromDate", dateFormatter.parse(fromDate));
        if (StringUtils.isNotBlank(toDate))
            query.setDate("toDate", dateFormatter.parse(toDate));
    } catch (final ParseException e) {
        LOGGER.error("Exception parsing Date" + e.getMessage());
    }
    if (StringUtils.isNotBlank(transactionId))
        query.setString("transactionnumber", "%" + transactionId + "%");
    queryStr.append(" order by opv.receiptdate desc");
    query.setResultTransformer(new AliasToBeanResultTransformer(OnlinePaymentResult.class));
    return query;
}

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  ww w  . j a va2s  .co  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.pgr.web.controller.reports.AgeingReportController.java

License:Open Source License

@ExceptionHandler(Exception.class)
@RequestMapping(value = "/ageing/resultList-update", method = RequestMethod.GET)
public @ResponseBody void springPaginationDataTablesUpdate(@RequestParam final String mode,
        @RequestParam final String complaintDateType, @RequestParam final DateTime fromDate,
        @RequestParam final String status, @RequestParam final DateTime toDate,
        final HttpServletRequest request, final HttpServletResponse response) throws IOException {

    final SQLQuery ageingreportQuery = ageingReportService.getageingReportQuery(fromDate, toDate, status,
            complaintDateType, mode);/*from  w  w w.  j a  va2s  .  c o m*/
    ageingreportQuery.setResultTransformer(Transformers.aliasToBean(AgeingReportResult.class));
    final List<AgeingReportResult> ageingresult = ageingreportQuery.list();

    final String result = new StringBuilder("{ \"data\":").append(toJSON(ageingresult)).append("}").toString();

    response.setContentType(MediaType.APPLICATION_JSON_VALUE);
    IOUtils.write(result, response.getWriter());

}

From source file:org.egov.pgr.web.controller.reports.ComplaintTypeWiseReportController.java

License:Open Source License

@ExceptionHandler(Exception.class)
@RequestMapping(value = "/complaintTypeReport/resultList-update", method = RequestMethod.GET)
public @ResponseBody void springPaginationDataTablesUpdate(@RequestParam final String complaintType,
        @RequestParam final String complaintTypeWithStatus, @RequestParam final String status,
        @RequestParam final String complaintDateType, @RequestParam final DateTime fromDate,
        @RequestParam final DateTime toDate, final HttpServletRequest request,
        final HttpServletResponse response) throws IOException {
    SQLQuery complaintTypeReportQuery = null;
    List<DrillDownReportResult> complaintTypeReportResult = null;
    String result = null;/*from   ww  w  . ja v  a  2  s  .  c  o  m*/
    if (complaintTypeWithStatus != null && status != null && !"".equals(complaintTypeWithStatus)
            && !"".equals(status)) {

        complaintTypeReportQuery = complaintTypeReportService.getComplaintTypeWiseReportQuery(fromDate, toDate,
                complaintDateType, complaintTypeWithStatus, status);
        complaintTypeReportQuery.setResultTransformer(Transformers.aliasToBean(DrillDownReportResult.class));
        complaintTypeReportResult = complaintTypeReportQuery.list();
        result = new StringBuilder("{ \"data\":").append(toJSONForComplaintType(complaintTypeReportResult))
                .append("}").toString();

    } else {
        complaintTypeReportQuery = complaintTypeReportService.getComplaintTypeWiseReportQuery(fromDate, toDate,
                complaintType, complaintDateType);
        complaintTypeReportQuery.setResultTransformer(Transformers.aliasToBean(DrillDownReportResult.class));
        complaintTypeReportResult = complaintTypeReportQuery.list();
        result = new StringBuilder("{ \"data\":").append(toJSON(complaintTypeReportResult)).append("}")
                .toString();
    }

    response.setContentType(MediaType.APPLICATION_JSON_VALUE);
    IOUtils.write(result, response.getWriter());

}

From source file:org.egov.pgr.web.controller.reports.DrillDownReportController.java

License:Open Source License

@ExceptionHandler(Exception.class)
@RequestMapping(value = "/drillDown/resultList-update", method = RequestMethod.GET)
public @ResponseBody void springPaginationDataTablesUpdate(@RequestParam final String groupBy,
        @RequestParam final String deptid, @RequestParam final String complainttypeid,
        @RequestParam final String selecteduserid, @RequestParam final String boundary,
        @RequestParam final String type, @RequestParam final String complaintDateType,
        @RequestParam final DateTime fromDate, @RequestParam final DateTime toDate,
        final HttpServletRequest request, final HttpServletResponse response) throws IOException {

    SQLQuery drillDownreportQuery = null;
    String result = null;//from  w  ww .j a  va2s . c om
    if (deptid != null && complainttypeid != null && selecteduserid != null && !"".equals(deptid)
            && !"".equals(complainttypeid) && !"".equals(selecteduserid)) {
        String userName = selecteduserid.split("~")[0];
        if (userName.equals(""))
            userName = null;
        drillDownreportQuery = drillDownReportService.getDrillDownReportQuery(fromDate, toDate,
                complaintDateType, deptid, boundary, complainttypeid, userName);
        drillDownreportQuery.setResultTransformer(Transformers.aliasToBean(DrillDownReportResult.class));

        final List<DrillDownReportResult> drillDownresult = drillDownreportQuery.list();
        result = new StringBuilder("{ \"data\":").append(toJSONForComplaintType(drillDownresult)).append("}")
                .toString();

    } else {
        drillDownreportQuery = drillDownReportService.getDrillDownReportQuery(fromDate, toDate,
                complaintDateType, groupBy, deptid, boundary, complainttypeid, selecteduserid);
        drillDownreportQuery.setResultTransformer(Transformers.aliasToBean(DrillDownReportResult.class));

        final List<AgeingReportResult> drillDownresult = drillDownreportQuery.list();
        result = new StringBuilder("{ \"data\":").append(toJSON(drillDownresult)).append("}").toString();

    }

    response.setContentType(MediaType.APPLICATION_JSON_VALUE);
    IOUtils.write(result, response.getWriter());

}

From source file:org.egov.ptis.actions.reports.ActiveDemandReportAction.java

License:Open Source License

public SQLQuery prepareQuery() {
    StringBuffer queryStr = new StringBuffer("");
    String groupBy = null;//from   w w w. j av a  2  s  .  c om
    String orderBy = null;
    if (reportType != null && reportType.equalsIgnoreCase(WARD)) {
        queryStr.append(
                "SELECT WARDBNDRY.ID_BNDRY \"boundaryId\", WARDBNDRY.NAME \"boundaryName\", \"count\", \"arrDmd\", \"currDmd\", \"arrDmd\" + \"currDmd\" \"totDmd\""
                        + " FROM (SELECT ACTDMD.WARDID \"wardId\", COUNT(*) \"count\", SUM(ACTDMD.ARREAR_DEMAND) \"arrDmd\", SUM(ACTDMD.CURR_DEMAND) \"currDmd\" ");
        groupBy = new String(
                " GROUP BY ACTDMD.WARDID), EG_BOUNDARY WARDBNDRY WHERE \"wardId\" = WARDBNDRY.ID_BNDRY");
        orderBy = new String(" order by \"wardId\"");
    } else if (reportType != null && reportType.equalsIgnoreCase(PART_NO)) {
        queryStr.append(
                "SELECT ACTDMD.PARTNO \"partNo\", COUNT(*) \"count\", SUM(ACTDMD.ARREAR_DEMAND) \"arrDmd\", SUM(ACTDMD.CURR_DEMAND) \"currDmd\", SUM(ACTDMD.ARREAR_DEMAND) + SUM(ACTDMD.CURR_DEMAND) \"totDmd\"  ");
        groupBy = new String(" GROUP BY ACTDMD.PARTNO");
        orderBy = new String(" order by \"partNo\"");
    } else {
        queryStr.append(
                "SELECT ZONEBNDRY.ID_BNDRY \"boundaryId\", ZONEBNDRY.NAME \"boundaryName\", \"count\", \"arrDmd\", \"currDmd\", \"arrDmd\" + \"currDmd\" \"totDmd\""
                        + " FROM (SELECT ACTDMD.ZONEID \"ZONEID\", COUNT(*) \"count\", NVL(SUM(ACTDMD.ARREAR_DEMAND), 0) \"arrDmd\", NVL(SUM(ACTDMD.CURR_DEMAND), 0) \"currDmd\" ");
        groupBy = new String(
                " GROUP BY ACTDMD.ZONEID), EG_BOUNDARY ZONEBNDRY WHERE \"ZONEID\" = ZONEBNDRY.ID_BNDRY");
        orderBy = new String(" order by \"ZONEID\"");
    }
    String fromClause = new String(
            " FROM EGPT_MV_ACTIVE_DEMAND ACTDMD, (SELECT UPICNO,MAX(DMD_ACTIVATION_DATE) AS MXDATE FROM EGPT_MV_ACTIVE_DEMAND");
    StringBuffer whereClause = new StringBuffer("");
    whereClause = prepareSearchCriteria(whereClause);
    whereClause.append(
            " GROUP BY UPICNO) M where ACTDMD.UPICNO = M.UPICNO AND ACTDMD.DMD_ACTIVATION_DATE = M.MXDATE ");
    queryStr.append(fromClause).append(whereClause).append(groupBy).append(orderBy);
    SQLQuery query = persistenceService.getSession().createSQLQuery(queryStr.toString());
    query.setResultTransformer(new AliasToBeanResultTransformer(ActiveDemandInfo.class));
    return query;
}

From source file:org.egov.ptis.actions.reports.AjaxDCBReportAction.java

License:Open Source License

/**
 * @ Description - Returns query that retrieves zone/ward/block/propertywise Arrear, Current Demand and Collection Details
 * @return//from w ww. ja  va2s .  c  om
 */
public SQLQuery prepareQuery() {
    //To conver multi selected propertyTypes values(json stringify) into list
    List<String> propertyType = new Gson().fromJson(propTypes, new TypeToken<ArrayList<String>>() {
    }.getType());
    final SQLQuery query = reportService.prepareQueryForDCBReport(boundaryId, mode, courtCase, propertyType);
    query.setResultTransformer(new AliasToBeanResultTransformer(DCBReportResult.class));
    return query;
}

From source file:org.egov.ptis.domain.service.report.DemandRegisterService.java

License:Open Source License

@ReadOnly
@SuppressWarnings("unchecked")
public List<DemandRegisterInfo> getDemandRegisterInfo(final String finYear, final Long ward, final String mode,
        final java.sql.Date finYearStartDate) {
    String propertyType = getPropertyType(mode);
    StringBuilder query = new StringBuilder(
            "select bp.propertyid \"assessmentNo\", at.ownersname \"ownerName\", at.doorno \"houseNo\", instm.financial_year \"financialYear\", cast(idi.demand as numeric) \"demand\", coalesce(cast(ici.collectiondate as character varying), '-') \"collectionDate\","
                    + "cast(coalesce(ici.amount, 0) as numeric) \"collectedAmount\", coalesce(ici.collectionmode, '-') \"collectionMode\", cast(idi.totalcollection as numeric) \"totalCollection\", cast(idi.writeoff as numeric) \"writeOff\", cast(idi.advance as numeric) \"advanceAmount\",cast(idi.installment as integer) \"installment\" "
                    + " from egpt_assessment_transactions at, egpt_basic_property bp, egpt_installment_demand_info idi left join egpt_installment_collection_info ici on idi.id=ici.installment_demand_info, eg_installment_master instm, egpt_property_type_master ptm where "
                    + "idi.assessment_transactions=at.id and at.basicproperty=bp.id and idi.installment=instm.id and instm.financial_year=:finYear and at.ward =:ward and at.propertytype=ptm.id and ptm.code =:propertyType and idi.demand > idi.totalCollection and at.transaction_date <:finYearStartDate order by bp.propertyid, at.transaction_date");
    final SQLQuery sqlQuery = ptCommonUtils.getSession().createSQLQuery(query.toString());
    sqlQuery.setParameter("finYear", finYear);
    sqlQuery.setParameter("ward", ward);
    sqlQuery.setParameter("propertyType", propertyType);
    sqlQuery.setParameter("finYearStartDate", finYearStartDate);
    sqlQuery.setResultTransformer(Transformers.aliasToBean(DemandRegisterInfo.class));
    return filterDemandRegisterInfo(sqlQuery.list());
}