Example usage for org.hibernate Query getQueryString

List of usage examples for org.hibernate Query getQueryString

Introduction

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

Prototype

String getQueryString();

Source Link

Document

Get the query string.

Usage

From source file:org.genedb.querying.core.HqlQuery.java

License:Open Source License

@Override
public int getTotalResultsSize() {

    Session session = SessionFactoryUtils.doGetSession(sessionFactory, false);

    Map<String, String> map = new HashMap<String, String>();
    map.put("ORGANISM", getOrganismHql());
    map.put("SELECTOR", countSelector);
    map.put("ORDERBY", "");// we don't use order by here

    String hql = restrictQuery(getHql(), map);

    org.hibernate.Query query = session.createQuery(hql);
    populateQueryWithParams(query);/*  ww w  .  j ava2s. c om*/

    logger.debug(query.getQueryString());

    long longCount = (Long) query.uniqueResult();

    if (longCount < Integer.MIN_VALUE || longCount > Integer.MAX_VALUE) {
        throw new IllegalArgumentException(longCount + " cannot be cast to int without changing its value.");
    }

    int count = (int) longCount;

    logger.info(String.format("%d == %d", longCount, count));

    return count;

}

From source file:org.jdal.dao.hibernate.HibernateDao.java

License:Apache License

/**
 * Get Hibernate named Query and configure with filter from page. 
 * Set the result count on page also. /*  www . ja  v  a  2s.co  m*/
 * @param page page 
 * @return Hibernate named Query.
 */
private Query getQuery(Page<?> page) {
    Object filter = page.getFilter();

    try {
        if (filter instanceof Filter) {
            Filter f = (Filter) filter;
            Query query = getSession().getNamedQuery(f.getFilterName());
            Query countQuery = getSession().createQuery(query.getQueryString().replaceFirst("select", "count"));
            query.setProperties(f.getParameterMap());
            query.setMaxResults(page.getPageSize());
            query.setFirstResult(page.getStartIndex());
            page.setCount((Integer) countQuery.uniqueResult());

            return query;
        }
    } catch (HibernateException e) {
    }
    return null;
}

From source file:org.mifos.framework.persistence.LegacyGenericDao.java

License:Open Source License

public int executeNamedQueryForUpdate(final String queryName, final Map<String, ?> queryParameters)
        throws PersistenceException {
    try {/*w ww . j  a v a2  s . co m*/
        StaticHibernateUtil.startTransaction();
        Query query = createdNamedQuery(queryName);
        System.out.println(query.getQueryString());
        query.setProperties(queryParameters);
        int result = query.executeUpdate();
        StaticHibernateUtil.commitTransaction();
        return result;

    } catch (Exception e) {
        throw new PersistenceException(e);
    }
}

From source file:org.n52.sos.ds.hibernate.util.HibernateHelper.java

License:Open Source License

/**
 * Get the SQL query string from HQL Query.
 *
 * @param query//w ww  .j  a  va2 s.c o m
 *            HQL query to convert to SQL
 * @return SQL query string from HQL
 */
public static String getSqlString(Query query, Session session) {
    final QueryTranslatorFactory ast = new ASTQueryTranslatorFactory();
    SessionFactory sessionFactory = session.getSessionFactory();
    final QueryTranslatorImpl qt = (QueryTranslatorImpl) ast.createQueryTranslator("id", query.getQueryString(),
            Maps.newHashMap(), (SessionFactoryImplementor) sessionFactory, null);
    qt.compile(null, false);
    return qt.getSQLString();
}

From source file:org.openbravo.erpCommon.ad_process.UpdateActuals.java

License:Open Source License

