List of usage examples for org.hibernate Query getQueryString
String getQueryString();
From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java
License:Educational Community License
public List<Stat> getVisitsTotalsStats(final String siteId, final Date iDate, final Date fDate, final PagingPosition page, final List<String> totalsBy, final String sortBy, final boolean sortAscending, final int maxResults) { StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_VISITSTOTALS, totalsBy, siteId, (Set<String>) null, null, showAnonymousAccessEvents, null, null, iDate, fDate, null, false, sortBy, sortAscending);//www .j a va 2 s . co m final String hql = sqlBuilder.getHQL(); final Map<Integer, Integer> columnMap = sqlBuilder.getHQLColumnMap(); // DO IT! HibernateCallback hcb = new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Query q = session.createQuery(hql); if (siteId != null) { q.setString("siteid", siteId); } if (iDate != null) q.setDate("idate", iDate); if (fDate != null) { // adjust final date Calendar c = Calendar.getInstance(); c.setTime(fDate); c.add(Calendar.DAY_OF_YEAR, 1); Date fDate2 = c.getTime(); q.setDate("fdate", fDate2); } if (page != null) { q.setFirstResult(page.getFirst() - 1); q.setMaxResults(page.getLast() - page.getFirst() + 1); } if (maxResults > 0) { q.setMaxResults(maxResults); } LOG.debug("getVisitsTotalsStats(): " + q.getQueryString()); List<Object[]> records = q.list(); List<SiteVisits> results = new ArrayList<SiteVisits>(); if (records.size() > 0) { Calendar cal = Calendar.getInstance(); for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) { Object[] s = iter.next(); SiteVisits c = new SiteVisitsImpl(); if (columnMap.containsKey(StatsSqlBuilder.C_SITE)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_SITE); c.setSiteId((String) s[ix]); } if (columnMap.containsKey(StatsSqlBuilder.C_DATE)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATE); c.setDate((Date) s[ix]); } if (columnMap.containsKey(StatsSqlBuilder.C_DATEMONTH) && columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) { int ixY = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR); int ixM = (Integer) columnMap.get(StatsSqlBuilder.C_DATEMONTH); int yr = 0, mo = 0; if (getDbVendor().equals("oracle")) { yr = Integer.parseInt((String) s[ixY]); mo = Integer.parseInt((String) s[ixM]) - 1; } else { yr = ((Integer) s[ixY]).intValue(); mo = ((Integer) s[ixM]).intValue() - 1; } cal.set(Calendar.YEAR, yr); cal.set(Calendar.MONTH, mo); c.setDate(cal.getTime()); } else if (columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR); int yr = 0; if (getDbVendor().equals("oracle")) { yr = Integer.parseInt((String) s[ix]); } else { yr = ((Integer) s[ix]).intValue(); } cal.set(Calendar.YEAR, yr); c.setDate(cal.getTime()); } if (columnMap.containsKey(StatsSqlBuilder.C_VISITS)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_VISITS); try { c.setTotalVisits(((Long) s[ix]).longValue()); } catch (ClassCastException cce) { c.setTotalVisits(((Integer) s[ix]).intValue()); } } if (columnMap.containsKey(StatsSqlBuilder.C_UNIQUEVISITS)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_UNIQUEVISITS); try { c.setTotalUnique(((Long) s[ix]).longValue()); } catch (ClassCastException cce) { c.setTotalUnique(((Integer) s[ix]).intValue()); } } results.add(c); } } return results; } }; return (List<Stat>) getHibernateTemplate().execute(hcb); }
From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java
License:Educational Community License
public List<Stat> getActivityTotalsStats(final String siteId, final List<String> events, final Date iDate, final Date fDate, final PagingPosition page, final List<String> totalsBy, final String sortBy, final boolean sortAscending, final int maxResults) { final Set<String> anonymousEvents = M_ers.getAnonymousEventIds(); StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_ACTIVITYTOTALS, totalsBy, siteId, events, anonymousEvents, showAnonymousAccessEvents, null, null, iDate, fDate, null, false, sortBy, sortAscending);/* ww w . j a v a2 s . c o m*/ final String hql = sqlBuilder.getHQL(); final Map<Integer, Integer> columnMap = sqlBuilder.getHQLColumnMap(); // DO IT! HibernateCallback hcb = new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Query q = session.createQuery(hql); if (siteId != null) { q.setString("siteid", siteId); } if (events != null) { if (events.isEmpty()) { events.add(""); } q.setParameterList("events", events); } if (iDate != null) q.setDate("idate", iDate); if (fDate != null) { // adjust final date Calendar c = Calendar.getInstance(); c.setTime(fDate); c.add(Calendar.DAY_OF_YEAR, 1); Date fDate2 = c.getTime(); q.setDate("fdate", fDate2); } if (columnMap.containsKey(StatsSqlBuilder.C_USER) && anonymousEvents != null && anonymousEvents.size() > 0) { q.setParameterList("anonymousEvents", anonymousEvents); } if (page != null) { q.setFirstResult(page.getFirst() - 1); q.setMaxResults(page.getLast() - page.getFirst() + 1); } if (maxResults > 0) { q.setMaxResults(maxResults); } LOG.debug("getActivityTotalsStats(): " + q.getQueryString()); List<Object[]> records = q.list(); List<EventStat> results = new ArrayList<EventStat>(); if (records.size() > 0) { Calendar cal = Calendar.getInstance(); Map<String, ToolInfo> eventIdToolMap = M_ers.getEventIdToolMap(); Map<String, Integer> toolIdEventStatIxMap = new HashMap<String, Integer>(); boolean groupByTool = columnMap.containsKey(StatsSqlBuilder.C_TOOL) && !columnMap.containsKey(StatsSqlBuilder.C_EVENT); for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) { Object[] s = iter.next(); EventStat c = null; int eventStatListIndex = -1; String toolId = null; if (!groupByTool) { c = new EventStatImpl(); } else { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_TOOL); ToolInfo ti = eventIdToolMap.get((String) s[ix]); toolId = ti != null ? ti.getToolId() : (String) s[ix]; Integer esIx = toolIdEventStatIxMap.get(toolId); if (esIx == null) { c = new EventStatImpl(); } else { eventStatListIndex = esIx.intValue(); c = results.get(eventStatListIndex); } } if (columnMap.containsKey(StatsSqlBuilder.C_SITE)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_SITE); c.setSiteId((String) s[ix]); } if (columnMap.containsKey(StatsSqlBuilder.C_EVENT)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_EVENT); c.setEventId((String) s[ix]); ToolInfo ti = eventIdToolMap.get((String) s[ix]); toolId = ti != null ? ti.getToolId() : (String) s[ix]; c.setToolId(toolId); } if (columnMap.containsKey(StatsSqlBuilder.C_TOOL)) { c.setToolId(toolId); } if (columnMap.containsKey(StatsSqlBuilder.C_DATE)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATE); c.setDate((Date) s[ix]); } if (columnMap.containsKey(StatsSqlBuilder.C_DATEMONTH) && columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) { int ixY = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR); int ixM = (Integer) columnMap.get(StatsSqlBuilder.C_DATEMONTH); int yr = 0, mo = 0; if (getDbVendor().equals("oracle")) { yr = Integer.parseInt((String) s[ixY]); mo = Integer.parseInt((String) s[ixM]) - 1; } else { yr = ((Integer) s[ixY]).intValue(); mo = ((Integer) s[ixM]).intValue() - 1; } cal.set(Calendar.YEAR, yr); cal.set(Calendar.MONTH, mo); c.setDate(cal.getTime()); } else if (columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR); int yr = 0; if (getDbVendor().equals("oracle")) { yr = Integer.parseInt((String) s[ix]); } else { yr = ((Integer) s[ix]).intValue(); } cal.set(Calendar.YEAR, yr); c.setDate(cal.getTime()); } if (columnMap.containsKey(StatsSqlBuilder.C_TOTAL)) { int ix = (Integer) columnMap.get(StatsSqlBuilder.C_TOTAL); c.setCount(c.getCount() + ((Long) s[ix]).longValue()); } if (eventStatListIndex == -1) { results.add(c); toolIdEventStatIxMap.put(toolId, results.size() - 1); } else { results.set(eventStatListIndex, c); } } } return results; } }; return (List<Stat>) getHibernateTemplate().execute(hcb); }
From source file:org.sakaiproject.tool.assessment.facade.QuestionPoolFacadeQueries.java
License:Educational Community License
public List getAllItemFacadesOrderByItemType(final Long questionPoolId, final String orderBy, final String ascending) { log.debug("QuestionPoolFacadeQueries: getAllItemFacadesOrderByItemType:: orderBy=" + orderBy); final HibernateCallback hcb = new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException, SQLException { Query q; if ("false".equals(ascending)) { q = session.createQuery( "select ab from ItemData ab, QuestionPoolItemData qpi, TypeD t where ab.itemId=qpi.itemId and ab.typeId=t.typeId and qpi.questionPoolId = ? order by t." + orderBy + " desc"); } else { q = session.createQuery( "select ab from ItemData ab, QuestionPoolItemData qpi, TypeD t where ab.itemId=qpi.itemId and ab.typeId=t.typeId and qpi.questionPoolId = ? order by t." + orderBy); }// ww w . ja v a 2 s. com q.setLong(0, questionPoolId.longValue()); log.debug("QuestionPoolFacadeQueries: getAllItemFacadesOrderByItemType:: getQueryString() = " + q.getQueryString()); return q.list(); }; }; List list = getHibernateTemplate().executeFind(hcb); // List list = getHibernateTemplate().find("select ab from ItemData ab, QuestionPoolItemData qpi, TypeD t where ab.itemId=qpi.itemId and ab.typeId=t.typeId and qpi.questionPoolId = ? order by t." + // orderBy, // new Object[] {questionPoolId} // , // new org.hibernate.type.Type[] {Hibernate. // LONG}); log.debug("QuestionPoolFacadeQueries: getAllItemFacadesOrderByItemType:: size = " + list.size()); ArrayList itemList = new ArrayList(); for (int i = 0; i < list.size(); i++) { ItemData itemdata = (ItemData) list.get(i); ItemFacade f = new ItemFacade(itemdata); itemList.add(f); } return itemList; }
From source file:org.squashtest.tm.service.internal.repository.hibernate.RequirementVersionCoverageDaoImpl.java
License:Open Source License
@SuppressWarnings("unchecked") @Override//from ww w. ja v a2 s. com public List<RequirementVersionCoverage> findAllByTestCaseId(long testCaseId, PagingAndSorting pas) { // we have to fetch our query and modify the hql a bit, hence the weird operation below Query namedquery = currentSession().getNamedQuery("RequirementVersionCoverage.findAllByTestCaseId"); String hql = namedquery.getQueryString(); hql = SortingUtils.addOrder(hql, pas); Query q = currentSession().createQuery(hql); if (!pas.shouldDisplayAll()) { PagingUtils.addPaging(q, pas); } q.setParameter("testCaseId", testCaseId); List<Object[]> raw = q.list(); // now we have to collect from the result set the only thing // we want : the coverages List<RequirementVersionCoverage> res = new ArrayList<>(raw.size()); for (Object[] tuple : raw) { res.add((RequirementVersionCoverage) tuple[0]); } return res; }
From source file:org.squashtest.tm.service.internal.repository.hibernate.RequirementVersionCoverageDaoImpl.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*from w w w . j a v a 2 s.c o m*/ public List<RequirementVersion> findDistinctRequirementVersionsByTestCases(Collection<Long> testCaseIds, PagingAndSorting pagingAndSorting) { if (testCaseIds.isEmpty()) { return Collections.emptyList(); } // we have to fetch our query and modify the hql a bit, hence the weird operation below Query namedquery = currentSession() .getNamedQuery("RequirementVersion.findDistinctRequirementVersionsByTestCases"); String hql = namedquery.getQueryString(); hql = SortingUtils.addOrder(hql, pagingAndSorting); Query q = currentSession().createQuery(hql); if (!pagingAndSorting.shouldDisplayAll()) { PagingUtils.addPaging(q, pagingAndSorting); } q.setParameterList("testCaseIds", testCaseIds, LongType.INSTANCE); List<Object[]> raw = q.list(); // now we have to collect from the result set the only thing // we want : the RequirementVersions List<RequirementVersion> res = new ArrayList<>(raw.size()); for (Object[] tuple : raw) { res.add((RequirementVersion) tuple[0]); } if ("endDate".equals(pagingAndSorting.getSortedAttribute())) { Collections.sort(res, new Comparator<RequirementVersion>() { @Override public int compare(RequirementVersion req1, RequirementVersion req2) { return compareReqMilestoneDate(req1, req2); } }); if (pagingAndSorting.getSortOrder() == SortOrder.ASCENDING) { Collections.reverse(res); } } return res; }
From source file:org.squashtest.tm.service.internal.repository.hibernate.TestCaseDaoImpl.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/* w w w .jav a2s . c o m*/ public List<TestCase> findAllByVerifiedRequirementVersion(long verifiedId, PagingAndSorting sorting) { // create the sorting, see comments above List<Sorting> effectiveSortings = createEffectiveSorting(sorting); // we have to fetch our query and modify the hql a bit, hence the weird operation below Query namedquery = currentSession().getNamedQuery("testCase.findVerifyingTestCases"); String hql = namedquery.getQueryString(); hql = SortingUtils.addOrders(hql, effectiveSortings); Query q = currentSession().createQuery(hql); if (!sorting.shouldDisplayAll()) { PagingUtils.addPaging(q, sorting); } q.setParameter("versionId", verifiedId); List<Object[]> raw = q.list(); // now we have to collect from the result set the only thing // we want : the test cases List<TestCase> res = new ArrayList<>(raw.size()); for (Object[] tuple : raw) { res.add((TestCase) tuple[0]); } if ("endDate".equals(sorting.getSortedAttribute())) { Collections.sort(res, new Comparator<TestCase>() { @Override public int compare(TestCase tc1, TestCase tc2) { return compareTcMilestoneDate(tc1, tc2); } }); if (sorting.getSortOrder() == SortOrder.ASCENDING) { Collections.reverse(res); } } return res; }
From source file:org.tynamo.hibernate.services.HibernatePersistenceServiceImpl.java
License:Apache License
/** * Execute an HQL query./*ww w. jav a 2 s . c o m*/ * * @param queryString a query expressed in Hibernate's query language * @param startIndex the index of the first item to be retrieved * @param maxResults the number of items to be retrieved, if <code>0</code> it retrieves ALL the items * @param parameters the (optional) parameters for the query. * @return a List of entities containing the results of the query execution */ public List findByQuery(String queryString, int startIndex, int maxResults, QueryParameter... parameters) { Query query = getSession().createQuery(queryString); for (QueryParameter parameter : parameters) { parameter.applyNamedParameterToQuery(query); } if (maxResults > 0) query.setMaxResults(maxResults); if (startIndex > 0) query.setFirstResult(startIndex); if (logger.isDebugEnabled()) logger.debug(query.getQueryString()); return query.list(); }
From source file:org.unitedinternet.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
/** * Tests uid query./*from w w w.j a v a 2 s .com*/ * @throws Exception - if something is wrong this exception is thrown. */ @Test public void testUidQuery() throws Exception { ItemFilter filter = new ItemFilter(); filter.setUid(Restrictions.eq("abc")); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.uid=:param0", query.getQueryString()); }
From source file:org.unitedinternet.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
@Test public void testModifiedSinceQuery() { NoteItemFilter filter = new NoteItemFilter(); Calendar c = Calendar.getInstance(); Date end = c.getTime();/*from www .ja va2 s . c om*/ c.add(Calendar.YEAR, -1); filter.setModifiedSince(Restrictions.between(c.getTime(), end)); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibNoteItem i where i.modifiedDate between :param0 and :param1", query.getQueryString()); }
From source file:org.unitedinternet.cosmo.dao.hibernate.query.StandardItemFilterProcessorTest.java
License:Apache License
/** * Tests display name query./*from w w w .jav a2s .c o m*/ * @throws Exception - if something is wrong this exception is thrown. */ @Test public void testDisplayNameQuery() throws Exception { ItemFilter filter = new ItemFilter(); filter.setDisplayName(Restrictions.eq("test")); Query query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName=:param0", query.getQueryString()); filter.setDisplayName(Restrictions.neq("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName!=:param0", query.getQueryString()); filter.setDisplayName(Restrictions.like("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName like :param0", query.getQueryString()); filter.setDisplayName(Restrictions.nlike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName not like :param0", query.getQueryString()); filter.setDisplayName(Restrictions.isNull()); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where i.displayName is null", query.getQueryString()); filter.setDisplayName(Restrictions.ilike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where lower(i.displayName) like :param0", query.getQueryString()); filter.setDisplayName(Restrictions.nilike("test")); query = queryBuilder.buildQuery(session, filter); Assert.assertEquals("select i from HibItem i where lower(i.displayName) not like :param0", query.getQueryString()); }