List of usage examples for org.hibernate.criterion Restrictions sqlRestriction
public static Criterion sqlRestriction(String sql, Object value, Type type)
From source file:org.opennms.web.svclayer.support.DefaultNodeListService.java
License:Open Source License
private void addCriteriaForSiteStatusView(OnmsCriteria criteria, String statusViewName, String statusSite, String rowLabel) {//from www . j av a 2s.c o m View view = m_siteStatusViewConfigDao.getView(statusViewName); RowDef rowDef = getRowDef(view, rowLabel); Set<String> categoryNames = getCategoryNamesForRowDef(rowDef); addCriteriaForCategories(criteria, categoryNames.toArray(new String[categoryNames.size()])); String sql = "{alias}.nodeId in (select nodeId from assets where " + view.getColumnName() + " = ?)"; criteria.add(Restrictions.sqlRestriction(sql, statusSite, new StringType())); }
From source file:org.photovault.imginfo.QueryFulltextCriteria.java
License:Open Source License
public void setupQuery(Criteria crit) { String sql = "MATCH(" + field.getName() + ") AGAINST('" + text + "')"; log.debug(sql);//from w ww. jav a2s. c o m crit.add( Restrictions.sqlRestriction("MATCH(" + field.getName() + ") AGAINST( ? )", text, Hibernate.STRING)); log.debug("Added"); }
From source file:org.photovault.imginfo.QueryFuzzyTimeCriteria.java
License:Open Source License
/** * Describe <code>setupQuery</code> method here. * * @param crit a <code>Criteria</code> value *//* w w w . ja va 2s .co m*/ public final void setupQuery(final Criteria crit) { log.debug("Entry: SetupQuery"); if (date == null) { log.warn("null query date"); return; } switch (strictness) { case INCLUDE_CERTAIN: log.debug("INCLUDE_CERTAIN"); /* Only certain results must be included, so the whole possible time period of object must be inside the accuracy limits. */ /* crit.addGreaterOrEqualThan( "subdate("+dateField.getName() + ", " + accuracyField.getName() + ")", date.getMinDate() ); crit.addLessOrEqualThan( "adddate("+dateField.getName() +", " + accuracyField.getName() + ")", date.getMaxDate() ); */ String gtclauseCertain = "{fn TIMESTAMPADD(SQL_TSI_SECOND, CAST( -3600*24*{alias}." + accuracyField.getColumnName() + " AS INT)" + ", {alias}." + dateField.getColumnName() + ")} >= ?"; crit.add(Restrictions.sqlRestriction(gtclauseCertain, date.getMinDate(), Hibernate.TIMESTAMP)); String ltclauseCertain = "{fn TIMESTAMPADD(SQL_TSI_SECOND, CAST( 3600*24*{alias}." + accuracyField.getColumnName() + " AS INT)" + ", {alias}." + dateField.getColumnName() + ")} <= ?"; crit.add(Restrictions.sqlRestriction(ltclauseCertain, date.getMaxDate(), Hibernate.TIMESTAMP)); break; case INCLUDE_PROBABLE: log.debug("INCLUDE_PROBABLE"); crit.add(Restrictions.between(dateField.getName(), date.getMinDate(), date.getMaxDate())); crit.add(Restrictions.le(accuracyField.getName(), new Double(date.getAccuracy()))); break; case INCLUDE_POSSIBLE: /* * Include photos whose time range intersects with the query range */ log.debug("INCLUDE_POSSIBLE"); String gtclause = "{fn TIMESTAMPADD(SQL_TSI_SECOND, CAST( 3600*24*{alias}." + accuracyField.getColumnName() + " AS INT)" + ", {alias}." + dateField.getColumnName() + ")} >= ?"; crit.add(Restrictions.sqlRestriction(gtclause, date.getMinDate(), Hibernate.TIMESTAMP)); log.debug(gtclause + " >= " + date.getMinDate()); String ltclause = "{fn TIMESTAMPADD(SQL_TSI_SECOND, CAST( -3600*24*{alias}." + accuracyField.getColumnName() + " AS INT)" + ", {alias}." + dateField.getColumnName() + ")} <= ?"; crit.add(Restrictions.sqlRestriction(ltclause, date.getMaxDate(), Hibernate.TIMESTAMP)); log.debug(ltclause + " <= " + date.getMaxDate()); break; default: log.error("Illegal value for strictness: " + strictness); } log.debug("Exit: SetupQuery"); }
From source file:org.rebioma.server.services.OccurrenceDbImpl.java
License:Apache License
/** * Adds a {@link Set} of search {@link OccurrenceFilter} to the * {@link Criteria}//ww w . j a v a 2s.com * * @param criteria {@link Criteria} to be added with filters * @param user an id of a user in db. * @param searchFilters a filters that use for find Occurrences. * @param resultFilter the {@link ResultFilter} to determine what public * filter is added. * @param tryCount TODO * @return {@link Set} of string filters was added to criteria. */ private Set<String> addCreterionByFilters(Criteria criteria, User user, Set<OccurrenceFilter> searchFilters, ResultFilter resultFilter, int tryCount) { Set<String> queryFilters = new HashSet<String>(); // if (userId != null) { // } // Adds restrictions for each query filter: boolean isMyOccurrence = false; String ownerField = getOccurrencePropertyName("ownerEmail"); List<Criterion> disjunctionCriterions = new ArrayList<Criterion>(); for (OccurrenceFilter filter : searchFilters) { if ((filter.getOperator() != Operator.IS_EMPTY && filter.getOperator() != Operator.IS_NOT_EMPTY) && filter.value instanceof String && ((String) filter.value).equals("")) { continue; } if (filter.column.equalsIgnoreCase(ownerField)) { isMyOccurrence = true; } String queryFilter = null; Criterion criterion = null; // if filter column is "quickSearch" then this is a simple search if (filter.column.equalsIgnoreCase(filter.getPropertyName("quickSearch"))) { Criterion acceptedSpeciesCri = null; Criterion verbatimSpeciesCri = null; Criterion scientificNameCri = null; String acceptedSpecies = filter.getPropertyName("acceptedSpecies"); String verbatimSpecies = filter.getPropertyName("verbatimSpecies"); String scientificName = filter.getPropertyName("scientificName"); if (tryCount == 1) { acceptedSpeciesCri = Restrictions.ilike(acceptedSpecies, filter.getValue().toString(), MatchMode.START); verbatimSpeciesCri = Restrictions.ilike(verbatimSpecies, filter.getValue().toString(), MatchMode.START); scientificNameCri = Restrictions.ilike(scientificName, filter.getValue().toString(), MatchMode.START); queryFilter = acceptedSpecies + " like '" + filter.getValue() + "%' or " + verbatimSpecies + " like '" + filter.getValue() + "%' or " + scientificName + " like '" + filter.getValue() + "%'"; } else if (tryCount == 2) { acceptedSpeciesCri = Restrictions.ilike(acceptedSpecies, filter.getValue().toString(), MatchMode.ANYWHERE); verbatimSpeciesCri = Restrictions.ilike(verbatimSpecies, filter.getValue().toString(), MatchMode.ANYWHERE); scientificNameCri = Restrictions.ilike(scientificName, filter.getValue().toString(), MatchMode.ANYWHERE); queryFilter = acceptedSpecies + " like '%" + filter.getValue() + "%' or " + verbatimSpecies + " like '%" + filter.getValue() + "%' or " + scientificName + " like '%" + filter.getValue() + "%'"; } criterion = Restrictions.or(scientificNameCri, Restrictions.or(acceptedSpeciesCri, verbatimSpeciesCri)); } else { Operator op = filter.getOperator(); String value; switch (op) { case CONTAIN: value = (String) filter.getValue(); criterion = Restrictions.ilike(filter.column, value, MatchMode.ANYWHERE); break; case EQUAL: criterion = null; if (filter.column.equals(filter.getPropertyName("sex"))) { value = (String) filter.getValue(); if (value.equalsIgnoreCase("unknown")) { Object[] filters = getComplexCriterion( new String[] { "male", "female", "hermaphroditic" }, filter.column, "!="); criterion = (Criterion) filters[0]; queryFilter = filters[1].toString(); // criterion = Restrictions.or(Restrictions., rhs) } else { criterion = Restrictions.eq(filter.column, value); } } else if (filter.column.equals(filter.getPropertyName("BasisOfRecord"))) { value = (String) filter.getValue(); if (value.equalsIgnoreCase("Non-standardSpecimen")) { Object[] filters = getComplexCriterion(VALID_BASIS_OF_RECORDS, filter.column, "!="); criterion = (Criterion) filters[0]; queryFilter = filters[1].toString(); } else { criterion = Restrictions.eq(filter.column, value); } } else { // {wd} on utilise "upper()" si type column = String if (StringUtil.isType(Occurrence.class, filter.column, String.class)) criterion = Restrictions.sqlRestriction("upper({alias}." + StringUtil.columnName(Occurrence.class, filter.column) + ") = upper(?)", filter.getValue(), Hibernate.STRING); else criterion = Restrictions.eq(filter.column, filter.getValue()); } break; case NOT_CONTAIN: value = (String) filter.getValue(); criterion = Restrictions.not(Restrictions.ilike(filter.column, value, MatchMode.ANYWHERE)); break; case NOT_EQUAL: criterion = null; if (filter.column.equals(filter.getPropertyName("sex"))) { value = (String) filter.getValue(); if (value.equalsIgnoreCase("unknown")) { Object[] filters = getComplexCriterion( new String[] { "male", "female", "hermaphroditic" }, filter.column, "="); criterion = (Criterion) filters[0]; queryFilter = filters[1].toString(); // criterion = Restrictions.or(Restrictions., rhs) } else { criterion = Restrictions.ne(filter.column, value); } } else if (filter.column.equals(filter.getPropertyName("BasisOfRecord"))) { value = (String) filter.getValue(); if (value.equalsIgnoreCase("Non-standardSpecimen")) { Object[] filters = getComplexCriterion(VALID_BASIS_OF_RECORDS, filter.column, "="); criterion = (Criterion) filters[0]; queryFilter = filters[1].toString(); } else { criterion = Restrictions.ne(filter.column, value); } } else { criterion = Restrictions.ne(filter.column, filter.getValue()); } break; case START_WITH: value = (String) filter.getValue(); criterion = Restrictions.ilike(filter.column, value, MatchMode.START); break; case NOT_START_WITH: value = (String) filter.getValue(); criterion = Restrictions.not(Restrictions.ilike(filter.column, value, MatchMode.START)); break; case LESS: criterion = Restrictions.lt(filter.column, filter.getValue()); break; case GREATER: criterion = Restrictions.gt(filter.column, filter.getValue()); break; case LESS_EQUAL: criterion = Restrictions.le(filter.column, filter.getValue()); break; case GREATER_EQUAL: criterion = Restrictions.ge(filter.column, filter.getValue()); break; case IN: if (filter.getValue() instanceof Collection<?>) { criterion = Restrictions.in(filter.column, (Collection<?>) filter.getValue()); //{WD } else if (StringUtil.isType(Occurrence.class, filter.column, Integer.class)) { Object values[] = filter.getIntegerValues(); criterion = Restrictions.in(filter.column, values); //} } else { String values[] = filter.getCollectionValues(); criterion = Restrictions.in(filter.column, values); } break; case NOT_IN: //{WD if (StringUtil.isType(Occurrence.class, filter.column, Integer.class)) { Object arryValues[] = filter.getIntegerValues(); criterion = Restrictions.not(Restrictions.in(filter.column, arryValues)); //} } else { String arryValues[] = filter.getCollectionValues(); criterion = Restrictions.not(Restrictions.in(filter.column, arryValues)); } break; case IS_EMPTY: if (StringUtil.isString(Occurrence.class, filter.column)) { // System.out.println(filter.column + "null and empty"); criterion = Restrictions.or(Restrictions.isNull(filter.column), Restrictions.eq(filter.column, "")); } else { // System.out.println(filter.column + "null"); criterion = Restrictions.isNull(filter.column); } // System.out.println(criterion); break; case IS_NOT_EMPTY: criterion = Restrictions.and(Restrictions.isNotNull(filter.column), Restrictions.ne(filter.column, "")); System.out.println(criterion); break; } } if (criterion != null) { if (queryFilter == null) { queryFilter = filter.toString(); } queryFilters.add(queryFilter); if (filter.isDisjunction()) { disjunctionCriterions.add(criterion); } else { criteria.add(criterion); } } } Set<String> filtersString = new HashSet<String>(); Criterion publicCriterion = getPublicCriterion(user, resultFilter, isMyOccurrence, filtersString); if (publicCriterion != null) { criteria.add(publicCriterion); queryFilters.addAll(filtersString); } Criterion disjunctionCriterion = null; for (Criterion c : disjunctionCriterions) { if (disjunctionCriterion == null) { disjunctionCriterion = Restrictions.disjunction().add(c); } else { disjunctionCriterion = Restrictions.or(disjunctionCriterion, c); } } if (disjunctionCriterion != null) { criteria.add(disjunctionCriterion); } return queryFilters; }
From source file:to.etc.domui.hibernate.model.CriteriaCreatingVisitor.java
License:Open Source License
private void handleLikeOperation(String name, PropertyMetaModel<?> pmm, Object value) throws Exception { //-- Check if there is a type mismatch in parameter type... if (!(value instanceof String)) throw new QQuerySyntaxException( "The argument to 'like' must be a string (and cannot be null), the value passed is: " + value); if (pmm == null || pmm.getActualType() == String.class) { m_last = Restrictions.like(name, value); return;/*from w w w.j ava 2 s . co m*/ } ClassMetadata hibmd = m_session.getSessionFactory().getClassMetadata(pmm.getClassModel().getActualClass()); if (null == hibmd) throw new QQuerySyntaxException("Cannot obtain Hibernate metadata for property=" + pmm); if (!(hibmd instanceof AbstractEntityPersister)) throw new QQuerySyntaxException("Cannot obtain Hibernate metadata for property=" + pmm + ": expecting AbstractEntityPersister, got a " + hibmd.getClass()); AbstractEntityPersister aep = (AbstractEntityPersister) hibmd; String[] colar = getPropertyColumnNamesFromLousyMetadata(aep, name); if (colar.length != 1) throw new IllegalStateException("Attempt to do a 'like' on a multi-column property: " + pmm); String columnName = colar[0]; int dotix = name.lastIndexOf('.'); if (dotix == -1) { //-- We need Hibernate metadata to find the column name.... m_last = Restrictions.sqlRestriction("{alias}." + columnName + " like ?", value, StringType.INSTANCE); return; } String sql = "{" + name + "} like ?"; m_last = new HibernateAliasedSqlCriterion(sql, value, StringType.INSTANCE); }