@Override
protected void doExecute(ProcessBundle bundle) throws Exception {
    final OBError msg = new OBError();

    try {// w ww.j a  v  a 2  s  .  co  m
        // retrieve the parameters from the bundle
        final String cBudgetId = (String) bundle.getParams().get("C_Budget_ID");

        String activity = null;
        String accountingSchema = null;
        String asset = null;
        String businessPartner = null;
        String businessPartnerCategory = null;
        String costcenter = null;
        String account = null;
        String accountSign = null;
        String period = null;
        String product = null;
        String productCategory = null;
        String project = null;
        String salesCampaign = null;
        String salesRegion = null;
        String user1 = null;
        String user2 = null;

        // Gets the budget lines
        Budget myBudget = OBDal.getInstance().get(Budget.class, cBudgetId);
        for (BudgetLine budgetLine : myBudget.getFinancialMgmtBudgetLineList()) {
            activity = (budgetLine.getActivity() != null) ? budgetLine.getActivity().getId() : "";
            accountingSchema = (budgetLine.getAccountingSchema() != null)
                    ? budgetLine.getAccountingSchema().getId()
                    : "";
            asset = (budgetLine.getAsset() != null) ? budgetLine.getAsset().getId() : "";
            businessPartner = (budgetLine.getBusinessPartner() != null)
                    ? budgetLine.getBusinessPartner().getId()
                    : "";
            businessPartnerCategory = (budgetLine.getBusinessPartnerCategory() != null)
                    ? budgetLine.getBusinessPartnerCategory().getId()
                    : "";
            costcenter = (budgetLine.getCostcenter() != null) ? budgetLine.getCostcenter().getId() : "";
            account = (budgetLine.getAccountElement() != null) ? budgetLine.getAccountElement().getId() : "";
            accountSign = (budgetLine.getAccountElement() != null)
                    ? budgetLine.getAccountElement().getAccountSign()
                    : "";
            period = (budgetLine.getPeriod() != null) ? budgetLine.getPeriod().getId() : "";
            product = (budgetLine.getProduct() != null) ? budgetLine.getProduct().getId() : "";
            productCategory = (budgetLine.getProductCategory() != null)
                    ? budgetLine.getProductCategory().getId()
                    : "";
            project = (budgetLine.getProject() != null) ? budgetLine.getProject().getId() : "";
            salesCampaign = (budgetLine.getSalesCampaign() != null) ? budgetLine.getSalesCampaign().getId()
                    : "";
            salesRegion = (budgetLine.getSalesRegion() != null) ? budgetLine.getSalesRegion().getId() : "";
            user1 = (budgetLine.getStDimension() != null) ? budgetLine.getStDimension().getId() : "";
            user2 = (budgetLine.getNdDimension() != null) ? budgetLine.getNdDimension().getId() : "";

            // get the natural tree
            TreeUtility treeUtility = new TreeUtility();
            String activityTree = activity != null
                    ? Utility.arrayListToString(
                            (new ArrayList<String>(treeUtility.getChildTree(activity, "AY", true))), true)
                    : activity;
            String productCategoryTree = productCategory != null ? Utility.arrayListToString(
                    (new ArrayList<String>(treeUtility.getChildTree(productCategory, "PC", true))), true)
                    : productCategory;
            String assetTree = asset != null ? Utility.arrayListToString(
                    (new ArrayList<String>(treeUtility.getChildTree(asset, "AS", true))), true) : asset;
            String costcenterTree = costcenter != null
                    ? Utility.arrayListToString(
                            (new ArrayList<String>(treeUtility.getChildTree(costcenter, "CC", true))), true)
                    : costcenter;
            String accountTree = account != null
                    ? Utility.arrayListToString(
                            (new ArrayList<String>(treeUtility.getChildTree(account, "EV", true))), true)
                    : account;
            String projectTree = project != null
                    ? Utility.arrayListToString(
                            new ArrayList<String>(treeUtility.getChildTree(project, "PJ", true)), true)
                    : project;
            String campaignTree = salesCampaign != null
                    ? Utility.arrayListToString(
                            new ArrayList<String>(treeUtility.getChildTree(salesCampaign, "MC", true)), true)
                    : salesCampaign;
            String regionTree = salesRegion != null
                    ? Utility.arrayListToString(
                            new ArrayList<String>(treeUtility.getChildTree(salesRegion, "SR", true)), true)
                    : salesRegion;
            String user1Tree = user1 != null ? Utility.arrayListToString(
                    (new ArrayList<String>(treeUtility.getChildTree(user1, "U1", true))), true) : user1;
            String user2Tree = user2 != null ? Utility.arrayListToString(
                    (new ArrayList<String>(treeUtility.getChildTree(user2, "U2", true))), true) : user2;

            final String orgId = myBudget.getOrganization().getId();
            ArrayList<String> organizationList = new ArrayList<String>(
                    OBContext.getOBContext().getOrganizationStructureProvider().getChildTree(orgId, true));
            String OrgTreeList = Utility.arrayListToString(organizationList, true);

            StringBuilder queryString = new StringBuilder();
            queryString.append("select SUM(e.credit) as credit,");
            queryString.append(" SUM(e.debit) as debit");
            queryString.append(" from FinancialMgmtAccountingFact e where");
            queryString.append(" e.client.id='").append(myBudget.getClient().getId()).append("'");
            queryString.append(" and e.organization.id in (").append(OrgTreeList).append(")");

            if (!"".equals(activity)) {
                queryString.append(" and e.activity.id in (").append(activityTree).append(")");
            }
            queryString.append(" and e.accountingSchema.id=:accountingSchema");
            if (!"".equals(asset)) {
                queryString.append(" and e.asset.id in (").append(assetTree).append(")");
            }
            if (!"".equals(businessPartner)) {
                queryString.append(" and e.businessPartner.id = :businessPartner");
            } else if (!"".equals(businessPartnerCategory)) {
                queryString.append(
                        " and exists (select 1 from BusinessPartner bp where businessPartnerCategory.id=:businessPartnerCategory)");
            }
            if (!"".equals(costcenter)) {
                queryString.append(" and e.costcenter.id in (").append(costcenterTree).append(")");
            }
            queryString.append(" and e.account.id in (").append(accountTree).append(")");
            if (!"".equals(period)) {
                queryString.append(" and e.period.id=:period");
            }
            if (!"".equals(product)) {
                queryString.append(" and e.product.id=:product");
            } else if (!"".equals(productCategory)) {
                queryString.append(" and exists (select 1 from Product p where productCategory.id in (")
                        .append(productCategoryTree).append("))");
            }
            if (!"".equals(project)) {
                queryString.append(" and e.project.id in (").append(projectTree).append(")");
            }
            if (!"".equals(salesCampaign)) {
                queryString.append(" and e.salesCampaign.id in (").append(campaignTree).append(")");
            }
            if (!"".equals(salesRegion)) {
                queryString.append(" and e.salesRegion.id in (").append(regionTree).append(")");
            }
            if (!"".equals(user1)) {
                queryString.append(" and e.stDimension.id in (").append(user1Tree).append(")");
            }
            if (!"".equals(user1)) {
                queryString.append(" and e.ndDimension.id in (").append(user2Tree).append(")");
            }
            Query query = OBDal.getInstance().getSession().createQuery(queryString.toString());
            query.setReadOnly(true);
            query.setString("accountingSchema", accountingSchema);
            if (!"".equals(businessPartner))
                query.setString("businessPartner", businessPartner);
            else if (!"".equals(businessPartnerCategory))
                query.setString("businessPartnerCategory", businessPartnerCategory);
            if (!"".equals(period))
                query.setString("period", period);
            if (!"".equals(product))
                query.setString("product", product);

            log4j.debug("Query String" + query.getQueryString());

            BigDecimal credit = BigDecimal.ZERO;
            BigDecimal debit = BigDecimal.ZERO;
            for (Object obj : query.list()) {
                if (obj != null) {
                    Object[] row = (Object[]) obj;
                    credit = (BigDecimal) ((row[0] != null) ? row[0] : new BigDecimal(0));
                    debit = (BigDecimal) ((row[1] != null) ? row[1] : new BigDecimal(0));
                }
            }

            if (("D").equals(accountSign)) {
                budgetLine.setActualAmount(debit.subtract(credit));
            } else {
                budgetLine.setActualAmount(credit.subtract(debit));
            }

            msg.setType("Success");
            msg.setTitle("Success");
            msg.setMessage("Actual Amount = " + budgetLine.getActualAmount());
            bundle.setResult(msg);
        }

    } catch (Exception e) {
        msg.setType("Error");
        msg.setTitle("Error");
        msg.setMessage(e.toString());
        bundle.setResult(msg);
        OBDal.getInstance().rollbackAndClose();
    } finally {
        OBDal.getInstance().commitAndClose();
    }
}

