List of usage examples for org.hibernate.criterion Restrictions sqlRestriction
public static Criterion sqlRestriction(String sql)
From source file:it.av.es.service.impl.OrderServiceHibernate.java
License:Apache License
/** * {@inheritDoc}// w w w . j av a 2 s.c o m */ @Override public Collection<Order> get(User user, Project project, Date filterDate, Date filterDeliveredDate, OrderStatus filterStatus, boolean excludeCancelled, int firstResult, int maxResult, String sortProperty, boolean isAscending) { Criteria criteria = getHibernateSession().createCriteria(getPersistentClass()); if (user.getUserProfile().equals(userProfileService.getAdminUserProfile()) || user.getUserProfile().equals(userProfileService.getOperatorUserProfile()) || user.getUserProfile().equals(userProfileService.getProjectManagerUserProfile())) { //sees all the orders } // if the user is Admin of a group, filter on the group member else if (!groupService.isUserAdministratorOAGroups(user).isEmpty()) { List<Group> g = groupService.isUserAdministratorOAGroups(user); List<User> users = new ArrayList<User>(); for (Group group : g) { users.addAll(group.getMembers()); } users.add(user); criteria.add(Restrictions.in(Order.USER_FIELD, users)); } else { // sees only his orders criteria.add(Restrictions.eq(Order.USER_FIELD, user)); } if (excludeCancelled) { criteria.add(Restrictions.eq(Order.ISCANCELLED_FIELD, Boolean.FALSE)); } if (filterDate != null) { criteria.add(Restrictions.sqlRestriction("date_trunc('day', this_.creation_time) = '" + DateUtil.SDF2SIMPLEUSA.print(filterDate.getTime()) + "'")); } if (filterDeliveredDate != null) { criteria.add(Restrictions.sqlRestriction("date_trunc('day', this_.delivered_time) = '" + DateUtil.SDF2SIMPLEUSA.print(filterDeliveredDate.getTime()) + "'")); } if (filterStatus != null) { criteria.add(Restrictions.eq(Order.STATUS_FIELD, filterStatus)); } Criterion critByProject = Restrictions.eq(Order.PROJECT_FIELD, project); org.hibernate.criterion.Order order = org.hibernate.criterion.Order.desc(Order.CREATIONTIME_FIELD); if (StringUtils.isNotBlank(sortProperty)) { if (isAscending) { order = org.hibernate.criterion.Order.asc(sortProperty); } else { order = org.hibernate.criterion.Order.desc(sortProperty); } } criteria.add(critByProject); //Crea l'alias per permetter il sort su property annidate come customer.corporateName criteria.createAlias("customer", "customer"); if (order != null) { criteria.addOrder(order); } if (firstResult > 0) { criteria.setFirstResult(firstResult); } if (maxResult > 0) { criteria.setMaxResults(maxResult); } return criteria.list(); }
From source file:it.av.es.service.impl.OrderServiceHibernate.java
License:Apache License
private int getLastInvoiceNumber(Project project, Date invoiceDate) { Criteria criteria = getHibernateSession().createCriteria(getPersistentClass()); criteria.add(Restrictions.sqlRestriction("date_trunc('year', this_.creation_time) = '" + DateUtil.SDF2SIMPLEUSA.print(DateUtils.truncate(invoiceDate, Calendar.YEAR).getTime()) + "'")); criteria.add(Restrictions.isNotNull("invoiceNumber")); criteria.addOrder(org.hibernate.criterion.Order.desc("invoiceNumber")); List<Order> list = criteria.list(); int lastInvoiceNumber = 0; if (list.size() > 0) { lastInvoiceNumber = list.get(0).getInvoiceNumber(); }// ww w . j a va 2 s .com return lastInvoiceNumber; }
From source file:models.db.acentera.impl.UserForgotKeysImpl.java
License:Open Source License
public static UserForgotKeys getByIdAndLock(String token, String newToken, String ip) { Session s = (Session) HibernateSessionFactory.getSession(); Criteria criteria = s.createCriteria(UserForgotKeys.class); UserForgotKeys dbToken = null;// ww w . ja v a 2s . com String sqlWhere = "CREATED > DATE_SUB(curdate(), INTERVAL 1 DAY)"; Logger.debug("gokenid : " + token + " new tokenid request " + newToken + " ip " + ip); try { //TODO Validate if DEV MODE... (H2 does not support DATE_SUB) //we fix this temporary by play.Play.isDev if (play.Play.isDev()) { dbToken = (UserForgotKeys) criteria .add(Restrictions.and(Restrictions.eq("tokenid", token), Restrictions.eq("redeemed", 0))) .uniqueResult(); } else { dbToken = (UserForgotKeys) criteria.add(Restrictions.and(Restrictions.eq("tokenid", token), Restrictions.eq("tokenid_request", null), Restrictions.eq("redeemed", 0), Restrictions.sqlRestriction(sqlWhere))).uniqueResult(); } } catch (Exception ee) { ee.printStackTrace(); } Logger.debug("DB TOKEN IS : " + dbToken); if (dbToken != null) { dbToken.setTokenid_request(newToken); dbToken.setIp(ip); try { dbToken = saveOrUpdate(dbToken); return dbToken; } catch (DAOException e) { e.printStackTrace(); } } //Couldn't get token return null; }
From source file:net.firejack.platform.core.store.AbstractStore.java
License:Apache License
@SuppressWarnings("unchecked") protected Criterion parseAdvancedSearchRequest(List<SearchQuery> searchQueries, Map<String, String> aliases, boolean skipNotValidValues) { int index = 0; LinkedList<Criterion> criterions = new LinkedList<Criterion>(); for (SearchQuery searchQuery : searchQueries) { Criterion criterion;/*from w w w . j a v a 2 s .co m*/ String field = searchQuery.getField(); if (field == null) { criterions.add(Restrictions.sqlRestriction("1 = 1")); } else { String[] fieldNames = field.split("\\."); if (fieldNames.length == 1) { String fieldName = fieldNames[0]; PropertyDescriptor propertyDescriptor = ClassUtils.getPropertyDescriptor(getClazz(), fieldName); if (propertyDescriptor != null) { Method readMethod = propertyDescriptor.getReadMethod(); if (readMethod != null) { Class<?> returnType = readMethod.getReturnType(); try { criterion = getRestrictions(searchQuery, returnType); criterions.add(criterion); } catch (IllegalArgumentException e) { if (!skipNotValidValues) { throw new BusinessFunctionException( "The field '" + fieldName + "' has type '" + returnType.getName() + "', but value '" + searchQuery.getValue() + "' is incorrect"); } } } else { throw new BusinessFunctionException("The field '" + fieldName + "' has not read method in class '" + getClazz().getName() + "'"); } } else { throw new BusinessFunctionException("The field '" + fieldName + "' does not exist in class '" + getClazz().getName() + "'"); } } else { Class<E> aClass = getClazz(); String indexedFieldName = null; for (int i = 0; i < fieldNames.length; i++) { String fieldName = fieldNames[i]; PropertyDescriptor propertyDescriptor = ClassUtils.getPropertyDescriptor(aClass, fieldName); if (propertyDescriptor != null) { Method readMethod = propertyDescriptor.getReadMethod(); if (readMethod != null) { Class<?> returnType = readMethod.getReturnType(); if (Collection.class.isAssignableFrom(returnType)) { returnType = (Class<?>) ((ParameterizedTypeImpl) readMethod .getGenericReturnType()).getActualTypeArguments()[0]; } if (AbstractModel.class.isAssignableFrom(returnType)) { aClass = (Class) returnType; String alias = i == 0 ? fieldName : indexedFieldName + "." + fieldName; indexedFieldName = aliases.get(alias); if (indexedFieldName == null) { indexedFieldName = fieldName + index++; aliases.put(alias, indexedFieldName); } } else { if (i == (fieldNames.length - 1)) { String queryFieldName = indexedFieldName + "." + fieldName; try { criterion = getRestrictions(new SearchQuery(queryFieldName, searchQuery.getOperation(), searchQuery.getValue()), returnType); criterions.add(criterion); } catch (IllegalArgumentException e) { if (!skipNotValidValues) { throw new BusinessFunctionException("The field '" + fieldName + "' has type '" + returnType.getName() + "', but value '" + searchQuery.getValue() + "' is incorrect"); } } } else { throw new BusinessFunctionException("Field name: '" + fieldName + "' is not correct in query: '" + field + "'"); } } } else { throw new BusinessFunctionException("The field '" + fieldName + "' has not read method in class '" + aClass + "'"); } } else { throw new BusinessFunctionException( "The field '" + fieldName + "' does not exist in class '" + aClass + "'"); } } } } } Criterion andCriterion = null; for (Criterion criterion : criterions) { andCriterion = criterions.getFirst() == criterion ? criterion : Restrictions.and(andCriterion, criterion); } return andCriterion; }
From source file:net.firejack.platform.core.store.BaseStore.java
License:Apache License
private Criteria createCriteria(Session session, Integer offset, Integer limit, Object example, List<String> nullableAssociations) { try {/*from w w w . ja v a 2 s.co m*/ Class<?> exampleClass = example.getClass(); Criteria criteria = session.createCriteria(exampleClass); // Map<String, Criteria> subcriterias = new HashMap<String, Criteria>(); if (limit != null && limit > -1) { criteria.setMaxResults(limit); } if (offset != null && offset > -1) { criteria.setFirstResult(offset); } Example exampleQuery = createExample(example); criteria.add(exampleQuery); SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory(); ClassMetadata meta = sessionFactory.getClassMetadata(exampleClass); String[] names = meta.getPropertyNames(); Type[] propertyTypes = meta.getPropertyTypes(); for (int i = 0; i < propertyTypes.length; i++) { if (propertyTypes[i].isAssociationType() && !propertyTypes[i].isCollectionType()) { String name = names[i]; Object value = PropertyUtils.getProperty(example, name); if (value != null) { Example subExample = createExample(value); Criteria subcriteria = criteria.createCriteria(name); // subcriterias.put(name, subcriteria); subcriteria.add(subExample); } else if (nullableAssociations.contains(name)) { criteria.add(Restrictions.isNull(name)); } } else if (propertyTypes[i].isCollectionType()) { String name = names[i]; Collection values = (Collection) PropertyUtils.getProperty(example, name); JoinTable joinTable = getMethodAnnotation(JoinTable.class, example, name); if (values != null && values.size() > 0 && joinTable != null) { Table table = getClassAnnotation(Table.class, example); Enumerated enumerated = getMethodAnnotation(Enumerated.class, example, name); Object obj = values.iterator().next(); if (obj.getClass().isEnum()) { String sqlWhere = "{alias}.id IN (SELECT DISTINCT id_" + table.name() + " FROM " + joinTable.name() + " WHERE element IN ("; List<String> ordinals = new ArrayList<String>(); for (Object v : values) { if (enumerated != null && EnumType.STRING.equals(enumerated.value())) { ordinals.add("'" + String.valueOf(((Enum) v).name()) + "'"); } else { ordinals.add(String.valueOf(((Enum) v).ordinal())); } } String whereValues = StringUtils.join(ordinals.toArray(), ","); sqlWhere = sqlWhere + whereValues + "))"; criteria.add(Restrictions.sqlRestriction(sqlWhere)); } } } } return criteria; } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (InvocationTargetException e) { throw new RuntimeException(e); } catch (NoSuchMethodException e) { throw new RuntimeException(e); } }
From source file:net.firejack.platform.core.store.statistics.LogTransactionStore.java
License:Apache License
private List<Criterion> createCriterionsForTermAndDates(String term, String nodeLookup, Date startDate, Date endDate) {// w w w .j av a 2 s . c o m List<Criterion> criterions = new ArrayList<Criterion>(); if (!StringUtils.isEmpty(term)) { Criterion termCriterion = Restrictions.sqlRestriction("{alias}.package_lookup LIKE '%" + term + "%'"); criterions.add(termCriterion); } if (!StringUtils.isEmpty(nodeLookup)) { Criterion nodeLookupCriterion = Restrictions .sqlRestriction("{alias}.package_lookup LIKE '" + nodeLookup + "%'"); criterions.add(nodeLookupCriterion); } if (startDate != null) { Criterion startDateCriterion = Restrictions.ge("created", startDate); criterions.add(startDateCriterion); } if (endDate != null) { Criterion endDateCriterion = Restrictions.lt("created", endDate); criterions.add(endDateCriterion); } return criterions; }
From source file:net.firejack.platform.core.store.statistics.MetricsEntryStore.java
License:Apache License
private List<Criterion> createCriterionsForTermAndDates(String term, String lookup, Date startDate, Date endDate, LogEntryType logEntryType) { List<Criterion> criterions = new ArrayList<Criterion>(); if (StringUtils.isNotEmpty(term)) { Criterion lookupCriterion = Restrictions.sqlRestriction("{alias}.lookup LIKE '%" + term + "%'"); Criterion usernameCriterion = Restrictions.sqlRestriction("{alias}.username LIKE '%" + term + "%'"); Criterion systemAccountNameCriterion = Restrictions .sqlRestriction("{alias}.system_account_name LIKE '%" + term + "%'"); Criterion termCriterion = Restrictions.or(Restrictions.or(lookupCriterion, usernameCriterion), systemAccountNameCriterion); criterions.add(termCriterion);/*w ww. j a va2s . c o m*/ } if (logEntryType != null && !LogEntryType.ALL.equals(logEntryType)) { criterions.add(Restrictions.eq("type", logEntryType)); } if (StringUtils.isNotEmpty(lookup)) { Criterion lookupCriterion = Restrictions.sqlRestriction("{alias}.lookup LIKE '%" + lookup + "%'"); criterions.add(lookupCriterion); } if (startDate != null) { Criterion startDateCriterion = Restrictions.ge("hourPeriod", startDate.getTime()); criterions.add(startDateCriterion); } if (endDate != null) { Criterion endDateCriterion = Restrictions.le("hourPeriod", endDate.getTime()); criterions.add(endDateCriterion); } return criterions; }
From source file:nl.edia.sakai.tool.skinmanager.impl.SkinArchiveServiceImpl.java
License:Educational Community License
@SuppressWarnings("unchecked") @Override//from w ww . j ava 2s. co m public List<SkinArchive> fetchActiveSkinArchives() { return (List<SkinArchive>) getHibernateTemplate().execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { Criteria myCriteria = session.createCriteria(SkinArchive.class) .add(Restrictions.eq("active", Boolean.TRUE)) .add(Restrictions.sqlRestriction(" {alias}.skin_version = (" + " select max(sa.skin_version) from edia_skinmanager_archive sa where sa.skin_name = {alias}.skin_name " + " ) ")); return myCriteria.list(); } }); }
From source file:org.freequiz.www.dao.hibernate.HibernateQuestionDAO.java
License:Open Source License
public List<Question> findRandomQuestionSetByExample(int numQuestions, Question exampleInstance) throws Exception { int maxTries = 5; int tries = 0; List<Question> questionList = new ArrayList<Question>(); try {//from ww w. j a v a 2 s . c om for (int i = 1; i <= numQuestions; i++) { Long low = Long.valueOf((i == 1) ? 1 : i - 1); // 1 is minimum low Long high = Long.valueOf((i == numQuestions) ? numQuestions : i + 1); // numQuestions is maximum high Criteria crit = HibernateUtil.getSession().createCriteria(getPersistentClass()); Example example = Example.create(exampleInstance).ignoreCase(); crit.add(example); if (exampleInstance.getTopic() != null) crit.add(Restrictions.eq("topic", exampleInstance.getTopic())); crit.add(Restrictions.ge("difficulty", low)).add(Restrictions.le("difficulty", high)); crit.add(Restrictions.sqlRestriction("1=1 order by random()")); crit.setMaxResults(1).uniqueResult(); Question nextQuestion = (Question) crit.list().get(0); if (questionList.contains(nextQuestion) && tries < maxTries) { tries++; // Re-run query if item already in set i--; continue; } else { questionList.add(nextQuestion); } } } catch (IndexOutOfBoundsException ex) { // TODO Display error, generate more questions System.err.println("No Questions for all difficulties"); throw ex; } catch (Exception ex) { // TODO Display Error System.err.println("Unable to find enough questions"); throw ex; } return questionList; }
From source file:org.hoteia.qalingo.core.dao.ProductDao.java
License:Apache License
public List<ProductMarketing> findProductMarketingByRandom(int maxResults, Object... params) { Random randomGenerator = new Random(); int randomInt = randomGenerator.nextInt(1000); Criteria criteria = createDefaultCriteria(ProductMarketing.class); handleSpecificProductMarketingFetchMode(criteria, params); criteria.add(Restrictions.sqlRestriction("1=1 ORDER BY RAND(" + randomInt + ")")); criteria.setMaxResults(maxResults);// w w w . j ava 2 s. co m @SuppressWarnings("unchecked") List<ProductMarketing> productMarketings = criteria.list(); return productMarketings; }