Example usage for org.hibernate Query setString

List of usage examples for org.hibernate Query setString

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setString

Introduction

In this page you can find the example usage for org.hibernate Query setString.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setString(String name, String val)

Source Link

Document

Bind a named String-valued parameter.

Usage

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the fiscal period id for the date passed
 *///from  w w  w .  j ava 2  s . c  o m
@Override
public String getFiscalPeriod(final String vDate, final Connection connection)
        throws TaskFailedException, Exception {
    String fiscalPeriodID = "null";
    try {
        final String query = "select id from fiscalperiod  where ? between startingdate and endingdate";
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, vDate);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            fiscalPeriodID = element[0].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info(">>>fiscalPeriodID " + fiscalPeriodID);
        }
        if (rset == null || rset.size() == 0)
            throw new TaskFailedException("fiscal Period Not Found");
    } catch (final TaskFailedException e) {
        LOGGER.error("fiscal Period Not Found=" + e.getMessage(), e);
        throw new TaskFailedException("fiscal Period Not Found");
    } catch (final Exception e) {
        LOGGER.error("failed to get fiscalperiodId " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return fiscalPeriodID;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the bank balance for the bank account
 *///from  w  w w. jav  a 2 s  .c  o m
@Override
public double getAccountBalance(final int bankAccountId, final String vcDate, final Connection connection)
        throws Exception {
    double opeAvailable = 0, totalAvailable = 0;
    try {

        final String str = "SELECT case when sum(openingDebitBalance) = null then 0 ELSE sum(openingDebitBalance) end - case when sum(openingCreditBalance) = null then 0 else sum(openingCreditBalance) end AS \"openingBalance\" "
                + "FROM transactionSummary WHERE financialYearId=( SELECT id FROM financialYear WHERE startingDate <= ?"
                + "AND endingDate >= ?)  AND glCodeId =(select glcodeid from bankaccount where id= ?)";
        if (LOGGER.isInfoEnabled())
            LOGGER.info(str);
        Query pst = persistenceService.getSession().createSQLQuery(str);
        pst.setString(0, vcDate);
        pst.setString(1, vcDate);
        pst.setInteger(2, bankAccountId);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset)
            opeAvailable = Double.parseDouble(element[0].toString());
        if (LOGGER.isInfoEnabled())
            LOGGER.info("opening balance  " + opeAvailable);

        final String str1 = "SELECT (case when sum(gl.debitAmount) = null then 0 else sum(gl.debitAmount) end - case when sum(gl.creditAmount)  = null then 0 else sum(gl.creditAmount) end) + "
                + opeAvailable + ""
                + " as \"totalAmount\" FROM   generalLedger gl, voucherHeader vh WHERE vh.id = gl.voucherHeaderId AND gl.glCodeid = (select glcodeid from bankaccount where id= ?) AND  "
                + " vh.voucherDate >=( SELECT TO_CHAR(startingDate, 'dd-Mon-yyyy') FROM financialYear WHERE startingDate <= ? AND endingDate >= ?) AND vh.voucherDate <= ?";
        if (LOGGER.isInfoEnabled())
            LOGGER.info(str1);
        pst = persistenceService.getSession().createSQLQuery(str1);
        pst.setInteger(0, bankAccountId);
        pst.setString(1, vcDate);
        pst.setString(2, vcDate);
        pst.setString(3, vcDate);
        rset = pst.list();
        for (final Object[] element : rset) {
            totalAvailable = Double.parseDouble(element[0].toString());
            if (LOGGER.isInfoEnabled())
                LOGGER.info("total balance  " + totalAvailable);
        }

    } catch (final Exception e) {
        LOGGER.error(" could not get Bankbalance  " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return totalAvailable;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the acccount code and name for the account with the purposeid
 *///w  w w .j  av  a2 s.c om
@Override
public String getCodeName(final String purposeId) throws Exception {
    String codeAndName = "null";
    try {
        final String query = "select a.glcode, a.name from chartofaccounts a,egf_accountcode_purpose b where a.purposeid=b.id and b.id= ?";
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, purposeId);
        List<Object[]> rset = pst.list();
        // for(int i=0;rset.next();i++){
        for (final Object[] element : rset) {
            codeAndName = element[0].toString();
            codeAndName = codeAndName + "#" + element[1].toString();
        }

    } catch (final Exception e) {
        LOGGER.error(" code not found for purpose id " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return codeAndName;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the name of the accountcode passed
 */// w  w  w.j av a  2  s .  c  om
@Override
public String getNameFromCode(final String glcode, final Connection connection) throws Exception {

    String codeName = "null";
    try {
        final String query = "select name from chartofaccounts where glcode= ?";
        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query   " + query);
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, glcode);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            codeName = element[0].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("  codeName   " + codeName);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("code not found");
    } catch (final Exception e) {
        LOGGER.error(" code not found " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return codeName;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * Get the accountcode of the is paased.
 *///from w  w w .  j  av a 2 s . co  m
@Override
public String getGlCode(final String glCodeId, final Connection connection) throws Exception {
    String glCode = "null";
    try {
        final String query = "select glcode from chartofaccounts where id= ?";
        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query   " + query);
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, glCodeId);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            glCode = element[0].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("  glCode   " + glCode);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("id not found");
    } catch (final Exception e) {
        LOGGER.error(" id not found " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return glCode;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * This is to check if a record already exist in integrationlog
 *//*from w  ww . j a v a2 s  . c  om*/
@Override
public String checkRecordIdInLog(final String recordId, final int userId, final Connection connection)
        throws Exception {
    String cgn = null;
    try {
        final String query = "SELECT VOUCHERNUMBER FROM integrationlog WHERE RECORDID= ? and USERID= ? order by id desc";
        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query   " + query);
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, recordId);
        pst.setInteger(1, userId);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            cgn = element[0].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("  cgn in log  " + cgn);
        }
    } catch (final Exception e) {
        LOGGER.error("Exp=" + e.getMessage(), e);
        throw new Exception(e.toString());
    }
    return cgn;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * This method gets the GlCodeId by passing GLCODE as parameter added by Sapna
 *///from ww w .j ava 2 s .c  o  m
@Override
public String getGlCodeId(final String glCode, final Connection connection) throws Exception {
    String glCodeId = "null";
    try {
        final String query = "select id from chartofaccounts where glCode like ?";
        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query   " + query);
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, glCode);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            glCodeId = element[0].toString();
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("  glCodeId   " + glCodeId);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("id not found");
    } catch (final Exception e) {
        LOGGER.error(" id not found " + e.toString(), e);
        throw new Exception(e.toString());
    }
    return glCodeId;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * This API will return the transaction no for any type of txn. Input :Type,transaction date and connection Output
 * :Transaction number in the format txnType+number+/+month+/+year
 *///from ww w  .  j av a 2 s .  c  om
@Override
public String getTxnNumber(final String txnType, final String vDate) throws Exception {
    String finYear = "";
    String fiscalPeriod = "";
    String retVal = "";

    final String month[] = vDate.split("/");
    if (LOGGER.isInfoEnabled())
        LOGGER.info(" The month :" + month[1]);
    final SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    // Date dt=new Date();
    // dt = sdf.parse( vDate );
    final String txndate = formatter.format(sdf.parse(vDate));

    final String sql = "select a.FINANCIALYEAR,b.id from FINANCIALYEAR a,fiscalperiod b  where a.id=b.financialyearid AND ? between b.startingdate and b.endingdate";
    if (LOGGER.isInfoEnabled())
        LOGGER.info(sql);
    try {
        Query pst = persistenceService.getSession().createSQLQuery(sql);
        pst.setString(0, txndate);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            finYear = element[0].toString();
            fiscalPeriod = element[1].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("finYear id>>>>>>>" + finYear + " fiscalPeriod :" + fiscalPeriod);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("Year is not defined in the system");

        final String year = finYear.substring(2, 4) + finYear.substring(finYear.length() - 2, finYear.length());
        if (LOGGER.isInfoEnabled())
            LOGGER.info(" The year String :" + year);
        // ---
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(" In CommonMethodsImpl :getTxnNumber method ");
        //persistenceService.setType(CFiscalPeriod.class);
        final CFiscalPeriod fiscalPeriodObj = (CFiscalPeriod) persistenceService
                .find("from CFiscalPeriod where id=?", Long.parseLong(fiscalPeriod));
        // Sequence name will be SQ_U_DBP_CGVN_FP7 for txnType U/DBP/CGVN and fiscalPeriodIdStr 7
        final String sequenceName = VoucherHelper.sequenceNameFor(txnType, fiscalPeriodObj.getName());
        Serializable runningNumber = genericSequenceNumberGenerator.getNextSequence(sequenceName);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("----- Txn Number : " + runningNumber);
        // ---

        retVal = txnType + runningNumber.toString() + "/" + month[1] + "/" + year;
        if (LOGGER.isInfoEnabled())
            LOGGER.info("Return value is :" + retVal);

    } catch (final Exception e) {
        LOGGER.error("Exp=" + e.getMessage(), e);
        throw new Exception(e.toString());
    }
    return retVal;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

/**
 * added by Iliyaraja This API will return the generated Voucher number. Input :Fund Id,txnType,transaction date and
 * connection Output :Transaction number in the format fundType+txnType+/+number+/+month+/+year
 *//* ww  w  .j a v a2s. c  o m*/
@Override
public String getTxnNumber(final String fundId, String txnType, final String vDate, final Connection con)
        throws Exception {
    if (txnType == null || txnType.equals(""))
        throw new Exception("Configuration setting for voucher numbering is not done");

    String fType = "";
    String finYear = "";
    String fiscalPeriod = "";
    String retVal = "";

    final String month[] = vDate.split("/");
    if (LOGGER.isInfoEnabled())
        LOGGER.info(" The month :" + month[1]);
    final SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    final String txndate = formatter.format(sdf.parse(vDate));

    final String sql = "select a.FINANCIALYEAR,b.id from FINANCIALYEAR a,fiscalperiod b  where a.id=b.financialyearid AND '"
            + txndate + "' between b.startingdate and b.endingdate";
    if (LOGGER.isInfoEnabled())
        LOGGER.info(sql);
    try {

        // This is for getting fund type based on the fund id.
        final String query = "SELECT identifier as \"fund_identi\" from fund where id= ?";
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, fundId);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            fType = element[0].toString();

            if (LOGGER.isInfoEnabled())
                LOGGER.info("Fund Id--->" + fundId + " Fund Type---->" + fType);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("Fund is not defined in the system");

        pst = persistenceService.getSession().createSQLQuery(sql);
        rset = pst.list();
        for (final Object[] element : rset) {
            finYear = element[0].toString();
            fiscalPeriod = element[1].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("finYear id>>>>>>>" + finYear + " fiscalPeriod :" + fiscalPeriod);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("Year is not defined in the system");

        final String year = finYear.substring(2, 4) + finYear.substring(finYear.length() - 2, finYear.length());
        if (LOGGER.isInfoEnabled())
            LOGGER.info(" The year String :" + year);
        txnType = fType.concat(txnType);

        // ---
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(" In CommonMethodsImpl :getTxnNumber method ");
        // persistenceService.setSessionFactory(new SessionFactory());
        //persistenceService.setType(CFiscalPeriod.class);
        final CFiscalPeriod fiscalPeriodObj = (CFiscalPeriod) persistenceService
                .find("from CFiscalPeriod where id=?", Long.parseLong(fiscalPeriod));
        // Sequence name will be SQ_U_DBP_CGVN_FP7 for txnType U/DBP/CGVN and fiscalPeriodIdStr 7
        final String sequenceName = VoucherHelper.sequenceNameFor(txnType, fiscalPeriodObj.getName());
        Serializable runningNumber = genericSequenceNumberGenerator.getNextSequence(sequenceName);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("----- Txn Number : " + runningNumber);
        // ---

        retVal = txnType + "/" + runningNumber.toString() + "/" + month[1] + "/" + year;
        if (LOGGER.isInfoEnabled())
            LOGGER.info("Return value is :" + retVal);

    } catch (final Exception e) {
        LOGGER.error("Exp=" + e.getMessage(), e);
        throw new Exception(e.toString());
    }
    return retVal;
}

From source file:com.exilant.eGov.src.transactions.CommonMethodsImpl.java

License:Open Source License

@Override
public String getTransRunningNumber(final String fundId, String txnType, final String vDate,
        final Connection con) throws Exception {

    if (txnType == null || txnType.equals(""))
        throw new Exception("Configuration setting for voucher numbering is not done");

    String fType = "";
    String finYear = "";
    String fiscalPeriod = "";
    String retVal = "";

    final SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    final SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");
    final String txndate = formatter.format(sdf.parse(vDate));

    final String sql = "select a.FINANCIALYEAR,b.id from FINANCIALYEAR a,fiscalperiod b  where a.id=b.financialyearid AND '"
            + txndate + "' between b.startingdate and b.endingdate";
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("sql in getTransRunningNumber() :" + sql);
    try {/*from w ww.j  a v a  2  s  . c  o  m*/

        // This is for getting fund type based on the fund id.
        final String query = "SELECT identifier as \"fund_identi\" from fund where id= ?";
        Query pst = persistenceService.getSession().createSQLQuery(query);
        pst.setString(0, fundId);
        List<Object[]> rset = pst.list();
        for (final Object[] element : rset) {
            fType = element[0].toString();
            if (LOGGER.isInfoEnabled())
                LOGGER.info("Fund Id  :--->" + fundId + " Fund Type  :---->" + fType);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("Fund is not defined in the system");
        pst = persistenceService.getSession().createSQLQuery(sql);
        rset = pst.list();
        for (final Object[] element : rset) {
            finYear = element[0].toString();
            fiscalPeriod = element[1].toString();
            if (LOGGER.isDebugEnabled())
                LOGGER.debug("finYear id>>>>>>>" + finYear + " fiscalPeriod :" + fiscalPeriod);
        }
        if (rset == null || rset.size() == 0)
            throw new Exception("Year is not defined in the system");

        txnType = fType.concat(txnType);
        // ---
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(" In CommonMethodsImpl :getTxnNumber method ");
        //persistenceService.setType(CFiscalPeriod.class);
        final CFiscalPeriod fiscalPeriodObj = (CFiscalPeriod) persistenceService
                .find("from CFiscalPeriod where id=?", Long.parseLong(fiscalPeriod));
        // Sequence name will be SQ_U_DBP_CGVN_FP7 for txnType U/DBP/CGVN and fiscalPeriodIdStr 7
        final String sequenceName = VoucherHelper.sequenceNameFor(txnType, fiscalPeriodObj.getName());
        Serializable runningNumber = genericSequenceNumberGenerator.getNextSequence(sequenceName);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("----- Running Number : " + runningNumber);
        // ---
        retVal = runningNumber.toString();
        if (LOGGER.isInfoEnabled())
            LOGGER.info("Return value is in getTransRunningNumber() :" + retVal);

    } catch (final Exception e) {
        LOGGER.error("Exp occured in getTransRunningNumber() :" + e.getMessage(), e);
        throw new Exception(e.toString());
    }
    return retVal;
}