Example usage for org.hibernate SQLQuery list

List of usage examples for org.hibernate SQLQuery list

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

Introduction

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

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.ett.self.print.biz.PrintBizImpl.java

protected void searchFlowFromSql(String sql, String idcard) {

    SessionFactory sessionFactory = this.getBaseDaoDrv().getHibernateSessionFactory();
    Session session = sessionFactory.openSession();
    Transaction tx = session.beginTransaction();

    String queryStr = MessageFormat.format(sql, idcard);
    log.debug("?" + queryStr.toString());
    SQLQuery query = session.createSQLQuery(queryStr.toString());
    query.addScalar("ywlx", new org.hibernate.type.StringType());
    query.addScalar("fee", new org.hibernate.type.StringType());
    query.addScalar("ywyy", new org.hibernate.type.StringType());
    query.addScalar("yyrq", new org.hibernate.type.TimestampType());
    query.addScalar("ksrq", new org.hibernate.type.TimestampType());
    query.addScalar("slrq", new org.hibernate.type.TimestampType());
    query.addScalar("lsh", new org.hibernate.type.StringType());
    query.addScalar("xm", new org.hibernate.type.StringType());
    query.addScalar("hphm", new org.hibernate.type.StringType());

    query.addScalar("jdsbh", new org.hibernate.type.StringType());
    query.addScalar("sfzmhm", new org.hibernate.type.StringType());
    query.addScalar("zjcx", new org.hibernate.type.StringType());
    query.addScalar("dabh", new org.hibernate.type.StringType());

    query.setResultTransformer(Transformers.aliasToBean(DrvPersonFlowObject.class));
    try {// w  ww . ja va 2  s  .  c  o  m
        tx.begin();
        //query.uniqueResult()
        this.lists = query.list();

        if (log.isInfoEnabled()) {
            log.info("?==" + lists.size());
        }
        if (this.lists == null || this.lists.size() == 0) {
            this.lists = new ArrayList();

            //this.lists.add(co);
        }
        session.flush();
        tx.commit();
    } catch (Exception e) {
        e.printStackTrace();
        log.info(e);
        if (tx != null) {
            tx.rollback();
        }
    } finally {
        session.close();
    }
    //return null;
}

From source file:com.exilant.eGov.src.domain.BankReconciliationSummary.java

License:Open Source License