From source file:org.openbravo.service.datasource.HQLDataSourceService.java

License:Open Source License

@Override
protected int getCount(Map<String, String> parameters) {
    Table table = getTableFromParameters(parameters);
    boolean justCount = true;
    Query countQuery = getQuery(table, parameters, justCount);
    String hqlQuery = countQuery.getQueryString();
    int nRows = -1;
    if (hqlQuery.toUpperCase().contains(GROUPBY)) {
        justCount = false;//ww w.  j a  v a2  s.c  o  m
        countQuery = getQuery(table, parameters, justCount);
        return getGroupedCount(countQuery);
    } else {
        nRows = ((Number) countQuery.uniqueResult()).intValue();
    }
    return nRows;
}

From source file:org.openhie.openempi.dao.hibernate.ReportDaoHibernate.java

License:Open Source License

public void reassignReportRequestsToReport(final Integer oldReportId, final Integer newReportId) {
    log.debug("Re-assigning report requests from report " + oldReportId + " to " + newReportId);
    Integer recordsUpdated = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            String updateStmt = "update ReportRequestEntry rre SET rre.report.reportId = :newReportId WHERE rre.report.reportId = :oldReportId";
            Query query = session.createQuery(updateStmt).setInteger("oldReportId", oldReportId)
                    .setInteger("newReportId", newReportId);
            log.debug("Doing report re-assignment using statement: " + query.getQueryString());
            int recordsUpdated = query.executeUpdate();
            return recordsUpdated;
        }//from  w w  w.jav  a  2  s  .  c o m
    });
    log.debug("Completed the reassignment on " + recordsUpdated + " entries.");
}

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * select orders.patient_id from drug, drug_order, orders where
 * drug_order.order_id = orders.order_id and drug.drug_id =
 * drug_order.drug_inventory_id and drug.drug_id IN (:drugIds) and
 * (discontinued_date >= '2006-01-01' and discontinued_date <= '2006-01-31')
 * //from  w ww  .  ja  v a 2s  .  c  o m
 * @param drugs
 *            the list of drugs to match against patient
 * @param whichColumn
 *            the start_date or discontinued_date
 * @param changedOnOrAfter
 *            the patient started or stopped the given drug(s) after this
 *            date
 * @param changedOnOrBefore
 *            the patient started or stopped the given drug(s) before this
 *            date
 * @return a cohort of patients that started or stopped the given list of
 *         drugs between the given dates
 */
