List of usage examples for org.hibernate Query getQueryString
String getQueryString();
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()); }