public String getUnReconciledDrCr(Integer bankAccId, Date fromDate, Date toDate) throws Exception {

    String instrumentsForTotal = "case when iv.voucherHeaderId is null then 0 else ih.instrumentAmount end)";
    String instrumentsForBrsEntryTotal = "(case when br.voucherHeaderId is null then ih.instrumentAmount else 0 end)";

    String totalQuery = "SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end))  AS \"brs_creditTotal\", "
            + " (sum( case when ih.ispaycheque= '0' then ih.instrumentAmount else 0 end) ) AS \"brs_debitTotal\" "
            + " FROM egf_instrumentheader ih    WHERE   ih.bankAccountId =:bankAccountId "
            + " AND IH.INSTRUMENTDATE >= :fromDate" + " AND IH.INSTRUMENTDATE <= :toDate"
            + " AND  ( (ih.ispaycheque='0' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited'))or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
            + " and ih.instrumentnumber is not null";
    //see u might need to exclude brs entries here 

    String otherTotalQuery = " SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end ))  AS \"brs_creditTotalOthers\", "
            + " (sum(case when ih.ispaycheque='0' then ih.instrumentAmount else 0 end ) ) AS \"brs_debitTotalOthers\" "
            + " FROM  egf_instrumentheader ih   WHERE   ih.bankAccountId =:bankAccountId"
            + " AND IH.transactiondate >= :fromDate" + " AND IH.transactiondate <= :toDate  "
            + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited'))or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
            + " AND ih.transactionnumber is not null";

    String brsEntryQuery = "select (sum(case when be.type='Receipt' then (case when be.voucherheaderid is null then be.txnamount else 0 end) else 0 end))AS \"brs_creditTotalBrsEntry\","
            + "(sum(case when be.type='Payment' then (case when be.voucherheaderid is null then be.txnamount else 0 end) else 0 end))AS \"brs_debitTotalBrsEntry\""
            + "FROM  bankentries be WHERE   be.bankAccountId = :bankAccountId and be.voucherheaderid is null AND be.txndate >=:fromDate   AND be.txndate <= :toDate";

    if (LOGGER.isInfoEnabled())
        LOGGER.info("  query  for  total : " + totalQuery);
    if (LOGGER.isInfoEnabled())
        LOGGER.info("  query  for other than cheque/DD: " + otherTotalQuery);
    if (LOGGER.isInfoEnabled())
        LOGGER.info("  query  for bankEntries: " + brsEntryQuery);

    String unReconciledDrCr = "";

    String creditTotal = null;/*w  ww.java 2  s . co m*/
    String creditOthertotal = null;
    String debitTotal = null;
    String debitOtherTotal = null;
    String creditTotalBrsEntry = null;
    String debitTotalBrsEntry = null;

    try {
        SQLQuery totalSQLQuery = persistenceService.getSession().createSQLQuery(totalQuery);
        totalSQLQuery.setInteger("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);

        List list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditTotal = my[0] != null ? my[0].toString() : null;
            debitTotal = my[1] != null ? my[1].toString() : null;
        }

        totalSQLQuery = persistenceService.getSession().createSQLQuery(otherTotalQuery);
        totalSQLQuery.setInteger("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);
        list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditOthertotal = my[0] != null ? my[0].toString() : null;
            debitOtherTotal = my[1] != null ? my[1].toString() : null;
        }

        totalSQLQuery = persistenceService.getSession().createSQLQuery(brsEntryQuery);
        totalSQLQuery.setInteger("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);
        list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditTotalBrsEntry = my[0] != null ? my[0].toString() : null;
            debitTotalBrsEntry = my[1] != null ? my[1].toString() : null;
        }

        unReconciledDrCr = (creditTotal != null ? creditTotal : "0") + "/"
                + (creditOthertotal != null ? creditOthertotal : "0") + "/"
                + (debitTotal != null ? debitTotal : "0") + "/"
                + (debitOtherTotal != null ? debitOtherTotal : "0") + "" + "/"
                + (creditTotalBrsEntry != null ? creditTotalBrsEntry : "0") + "/"
                + (debitTotalBrsEntry != null ? debitTotalBrsEntry : "0") + "";
    } catch (Exception e) {
        LOGGER.error("Exp in getUnReconciledDrCr" + e.getMessage());
        throw e;
    }
    return unReconciledDrCr;
}

From source file:com.exilant.GLEngine.CoaCache.java

