List of usage examples for org.hibernate.criterion Restrictions sqlRestriction
public static Criterion sqlRestriction(String sql, Object value, Type type)
From source file:org.codehaus.groovy.grails.orm.hibernate.query.AbstractHibernateQuery.java
License:Apache License
org.hibernate.criterion.Criterion getRestrictionForFunctionCall(FunctionCallingCriterion criterion,
PersistentEntity entity) {//from w w w . j av a2s .c om
org.hibernate.criterion.Criterion sqlRestriction;
SessionFactory sessionFactory = ((IHibernateTemplate) session.getNativeInterface()).getSessionFactory();
String property = criterion.getProperty();
Criterion datastoreCriterion = criterion.getPropertyCriterion();
PersistentProperty pp = entity.getPropertyByName(property);
if (pp == null)
throw new InvalidDataAccessResourceUsageException(
"Cannot execute function defined in query [" + criterion.getFunctionName()
+ "] on non-existent property [" + property + "] of [" + entity.getJavaClass() + "]");
String functionName = criterion.getFunctionName();
Dialect dialect = getDialect(sessionFactory);
SQLFunction sqlFunction = dialect.getFunctions().get(functionName);
if (sqlFunction != null) {
TypeResolver typeResolver = getTypeResolver(sessionFactory);
BasicType basic = typeResolver.basic(pp.getType().getName());
if (basic != null && datastoreCriterion instanceof PropertyCriterion) {
PropertyCriterion pc = (PropertyCriterion) datastoreCriterion;
final org.hibernate.criterion.Criterion hibernateCriterion = createHibernateCriterionAdapter(
getEntity(), datastoreCriterion, alias).toHibernateCriterion(this);
if (hibernateCriterion instanceof SimpleExpression) {
SimpleExpression expr = (SimpleExpression) hibernateCriterion;
Object op = ReflectionUtils.getField(opField, expr);
PropertyMapping mapping = getEntityPersister(entity.getJavaClass().getName(), sessionFactory);
String[] columns;
if (alias != null) {
columns = mapping.toColumns(alias, property);
} else {
columns = mapping.toColumns(property);
}
String root = render(basic, Arrays.asList(columns), sessionFactory, sqlFunction);
Object value = pc.getValue();
if (value != null) {
sqlRestriction = Restrictions.sqlRestriction(root + op + "?", value,
typeResolver.basic(value.getClass().getName()));
} else {
sqlRestriction = Restrictions.sqlRestriction(root + op + "?", value, basic);
}
} else {
throw new InvalidDataAccessResourceUsageException(
"Unsupported function [" + functionName + "] defined in query for property [" + property
+ "] with type [" + pp.getType() + "]");
}
} else {
throw new InvalidDataAccessResourceUsageException("Unsupported function [" + functionName
+ "] defined in query for property [" + property + "] with type [" + pp.getType() + "]");
}
} else {
throw new InvalidDataAccessResourceUsageException(
"Unsupported function defined in query [" + functionName + "]");
}
return sqlRestriction;
}
From source file:org.dspace.content.dao.impl.ItemDAOImpl.java
License:BSD License
@Override public Iterator<Item> findByMetadataQuery(Context context, List<List<MetadataField>> listFieldList, List<String> query_op, List<String> query_val, List<UUID> collectionUuids, String regexClause, int offset, int limit) throws SQLException { Criteria criteria = createCriteria(context, Item.class, "item"); criteria.setFirstResult(offset);//ww w .j a v a 2 s. co m criteria.setMaxResults(limit); if (!collectionUuids.isEmpty()) { DetachedCriteria dcollCriteria = DetachedCriteria.forClass(Collection.class, "coll"); dcollCriteria.setProjection(Projections.property("coll.id")); dcollCriteria.add(Restrictions.eqProperty("coll.id", "item.owningCollection")); dcollCriteria.add(Restrictions.in("coll.id", collectionUuids)); criteria.add(Subqueries.exists(dcollCriteria)); } int index = Math.min(listFieldList.size(), Math.min(query_op.size(), query_val.size())); StringBuilder sb = new StringBuilder(); for (int i = 0; i < index; i++) { OP op = OP.valueOf(query_op.get(i)); if (op == null) { log.warn("Skipping Invalid Operator: " + query_op.get(i)); continue; } if (op == OP.matches || op == OP.doesnt_match) { if (regexClause.isEmpty()) { log.warn("Skipping Unsupported Regex Operator: " + query_op.get(i)); continue; } } DetachedCriteria subcriteria = DetachedCriteria.forClass(MetadataValue.class, "mv"); subcriteria.add(Property.forName("mv.dSpaceObject").eqProperty("item.id")); subcriteria.setProjection(Projections.property("mv.dSpaceObject")); if (!listFieldList.get(i).isEmpty()) { subcriteria.add(Restrictions.in("metadataField", listFieldList.get(i))); } sb.append(op.name() + " "); if (op == OP.equals || op == OP.not_equals) { subcriteria.add(Property.forName("mv.value").eq(query_val.get(i))); sb.append(query_val.get(i)); } else if (op == OP.like || op == OP.not_like) { subcriteria.add(Property.forName("mv.value").like(query_val.get(i))); sb.append(query_val.get(i)); } else if (op == OP.contains || op == OP.doesnt_contain) { subcriteria.add(Property.forName("mv.value").like("%" + query_val.get(i) + "%")); sb.append(query_val.get(i)); } else if (op == OP.matches || op == OP.doesnt_match) { subcriteria .add(Restrictions.sqlRestriction(regexClause, query_val.get(i), StandardBasicTypes.STRING)); sb.append(query_val.get(i)); } if (op == OP.exists || op == OP.equals || op == OP.like || op == OP.contains || op == OP.matches) { criteria.add(Subqueries.exists(subcriteria)); } else { criteria.add(Subqueries.notExists(subcriteria)); } } log.debug(String.format("Running custom query with %d filters", index)); return list(criteria).iterator(); }
From source file:org.egov.works.master.service.ContractorService.java
License:Open Source License
public void searchContractor(final Map<String, Object> criteriaMap) { if (logger.isDebugEnabled()) logger.debug("Inside searchContractor"); final String contractorName = (String) criteriaMap.get(WorksConstants.CONTRACTOR_NAME); final String contractorCode = (String) criteriaMap.get(WorksConstants.CONTRACTOR_CODE); final Long departmentId = (Long) criteriaMap.get(WorksConstants.DEPARTMENT_ID); final Long gradeId = (Long) criteriaMap.get(WorksConstants.GRADE_ID); final Date searchDate = (Date) criteriaMap.get(WorksConstants.SEARCH_DATE); final List<AppConfigValues> configList = worksService.getAppConfigValue("Works", "CONTRACTOR_STATUS"); final String status = configList.get(0).getValue(); final Criteria criteria = getSession().createCriteria(Contractor.class); if (org.apache.commons.lang.StringUtils.isNotEmpty(contractorCode)) criteria.add(Restrictions.sqlRestriction("lower({alias}.code) like lower(?)", "%" + contractorCode.trim() + "%", StringType.INSTANCE)); if (org.apache.commons.lang.StringUtils.isNotEmpty(contractorName)) criteria.add(Restrictions.sqlRestriction("lower({alias}.name) like lower(?)", "%" + contractorName.trim() + "%", StringType.INSTANCE)); criteria.createAlias("contractorDetails", "detail").createAlias("detail.status", "status"); criteria.add(Restrictions.eq("status.description", status)); if (departmentId != null) criteria.add(Restrictions.eq("detail.department.id", departmentId)); if (gradeId != null) criteria.add(Restrictions.eq("detail.grade.id", gradeId)); if (searchDate != null) criteria.add(Restrictions.le("detail.validity.startDate", searchDate)) .add(Restrictions.or(Restrictions.ge("detail.validity.endDate", searchDate), Restrictions.isNull("detail.validity.endDate"))); criteria.addOrder(Order.asc("name")); criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); criteria.list();/*from ww w. j a va2 s . c om*/ }
From source file:org.etudes.component.app.melete.MeleteLicenseDB.java
License:Apache License
/** * called from backing bean addModulePage to get the license url and license name * @param reqAttr// w w w. ja v a2s.co m * @param allowCmrcl * @param allowMod * @return */ public String[] fetchCcLicenseURL(Boolean reqAttr, Boolean allowCmrcl, Integer allowMod) { String[] licenseInfo = new String[2]; try { Session session = getHibernateUtil().currentSession(); CcLicense Cc = (CcLicense) session.createCriteria(CcLicense.class) .add(Restrictions.sqlRestriction("req_attr=?", reqAttr, Hibernate.BOOLEAN)) .add(Restrictions.sqlRestriction("allow_Cmrcl=?", allowCmrcl, Hibernate.BOOLEAN)) .add(Restrictions.sqlRestriction("allow_Mod=?", allowMod, Hibernate.INTEGER)).list().get(0); licenseInfo[0] = Cc.getUrl(); licenseInfo[1] = Cc.getName(); getHibernateUtil().closeSession(); } catch (Exception ex) { //ex.printStackTrace(); logger.error(ex.toString()); } return licenseInfo; }
From source file:org.geomajas.plugin.deskmanager.security.role.authorization.DeskmanagerAuthorization.java
License:Open Source License
@Override public Criterion getFilterBlueprints() { if (getRole() == Role.ADMINISTRATOR) { return null; } else if (getRole() == Role.DESK_MANAGER) { // You must add an alias for the groups collection or // this/* www . j a v a2 s .c om*/ // won't // work! return Restrictions.and(Restrictions.eq("active", true), Restrictions.eq("groups.id", getTerritory().getId())); } return Restrictions.sqlRestriction("1 = ?", 2, new IntegerType()); }
From source file:org.geomajas.plugin.deskmanager.security.role.authorization.DeskmanagerAuthorization.java
License:Open Source License
@Override public Criterion getFilterLayerModels() { if (getRole() == Role.ADMINISTRATOR) { return null; } else if (getRole() == Role.DESK_MANAGER) { return Restrictions.and(Restrictions.eq("active", true), Restrictions.eq("owner", getTerritory())); }/*from w ww.ja v a 2 s.c o m*/ return Restrictions.sqlRestriction("1 = ?", 2, new IntegerType()); }
From source file:org.geomajas.plugin.deskmanager.security.role.authorization.DeskmanagerAuthorization.java
License:Open Source License
@Override public Criterion getFilterGeodesks() { if (getRole() == Role.ADMINISTRATOR) { return null; } else if (getRole() == Role.DESK_MANAGER) { return Restrictions.eq("owner", getTerritory()); } else {/* w w w. j a v a 2 s . c o m*/ return Restrictions.sqlRestriction("1 = ?", 2, new IntegerType()); } }
From source file:org.glite.security.voms.admin.persistence.dao.hibernate.AuditSearchDAOHibernate.java
License:Apache License
protected Criteria buildCriteriaFromParams(AuditLogSearchParams sp) { Criteria crit = createCriteria();// www . jav a 2 s .c o m crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); if (sp.getFromTime() != null) { crit.add(Restrictions.ge("timestamp", sp.getFromTime())); } if (sp.getToTime() != null) { crit.add(Restrictions.le("timestamp", sp.getToTime())); } if (sp.getFilterString() != null && !sp.getFilterString().trim().equals("")) { if (sp.getFilterType().equals(AuditLogSearchParams.FULL_SEARCH_KEY)) { // Full search is basically search over principal // and audit event data point values String filterString = String.format("%%%s%%", sp.getFilterString()); // This is due to another ugly limitation of Hibernate 3.3 // which does not support criteria queries on embedded // collections // See https://hibernate.atlassian.net/browse/HHH-869 crit.add(Restrictions.disjunction().add(Restrictions.sqlRestriction( "{alias}.event_id in (select ae.event_id from audit_event ae, audit_event_data aed where ae.event_id = aed.event_id and aed.value like ?)", filterString, StringType.INSTANCE)) .add(Restrictions.like("principal", sp.getFilterString().trim(), MatchMode.ANYWHERE))); } else { crit.add(Restrictions.like(sp.getFilterType(), sp.getFilterString().trim(), MatchMode.ANYWHERE)); } } if (sp.getFirstResult() != null) { crit.setFirstResult(sp.getFirstResult()); } if (sp.getMaxResults() != null) { crit.setMaxResults(sp.getMaxResults()); } crit.addOrder(Order.desc("timestamp")); return crit; }
From source file:org.grails.orm.hibernate.query.AbstractHibernateQuery.java
License:Apache License
org.hibernate.criterion.Criterion getRestrictionForFunctionCall(FunctionCallingCriterion criterion,
PersistentEntity entity) {/*from www . ja va2 s . co m*/
org.hibernate.criterion.Criterion sqlRestriction;
SessionFactory sessionFactory = ((IHibernateTemplate) session.getNativeInterface()).getSessionFactory();
String property = criterion.getProperty();
Criterion datastoreCriterion = criterion.getPropertyCriterion();
PersistentProperty pp = entity.getPropertyByName(property);
if (pp == null)
throw new InvalidDataAccessResourceUsageException(
"Cannot execute function defined in query [" + criterion.getFunctionName()
+ "] on non-existent property [" + property + "] of [" + entity.getJavaClass() + "]");
String functionName = criterion.getFunctionName();
Dialect dialect = getDialect(sessionFactory);
SQLFunction sqlFunction = dialect.getFunctions().get(functionName);
if (sqlFunction != null) {
TypeResolver typeResolver = getTypeResolver(sessionFactory);
BasicType basic = typeResolver.basic(pp.getType().getName());
if (basic != null && datastoreCriterion instanceof PropertyCriterion) {
PropertyCriterion pc = (PropertyCriterion) datastoreCriterion;
final org.hibernate.criterion.Criterion hibernateCriterion = getHibernateCriterionAdapter()
.toHibernateCriterion(this, datastoreCriterion, alias);
if (hibernateCriterion instanceof SimpleExpression) {
SimpleExpression expr = (SimpleExpression) hibernateCriterion;
Object op = ReflectionUtils.getField(opField, expr);
PropertyMapping mapping = getEntityPersister(entity.getJavaClass().getName(), sessionFactory);
String[] columns;
if (alias != null) {
columns = mapping.toColumns(alias, property);
} else {
columns = mapping.toColumns(property);
}
String root = render(basic, Arrays.asList(columns), sessionFactory, sqlFunction);
Object value = pc.getValue();
if (value != null) {
sqlRestriction = Restrictions.sqlRestriction(root + op + "?", value,
typeResolver.basic(value.getClass().getName()));
} else {
sqlRestriction = Restrictions.sqlRestriction(root + op + "?", value, basic);
}
} else {
throw new InvalidDataAccessResourceUsageException(
"Unsupported function [" + functionName + "] defined in query for property [" + property
+ "] with type [" + pp.getType() + "]");
}
} else {
throw new InvalidDataAccessResourceUsageException("Unsupported function [" + functionName
+ "] defined in query for property [" + property + "] with type [" + pp.getType() + "]");
}
} else {
throw new InvalidDataAccessResourceUsageException(
"Unsupported function defined in query [" + functionName + "]");
}
return sqlRestriction;
}
From source file:org.jasig.ssp.dao.PersonSearchDao.java
License:Apache License
/** * Search people by the specified terms. * /* w ww . jav a2 s . c o m*/ * @param programStatus * program status filter * @param requireProgramStatus * implicitly <code>true</code> if <code>programStatus</code> is * non-null. Else <code>false</code> allows searching without * requiring a program status; defaults to <code>true</code> * @param outsideCaseload * false allows searches without checking the Coach (advisor) * property; defaults to true * @param searchTerm * Search term that search first and last name and school ID; * required * @param advisor * required if outsideCaseload is not {@link Boolean#FALSE}. * @param sAndP * Sorting and paging parameters * @return List of people that match the specified filters */ public PagingWrapper<Person> searchBy(@NotNull final ProgramStatus programStatus, final Boolean requireProgramStatus, final Boolean outsideCaseload, @NotNull final String searchTerm, final Person advisor, final SortingAndPaging sAndP) { if (StringUtils.isBlank(searchTerm)) { throw new IllegalArgumentException("search term must be specified"); } final Criteria query = createCriteria(); boolean isRequiringProgramStatus = programStatus != null || requireProgramStatus == null || Boolean.TRUE.equals(requireProgramStatus); JoinType programStatusJoinType = isRequiringProgramStatus ? JoinType.INNER_JOIN : JoinType.LEFT_OUTER_JOIN; query.createAlias("programStatuses", "personProgramStatus", programStatusJoinType); if (programStatus != null) { query.add(Restrictions.eq("personProgramStatus.programStatus", programStatus)); } if ((sAndP != null) && sAndP.isFilteredByStatus()) { query.add(Restrictions.isNull("personProgramStatus.expirationDate")); } if (Boolean.FALSE.equals(outsideCaseload)) { query.add(Restrictions.eq("coach", advisor)); } // searchTerm : Can be firstName, lastName, studentId or firstName + ' ' // + lastName final Disjunction terms = Restrictions.disjunction(); final String searchTermLowercase = searchTerm.toLowerCase(Locale.getDefault()); terms.add(Restrictions.ilike("firstName", searchTermLowercase, MatchMode.ANYWHERE)); terms.add(Restrictions.ilike("lastName", searchTermLowercase, MatchMode.ANYWHERE)); terms.add(Restrictions.ilike("schoolId", searchTermLowercase, MatchMode.ANYWHERE)); terms.add(Restrictions.sqlRestriction( "lower({alias}.first_name) " + configService.getDatabaseConcatOperator() + " ' ' " + configService.getDatabaseConcatOperator() + " lower({alias}.last_name) like ? ", searchTermLowercase, new StringType())); query.add(terms); // eager load program status query.setFetchMode("personProgramStatus", FetchMode.JOIN); query.setFetchMode("personProgramStatus.programStatus", FetchMode.JOIN); final PagingWrapper<Object[]> results = processCriteriaWithSortingAndPaging(query, sAndP, true); final List<Person> people = Lists.newArrayList(); for (Object[] personAndProgramStatus : results) { if ((personAndProgramStatus != null) && (personAndProgramStatus.length > 0)) { if (personAndProgramStatus[0] instanceof Person) { people.add((Person) personAndProgramStatus[0]); } else if (personAndProgramStatus[1] instanceof Person) { people.add((Person) personAndProgramStatus[1]); } } } return new PagingWrapper<Person>(results.getResults(), people); }