List of usage examples for org.hibernate.criterion Projections sqlProjection
public static Projection sqlProjection(String sql, String[] columnAliases, Type[] types)
From source file:com.court.controller.HomeFXMLController.java
private Map<String, Number> getLoanReleasedData() { LocalDate now = LocalDate.now(); Map<String, Number> map = new HashMap<>(); Session s = HibernateUtil.getSessionFactory().openSession(); Criteria c = s.createCriteria(MemberLoan.class); ProjectionList pList = Projections.projectionList(); ClassMetadata lpMeta = s.getSessionFactory().getClassMetadata(MemberLoan.class); pList.add(Projections.property(lpMeta.getIdentifierPropertyName())); for (String prop : lpMeta.getPropertyNames()) { pList.add(Projections.property(prop), prop); }/*from w ww . jav a 2s . c o m*/ c.add(Restrictions.eq("status", true)); c.add(Restrictions.between("grantedDate", FxUtilsHandler.getDateFrom(now.with(firstDayOfYear())), FxUtilsHandler.getDateFrom(now.with(lastDayOfYear())))); c.setProjection(pList .add(Projections.sqlGroupProjection("DATE_FORMAT(granted_date, '%Y-%m-01') AS groupPro", "groupPro", new String[] { "groupPro" }, new Type[] { StringType.INSTANCE })) .add(Projections.sqlProjection("SUM(loan_amount) AS lSum", new String[] { "lSum" }, new Type[] { DoubleType.INSTANCE }))); c.addOrder(Order.asc("grantedDate")); c.setResultTransformer(Transformers.aliasToBean(MemberLoan.class)); List<MemberLoan> list = (List<MemberLoan>) c.list(); for (MemberLoan ml : list) { map.put(ml.getGroupPro(), ml.getlSum()); } s.close(); return map; }
From source file:com.court.controller.HomeFXMLController.java
private Map<String, Number> getLoanCollectionData() { LocalDate now = LocalDate.now(); Map<String, Number> map = new HashMap<>(); Session s = HibernateUtil.getSessionFactory().openSession(); Criteria c = s.createCriteria(LoanPayment.class); ProjectionList pList = Projections.projectionList(); ClassMetadata lpMeta = s.getSessionFactory().getClassMetadata(LoanPayment.class); pList.add(Projections.property(lpMeta.getIdentifierPropertyName())); for (String prop : lpMeta.getPropertyNames()) { pList.add(Projections.property(prop), prop); }//from w w w.ja va2s.c o m c.add(Restrictions.between("paymentDate", FxUtilsHandler.getDateFrom(now.with(firstDayOfYear())), FxUtilsHandler.getDateFrom(now.with(lastDayOfYear())))); c.setProjection(pList .add(Projections.sqlGroupProjection("DATE_FORMAT(payment_date, '%Y-%m-01') AS groupPro", "groupPro", new String[] { "groupPro" }, new Type[] { StringType.INSTANCE })) .add(Projections.sqlProjection("SUM(paid_amt) AS lSum", new String[] { "lSum" }, new Type[] { DoubleType.INSTANCE }))); c.addOrder(Order.asc("paymentDate")); c.setResultTransformer(Transformers.aliasToBean(LoanPayment.class)); List<LoanPayment> list = (List<LoanPayment>) c.list(); for (LoanPayment lp : list) { map.put(lp.getGroupPro(), lp.getlSum()); } s.close(); return map; }
From source file:com.ihsolution.hqipo.dao.utils.QueryHelper.java
License:Open Source License
public void applyOrderBy() throws Exception { if (!doOrder) return;/* ww w . j ava2 s . com*/ if (dto == null) throw new Exception("dto is null"); Map<String, Boolean> sortBy = dto.getSortByMap(); // logger.debug("order by Map size="+sortBy.size()); if ((this.orderBy == null || "".equals(orderBy)) && !dto.isSortByMap()) { return; } // Map has highest priority if (dto.isSortByMap()) { for (Map.Entry<String, Boolean> entry : sortBy.entrySet()) { String alias = this.createAliases(entry.getKey()); if (logger.isDebugEnabled()) logger.debug("Using sortByMap order by = " + alias); if (entry.getValue()) detCriteria.addOrder(Order.asc(alias).ignoreCase()); else detCriteria.addOrder(Order.desc(alias).ignoreCase()); } return; } // if we have arithmetic operators, checking only for plus for now if (orderBy.contains("+")) { String[] fields = orderBy.split("\\+"); if (fields.length > 1) { String sql = this.createSqlAliasAndField(fields[0]) + "+" + this.createSqlAliasAndField(fields[1]) + " as sF"; detCriteria.setProjection(Projections.projectionList() .add(Projections.alias(Projections.sqlProjection(sql, new String[] { "sF" }, new Type[] { Hibernate.INTEGER }), "sumF")) .add(Projections.sqlProjection("this_.*", new String[] { this.targetClass.getSimpleName() + "_id" }, new Type[] { Hibernate.entity(this.targetClass) }))); detCriteria.addOrder(Order.desc("sumF")); if (fields.length > 2) { String alias = this.createAliases(fields[2]); detCriteria.addOrder(Order.asc(alias).ignoreCase()); } return; } } if (orderBy != null && !"".equals(orderBy)) { String[] obs = orderBy.split(";"); for (String ob : obs) { String alias = this.createAliases(ob); if (logger.isDebugEnabled()) logger.debug("order by = " + alias); if (asc) detCriteria.addOrder(Order.asc(alias).ignoreCase()); else detCriteria.addOrder(Order.desc(alias).ignoreCase()); } } }
From source file:de.decidr.model.commands.tenant.GetUsersOfTenantCommand.java
License:Apache License
@SuppressWarnings("unchecked") @Override/*from ww w .jav a 2s. co m*/ public void transactionAllowed(TransactionStartedEvent evt) throws TransactionException { PaginatingCriteria c = new PaginatingCriteria(User.class, "u", evt.getSession()); String rootAlias = c.getAlias(); // Create criterion "user is a member of the tenant" DetachedCriteria memberRel = DetachedCriteria.forClass(UserIsMemberOfTenant.class, "memberRel"); memberRel.add(Property.forName("memberRel.user.id").eqProperty(rootAlias + ".id")) .add(Property.forName("memberRel.tenant.id").eq(getTenantId())); // Create criterion "user is the administrator of the tenant" DetachedCriteria adminRel = DetachedCriteria.forClass(Tenant.class, "adminTenant"); adminRel.add(Property.forName("adminTenant.id").eq(getTenantId())) .add(Property.forName("adminTenant.admin.id").eqProperty(rootAlias + ".id")); /* * Workaround for Hibernate issue HHH-993: Criteria subquery without * projection fails throwing NullPointerException. * * Additionally, Mysql doesn't seem to like aliases in EXISTS * subqueries, so we have to explicitly specify "*" */ Projection existsSubqueryProjection = Projections.sqlProjection("*", new String[0], new Type[0]); memberRel.setProjection(existsSubqueryProjection); adminRel.setProjection(existsSubqueryProjection); c.add(Restrictions.or(Subqueries.exists(memberRel), Subqueries.exists(adminRel))); // preload user profiles - no lazy loading desired c.createCriteria("userProfile", CriteriaSpecification.LEFT_JOIN); c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); if (paginator != null) { paginator.apply(c); } result = c.list(); }
From source file:de.decidr.model.commands.user.GetAdministratedWorkflowModelsCommand.java
License:Apache License
@SuppressWarnings("unchecked") @Override//ww w. j av a 2 s.c o m public void transactionAllowed(TransactionStartedEvent evt) throws TransactionException { result = null; // does the user exist? returning an empty list might be ambigous. String hql = "select u.id from User u where u.id = :userId"; Object id = evt.getSession().createQuery(hql).setLong("userId", getUserId()).setMaxResults(1) .uniqueResult(); if (id == null) { throw new EntityNotFoundException(User.class, getUserId()); } /* * Criteria that represent the following query: * * "from WorkflowModel w where w.tenant.id = DEFAULT_TENANT_ID or * (exists(from UserAdministratesWorkflowModel rel where * rel.workflowModel = w and rel.user.id = :userId) or w.tenant.admin.id * = :userId) or exists (from SystemSettings s where s.admin.id = * :userId))" */ PaginatingCriteria criteria = new PaginatingCriteria(WorkflowModel.class, "m", evt.getSession()); /* * A user administers a workflow model if there's an explicit * relationship. */ DetachedCriteria explicitWorkflowAdminCriteria = DetachedCriteria .forClass(UserAdministratesWorkflowModel.class, "rel"); explicitWorkflowAdminCriteria .add(Restrictions.conjunction().add(Restrictions.eqProperty("rel.workflowModel.id", "m.id")) .add(Restrictions.eq("rel.user.id", getUserId()))); /* * A user administers *any* workflow model if he is the super admin. */ DetachedCriteria superAdminCriteria = DetachedCriteria.forClass(SystemSettings.class, "s"); superAdminCriteria.add(Restrictions.eq("s.superAdmin.id", getUserId())); /* * Workaround for Hibernate issue HHH-993: Criteria subquery without * projection fails throwing NullPointerException. * * Additionally, Mysql doesn't seem to like aliases in EXISTS * subqueries, so we have to explicitly specify "*" */ explicitWorkflowAdminCriteria.setProjection(Projections.sqlProjection("*", new String[0], new Type[0])); superAdminCriteria.setProjection(Projections.sqlProjection("*", new String[0], new Type[0])); /* * Finally, a user administers a workflow model if he is the tenant * admin of the tenant that owns the model. We now add each criterion to * a disjuncion. */ criteria.createAlias("tenant", "t"); Disjunction allAdministrationCriteria = Restrictions.disjunction(); allAdministrationCriteria.add(Subqueries.exists(superAdminCriteria)); allAdministrationCriteria.add(Subqueries.exists(explicitWorkflowAdminCriteria)); allAdministrationCriteria.add(Restrictions.eq("t.admin.id", getUserId())); /* * Everyone is a workflow admin for models within the default tenant. */ criteria.add(Restrictions.disjunction().add(allAdministrationCriteria) .add(Restrictions.eq("m.tenant.id", DecidrGlobals.DEFAULT_TENANT_ID))); Filters.apply(criteria, filters, paginator); criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); result = criteria.list(); }
From source file:de.decidr.model.filters.StartableWorkflowModelFilter.java
License:Apache License
/** * {@inheritDoc}// w w w. ja va2 s .c om */ public void apply(Criteria criteria) { DetachedCriteria exists = DetachedCriteria.forClass(StartableWorkflowModelView.class, "startableFilter"); exists.add(Restrictions.eqProperty(exists.getAlias() + ".id", criteria.getAlias() + ".id")); /* * Workaround for Hibernate issue HHH-993: Criteria subquery without * projection fails throwing NullPointerException. * * Additionally, Mysql doesn't seem to like aliases in EXISTS * subqueries, so we have to explicitly specify "*" */ exists.setProjection(Projections.sqlProjection("*", new String[0], new Type[0])); criteria.add(Subqueries.exists(exists)); }
From source file:eionet.webq.dao.UserFileStorageImpl.java
License:Mozilla Public License
@Override public Number getUserWebFormFileMaxNum(String userId, String xmlSchema, String fileName, char numDelim, char extensionDelim) { String fileNameSearchCriteria; String maxProjection;/*w ww . ja va 2 s .co m*/ int lastIndexOfDot = fileName.lastIndexOf(extensionDelim); if (lastIndexOfDot > 0) { fileNameSearchCriteria = fileName.substring(0, lastIndexOfDot) + numDelim + "%" + fileName.substring(lastIndexOfDot); maxProjection = "MAX(CAST(SUBSTRING(file_name, LOCATE('" + numDelim + "', file_name) + 1, LOCATE('" + extensionDelim + "', file_name) - LOCATE('" + numDelim + "', file_name) - 1) AS SIGNED)) AS num"; } else { fileNameSearchCriteria = fileName + numDelim + "%"; maxProjection = "MAX(CAST(SUBSTRING(file_name, LOCATE('" + numDelim + "', file_name) + 1) AS SIGNED)) AS num"; } return (Number) (getCriteria().add(eq("userId", userId)).add(like("xmlSchema", xmlSchema)) .add(like("file.name", fileNameSearchCriteria)).setProjection(Projections .sqlProjection(maxProjection, new String[] { "num" }, new Type[] { new IntegerType() })) .uniqueResult()); }
From source file:org.grouter.domain.dao.spring.NodeDAOImpl.java
License:Apache License
public List<Node> findNodes() { // Todo fix this to return count(*) for a particular node String sqlFragment = "(select count(*) from Message i where i.node_fk = node_fk) as numOfItems"; Criteria criteria = getSession().createCriteria(getEntityClass()) .setProjection(Projections.projectionList().add(Projections.property("id")) .add(Projections.property("name")).add(Projections.sqlProjection(sqlFragment, new String[] { "numOfItems" }, new Type[] { Hibernate.LONG }))); return criteria.list(); }
From source file:org.transitime.reports.RoutePerformanceQuery.java
License:Open Source License
public List<Object[]> query(String agencyId, Date startDate, Date endDate, double allowableEarlyMin, double allowableLateMin, String predictionType, String predictionSource) { int msecLo = (int) (allowableEarlyMin * 60 * 1000); int msecHi = (int) (allowableLateMin * 60 * 1000); // Project to: # of predictions in which route is on time / # of predictions // for route. This cannot be done with pure Criteria API. This could be // moved to a separate class or XML file. String sqlProjection = "avg(predictionAccuracyMsecs BETWEEN " + Integer.toString(msecLo) + " AND " + Integer.toString(msecHi) + ") AS avgAccuracy"; try {//from ww w.ja v a 2 s . co m session = HibernateUtils.getSession(agencyId); Projection proj = Projections.projectionList().add(Projections.groupProperty("routeId"), "routeId") .add(Projections.sqlProjection(sqlProjection, new String[] { "avgAccuracy" }, new Type[] { DoubleType.INSTANCE }), "performance"); Criteria criteria = session.createCriteria(PredictionAccuracy.class).setProjection(proj) .add(Restrictions.between("arrivalDepartureTime", startDate, endDate)); if (predictionType == PREDICTION_TYPE_AFFECTED) criteria.add(Restrictions.eq("affectedByWaitStop", true)); else if (predictionType == PREDICTION_TYPE_NOT_AFFECTED) criteria.add(Restrictions.eq("affectedByWaitStop", false)); if (predictionSource != "") criteria.add(Restrictions.eq("predictionSource", predictionSource)); criteria.addOrder(Order.desc("performance")); criteria.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP); @SuppressWarnings("unchecked") List<Object[]> results = criteria.list(); return results; } catch (HibernateException e) { logger.error(e.toString()); return null; } finally { session.close(); } }