private Cohort getPatientsHavingStartedOrCompletedDrugOrders(List<Drug> drugs, String whichColumn,
        Date changedOnOrAfter, Date changedOnOrBefore) {

    List<Integer> drugIds = new ArrayList<Integer>();
    for (Drug drug : drugs)
        drugIds.add(drug.getDrugId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select orders.patient_id ");
    sql.append("from drug, drug_order, orders ");
    sql.append("where orders.order_id = drug_order.order_id ");
    sql.append("and drug.drug_id = drug_order.drug_inventory_id ");
    if (drugIds != null && !drugIds.isEmpty()) {
        sql.append("and drug_order.drug_inventory_id in (:drugIds) ");
    }
    sql.append("and orders." + whichColumn + " is not null ");
    if (changedOnOrAfter != null) {
        sql.append("and orders." + whichColumn + " >= :changedOnOrAfter ");
    }
    if (changedOnOrBefore != null) {
        sql.append("and orders." + whichColumn + " <= :changedOnOrBefore ");
    }
    sql.append("and drug.retired = false ");
    sql.append("and orders.voided = false ");
    sql.append("group by orders.patient_id ");
    log.warn("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    log.debug("Patients having started or stopped drug orders between dates:\n " + query.getQueryString());

    if (drugIds != null && !drugIds.isEmpty())
        query.setParameterList("drugIds", drugIds);
    if (changedOnOrAfter != null)
        query.setDate("changedOnOrAfter", changedOnOrAfter);
    if (changedOnOrBefore != null)
        query.setDate("changedOnOrBefore", changedOnOrBefore);
    return new Cohort(query.list());
}

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * // w w  w  .  ja va 2  s . c  o  m
 * @param drugs
 *            the list of drugs to match against patient
 * @param asOfDate
 *            the date up to which the patient should be on the given drugs
 * @return a cohort of patients that started or stopped the given list of
 *         drugs between the given dates
 */
public Cohort getPatientsHavingActiveDrugOrders(List<Drug> drugs, Date asOfDate) {
    List<Integer> drugIds = new ArrayList<Integer>();
    for (Drug drug : drugs)
        drugIds.add(drug.getDrugId());

    // Create SQL query
    StringBuilder sql = new StringBuilder();
    sql.append("select orders.patient_id ");
    sql.append("from drug, drug_order, orders ");
    sql.append("where orders.order_id = drug_order.order_id ");
    sql.append("and drug.drug_id = drug_order.drug_inventory_id ");
    if (drugIds != null && !drugIds.isEmpty()) {
        sql.append("and drug_order.drug_inventory_id in (:drugIds) ");
    }
    sql.append("and orders.start_date is not null ");
    if (asOfDate != null) {
        sql.append("and orders.start_date <= :asOfDate ");
        sql.append("and (orders.auto_expire_date is null or orders.auto_expire_date > :asOfDate) ");
        sql.append("and (orders.discontinued_date is null or orders.discontinued_date > :asOfDate) ");
    }
    sql.append("and drug.retired = false ");
    sql.append("and orders.voided = false ");
    sql.append("group by orders.patient_id ");
    log.warn("query: " + sql);

    // Execute query
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    log.debug("Patients having active drug orders between dates:\n " + query.getQueryString());

    if (drugIds != null && !drugIds.isEmpty())
        query.setParameterList("drugIds", drugIds);
    if (asOfDate != null)
        query.setDate("asOfDate", asOfDate);
    return new Cohort(query.list());
}

From source file:org.openmrs.module.reporting.cohort.query.db.hibernate.HibernateCohortQueryDAO.java

License:Open Source License

/**
 * TODO: Fails to leave out patients who are voided.  
 * // www  .  j  a va2  s .  co m
 * Returns the set of patients that are in a given program, 
 * workflow, and state, within a given date range
 * 
 * @param program
 *            The program the patient must have been in
 * @param stateList
 *            List of states the patient must have been in (implies a
 *            workflow) (can be null)
 * @param fromDate
 *            If not null, then only patients in the given
 *            program/workflow/state on or after this date
 * @param toDate
 *            If not null, then only patients in the given
 *            program/workflow/state on or before this date
 * @return Cohort of Patients matching criteria
 */
public Cohort getPatientsByProgramAndState(Program program, List<ProgramWorkflowState> stateList, Date fromDate,
        Date toDate) {
    Integer programId = program == null ? null : program.getProgramId();
    List<Integer> stateIds = null;
    if (stateList != null && stateList.size() > 0) {
        stateIds = new ArrayList<Integer>();
        for (ProgramWorkflowState state : stateList)
            stateIds.add(state.getProgramWorkflowStateId());
    }

    List<String> clauses = new ArrayList<String>();
    clauses.add("pp.voided = false");
    if (programId != null)
        clauses.add("pp.program_id = :programId");
    if (stateIds != null) {
        clauses.add("ps.state in (:stateIds)");
        clauses.add("ps.voided = false");
    }
    if (fromDate != null) {
        clauses.add("(pp.date_completed is null or pp.date_completed >= :fromDate)");
        if (stateIds != null)
            clauses.add("(ps.end_date is null or ps.end_date >= :fromDate)");
    }
    if (toDate != null) {
        clauses.add("(pp.date_enrolled is null or pp.date_enrolled <= :toDate)");
        if (stateIds != null)
            clauses.add("(ps.start_date is null or ps.start_date <= :toDate)");
    }

    StringBuilder sql = new StringBuilder();
    sql.append("select pp.patient_id ");
    sql.append("from patient_program pp ");
    if (stateIds != null)
        sql.append("inner join patient_state ps on pp.patient_program_id = ps.patient_program_id ");
    for (ListIterator<String> i = clauses.listIterator(); i.hasNext();) {
        sql.append(i.nextIndex() == 0 ? " where " : " and ");
        sql.append(i.next());
    }
    sql.append(" group by pp.patient_id");
    log.debug("query: " + sql);

    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());

    log.debug("Patients having programs and states between dates: " + query.getQueryString());

    if (programId != null)
        query.setInteger("programId", programId);
    if (stateIds != null)
        query.setParameterList("stateIds", stateIds);
    if (fromDate != null)
        query.setDate("fromDate", fromDate);
    if (toDate != null)
        query.setDate("toDate", toDate);

    return new Cohort(query.list());
}