List of usage examples for org.hibernate.criterion Restrictions sqlRestriction
public static Criterion sqlRestriction(String sql, Object value, Type type)
From source file:es.jpons.persistence.query.CriteriaAllenRelations.java
License:Open Source License
public static Criteria starts(Criteria initializedCriteria, PossibilisticVTP pvp) { initializedCriteria = initializedCriteria.add(Restrictions.or( Restrictions.sqlRestriction("({alias}.startMP + {alias}.startRight ) <= ?", pvp.getStartMP() - pvp.getStartLeft(), LongType.INSTANCE), Restrictions.sqlRestriction("({alias}.startMP - {alias}.startLeft ) >= ?", pvp.getStartMP() + pvp.getStartRight(), LongType.INSTANCE))); return initializedCriteria; }
From source file:es.jpons.persistence.query.CriteriaAllenRelations.java
License:Open Source License
public static Criteria ends(Criteria initializedCriteria, PossibilisticVTP pvp) { initializedCriteria = initializedCriteria.add(Restrictions.or( Restrictions.sqlRestriction("({alias}.endMP + {alias}.endRight ) <= ?", pvp.getEndMP() - pvp.getEndLeft(), LongType.INSTANCE), Restrictions.sqlRestriction("({alias}.endMP - {alias}.endLeft ) >= ?", pvp.getEndMP() + pvp.getEndRight(), LongType.INSTANCE))); return initializedCriteria; }
From source file:fr.gael.dhus.olingo.v1.SQLVisitor.java
License:Open Source License
@Override public Object visitMethod(MethodExpression method_expression, MethodOperator method, List<Object> parameters) { Criterion criterion;/*from w w w . j a v a 2 s .co m*/ switch (method) { // String functions case CONCAT: { criterion = Restrictions.sqlRestriction("CONCAT(?,?)", new Object[] { parameters.get(0), parameters.get(1) }, new Type[] { StandardBasicTypes.STRING, StandardBasicTypes.STRING }); break; } case INDEXOF: { criterion = Restrictions.sqlRestriction("LOCATE(?,?)", new Object[] { parameters.get(0), parameters.get(1) }, new Type[] { StandardBasicTypes.STRING, StandardBasicTypes.STRING }); break; } case LENGTH: { criterion = Restrictions.sqlRestriction("LENGTH(?)", parameters.get(0), StandardBasicTypes.STRING); break; } case SUBSTRING: { criterion = Restrictions.sqlRestriction("SUBSTR(?,?)", new Object[] { parameters.get(0), parameters.get(1) }, new Type[] { StandardBasicTypes.STRING, StandardBasicTypes.STRING }); break; } case TOUPPER: { criterion = Restrictions.sqlRestriction("UPPER(?)", parameters.get(0), StandardBasicTypes.STRING); break; } case TOLOWER: { criterion = Restrictions.sqlRestriction("LOWER(?)", parameters.get(0), StandardBasicTypes.STRING); break; } case TRIM: { criterion = Restrictions.sqlRestriction("TRIM(?)", parameters.get(0), StandardBasicTypes.STRING); break; } case ENDSWITH: case STARTSWITH: { criterion = getCriterionFunction(method, parameters.get(0), parameters.get(1)); break; } case SUBSTRINGOF: { criterion = getCriterionFunction(method, parameters.get(1), parameters.get(0)); break; } // Date functions case DAY: { criterion = Restrictions.sqlRestriction("DAYOFMONTH(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } case HOUR: { criterion = Restrictions.sqlRestriction("HOUR(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } case MINUTE: { criterion = Restrictions.sqlRestriction("MINUTE(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } case MONTH: { criterion = Restrictions.sqlRestriction("MONTH(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } case SECOND: { criterion = Restrictions.sqlRestriction("SECOND(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } case YEAR: { criterion = Restrictions.sqlRestriction("YEAR(?)", parameters.get(0), StandardBasicTypes.TIMESTAMP); break; } // Math functions case CEILING: { criterion = Restrictions.sqlRestriction("CEILING(?)", parameters.get(0), StandardBasicTypes.DOUBLE); break; } case FLOOR: { criterion = Restrictions.sqlRestriction("FLOOR (?)", parameters.get(0), StandardBasicTypes.DOUBLE); break; } case ROUND: { criterion = Restrictions.sqlRestriction("ROUND(?)", parameters.get(0), StandardBasicTypes.DOUBLE); break; } default: throw new UnsupportedOperationException("Unsupported method: " + method.toUriLiteral()); } return criterion; }
From source file:fr.gael.dhus.olingo.v1.SQLVisitor.java
License:Open Source License
private Criterion getCriterionFunction(MethodOperator method, Object... args) { Criterion criterion;/*from w w w.ja v a2s . c om*/ if (args[0] instanceof Member) { String property = ((Member) args[0]).getName(); switch (method) { case ENDSWITH: { String pattern = "%" + args[1]; criterion = Restrictions.like(property, pattern); break; } case STARTSWITH: { String pattern = args[1] + "%"; criterion = Restrictions.like(property, pattern); break; } case SUBSTRINGOF: { String pattern = "%" + args[1] + "%"; criterion = Restrictions.like(property, pattern); break; } default: { throw new UnsupportedOperationException("Unsupported method: " + method.toUriLiteral()); } } } else { Type[] types = { StandardBasicTypes.STRING, StandardBasicTypes.STRING }; switch (method) { case ENDSWITH: { Object[] parameters = { args[0], ("%" + args[1]) }; criterion = Restrictions.sqlRestriction("? LIKE ?", parameters, types); break; } case STARTSWITH: { Object[] parameters = { args[0], (args[1] + "%") }; criterion = Restrictions.sqlRestriction("? LIKE ?", parameters, types); break; } case SUBSTRINGOF: { Object[] parameters = { args[0], ("%" + args[1] + "%") }; criterion = Restrictions.sqlRestriction("? LIKE ?", parameters, types); break; } default: { throw new UnsupportedOperationException("Unsupported method: " + method.toUriLiteral()); } } } return criterion; }
From source file:fsl.ta.toms.roms.dao.impl.ReportDAOImpl.java
@SuppressWarnings("unchecked") @Override//from w ww . jav a2s .c o m public RoadOperationsStatisticsReportBO performanceSaisticsReport( PerformanceStatisticsReportCriteriaBO reportCriteria, String userName, String userRegion, ReportDisplayInformationDAOImpl reportDisplayInformation) { /* Specify search criteria for report */ Criteria criteria = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(RoadOperationDO.class, "roadOp"); /* List of all aliases used */ criteria.createAlias("roadOp.category", "category"); /* _______________________ */ /* Apply filters to search results */ Criterion mainCriteron = Restrictions.or( Restrictions.between("roadOp.scheduledStartDtime", DateUtils.searchDateFormater(reportCriteria.getStartDate(), DateUtils.SEARCHDATETYPE.START), DateUtils.searchDateFormater(reportCriteria.getEndDate(), DateUtils.SEARCHDATETYPE.END)), Restrictions.between("roadOp.scheduledEndDtime", DateUtils.searchDateFormater(reportCriteria.getStartDate(), DateUtils.SEARCHDATETYPE.START), DateUtils.searchDateFormater(reportCriteria.getEndDate(), DateUtils.SEARCHDATETYPE.END))); if (StringUtil.isSet(reportCriteria.getTAOfficeRegion())) { mainCriteron = Restrictions.and(mainCriteron, Restrictions.eq("roadOp.officeLocCode", reportCriteria.getTAOfficeRegion().trim())); } if (StringUtil.isSet(reportCriteria.getOperationCategory())) { mainCriteron = Restrictions.and(mainCriteron, Restrictions.eq("category.categoryId", reportCriteria.getOperationCategory().trim())); } if (reportCriteria.getTeamLeadId() != null && !reportCriteria.getTeamLeadId().isEmpty()) { List<Integer> roadOpIds = this.getListOfRoadOpIdsBasedOnTeamLead(reportCriteria.getTeamLeadId()); if (roadOpIds != null) mainCriteron = Restrictions.and(mainCriteron, Restrictions.in("roadOp.roadOperationId", roadOpIds)); } if (StringUtil.isSet(reportCriteria.getTeamLeadTRN())) { List<Integer> roadOpIds = this.getListOfRoadOpIdsBasedOnTeamLeadTRN(reportCriteria.getTeamLeadTRN()); if (roadOpIds != null) mainCriteron = Restrictions.and(mainCriteron, Restrictions.in("roadOp.roadOperationId", roadOpIds)); } if (StringUtil.isSet(reportCriteria.getRoadOperationName())) { mainCriteron = Restrictions.and(mainCriteron, Restrictions .like("roadOp.operationName", reportCriteria.getRoadOperationName(), MatchMode.ANYWHERE) .ignoreCase()); } if (reportCriteria.getRoadOperationId() != null && reportCriteria.getRoadOperationId() > 0) { mainCriteron = Restrictions.and(mainCriteron, Restrictions.eq("roadOp.roadOperationId", reportCriteria.getRoadOperationId())); } if (StringUtil.isSet(reportCriteria.getTAStaffId()) || StringUtil.isSet(reportCriteria.getTAStaffTRN())) { Criteria criteriaTA = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(TAStaffDO.class, "ta"); criteriaTA.createAlias("ta.person", "taPerson"); if (StringUtil.isSet(reportCriteria.getTAStaffId())) criteriaTA.add(Restrictions.eq("ta.staffId", reportCriteria.getTAStaffId().trim())); if (StringUtil.isSet(reportCriteria.getTAStaffTRN())) criteriaTA.add(Restrictions.eq("taPerson.trnNbr", reportCriteria.getTAStaffTRN().trim())); TAStaffDO taStaff = null; List<TAStaffDO> staffList = criteriaTA.list(); if (!staffList.isEmpty()) taStaff = staffList.get(0); if (taStaff != null) { /* Get a list of all assigned persons for a road operation. */ Criteria criteriaAssignedPersons = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(AssignedPersonDO.class, "assignedP"); criteriaAssignedPersons.add(Restrictions.sqlRestriction("{alias}.person_id = ?", taStaff.getPerson().getPersonId(), Hibernate.INTEGER)); List<Integer> roadOpsWithTAStaff = new ArrayList<Integer>(); for (AssignedPersonDO assignee : (List<AssignedPersonDO>) criteriaAssignedPersons.list()) { roadOpsWithTAStaff .add(assignee.getAssignedPersonKey().getTeam().getRoadOperation().getRoadOperationId()); } mainCriteron = Restrictions.and(mainCriteron, Restrictions.in("roadOp.roadOperationId", roadOpsWithTAStaff)); } else { return null; } } /* ______________________________ */ /*** Check which road operations employ the strategies selected ****/ if (reportCriteria.getStrategyIds() != null && !reportCriteria.getStrategyIds().isEmpty()) { // Criteria criteriaStrategies = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(OperationStrategyDO.class, "opStrat"); // criteriaStrategies.createAlias("opStrat.operationStrategyKey.roadOperation", // "roadOp"); // criteriaStrategies.createAlias("opStrat.operationStrategyKey.strategy", // "strat"); criteriaStrategies.setProjection(Projections .distinct(Projections.property("opStrat.operationStrategyKey.roadOperation.roadOperationId"))); criteriaStrategies.add(Restrictions.in("opStrat.operationStrategyKey.strategy.strategyId", reportCriteria.getStrategyIds())); List<Integer> roadOpWithStratsList = criteriaStrategies.list(); if (roadOpWithStratsList != null && !roadOpWithStratsList.isEmpty()) { mainCriteron = Restrictions.and(mainCriteron, Restrictions.in("roadOp.roadOperationId", roadOpWithStratsList)); } else { return null; } } /* ______________________________________________________________ */ /* * Create Return objects which are going to be filled during report * processing. */ String stringStartDate = ""; String stringEndDate = ""; try { stringStartDate = DateUtils.getFormattedUtilDate(reportCriteria.getStartDate()); stringEndDate = DateUtils.getFormattedUtilDate(reportCriteria.getEndDate()); } catch (Exception exe) { } /* Get report criteria names and descriptions */ reportCriteria .setTAOfficeDescription(this.getTAOfficeRegionDescription(reportCriteria.getTAOfficeRegion())); reportCriteria.setOperationCategoryDescription( this.getOperationCategoryDesc(reportCriteria.getOperationCategory())); if (StringUtil.isSet(reportCriteria.getTAStaffId())) reportCriteria.setTAStaffName(this.getTAStaffName(reportCriteria.getTAStaffId())); else if (StringUtil.isSet(reportCriteria.getTAStaffTRN())) reportCriteria.setTAStaffName(this.getPersonName(reportCriteria.getTAStaffTRN())); if (StringUtil.isSet(reportCriteria.getTeamLeadId())) reportCriteria.setTeamLeadName(this.getTAStaffName(reportCriteria.getTeamLeadId())); else if (StringUtil.isSet(reportCriteria.getTeamLeadTRN())) reportCriteria.setTeamLeadName(this.getPersonName(reportCriteria.getTeamLeadTRN())); if (reportCriteria.getStrategyIds() != null && !reportCriteria.getStrategyIds().isEmpty()) { StringBuilder strategyDescriptions = new StringBuilder(""); for (Integer strategyId : reportCriteria.getStrategyIds()) { StrategyDO strategyDO = this.hibernateTemplate.get(StrategyDO.class, strategyId); if (strategyDO != null) { if (!strategyDescriptions.toString().isEmpty()) strategyDescriptions.append(", "); strategyDescriptions.append(strategyDO.getDescription()); } } reportCriteria.setStrategyDescriptions(strategyDescriptions.toString()); } RoadOperationsStatisticsReportBO roadOpReportStatsOuput = new RoadOperationsStatisticsReportBO(userName, userRegion, reportDisplayInformation.applicationName, reportDisplayInformation.getPerformanceStatisticsReportTitle() + stringStartDate + " TO " + stringEndDate, reportCriteria.getStartDate(), reportCriteria.getEndDate(), reportCriteria.getSearchCriteriaString(), this.getTAOfficeRegionDescription(userRegion)); List<RegionStatisticsBO> regionStats = new ArrayList<RegionStatisticsBO>(); RegionStatisticsBO currentRegionStats = null; OperationSummaryReportCriteriaBO reportCriteriaForRoadOps = null; roadOpReportStatsOuput.setRegionStatistics(regionStats); /* ____________________________________ */ /* Loop through list of road operations and get statistics for persons. */ criteria.add(mainCriteron); criteria.addOrder(Order.asc("roadOp.officeLocCode")); criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List criteriaList = criteria.list(); Iterator iterator = criteriaList.iterator(); while (iterator.hasNext()) { Map map = (Map) iterator.next(); final RoadOperationDO roadOpDO = (RoadOperationDO) map.get("roadOp"); // TAStaffDO teamLead = (TAStaffDO)map.get("teamLead"); if (currentRegionStats == null) { /* Create List of RoadOperationSummaryBO */ reportCriteriaForRoadOps = new OperationSummaryReportCriteriaBO(reportCriteria.getStartDate(), reportCriteria.getEndDate(), new ArrayList<String>() { { add(roadOpDO.getOfficeLocCode()); } }, this.getTeamLeadIdsForRoadOp(roadOpDO.getRoadOperationId()), roadOpDO.getCategory().getCategoryId(), roadOpDO.getRoadOperationId()); currentRegionStats = new RegionStatisticsBO(roadOpDO.getOfficeLocCode(), this.getTAOfficeRegionDescription(roadOpDO.getOfficeLocCode())); currentRegionStats.setRoadOpSummary(this.operationSummaryReport(reportCriteriaForRoadOps, userName, userRegion, reportDisplayInformation).getResults()); List<RoadOperationSummaryResultsBO> roadOpSummary = this.operationSummaryReport( reportCriteriaForRoadOps, userName, userRegion, reportDisplayInformation).getResults(); currentRegionStats.setRoadOpSummary(roadOpSummary); currentRegionStats.setRoadOperationStatistics(new ArrayList<RoadOperationStatisticsBO>()); roadOpReportStatsOuput.getRegionStatistics().add(currentRegionStats); } else if (!currentRegionStats.getOfficeLocCode().equalsIgnoreCase(roadOpDO.getOfficeLocCode())) { reportCriteriaForRoadOps = new OperationSummaryReportCriteriaBO(reportCriteria.getStartDate(), reportCriteria.getEndDate(), new ArrayList<String>() { { add(roadOpDO.getOfficeLocCode()); } }, null, roadOpDO.getCategory().getCategoryId(), null); currentRegionStats = new RegionStatisticsBO(roadOpDO.getOfficeLocCode(), this.getTAOfficeRegionDescription(roadOpDO.getOfficeLocCode())); ; currentRegionStats.setRoadOpSummary(this.operationSummaryReport(reportCriteriaForRoadOps, userName, userRegion, reportDisplayInformation).getResults()); List<RoadOperationSummaryResultsBO> roadOpSummary = this.operationSummaryReport( reportCriteriaForRoadOps, userName, userRegion, reportDisplayInformation).getResults(); currentRegionStats.setRoadOpSummary(roadOpSummary); currentRegionStats.setRoadOperationStatistics(new ArrayList<RoadOperationStatisticsBO>()); roadOpReportStatsOuput.getRegionStatistics().add(currentRegionStats); } RoadOperationStatisticsBO roadOpStats = new RoadOperationStatisticsBO( getListOfTeamLeadNamesBasedOnRoadOpId(roadOpDO.getRoadOperationId()), roadOpDO.getOperationName()); roadOpStats.setITAExaminerSummary(new ArrayList<ITAExaminerStatisticsBO>()); roadOpStats.setJPSummary(new ArrayList<JPStatisticsBO>()); roadOpStats.setPoliceOfficerSummary(new ArrayList<PoliceOfficerStatisticsBO>()); roadOpStats.setTAOfficerSummary(new ArrayList<TAOfficerStatisticsBO>()); /* Get a list of all assigned persons for a road operation. */ Criteria criteriaAssignedPersons = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(AssignedPersonDO.class, "assignedP"); // criteriaAssignedPersons.add(Restrictions.sqlRestriction("{alias}.road_operation_id = ?", // roadOpDO.getRoadOperationId(), Hibernate.INTEGER)); List<Integer> teamIds = this.getTeamIdsForRoadOp(roadOpDO.getRoadOperationId()); if (teamIds != null && teamIds.size() > 0) criteriaAssignedPersons.add(Restrictions.in("assignedPersonKey.team.teamId", teamIds)); if (StringUtil.isSet(reportCriteria.getTAStaffId())) { TAStaffDO taStaff = (TAStaffDO) this.hibernateTemplate.getSessionFactory().getCurrentSession() .get(TAStaffDO.class, reportCriteria.getTAStaffId().trim()); if (taStaff != null) { criteriaAssignedPersons.add(Restrictions.sqlRestriction("{alias}.person_id = ?", taStaff.getPerson().getPersonId(), Hibernate.INTEGER)); } else { return null; } } for (AssignedPersonDO assignedPerson : (List<AssignedPersonDO>) criteriaAssignedPersons.list()) { /* Looping through a list of persons based on road operation id. */ PersonDO person = assignedPerson.getAssignedPersonKey().getPerson(); CDPersonTypeDO personType = assignedPerson.getAssignedPersonKey().getPersonType(); if (personType.getPersonTypeId().toLowerCase().equalsIgnoreCase(Constants.PersonType.JP)) { /* Get Statistics for JP Person */ Criteria criteriaJP = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(JPDO.class); criteriaJP.add(Restrictions.eq("person.personId", person.getPersonId())); JPDO jp = (JPDO) criteriaJP.uniqueResult(); JPStatisticsBO jpStatsBO = new JPStatisticsBO( NameUtil.getName(person.getFirstName(), person.getLastName())/* fullName */, jp.getRegNumber()/* regNumber */, this.summonsCount(roadOpDO.getRoadOperationId(), person.getPersonId(), Constants.PersonType.JP)/* countSummonsSigned */, assignedPerson.getAttended()/* attended */); roadOpStats.getJPSummary().add(jpStatsBO); } else if (personType.getPersonTypeId().equalsIgnoreCase(Constants.PersonType.TA_STAFF)) { /* Get Statistics for TA Staff. */ Criteria criteriaTA = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(TAStaffDO.class); criteriaTA.add(Restrictions.eq("person.personId", person.getPersonId())); //System.out.println("Person ID is " + person.getPersonId()); TAStaffDO ta = (TAStaffDO) criteriaTA.uniqueResult(); TAOfficerStatisticsBO taStatsBO = new TAOfficerStatisticsBO( NameUtil.getName(person.getFirstName(), person.getLastName())/* fullName */, ta.getStaffTypeCode()/* staffType */, this.complianceCount(roadOpDO.getRoadOperationId(), person.getPersonId(), personType.getPersonTypeId())/* countCompliancyChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.MOTOR_VEHICLE, person.getPersonId(), personType.getPersonTypeId())/* countMVChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.DRIVERS_LICENCE, person.getPersonId(), personType.getPersonTypeId())/* countDLChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.BADGE, person.getPersonId(), personType.getPersonTypeId())/* countBadgeChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.CITATION, person.getPersonId(), personType.getPersonTypeId())/* countCitationChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.ROAD_LICENCE, person.getPersonId(), personType.getPersonTypeId())/* countRLChecks */, this.roadCheckTypeCount(roadOpDO.getRoadOperationId(), Constants.RoadCheckType.OTHER, person.getPersonId(), personType.getPersonTypeId())/* countOtherChecks */, this.warningNoticeCount(roadOpDO.getRoadOperationId(), person.getPersonId(), personType.getPersonTypeId())/* countWarningNoticesIssued */, this.summonsCount(roadOpDO.getRoadOperationId(), person.getPersonId(), personType.getPersonTypeId())/* countSummonsIssued */, this.roadCheckOutcomeCount(roadOpDO.getRoadOperationId(), Constants.OutcomeType.VEHICLE_SEIZURE, person.getPersonId(), personType.getPersonTypeId())/* countVehiclesSeized */, assignedPerson.getAttended()/* attended */, this.roadCheckOutcomeCount(roadOpDO.getRoadOperationId(), Constants.OutcomeType.REMOVE_PLATES, person.getPersonId(), personType.getPersonTypeId())/* countPlatesRemoved */, this.roadCheckOutcomeCount(roadOpDO.getRoadOperationId(), Constants.OutcomeType.WARNED_FOR_PROSECUTION, person.getPersonId(), personType.getPersonTypeId())/* warningsForProcecution */, this.roadCheckOutcomeCount(roadOpDO.getRoadOperationId(), Constants.OutcomeType.ALL_IN_ORDER, person.getPersonId(), personType.getPersonTypeId())/* allInOrders */, ta.getStaffId()/* staff id */); roadOpStats.getTAOfficerSummary().add(taStatsBO); } else if (personType.getPersonTypeId().equalsIgnoreCase(Constants.PersonType.ITA_EXAMINER)) { /* Get Statistics for ITA Examiner */ Criteria criteriaITA = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(ITAExaminerDO.class); criteriaITA.add(Restrictions.eq("person.personId", person.getPersonId())); ITAExaminerDO ita = (ITAExaminerDO) criteriaITA.uniqueResult(); ITAExaminerStatisticsBO itaStats = new ITAExaminerStatisticsBO( NameUtil.getName(person.getFirstName(), person.getLastName()), assignedPerson.getAttended(), ita.getExaminerId()); roadOpStats.getITAExaminerSummary().add(itaStats); } else if (personType.getPersonTypeId().equalsIgnoreCase(Constants.PersonType.POLICE_OFFCER)) { /* Get Statistics for Police Officer */ Criteria criteriaITA = this.hibernateTemplate.getSessionFactory().getCurrentSession() .createCriteria(PoliceOfficerDO.class); criteriaITA.add(Restrictions.eq("person.personId", person.getPersonId())); PoliceOfficerDO police = (PoliceOfficerDO) criteriaITA.uniqueResult(); PoliceOfficerStatisticsBO policeStats = new PoliceOfficerStatisticsBO( NameUtil.getName(person.getFirstName(), person.getLastName()), assignedPerson.getAttended(), police.getPolOfficerCompNo()); roadOpStats.getPoliceOfficerSummary().add(policeStats); } } /* ____________________________________ */ currentRegionStats.getRoadOperationStatistics().add(roadOpStats); } /* ________________________________ */ roadOpReportStatsOuput.setRegionStatistics(regionStats); return roadOpReportStatsOuput; }
From source file:gov.nih.nci.cabio.annotations.ArrayAnnotationServiceImpl.java
License:BSD License
public Collection<SNP> getSnpsNearGene(String symbol, Long kbUpstream, Long kbDownstream, String assembly) throws ApplicationException { List params = new ArrayList(); params.add(assembly);/*w w w . j av a 2 s. c o m*/ params.add(symbol); params.add(symbol); params.add(taxon); Collection<GenePhysicalLocation> result = appService.query(new HQLCriteria(GET_SNPS_NEAR_GENE_HQL, QueryUtils.createCountQuery(GET_SNPS_NEAR_GENE_HQL), params)); if (result == null || result.isEmpty()) throw new ApplicationException("No genes found for symbol " + symbol); Collection<GenomeRange> rawRanges = new TreeSet<GenomeRange>(); Long upPad = kbUpstream * 1000; Long downPad = kbDownstream * 1000; Long chromosomeId = null; // construct all padded ranges for (GenePhysicalLocation pl : result) { if (chromosomeId == null) chromosomeId = pl.getChromosome().getId(); rawRanges.add(new GenomeRange(pl.getChromosomalStartPosition() - upPad, pl.getChromosomalEndPosition() + downPad)); } // combine overlapping ranges Collection<GenomeRange> ranges = new ArrayList<GenomeRange>(); GenomeRange last = null; for (GenomeRange gr : rawRanges) { if ((last == null) || (last.getEnd() < gr.getStart() - 1)) { ranges.add(gr); last = gr; } else if (gr.getEnd() > last.getEnd()) { last.setEnd(gr.getEnd()); } } // query for SNPs on the given assembly in the combined ranges DetachedCriteria dc = DetachedCriteria.forClass(SNP.class).createCriteria("physicalLocationCollection") .add(Restrictions.eq("assembly", assembly)); Disjunction or = Restrictions.disjunction(); for (GenomeRange gr : ranges) { or.add(Restrictions.and(Restrictions.ge("chromosomalStartPosition", gr.getStart()), Restrictions.le("chromosomalEndPosition", gr.getEnd()))); } dc.add(or).addOrder(Order.asc("chromosomalStartPosition")) .add(Restrictions.sqlRestriction("{alias}.chromosome_id = ?", chromosomeId, Hibernate.LONG)); List<SNP> results = appService.query(dc); return results; }
From source file:love.sola.netsupport.api.stuff.ToolsCheck.java
License:Open Source License
private Object query(HttpServletRequest req, WxSession session) { int status = Integer.valueOf(getParameterWithDefault(req.getParameter("status"), "0")); Date after = getDay(getParameterAsDate(req.getParameter("after"), getToday())); Date before = getDay(getParameterAsDate(req.getParameter("before"), getToday())); before = DateUtils.addDays(before, 1); int block = Integer.valueOf(getParameterWithDefault(req.getParameter("block"), "0")); try (Session s = SQLCore.sf.openSession()) { Criteria query = s.createCriteria(love.sola.netsupport.pojo.ToolsCheck.class); query.add(Restrictions.sqlRestriction("{alias}.status & ? = ?", new Object[] { status, status }, new Type[] { IntegerType.INSTANCE, IntegerType.INSTANCE })); query.add(Restrictions.between("checkTime", after, before)); if (block != 0) query.add(Restrictions.eq("block", block)); return query.list(); }/* w ww. j av a 2 s . c o m*/ }
From source file:net.webpasswordsafe.server.dao.PasswordDAOHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public List<Password> findPasswordByFuzzySearch(String query, User user, boolean activeOnly, Collection<Tag> tags, Match tagMatch) { //kludge to not use ilike on text column if MSSQL boolean isMSSQL = ((SessionFactoryImpl) getSessionFactory()).getDialect().toString().contains("SQLServer"); Criteria crit = getSession().createCriteria(getPersistentClass()); crit.setFetchMode("tags", FetchMode.JOIN); crit.add(Restrictions.or(// www. j a v a 2 s .c o m Restrictions.or(Restrictions.ilike("name", query, MatchMode.ANYWHERE), Restrictions.ilike("username", query, MatchMode.ANYWHERE)), isMSSQL ? Restrictions.like("notes", query, MatchMode.ANYWHERE) : Restrictions.ilike("notes", query, MatchMode.ANYWHERE))); if (activeOnly) { crit.add(Restrictions.eq("active", true)); } Criterion tagsCriterion = null; for (Tag tag : tags) { Criterion tc = Restrictions.sqlRestriction( "? in (select tag_id from password_tags where password_id = {alias}.id)", tag.getId(), StandardBasicTypes.LONG); if (null == tagsCriterion) { tagsCriterion = tc; } else { tagsCriterion = tagMatch.equals(Match.AND) ? Restrictions.and(tagsCriterion, tc) : Restrictions.or(tagsCriterion, tc); } } if (tagsCriterion != null) crit.add(tagsCriterion); crit.createAlias("permissions", "pm"); crit.add(Restrictions.in("pm.accessLevel", new String[] { AccessLevel.READ.name(), AccessLevel.WRITE.name(), AccessLevel.GRANT.name() })); if (!authorizer.isAuthorized(user, Function.BYPASS_PASSWORD_PERMISSIONS.name())) { DetachedCriteria groupQuery = DetachedCriteria.forClass(Group.class); groupQuery.setProjection(Projections.id()); groupQuery.createCriteria("users", "u").add(Restrictions.eq("u.id", user.getId())); crit.add(Restrictions.or(Restrictions.eq("pm.subject", user), Subqueries.propertyIn("pm.subject", groupQuery))); } crit.addOrder(Order.asc("name")); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); return crit.list(); }
From source file:org.babyfish.hibernate.collection.spi.persistence.MapBasePersistence.java
License:Open Source License
@SuppressWarnings("unchecked") public Ref<V> visionallyRead(K key) { Arguments.mustNotBeNull("key", key); String role = this.getNonNullRole(); SessionImplementor session = this.getSession(); if (session == null || !session.isOpen() || !session.isConnected()) { return null; }/*from w ww.j a va 2 s .c o m*/ SessionFactoryImplementor sessionFactory = session.getFactory(); QueryableCollection collection = (QueryableCollection) sessionFactory.getCollectionPersister(role); EntityPersister elementPersister = collection.getElementPersister(); String[] indexNames = collection.getIndexColumnNames(); if (indexNames == null || indexNames[0] == null) { indexNames = collection.getIndexFormulas(); } CriteriaImpl criteria = new CriteriaImpl(elementPersister.getEntityName(), session); //ownerKey, not ownerId Object ownerKey = collection.getCollectionType().getKeyOfOwner(this.getOwner(), session); //In Hibernate, isOneToMany means that there is no middle table //The @OneToMany of JPA with middle table is consider as many-to-many in Hibernate if (sessionFactory.getCollectionPersister(role).isOneToMany()) { String[] joinOwnerColumns = collection.getKeyColumnNames(); StringBuilder sqlBuilder = new StringBuilder(); for (int i = 0; i < joinOwnerColumns.length; i++) { if (i != 0) { sqlBuilder.append(" and "); } sqlBuilder.append("{alias}.").append(joinOwnerColumns[i]).append(" = ?"); } criteria.add(Restrictions.sqlRestriction(sqlBuilder.toString(), ownerKey, collection.getKeyType())); sqlBuilder = new StringBuilder(); for (int i = 0; i < indexNames.length; i++) { if (i != 0) { sqlBuilder.append(" and "); } sqlBuilder.append("{alias}.").append(indexNames[i]).append(" = ?"); } criteria.add(Restrictions.sqlRestriction(sqlBuilder.toString(), key, collection.getIndexType())); } else { String lhsPropertyName = collection.getCollectionType().getLHSPropertyName(); int lhsPropertyIndex = -1; if (lhsPropertyName != null) { String[] propertyNames = collection.getOwnerEntityPersister().getPropertyNames(); for (int i = propertyNames.length - 1; i >= 0; i--) { if (propertyNames[i].equals(lhsPropertyName)) { lhsPropertyIndex = i; break; } } } String[] lhsColumnNames = JoinHelper.getLHSColumnNames(collection.getCollectionType(), lhsPropertyIndex, (OuterJoinLoadable) elementPersister, sessionFactory); String[] joinElementColumnNames = collection.getElementColumnNames(); String[] joinOwnerColumnNames = collection.getKeyColumnNames(); StringBuilder subQueryBuilder = new StringBuilder(); subQueryBuilder.append("exists(select * from ").append(collection.getTableName()).append(" as ") .append(MIDDLE_TABLE_ALIAS).append(" where "); for (int i = 0; i < joinElementColumnNames.length; i++) { if (i != 0) { subQueryBuilder.append(" and "); } subQueryBuilder.append("{alias}.").append(lhsColumnNames[i]).append(" = ") .append(MIDDLE_TABLE_ALIAS).append(".").append(joinElementColumnNames[i]); } for (int i = 0; i < joinOwnerColumnNames.length; i++) { subQueryBuilder.append(" and ").append(MIDDLE_TABLE_ALIAS).append('.') .append(joinOwnerColumnNames[i]).append(" = ?"); } for (int i = 0; i < indexNames.length; i++) { subQueryBuilder.append(" and ").append(MIDDLE_TABLE_ALIAS).append('.').append(indexNames[i]) .append(" = ?"); } subQueryBuilder.append(')'); criteria.add(Restrictions.sqlRestriction(subQueryBuilder.toString(), new Object[] { ownerKey, key }, new Type[] { collection.getKeyType(), collection.getIndexType() })); } FlushMode oldFlushMode = session.getFlushMode(); session.setFlushMode(FlushMode.MANUAL); try { return new Ref<V>((V) criteria.uniqueResult()); } finally { session.setFlushMode(oldFlushMode); } }
From source file:org.babyfish.hibernate.collection.spi.persistence.SetBasePersistence.java
License:Open Source License
/** * This method is used to replace /*w ww .ja v a2 s . c o m*/ * "org.hibernate.collection.AbstractPersistentCollection#readElementExistence(Object element)" * @param element The example element to be read * @return The ref or readed element * <ul> * <li>NonNull: Read successfully, check the value of ref to check the read value is null or not</li> * <li>Null: Read failed</li> * </ul> */ @SuppressWarnings("unchecked") public Ref<E> visionallyRead(E element) { Arguments.mustNotBeNull("element", element); String role = this.getNonNullRole(); SessionImplementor session = this.getSession(); if (session == null || !session.isOpen() || !session.isConnected()) { return null; } SessionFactoryImplementor sessionFactory = session.getFactory(); QueryableCollection collection = (QueryableCollection) sessionFactory.getCollectionPersister(role); EntityPersister elementPersister = collection.getElementPersister(); Object elementId = elementPersister.getIdentifier(element, this.getSession()); if (elementId == null) { return new Ref<>(); } if (elementPersister.getEntityMetamodel().getIdentifierProperty().getUnsavedValue() .isUnsaved((Serializable) elementId)) { return new Ref<>(); } CriteriaImpl criteria = new CriteriaImpl(elementPersister.getEntityName(), session); /* * Add the condition of element. */ criteria.add(Restrictions.idEq(elementId)); //ownerKey, not ownerId Object ownerKey = collection.getCollectionType().getKeyOfOwner(this.getOwner(), session); //In Hibernate, isOneToMany means that there is no middle table //The @OneToMany of JPA with middle table is consider as many-to-many in Hibernate if (sessionFactory.getCollectionPersister(role).isOneToMany()) { String[] joinOwnerColumns = collection.getKeyColumnNames(); StringBuilder sqlBuilder = new StringBuilder(); for (int i = 0; i < joinOwnerColumns.length; i++) { if (i != 0) { sqlBuilder.append(" and "); } sqlBuilder.append("{alias}.").append(joinOwnerColumns[i]).append(" = ?"); } criteria.add(Restrictions.sqlRestriction(sqlBuilder.toString(), ownerKey, collection.getKeyType())); } else { String lhsPropertyName = collection.getCollectionType().getLHSPropertyName(); int lhsPropertyIndex = -1; if (lhsPropertyName != null) { String[] propertyNames = collection.getOwnerEntityPersister().getPropertyNames(); for (int i = propertyNames.length - 1; i >= 0; i--) { if (propertyNames[i].equals(lhsPropertyName)) { lhsPropertyIndex = i; break; } } } String[] lhsColumnNames = JoinHelper.getLHSColumnNames(collection.getCollectionType(), lhsPropertyIndex, (OuterJoinLoadable) elementPersister, sessionFactory); String[] joinElementColumnNames = collection.getElementColumnNames(); String[] joinOwnerColumnNames = collection.getKeyColumnNames(); StringBuilder subQueryBuilder = new StringBuilder(); subQueryBuilder.append("exists(select * from ").append(collection.getTableName()).append(" as ") .append(MIDDLE_TABLE_ALIAS).append(" where "); for (int i = 0; i < joinElementColumnNames.length; i++) { if (i != 0) { subQueryBuilder.append(" and "); } subQueryBuilder.append("{alias}.").append(lhsColumnNames[i]).append(" = ") .append(MIDDLE_TABLE_ALIAS).append('.').append(joinElementColumnNames[i]); } for (int i = 0; i < joinOwnerColumnNames.length; i++) { subQueryBuilder.append(" and ").append(MIDDLE_TABLE_ALIAS).append(".") .append(joinOwnerColumnNames[i]).append(" = ?"); } subQueryBuilder.append(')'); criteria.add( Restrictions.sqlRestriction(subQueryBuilder.toString(), ownerKey, collection.getKeyType())); } FlushMode oldFlushMode = session.getFlushMode(); session.setFlushMode(FlushMode.MANUAL); try { return new Ref<>((E) criteria.uniqueResult()); } finally { session.setFlushMode(oldFlushMode); } }