List of usage examples for org.hibernate.criterion Restrictions sqlRestriction
public static Criterion sqlRestriction(String sql, Object value, Type type)
From source file:com.cosylab.cdb.jdal.HibernateWDALImpl.java
License:Open Source License
protected Criterion getRegularExpressionRestriction(String columnName, String re) { if (forceInMemory) { return Restrictions.sqlRestriction("REGEXP_MATCHES(" + columnName + ", ?)", re, org.hibernate.Hibernate.STRING); //HSQLDB } else {/* w w w . ja v a 2 s.c o m*/ //return Restrictions.sqlRestriction(columnName+" rlike ?", re, org.hibernate.Hibernate.STRING); //MySQL return Restrictions.sqlRestriction("REGEXP_LIKE(" + columnName + ", ?)", re, org.hibernate.Hibernate.STRING); //Oracle } }
From source file:com.dungnv.streetfood.business.TagsBusiness.java
License:Open Source License
@Override public Map<String, String> getMapTagsByName(List<String> listTagName) { Map<String, String> map = new HashMap<>(); Type[] types = new Type[listTagName.size()]; String[] param = new String[listTagName.size()]; Arrays.fill(types, StringType.INSTANCE); Criteria cri = gettDAO().getSession().createCriteria(Tags.class); cri.add(Restrictions.sqlRestriction( " lower(" + Tags.NAME + ") in " + QueryUtil.getParameterHolderString(listTagName.size())// , StringUtils.trimStringToNewList(listTagName, Boolean.TRUE).toArray(param)// , types));// w w w. j a va 2 s .c om List<Tags> listTags = cri.list(); for (Tags tags : listTags) { map.put(tags.getName().toLowerCase(), tags.getId().toString()); } for (String tags : listTagName) { if (!map.containsKey(tags.trim().toLowerCase())) { map.put(tags.trim().toLowerCase(), null); } } return map; }
From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java
@Override public List<EmpData> getAllDataByParamWithDetail(List<Department> deptId, List<GolonganJabatan> golJabId, String[] empTypeName, List<Integer> listAge, List<Integer> listJoinDate, List<String> listNik, int firstResult, int maxResults, Order order) { List<Long> listDepartment = new ArrayList<Long>(); List<Long> listGolJab = new ArrayList<Long>(); for (Department department : deptId) { listDepartment.add(department.getId()); }//from w w w . j a v a2 s. c o m for (GolonganJabatan golonganJabatan : golJabId) { listGolJab.add(golonganJabatan.getId()); } final org.hibernate.type.Type[] typeJoinDate = new org.hibernate.type.Type[listJoinDate.size()]; Arrays.fill(typeJoinDate, org.hibernate.type.StandardBasicTypes.INTEGER); final org.hibernate.type.Type[] typeAge = new org.hibernate.type.Type[listAge.size()]; Arrays.fill(typeAge, org.hibernate.type.StandardBasicTypes.INTEGER); final StringBuilder joinDateList = new StringBuilder(); final StringBuilder ageList = new StringBuilder(); for (int i = 0; i < listJoinDate.size(); i++) { if (i > 0) { joinDateList.append(","); } joinDateList.append("?"); } for (int i = 0; i < listAge.size(); i++) { if (i > 0) { ageList.append(","); } ageList.append("?"); } Criteria criteria = getCurrentSession().createCriteria(getEntityClass()); /** * automatically get relations of jabatanByJabatanId, department, * company don't create alias for that entity, or will get error : * duplicate association path */ criteria = this.addJoinRelationsOfCompanyId(criteria, HrmUserInfoUtil.getCompanyId()); criteria.add(Restrictions.not(Restrictions.eq("status", HRMConstant.EMP_TERMINATION))); // criteria.createAlias("bioData", "bioData", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("golonganJabatan", "golonganJabatan", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("employeeType", "employeeType", JoinType.LEFT_OUTER_JOIN); // criteria.createCriteria("bioData", "bio", JoinType.LEFT_OUTER_JOIN); if (!deptId.isEmpty()) { criteria.add(Restrictions.in("department.id", listDepartment)); } if (!golJabId.isEmpty()) { criteria.add(Restrictions.in("golonganJabatan.id", listGolJab)); } if (empTypeName.length != 0) { criteria.add(Restrictions.in("employeeType.name", empTypeName)); } if (listJoinDate.get(0) != 0) { criteria.add(Restrictions .sqlRestriction("DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS({alias}.join_date)), '%Y')+0 in (" + joinDateList.toString() + ")", listJoinDate.toArray(), typeJoinDate)); } if (listAge.get(0) != 0) { // criteria.createAlias("bioData", "bioData", JoinType.LEFT_OUTER_JOIN); Criteria criteriaBiodata = criteria.createCriteria("bioData", JoinType.LEFT_OUTER_JOIN); // criteria.createCriteria("bioData", JoinType.LEFT_OUTER_JOIN); criteriaBiodata.add(Restrictions.sqlRestriction( "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS({alias}.date_of_birth)), '%Y')+0 in (" + ageList.toString() + ")", listAge.toArray(), typeAge)); } if (!listNik.isEmpty()) { criteria.add(Restrictions.in("nik", listNik)); } criteria.addOrder(order); criteria.setFirstResult(firstResult); criteria.setMaxResults(maxResults); return criteria.list(); }
From source file:com.inkubator.hrm.dao.impl.EmpDataDaoImpl.java
@Override public Long getTotalByParamWithDetail(List<Department> deptId, List<GolonganJabatan> golJabId, String[] empTypeName, List<Integer> listAge, List<Integer> listJoinDate, List<String> listNik) { List<Long> listDepartment = new ArrayList<Long>(); List<Long> listGolJab = new ArrayList<Long>(); for (Department department : deptId) { listDepartment.add(department.getId()); }/*from w ww . j ava2 s .c om*/ for (GolonganJabatan golonganJabatan : golJabId) { listGolJab.add(golonganJabatan.getId()); } final org.hibernate.type.Type[] typeJoinDate = new org.hibernate.type.Type[listJoinDate.size()]; Arrays.fill(typeJoinDate, org.hibernate.type.StandardBasicTypes.INTEGER); final org.hibernate.type.Type[] typeAge = new org.hibernate.type.Type[listAge.size()]; Arrays.fill(typeAge, org.hibernate.type.StandardBasicTypes.INTEGER); final StringBuilder joinDateList = new StringBuilder(); final StringBuilder ageList = new StringBuilder(); for (int i = 0; i < listJoinDate.size(); i++) { if (i > 0) { joinDateList.append(","); } joinDateList.append("?"); } for (int i = 0; i < listAge.size(); i++) { if (i > 0) { ageList.append(","); } ageList.append("?"); } Criteria criteria = getCurrentSession().createCriteria(getEntityClass()); /** * automatically get relations of jabatanByJabatanId, department, * company don't create alias for that entity, or will get error : * duplicate association path */ criteria = this.addJoinRelationsOfCompanyId(criteria, HrmUserInfoUtil.getCompanyId()); criteria.add(Restrictions.not(Restrictions.eq("status", HRMConstant.EMP_TERMINATION))); // criteria.createAlias("bioData", "bioData", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("golonganJabatan", "golonganJabatan", JoinType.LEFT_OUTER_JOIN); criteria.createAlias("employeeType", "employeeType", JoinType.LEFT_OUTER_JOIN); // criteria.createCriteria("bioData", "bio", JoinType.LEFT_OUTER_JOIN); if (!deptId.isEmpty()) { criteria.add(Restrictions.in("department.id", listDepartment)); } if (!golJabId.isEmpty()) { criteria.add(Restrictions.in("golonganJabatan.id", listGolJab)); } if (empTypeName.length != 0) { criteria.add(Restrictions.in("employeeType.name", empTypeName)); } if (listJoinDate.get(0) != 0) { criteria.add(Restrictions .sqlRestriction("DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS({alias}.join_date)), '%Y')+0 in (" + joinDateList.toString() + ")", listJoinDate.toArray(), typeJoinDate)); } if (listAge.get(0) != 0) { Criteria criteriaBiodata = criteria.createCriteria("bioData", JoinType.LEFT_OUTER_JOIN); // criteria.createCriteria("bioData", JoinType.LEFT_OUTER_JOIN); criteriaBiodata.add(Restrictions.sqlRestriction( "DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS({alias}.date_of_birth)), '%Y')+0 in (" + ageList.toString() + ")", listAge.toArray(), typeAge)); } if (!listNik.isEmpty()) { criteria.add(Restrictions.in("nik", listNik)); } return (Long) criteria.setProjection(Projections.rowCount()).uniqueResult(); }
From source file:com.mercatis.lighthouse3.persistence.environment.hibernate.ProcessTaskRegistryImplementation.java
License:Apache License
@Override protected Criteria entityToCriteria(Session session, ProcessTask entityTemplate) { Criteria criteria = super.entityToCriteria(session, entityTemplate); if (entityTemplate.getLongName() != null) criteria.add(Restrictions.eq("longName", entityTemplate.getLongName())); if (entityTemplate.getVersion() != null) criteria.add(Restrictions.eq("version", entityTemplate.getVersion())); if (entityTemplate.getDescription() != null) criteria.add(Restrictions.eq("description", entityTemplate.getDescription())); if (entityTemplate.getContact() != null) criteria.add(Restrictions.eq("contact", entityTemplate.getContact())); if (entityTemplate.getContactEmail() != null) criteria.add(Restrictions.eq("contactEmail", entityTemplate.getContactEmail())); for (Entry<String, String> swimlane : entityTemplate.getSwimlaneData().entrySet()) { criteria.add(Restrictions.sqlRestriction( "exists (select psl.* from PROCESS_SWIMLANES psl where {alias}.STC_ID = psl.PRO_ID and psl.SWIMLANE = ? and psl.PRO_CODE = ?)", new String[] { swimlane.getValue(), swimlane.getKey() }, new Type[] { StringType.INSTANCE, StringType.INSTANCE })); }/*from ww w. j ava2s .c o m*/ for (Tuple<String, String> transition : entityTemplate.getTransitionData()) { criteria.add(Restrictions.sqlRestriction( "exists (select pst.* from PROCESS_TRANSITIONS pst where {alias}.STC_ID = pst.PRO_ID and pst.FROM_PRO_CODE = ? and pst.TO_PRO_CODE = ?)", new String[] { transition.getA(), transition.getB() }, new Type[] { StringType.INSTANCE, StringType.INSTANCE })); } criteria.setCacheable(true); return criteria; }
From source file:com.mercatis.lighthouse3.persistence.events.hibernate.EventRegistryImplementation.java
License:Apache License
/** * This method generates criteria for a given event template that also * contain an ordering clause on the date of occurrence. * /*w ww . ja v a 2 s .c om*/ * @param session * the Hibernate session to use for criteria generation * @param entityTemplate * the template for which to generate the criteria * @param descending * <code>true</code> if descending order is wanted (the default) * or <code>false</code> for ascending order. * @return */ public Criteria generateOrderingCriteria(Session session, Event entityTemplate, boolean descending) { Criteria criteria = super.entityToCriteria(session, entityTemplate); if (entityTemplate.getContext() != null) { if (!Ranger.isEnumerationRange(entityTemplate.getContext())) criteria.add(Restrictions.eq("context", entityTemplate.getContext())); else criteria.add(Restrictions.in("context", Ranger.castToEnumerationRange(entityTemplate.getContext()).getEnumeration())); } if (entityTemplate.getCode() != null) { if (!Ranger.isEnumerationRange(entityTemplate.getCode())) criteria.add(Restrictions.eq("code", entityTemplate.getCode())); else criteria.add(Restrictions.in("code", Ranger.castToEnumerationRange(entityTemplate.getCode()).getEnumeration())); } if (entityTemplate.getLevel() != null) { if (!Ranger.isEnumerationRange(entityTemplate.getLevel())) criteria.add(Restrictions.eq("level", entityTemplate.getLevel())); else criteria.add(Restrictions.in("level", Ranger.castToEnumerationRange(entityTemplate.getLevel()).getEnumeration())); } if (entityTemplate.getMachineOfOrigin() != null) criteria.add(Restrictions.eq("machineOfOrigin", entityTemplate.getMachineOfOrigin())); if (entityTemplate.getMessage() != null) { criteria.add(Restrictions.ilike("message", "%" + entityTemplate.getMessage() + "%")); } if (entityTemplate.getStackTrace() != null) { if (this.unitOfWork.getSqlDialect() instanceof org.hibernate.dialect.MySQL5InnoDBDialect) criteria.add(Restrictions.sqlRestriction("match ({alias}.STACK_TRACE) against (?)", entityTemplate.getStackTrace(), StringType.INSTANCE)); else criteria.add(Restrictions.ilike("stackTrace", "%" + entityTemplate.getStackTrace() + "%")); } if (entityTemplate.getDateOfOccurrence() != null) { if (!Ranger.isIntervalRange(entityTemplate.getDateOfOccurrence())) { criteria.add(Restrictions.eq("dateOfOccurrence", entityTemplate.getDateOfOccurrence())); } else { Date lowerBound = Ranger.castToIntervalRange(entityTemplate.getDateOfOccurrence()).getLowerBound(); Date upperBound = Ranger.castToIntervalRange(entityTemplate.getDateOfOccurrence()).getUpperBound(); if ((lowerBound == null) && (upperBound != null)) criteria.add(Restrictions.le("dateOfOccurrence", upperBound)); else if ((lowerBound != null) && (upperBound == null)) criteria.add(Restrictions.ge("dateOfOccurrence", lowerBound)); else if ((lowerBound != null) && (upperBound != null)) { criteria.add(Restrictions.le("dateOfOccurrence", upperBound)); criteria.add(Restrictions.ge("dateOfOccurrence", lowerBound)); } } } if (!entityTemplate.getTransactionIds().isEmpty()) { Set<Criterion> transactionRestrictions = new HashSet<Criterion>(); for (String transactionId : entityTemplate.getTransactionIds()) transactionRestrictions.add(Restrictions.sqlRestriction( "exists (select lti.* from EVENT_TRANSACTION_IDS lti where {alias}.EVT_ID = lti.EVT_ID and lti.TRANSACTION_ID = ?)", transactionId, StringType.INSTANCE)); if (transactionRestrictions.size() == 1) { criteria.add(transactionRestrictions.iterator().next()); } else { Iterator<Criterion> restrictions = transactionRestrictions.iterator(); Criterion orCriterion = restrictions.next(); while (restrictions.hasNext()) { orCriterion = Restrictions.or(orCriterion, restrictions.next()); } criteria.add(orCriterion); } } for (String tag : entityTemplate.getTags()) criteria.add(Restrictions.sqlRestriction( "exists (select lt.* from EVENT_TAGS lt where {alias}.EVT_ID = lt.EVT_ID and lt.TAG = ?)", tag, StringType.INSTANCE)); for (String udf : entityTemplate.getUdfs().keySet()) { Object value = entityTemplate.getUdf(udf); if (udf.equals("eventRESTResourceLimitRestriction")) { criteria.setMaxResults((Integer) value); break; } String columnName = ""; Type valueType = StringType.INSTANCE; if (value instanceof Boolean) { columnName = "BOOLEAN_VAL"; valueType = BooleanType.INSTANCE; } if (value instanceof Integer) { columnName = "INTEGER_VAL"; valueType = IntegerType.INSTANCE; } if (value instanceof Long) { columnName = "LONG_VAL"; valueType = LongType.INSTANCE; } if (value instanceof Float) { columnName = "FLOAT_VAL"; valueType = FloatType.INSTANCE; } if (value instanceof Double) { columnName = "DOUBLE_VAL"; valueType = DoubleType.INSTANCE; } if (value instanceof Date) { columnName = "DATE_VAL"; valueType = DateType.INSTANCE; } if (value instanceof byte[]) { columnName = "BINARY_VAL"; valueType = BlobType.INSTANCE; } if (value instanceof String) { columnName = "STRING_VAL"; valueType = StringType.INSTANCE; } criteria.add(Restrictions.sqlRestriction( "exists (select lu.* from EVENT_UDFS lu where {alias}.EVT_ID = lu.EVT_ID and lu.UDF = ? and lu." + columnName + " = ?)", new Object[] { udf, value }, new Type[] { StringType.INSTANCE, valueType })); } if (descending) criteria.addOrder(Order.desc("dateOfOccurrence")); else criteria.addOrder(Order.asc("dateOfOccurrence")); return criteria; }
From source file:com.mercatis.lighthouse3.persistence.status.hibernate.StatusRegistryImplementation.java
License:Apache License
@SuppressWarnings("unchecked") public List<Status> getStatusForCarrier(StatusCarrier carrier) { List<Status> result = this.unitOfWork.getCurrentSession().createCriteria(Status.class) .add(Restrictions.sqlRestriction("{alias}.STA_CONTEXT_ID = ?", carrier.getId(), LongType.INSTANCE)) .list();/* www .j a v a 2 s . c o m*/ return result; }
From source file:com.netxforge.oss2.model.OnmsRestrictions.java
License:Open Source License
/** * Performs an iplike match on the ipAddr column of the current table. * * @param value iplike match/*from ww w .java2 s . c om*/ * @return SQL restriction for this iplike match */ public static Criterion ipLike(String value) { return Restrictions.sqlRestriction("iplike({alias}.ipAddr, ?)", value, STRING_TYPE); }
From source file:com.painiu.core.dao.hibernate.PhotoDAOHibernate.java
License:Open Source License
static Criteria buildPhotoCriteria(final Session session, User user, String[] tags, boolean taggedAll, String text, Relation relation, boolean count) { Criteria criteria = session.createCriteria(Photo.class); if (user != null) { criteria.add(Restrictions.eq("user", user)); if (relation != null) { criteria.add(/*from ww w .jav a2 s .co m*/ Restrictions.sqlRestriction(" {alias}.privacy & ? > 0", relation, UserTypes.relation())); } } else { criteria.add( Restrictions.sqlRestriction(" {alias}.privacy & ? > 0", Relation.NONE, UserTypes.relation())); criteria.setFetchMode("user", FetchMode.JOIN); } //if (user == null && group == null) { if (user == null) { Disjunction disjState = Restrictions.disjunction(); disjState.add(Restrictions.eq("state", Photo.State.USER_POPULAR)); disjState.add(Restrictions.eq("state", Photo.State.USER_COMMENDATORY)); disjState.add(Restrictions.eq("state", Photo.State.USER_SENIOR)); criteria.add(disjState); } //if (album != null) { // criteria.createAlias("albumPhotos", "ap"); // criteria.add( Restrictions.eq("ap.album", album) ); //} //if (group != null) { // criteria.createAlias("groupPhotos", "gp"); // criteria.add( Restrictions.eq("gp.group", group) ); //} if ((tags != null && tags.length > 0) || text != null) { Criteria subCriteria = criteria.createCriteria("photoTags", "tags"); if (tags != null && tags.length > 0) { if (taggedAll) { Conjunction conj = Restrictions.conjunction(); for (int i = 0; i < tags.length; i++) { conj.add(Restrictions.eq("tagName", tags[i])); } subCriteria.add(conj); } else { Disjunction disj = Restrictions.disjunction(); for (int i = 0; i < tags.length; i++) { disj.add(Restrictions.eq("tagName", tags[i])); } subCriteria.add(disj); } } if (text != null) { Disjunction disj = Restrictions.disjunction(); disj.add(Restrictions.like("title", text, MatchMode.ANYWHERE)); disj.add(Restrictions.like("description", text, MatchMode.ANYWHERE)); disj.add(Restrictions.eq("tags.tagName", text)); criteria.add(disj); } } // TODO order parameters if (!count) { /*if (album != null) { criteria.addOrder(Order.asc("ap.position")); } else*/ /*if (group != null) { criteria.addOrder(Order.asc("gp.position")); } else {*/ criteria.addOrder(Order.desc("timestamp")); //} } // distinct ? if ((tags != null && tags.length > 1) || text != null) { ProjectionList proj = Projections.projectionList(); proj.add(Projections.property("id")).add(Projections.property("title")) .add(Projections.property("width")).add(Projections.property("height")) .add(Projections.property("address.host")).add(Projections.property("address.dir")) .add(Projections.property("address.filename")).add(Projections.property("address.secret")) .add(Projections.property("address.username")).add(Projections.property("address.fileKey")); if (user == null) { criteria.createAlias("user", "user"); proj.add(Projections.property("user.id")).add(Projections.property("user.username")) .add(Projections.property("user.nickname")).add(Projections.property("user.buddyIcon.host")) .add(Projections.property("user.buddyIcon.dir")) .add(Projections.property("user.buddyIcon.filename")) .add(Projections.property("user.buddyIcon.username")) .add(Projections.property("user.buddyIcon.fileKey")); } criteria.setProjection(Projections.distinct(proj)); criteria.setResultTransformer(new PhotoBeanResultTransformer()); } return criteria; }
From source file:com.quix.aia.cn.imo.mapper.AnnouncementMaintenance.java
License:Open Source License
/** *<p>Method is used to get searched announcement details.</p> * @param req Servlet Request Parameter * @return ArrayList of announcement//from ww w. ja v a2 s .c o m */ public ArrayList getSearchedAnnouncements(HttpServletRequest req) { Session session = null; String subject = req.getParameter(SUBJECT_PARAM); String month = req.getParameter(MONTH_PARAM); String year = req.getParameter(YEAR_PARAM); String bu = req.getParameter(BU_PARAM); String district = req.getParameter(DISTRICT_PARAM); String city = req.getParameter(CITY_PARAM); String ssc = req.getParameter(SSC_PARAM); String office = req.getParameter("office"); String branch = req.getParameter("branch"); ArrayList announcementList = new ArrayList(); try { session = HibernateFactory.openSession(); Criteria crit = session.createCriteria(Announcement.class); if (subject != null && subject.length() > 0) crit.add(Restrictions.like("subject", subject, MatchMode.ANYWHERE)); if (month != null) crit.add(Restrictions.sqlRestriction("MONTH(PUBLISHED_DATE)=?", Integer.parseInt(month), Hibernate.INTEGER)); if (year != null) crit.add(Restrictions.sqlRestriction("YEAR(PUBLISHED_DATE)=?", Integer.parseInt(year), Hibernate.INTEGER)); if (bu != null && Integer.parseInt(bu) != 0) crit.add(Restrictions.eq("buCode", Integer.parseInt(bu))); if (district != null && Integer.parseInt(district) != 0) crit.add(Restrictions.eq("district", Integer.parseInt(district))); if (branch != null && Integer.parseInt(branch) != 0) crit.add(Restrictions.eq("branchCode", Integer.parseInt(branch))); if (city != null && !city.equals("0")) crit.add(Restrictions.eq("cityCode", city)); if (office != null && !office.equals("0")) crit.add(Restrictions.eq("officeCode", office)); if (ssc != null && !ssc.equals("0")) crit.add(Restrictions.eq("sscCode", ssc)); crit.add(Restrictions.eq("status", true)); announcementList = (ArrayList) crit.list(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("AnnouncementMaintenance", Level.SEVERE + "", errors.toString()); } finally { try { HibernateFactory.close(session); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } return announcementList; }