License:Open Source License

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void loadAccountData() {

    /*//from   w w  w  .  j a  v a 2 s. com
     * 1.Loads all the account codes and details of that as GLAccount objects in theGLAccountCode,theGLAccountId HashMap's
     */

    // Temporary place holders
    final HashMap glAccountCodes = new HashMap();
    final HashMap glAccountIds = new HashMap();
    final HashMap accountDetailType = new HashMap();

    String sql = "select id as \"id\",name as \"name\",tableName as \"tableName\","
            + "description as \"description\",columnName as \"columnName\",attributeName as \"attributeName\""
            + ",nbrOfLevels as  \"nbrOfLevels\" from AccountDetailType";

    final Session currentSession = persistenceService.getSession();
    SQLQuery createSQLQuery = currentSession.createSQLQuery(sql);
    createSQLQuery.addScalar("id", IntegerType.INSTANCE).addScalar("name").addScalar("tableName")
            .addScalar("description").addScalar("columnName").addScalar("attributeName")
            .setResultTransformer(Transformers.aliasToBean(AccountDetailType.class));
    List<AccountDetailType> accountDetailTypeList = new ArrayList<AccountDetailType>();
    List<GLAccount> glAccountCodesList = new ArrayList<GLAccount>();
    new ArrayList<GLAccount>();

    accountDetailTypeList = createSQLQuery.list();
    for (final AccountDetailType type : accountDetailTypeList)
        accountDetailType.put(type.getAttributeName(), type);
    sql = "select ID as \"ID\", glCode as \"glCode\" ,name as \"name\" ,"
            + "isActiveForPosting as \"isActiveForPosting\" ,classification as \"classification\", functionReqd as \"functionRequired\" from chartofaccounts ";
    createSQLQuery = currentSession.createSQLQuery(sql);
    createSQLQuery.addScalar("ID", IntegerType.INSTANCE).addScalar("glCode").addScalar("name")
            .addScalar("isActiveForPosting", BooleanType.INSTANCE)
            .addScalar("classification", LongType.INSTANCE).addScalar("functionRequired", BooleanType.INSTANCE)
            .setResultTransformer(Transformers.aliasToBean(GLAccount.class));

    glAccountCodesList = createSQLQuery.list();
    for (final GLAccount type : glAccountCodesList)
        glAccountCodes.put(type.getCode(), type);
    for (final GLAccount type : glAccountCodesList)
        glAccountIds.put(type.getId(), type);
    loadParameters(glAccountCodes, glAccountIds);
    try {
        final HashMap<String, HashMap> hm = new HashMap<String, HashMap>();
        hm.put(ACCOUNTDETAILTYPENODE, accountDetailType);
        hm.put(GLACCCODENODE, glAccountCodes);
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Loading size:" + glAccountCodes.size());
        hm.put(GLACCIDNODE, glAccountIds);
        applicationCacheManager.put(ROOTNODE, hm);
    } catch (final Exception e) {
        throw e;
    }

}

From source file:com.football.site.db.DbHelper.java

