Example usage for org.hibernate Query setParameterList

List of usage examples for org.hibernate Query setParameterList

Introduction

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

Prototype

Query<R> setParameterList(int position, Object[] values);

Source Link

Usage

From source file:com.model.database.queries.EventManager.java

License:Open Source License

public static List<GeoStopRouteMap> getroutesbyurban(String urbanId, List<String> selectedAgencies,
        int sessionindex) throws FactoryException, TransformException {
    session[sessionindex].beginTransaction();
    Query q = session[sessionindex].getNamedQuery("ROUTES_BY_AURBAN_SEL_AGENCIES");
    q.setParameter("id", urbanId);
    q.setParameterList("sa", selectedAgencies);
    @SuppressWarnings("unchecked")
    List<GeoStopRouteMap> result = q.list();
    Hutil.getSessionFactory()[sessionindex].close();
    return result;
}

From source file:com.model.database.queries.EventManager.java

License:Open Source License

public static int getroutescountbycongdist(String congdistId, List<String> selectedAgencies, int sessionindex)
        throws FactoryException, TransformException {
    session[sessionindex].beginTransaction();
    Query q = session[sessionindex].getNamedQuery("ROUTES_BY_CONGDIST_SEL_AGENCIES");
    q.setParameter("id", congdistId);
    q.setParameterList("sa", selectedAgencies);
    @SuppressWarnings("unchecked")
    List<GeoStopRouteMap> result = q.list();
    Hutil.getSessionFactory()[sessionindex].close();
    return result.size();
}

From source file:com.mysema.query.jpa.hibernate.HibernateUtil.java

License:Apache License

private static void setValue(Query query, String key, Object val) {
    if (val instanceof Collection<?>) {
        query.setParameterList(key, (Collection<?>) val);
    } else if (val.getClass().isArray()) {
        query.setParameterList(key, (Object[]) val);
    } else if (TYPES.containsKey(val.getClass())) {
        query.setParameter(key, val, TYPES.get(val.getClass()));
    } else {//  ww  w . ja v a 2s  .co m
        query.setParameter(key, val);
    }
}

From source file:com.nec.harvest.service.impl.ActualViewServiceImpl.java

License:Open Source License