public static ArrayList<FixturesRet> GetLeagueFixture(int leagueId) {
    ArrayList<FixturesRet> retList = new ArrayList<>();
    Transaction tran = null;// ww  w .  j  a  v a 2s .  c o m
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("SELECT * FROM fixtures_view WHERE legue_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        List lst = q.list();
        FixturesRet item = null;
        Object[] objArr = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new FixturesRet();
            item.setFixtureId(Integer.parseInt(objArr[0].toString()));
            item.setLeagueId(Integer.parseInt(objArr[1].toString()));
            item.setMatchDate(df.parse(objArr[2].toString()));
            item.setStatus(objArr[3].toString());
            item.setMatchday(Integer.parseInt(objArr[4].toString()));
            item.setHomeTeamName(objArr[5].toString());
            item.setAwayTeamName(objArr[6].toString());
            item.setHomeTeamGoal(Integer.parseInt(objArr[7].toString()));
            item.setAwayTeamGoal(Integer.parseInt(objArr[8].toString()));
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

From source file:com.football.site.db.DbHelper.java

public static LeagueTableRet GetLeagueTable(int leagueId) {
    LeagueTableRet retList = new LeagueTableRet();
    Transaction tran = null;// w  w  w  .j  a  va2 s .c om
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        Criteria cr = s.createCriteria(Leaguetable.class);
        cr.add(Restrictions.eq("leagueId", leagueId));
        Leaguetable ligTable = (Leaguetable) cr.uniqueResult();

        cr = s.createCriteria(LeagueTableRows.class);
        cr.add(Restrictions.eq("leagueTableId", ligTable.getRecordId()));
        ArrayList<LeagueTableRows> dbRowList = (ArrayList<LeagueTableRows>) cr.list();

        ArrayList<LeagueTableRowsRet> rowList = new ArrayList<>();
        LeagueTableRowsRet item = null;
        LeagueTableRows dbItem = null;
        ArrayList<LeagueTableTeamStatistics> statisticList = null;
        for (Iterator<LeagueTableRows> i = dbRowList.iterator(); i.hasNext();) {
            dbItem = i.next();
            cr = s.createCriteria(LeagueTableTeamStatistics.class);
            cr.add(Restrictions.eq("leagueTableRowId", dbItem.getRecordId()));
            statisticList = (ArrayList<LeagueTableTeamStatistics>) cr.list();
            item = new LeagueTableRowsRet();
            item.setRow(dbItem);
            if (statisticList.size() > 0) {
                if (statisticList.get(0).getStatisticOwn().equals("HOME")) {
                    item.setHomeInfo(statisticList.get(0));
                    item.setAwayInfo(statisticList.get(1));
                } else {
                    item.setHomeInfo(statisticList.get(1));
                    item.setAwayInfo(statisticList.get(0));
                }
            }
            rowList.add(item);
        }
        retList.setLeagueTable(ligTable);
        retList.setRowsList(rowList);

        SQLQuery q = s.createSQLQuery("SELECT * FROM league_table_home WHERE league_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        List lst = q.list();
        Object[] objArr = null;
        ArrayList<LeagueTableRowsRet> rowListHome = new ArrayList<>();
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new LeagueTableRowsRet();
            dbItem = new LeagueTableRows();
            dbItem.setPosition(String.valueOf(i + 1));
            dbItem.setLeagueTableId(Integer.parseInt(objArr[0].toString()));
            dbItem.setTeamName(objArr[2].toString());
            dbItem.setPlayedGames(Integer.parseInt(objArr[3].toString()));
            dbItem.setGoals(Integer.parseInt(objArr[4].toString()));
            dbItem.setGoalsAgainst(Integer.parseInt(objArr[5].toString()));
            dbItem.setGoalDifference(Integer.parseInt(objArr[6].toString()));
            dbItem.setWins(Integer.parseInt(objArr[7].toString()));
            dbItem.setDraws(Integer.parseInt(objArr[8].toString()));
            dbItem.setLosses(Integer.parseInt(objArr[9].toString()));
            dbItem.setPoints(Integer.parseInt(objArr[10].toString()));
            dbItem.setCrestUrl(objArr[11].toString());
            dbItem.setCrestPicture((byte[]) objArr[12]);
            dbItem.setRecordId(0);
            item.setRow(dbItem);
            rowListHome.add(item);
        }
        retList.setRowsListHome(rowListHome);

        q = s.createSQLQuery("SELECT * FROM league_table_away WHERE league_id=:leagueId");
        q.setParameter("leagueId", leagueId);
        lst = q.list();
        ArrayList<LeagueTableRowsRet> rowListAway = new ArrayList<>();
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new LeagueTableRowsRet();
            dbItem = new LeagueTableRows();
            dbItem.setPosition(String.valueOf(i + 1));
            dbItem.setLeagueTableId(Integer.parseInt(objArr[0].toString()));
            dbItem.setTeamName(objArr[2].toString());
            dbItem.setPlayedGames(Integer.parseInt(objArr[3].toString()));
            dbItem.setGoals(Integer.parseInt(objArr[4].toString()));
            dbItem.setGoalsAgainst(Integer.parseInt(objArr[5].toString()));
            dbItem.setGoalDifference(Integer.parseInt(objArr[6].toString()));
            dbItem.setWins(Integer.parseInt(objArr[7].toString()));
            dbItem.setDraws(Integer.parseInt(objArr[8].toString()));
            dbItem.setLosses(Integer.parseInt(objArr[9].toString()));
            dbItem.setPoints(Integer.parseInt(objArr[10].toString()));
            dbItem.setCrestUrl(objArr[11].toString());
            dbItem.setCrestPicture((byte[]) objArr[12]);
            dbItem.setRecordId(0);
            item.setRow(dbItem);
            rowListAway.add(item);
        }
        retList.setRowsListAway(rowListAway);
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

From source file:com.football.site.db.DbHelper.java

public static ArrayList<Players> GetTeamPlayers(int teamId) {
    ArrayList<Players> retList = new ArrayList<>();
    Transaction tran = null;/*from  w  w  w.j  a  v  a 2s . co  m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("select * from players_view where teams_id = :teams_id");
        q.setParameter("teams_id", teamId);
        List lst = q.list();
        Object[] objArr = null;
        Players item = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new Players();
            item.setRecordId(Integer.parseInt(objArr[0].toString()));
            item.setTeamsId(Integer.parseInt(objArr[1].toString()));
            item.setName(objArr[2].toString());
            item.setPosition(objArr[3].toString());
            item.setJerseyNumber(Integer.parseInt(objArr[4].toString()));
            item.setDateOfBirth(df.parse(objArr[5].toString()));
            item.setNationality(objArr[6].toString());
            item.setContractUntil(df.parse(objArr[7].toString()));
            item.setMarketValue(objArr[8].toString());
            item.setMarketValueNumber(Double.parseDouble(objArr[9].toString()));
            item.setMarketValueMoneyType(objArr[10].toString());
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

From source file:com.football.site.db.DbHelper.java

public static ArrayList<FixturesRet> GetTeamFixture(int teamId) {
    ArrayList<FixturesRet> retList = new ArrayList<>();
    Transaction tran = null;//from  w w  w .  j  a  v a  2  s .  co  m
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s.createSQLQuery("SELECT * FROM fixtures_team_view WHERE team_id=:teamId");
        q.setParameter("teamId", teamId);
        List lst = q.list();
        FixturesRet item = null;
        Object[] objArr = null;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        for (int i = 0; i < lst.size(); i++) {
            objArr = (Object[]) lst.get(i);
            item = new FixturesRet();
            item.setFixtureId(Integer.parseInt(objArr[0].toString()));
            item.setLeagueId(Integer.parseInt(objArr[1].toString()));
            item.setMatchDate(df.parse(objArr[2].toString()));
            item.setStatus(objArr[3].toString());
            item.setMatchday(Integer.parseInt(objArr[4].toString()));
            item.setHomeTeamName(objArr[5].toString());
            item.setAwayTeamName(objArr[6].toString());
            item.setHomeTeamGoal(Integer.parseInt(objArr[7].toString()));
            item.setAwayTeamGoal(Integer.parseInt(objArr[8].toString()));
            retList.add(item);
        }
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        tran.commit();
        HibernateUtil.closeSession();
    }
    return retList;
}

From source file:com.football.site.db.DbHelper.java

public static boolean IsLeagueTemasInsertBefore(int leagueId, int year) {
    boolean retVal = false;
    Transaction tran = null;/*from   w  w w. j  a  v a  2 s. c  o m*/
    try {
        Session s = HibernateUtil.currentSession();
        tran = s.beginTransaction();
        SQLQuery q = s
                .createSQLQuery("SELECT * FROM league_teams_info WHERE league_id=:leagueId AND yearyer=:year");
        q.setParameter("teamId", leagueId);
        q.setParameter("year", year);
        List lst = q.list();
        retVal = lst.size() > 0;
    } catch (Exception e) {
        HelperUtil.AddErrorLog(logger, e);
    } finally {
        if (null != tran) {
            tran.commit();
        }
        HibernateUtil.closeSession();
    }
    return retVal;
}

From source file:com.formkiq.core.dao.ReportDaoImpl.java

License:Apache License

@SuppressWarnings({ "unchecked", "resource" })
@Override/*w w  w . j  a v a2s . co  m*/
public List<Map<String, Object>> runReport(final ReportInput input) {

    StringBuilder sb = new StringBuilder();

    appendSelectSQL(sb, input);

    appendFromSQL(sb, input);

    appendWhereSQL(sb, input);

    appendWhereDateSQL(sb, input);

    appendGroupBySQL(sb, input);

    Session session = getEntityManager().unwrap(Session.class);

    SQLQuery q = session.createSQLQuery(sb.toString());
    addScalars(q, input);

    Map<Object, Object> map = createParameterMap(input);
    q.setProperties(map);

    List<Object[]> list = q.list();

    return translateToMap(input.getColumns(), list);
}

From source file:com.ghy.common.orm.hibernate.HibernateDao.java

License:Apache License

/**
 * ?sql??map?list//from   w  w  w  .jav a2 s .  c  o m
 * @param sql
 * @param params
 * @return
 */
public List<Object[]> findBySql(String sql, Map<String, ?> params) {
    SQLQuery queryObject = getSession().createSQLQuery(sql);
    if (params != null && params.size() > 0)
        queryObject.setProperties(params);
    return queryObject.list();
}