/** {@inheritDoc}*/
@Override/*from w ww  .j av  a2s  . c o m*/
public List<VJiseki> findByOrgCodeAndMonthies(String orgCode, String... monthlies) throws ServiceException {
    if (StringUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Organization must not be null or empty");
    }
    if (ArrayUtils.isEmpty(monthlies)) {
        throw new IllegalArgumentException("The monthlies must not be null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<VJiseki> jisekis = new ArrayList<>();
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session,
                "SELECT  UriSkKG as uriSkKG, KtSkKG as ktSkKG, IdoSkKGU as idoSkKGU, IdoSkKGH as idoSkKGH, UriKrKG as uriKrKG,"
                        + " KtKrKG as ktKrKG, KnSrKG as knSrKG, KtSrKG as ktSrKG, KgcSrKG as kgcSrKG, IdoSrKGU as idoSrKGU, IdoSrKGH as idoSrKGH, KtJkKG as ktJkKG, JkJkKG as jkJkKG, KgcJkKG as kgcJkKG, IdoJkKGU as idoJkKGU, IdoJkKGH as idoJkKGH, HelpJkKGU as helpJkKGU, "
                        + " HelpJkKGH as helpJkKGH, KtKhKG as ktKhKG, KnKhKG as knKhKG, KgcKhKG as kgcKhKG, IdoKhKGU as idoKhKGU, IdoKhKGH as idoKhKGH, UriKhKG as uriKhKG FROM V_JISEKI WHERE StrCode = :strCode AND Getsudo in (:monthlies)");
        query.setParameter("strCode", orgCode);
        query.setParameterList("monthlies", monthlies);
        query.setResultTransformer(Transformers.aliasToBean(VJiseki.class));

        jisekis = repository.findByQuery(query);
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(jisekis)) {
            throw new ObjectNotFoundException("There is no actual view object");
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException(
                "An exception occured while getting VJiseki data for the organization " + orgCode, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return jisekis;
}

From source file:com.nec.harvest.service.impl.ActualViewServiceImpl.java

License:Open Source License

/** {@inheritDoc} */
@Override/* w  ww  .j ava  2  s .  c  om*/
public boolean checkAvailableByOrgCodeAndMonthly(String orgCode, String... monthlies) throws ServiceException {
    if (StringUtils.isEmpty(orgCode) || monthlies == null) {
        throw new IllegalArgumentException(
                "Can not check month available with organization code or monthly empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session,
                "SELECT count(StrCode) FROM v_jiseki WHERE StrCode = :orgCode AND GetSudo in (:monthlies)");
        query.setParameter("orgCode", orgCode);
        query.setParameterList("monthlies", monthlies);

        Object count = query.uniqueResult();
        if (count != null) {
            isAvailable = Long.parseLong(count.toString()) > 0;
        }
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while checking data for the organization " + orgCode,
                ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isAvailable;
}

From source file:com.nec.harvest.service.impl.ActualViewServiceImpl.java

License:Open Source License

/** {@inheritDoc} */
@Override//from   w  w  w.jav a 2s .c o  m
public List<VJiseki> findByOrgCodesAndMonth(String month, String... orgCodes) throws ServiceException {
    if (StringUtils.isEmpty(month)) {
        throw new IllegalArgumentException("Can not find actual view objects with month empty");
    }
    if (ArrayUtils.isEmpty(orgCodes)) {
        throw new IllegalArgumentException("Can not find actual view objects with organization codes null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<VJiseki> jisekis = new ArrayList<>();
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session,
                " SELECT StrCode as strCode, UriSkKG as uriSkKG, KtSkKG as ktSkKG, IdoSkKGU as idoSkKGU, IdoSkKGH as idoSkKGH, UriKrKG as uriKrKG, "
                        + " KtKrKG as ktKrKG, KnSrKG as knSrKG, KtSrKG as ktSrKG, "
                        + " KgcSrKG as kgcSrKG, IdoSrKGU as idoSrKGU, IdoSrKGH as idoSrKGH, "
                        + " KtJkKG as ktJkKG, JkJkKG as jkJkKG, KgcJkKG as kgcJkKG, "
                        + " IdoJkKGU as idoJkKGU, IdoJkKGH as idoJkKGH, HelpJkKGU as helpJkKGU, HelpJkKGH as helpJkKGH, "
                        + " KtKhKG as ktKhKG, KnKhKG as knKhKG, KgcKhKG as kgcKhKG, "
                        + " IdoKhKGU as idoKhKGU, IdoKhKGH as idoKhKGH, UriKhKG as uriKhKG "
                        + " FROM V_JISEKI WHERE StrCode IN (:orgCodes) AND Getsudo = :month");

        query.setParameterList("orgCodes", orgCodes);
        query.setString("month", month);
        query.setResultTransformer(Transformers.aliasToBean(VJiseki.class));

        // 
        jisekis = repository.findByQuery(query);
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(jisekis)) {
            throw new ObjectNotFoundException("No jisekis (actual view object) found");
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException(
                "An exception occur when get a list of actual view objects of multi organization and month",
                ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return jisekis;
}

From source file:com.nec.harvest.service.impl.ActualViewServiceImpl.java

License:Open Source License

/** {@inheritDoc}*/
@Override/*w  w  w.ja v  a  2s  .c  om*/
public List<VJiseki> findByPeriodMonthlyAndOrgCodes(String startMonth, String endMonth, List<String> orgCodes)
        throws ServiceException {
    if (CollectionUtils.isEmpty(orgCodes)) {
        throw new IllegalArgumentException("Organizations must not be null or empty");
    }
    if (StringUtils.isEmpty(startMonth)) {
        throw new IllegalArgumentException("Start month must not be null or empty");
    }
    if (StringUtils.isEmpty(endMonth)) {
        throw new IllegalArgumentException("End month must not be null or empty");
    }

    //
    logger.info(" Find soneki suii by organization's code " + StringUtils.join(orgCodes, ",") + " startMonth "
            + startMonth + " endMonth " + endMonth);
    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    List<VJiseki> jisekis = new ArrayList<>();
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder(
                " SELECT c.getSudo as getSudo, c.uriSkKG, c.ktSkKG, c.idoSkKGU, c.idoSkKGH, c.uriKrKG, ");
        sql.append(
                " c.ktKrKG, c.knSrKG, c.ktSrKG, c.kgcSrKG, c.idoSrKGU, c.idoSrKGH, c.jkJkKG, c.kgcJkKG, c.idoJkKGU, c.idoJkKGH, ");
        sql.append(
                " c.helpJkKGU, c.helpJkKGH, c.ktKhKG, c.knKhKG, c.kgcKhKG, c.idoKhKGU, c.idoKhKGH, c.uriKhKG, c.ktJkKG");
        sql.append(" FROM v_jiseki c WHERE c.strCode IN (:strCode) ");
        sql.append(" AND c.getSudo >= :startMonth AND c.getSudo <= :endMonth ");
        sql.append(" ORDER BY c.getSudo ");

        // get data in v_jiseki view  
        Query query = repository.getSQLQuery(session, sql.toString());
        query.setParameterList("strCode", orgCodes);
        query.setParameter("startMonth", startMonth);
        query.setParameter("endMonth", endMonth);
        query.setResultTransformer(Transformers.aliasToBean(VJiseki.class));

        jisekis = repository.findByQuery(query);
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(jisekis)) {
            throw new ObjectNotFoundException("There is no actual view object");
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while getting the data for organization "
                + StringUtils.join(orgCodes, ","), ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return jisekis;
}

From source file:com.nec.harvest.service.impl.ActualViewServiceImpl.java

License:Open Source License

/** {@inheritDoc} */
@Override//from  ww w  . jav  a2 s .  c  om
public boolean checkAvailableByOrgCodesAndMonthlies(List<String> orgCode, String... monthlies)
        throws ServiceException {
    if (CollectionUtils.isEmpty(orgCode)) {
        throw new IllegalArgumentException("Can not check month available with organization code empty");
    }
    if (ArrayUtils.isEmpty(monthlies)) {
        throw new IllegalArgumentException("Can not check month available with monthlies empty");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    boolean isAvailable = Boolean.FALSE;
    try {
        tx = session.beginTransaction();
        Query query = repository.getSQLQuery(session,
                "SELECT count(StrCode) FROM v_jiseki WHERE StrCode in (:orgCode) AND GetSudo in (:monthlies)");
        query.setParameterList("orgCode", orgCode);
        query.setParameterList("monthlies", monthlies);
        Object count = query.uniqueResult();
        if (count != null) {
            isAvailable = Long.parseLong(count.toString()) > 0;
        }
        tx.commit();
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException("An exception occured while checking data for the organization " + orgCode,
                ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return isAvailable;
}

From source file:com.nec.harvest.service.impl.BudgetPerformanceServiceImpl.java

License:Open Source License

/** {@inheritDoc} */
@Override/*from   w  w  w  .j  a  v a2s. c  o m*/
public Map<String, BudgetPerformanceBean> findByOrgCodesAndMonthAndKmkCodeJs(String month, String[] orgCodes,
        String... kmkCodeJs) throws ServiceException {
    if (StringUtils.isEmpty(month)) {
        throw new IllegalArgumentException("Can not find budget performance with given month is empty");
    }
    if (ArrayUtils.isEmpty(orgCodes)) {
        throw new IllegalArgumentException(
                "Can not find budget performance with given organization code array is null");
    }
    if (ArrayUtils.isEmpty(kmkCodeJs)) {
        throw new IllegalArgumentException(
                "Can not find budget performance with given kmkCodeJs array is null");
    }

    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    Map<String, BudgetPerformanceBean> mapBudgetPerformances = new HashMap<String, BudgetPerformanceBean>();
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder(
                "SELECT KmkCodeJ as kmkCodeJ, SUM(YosanKingaku) as yosanKingaku, SUM(JisekiKingaku) as jisekiKingaku ");
        sql.append(" FROM " + TblConstants.TBL_BUDGET_PERFORMANCE);
        sql.append(" WHERE StrCode IN (:orgCodes) ");
        sql.append(" AND GetSudo = :month ");
        sql.append(" AND KmkCodeJ IN (:kmkCodeJs) ");
        sql.append(" AND DelKbn = 2 ");
        sql.append(" GROUP BY Getsudo, KmkCodeJ");

        Query query = repository.getSQLQuery(session, sql.toString());
        query.setParameterList("orgCodes", orgCodes);
        query.setString("month", month);
        query.setParameterList("kmkCodeJs", kmkCodeJs);
        query.setResultTransformer(Transformers.aliasToBean(BudgetPerformanceBean.class));

        List<BudgetPerformanceBean> budgetPerformances = query.list();
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(budgetPerformances)) {
            throw new ObjectNotFoundException("No budget performance object found");
        }

        for (BudgetPerformanceBean budgetPerformance : budgetPerformances) {
            mapBudgetPerformances.put(budgetPerformance.getKmkCodeJ(), budgetPerformance);
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException(
                "Runtime exception occur when get a map of budget performance objects with given organization codes, kmkCodeJs and month",
                ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return mapBudgetPerformances;
}

From source file:com.nec.harvest.service.impl.BudgetPerformanceServiceImpl.java

License:Open Source License

/** {@inheritDoc} */
@Override/*from  www. j  a va  2  s.c  om*/
public Map<String, BudgetPerformanceBean> findByOrgCodesAndPeriodMonthAndKmkCodeJs(List<String> orgCodes,
        String startMonth, String endMonth, String... kmkCodeJs) throws ServiceException {
    if (CollectionUtils.isEmpty(orgCodes)) {
        throw new IllegalArgumentException(
                "Organization codes or current month in acton must not be null or empty");
    }
    if (StringUtils.isEmpty(startMonth)) {
        throw new IllegalArgumentException("Start month must not be null or empty");
    }
    if (StringUtils.isEmpty(endMonth)) {
        throw new IllegalArgumentException("End month month must not be null or empty");
    }
    if (ArrayUtils.isEmpty(kmkCodeJs)) {
        throw new IllegalArgumentException("KmkCodeJs must not be null");
    }

    logger.info(
            "Get budgetperformance by  organization codes:" + StringUtils.join(orgCodes, ",") + " startMonth "
                    + startMonth + " endMonth " + endMonth + "kmkcodej " + StringUtils.join(kmkCodeJs, ","));
    final Session session = HibernateSessionManager.getSession();
    Transaction tx = null;

    Map<String, BudgetPerformanceBean> mapBudgetPerformances = new HashMap<String, BudgetPerformanceBean>();
    try {
        tx = session.beginTransaction();
        StringBuilder sql = new StringBuilder(
                "SELECT Getsudo as getSudo, KmkCodej as kmkCodeJ, SUM(YosanKingaku) as yosanKingaku,");
        sql.append(" SUM(JisekiKingaku) as jisekiKingaku ");
        sql.append(" FROM " + TblConstants.TBL_BUDGET_PERFORMANCE);
        sql.append(" WHERE StrCode IN (:strCode) ");
        sql.append(" AND Getsudo >= :startMonth AND Getsudo <= :endMonth ");
        sql.append(" AND KmkCodej IN (:kmkCodeJs)");
        sql.append(" AND DelKbn = " + Constants.STATUS_ACTIVE);
        sql.append(" GROUP BY Getsudo, KmkCodej ");
        sql.append(" ORDER BY Getsudo");

        Query query = repository.getSQLQuery(session, sql.toString());
        query.setParameterList("strCode", orgCodes);
        query.setParameter("startMonth", startMonth);
        query.setParameter("endMonth", endMonth);
        query.setParameterList("kmkCodeJs", kmkCodeJs);
        query.setResultTransformer(Transformers.aliasToBean(BudgetPerformanceBean.class));

        List<BudgetPerformanceBean> budgetPerformances = query.list();
        // Release transaction
        tx.commit();
        if (CollectionUtils.isEmpty(budgetPerformances)) {
            throw new ObjectNotFoundException("There is no the budget performance object");
        }

        for (BudgetPerformanceBean budgetPerformance : budgetPerformances) {
            String key = budgetPerformance.getGetSudo() + budgetPerformance.getKmkCodeJ();
            mapBudgetPerformances.put(key, budgetPerformance);
        }
    } catch (SQLGrammarException | GenericJDBCException ex) {
        if (tx != null) {
            tx.rollback();
        }
        throw new ServiceException(
                "An exception occured while get budget performance data by organization code " + orgCodes, ex);
    } finally {
        HibernateSessionManager.closeSession(session);
    }
    return mapBudgetPerformances;
}