private void generate(Date date){ Parameter<Date> p1 = baseQuery.getParameter("date1", Date.class); baseQuery.setParameter(p1, date); List<Object[]> results = baseQuery.getResultList(); for (Object[] result : results){ DailySales hourlySales = new DailySales(); hourlySales.setRegionId((Region)result[0]); //hourlySales.setEmployeeId(null); //hourlySales.setDealerId(null); hourlySales.setProductId((Product)result[1]); hourlySales.setStateProvCd((String)result[2]); hourlySales.setQuantity(((Long)result[3]).intValue()); Calendar cal = Calendar.getInstance(); cal.clear(); cal.set(Calendar.YEAR, (Integer) result[4]); cal.set(Calendar.MONTH, (Integer) result[5]-1); cal.set(Calendar.DAY_OF_MONTH, (Integer) result[6]); hourlySales.setDate(cal.getTime()); em.persist(hourlySales); } removeStaleEntries(date); }
@GET @Produces({"application/xml", "application/json"}) @Path("/region/{regionId}") public List<TransitCumulativeSales> findAllRegion(@PathParam("regionId") Integer regionId) { Query baseRangeQuery = em.createQuery(REGION_RANGE_QUERY); Parameter<Integer> p1 = baseRangeQuery.getParameter("regionId", Integer.class); baseRangeQuery.setParameter(p1, regionId); List<TransitCumulativeSales> result = new ArrayList<TransitCumulativeSales>(); List<Object[]> resultList = baseRangeQuery.getResultList(); for (int i=0; i < resultList.size(); i++){ Object o[] = resultList.get(i); TransitCumulativeSales t = new TransitCumulativeSales(); t.setStartDailySalesId((Integer)o[0]); t.setEndDailySalesId((Integer)o[1]); t.setCost((Double)o[2]); t.setSales((Double) o[3]); t.setDate((Date)o[4]); result.add(t); } return result; }
private HashMap<String, Long> runBaseQuery(Date date){ Calendar cal = Calendar.getInstance(); long DIFF, TIME = System.currentTimeMillis(), START_TIME = System.currentTimeMillis(); Query baseQuery = em.createQuery(BASE_QUERY); HashMap<String, Long> result = new HashMap<String, Long>(); { cal.setTime(date); int dayMin = cal.getActualMinimum(Calendar.DAY_OF_MONTH); int dayMax = cal.getActualMaximum(Calendar.DAY_OF_MONTH); cal.set(Calendar.DAY_OF_MONTH, dayMin); Parameter<Date> p1 = baseQuery.getParameter("oldStartDate", Date.class); baseQuery.setParameter(p1, cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, dayMax); Parameter<Date> p2 = baseQuery.getParameter("oldEndDate", Date.class); baseQuery.setParameter(p2, cal.getTime()); List<Object[]> resultList = baseQuery.getResultList(); DIFF = (System.currentTimeMillis() - TIME); System.out.println(" Q TIME = "+DIFF+"ms"); for (int i=0; i < resultList.size(); i++){ Object o[] = resultList.get(i); result.put((String)o[1],(Long)o[0]); } } return result; }
@Override public <T> Query setParameter(Parameter<T> par, T value) { query = query.setParameter(par, value); // try { // int position = -1; // String name = PARAMETER_NAME_PREFIX; // ParameterType ptype = ParameterType.JPA_NAMED_PARAMETER; // if (par.getPosition() != null) { // position = par.getPosition(); // name = name + position; // ptype = ParameterType.JPA_INDEXED_PARAMETER; // } else if (par.getName() != null) { // name = par.getName(); // } // ResourceParameter param = new ResourceParameter(name, // Object.class.getName(), value, ptype, position); // parameters.put(param.getName(), param); // } catch (IOException e) { // log.error(e.getMessage(), e); // throw new IllegalArgumentException(e.getMessage(), e); // } return this; }
@Override public Query setParameter(Parameter<Calendar> par, Calendar value, TemporalType ttype) { query = query.setParameter(par, value, ttype); // try { // int position = -1; // String name = PARAMETER_NAME_PREFIX; // ParameterType ptype = ParameterType.JPA_NAMED_CALENDAR_PARAMETER; // if (par.getPosition() != null) { // position = par.getPosition(); // name = name + position; // ptype = ParameterType.JPA_INDEXED_CALENDAR_PARAMETER; // } else if (par.getName() != null) { // name = par.getName(); // } // ResourceParameter param = new ResourceParameter(name, ttype.name(), // value, ptype, position); // parameters.put(param.getName(), param); // } catch (IOException e) { // log.error(e.getMessage(), e); // throw new IllegalArgumentException(e.getMessage(), e); // } return this; }
@Override public Query setParameter(Parameter<Date> par, Date value, TemporalType ttype) { query = query.setParameter(par, value, ttype); // try { // int position = -1; // String name = PARAMETER_NAME_PREFIX; // ParameterType ptype = ParameterType.JPA_NAMED_DATE_PARAMETER; // if (par.getPosition() != null) { // position = par.getPosition(); // name = name + position; // ptype = ParameterType.JPA_INDEXED_DATE_PARAMETER; // } else if (par.getName() != null) { // name = par.getName(); // } // ResourceParameter param = new ResourceParameter(name, ttype.name(), // value, ptype, position); // parameters.put(param.getName(), param); // } catch (IOException e) { // log.error(e.getMessage(), e); // throw new IllegalArgumentException(e.getMessage(), e); // } return this; }
private static Object[] getPositionalParameters(Query jpaQuery) { if (jpaQuery.getParameters() == null) return null; int max = 0; List<Integer> positions = new ArrayList<>(); for (Parameter parameter : jpaQuery.getParameters()) { if (parameter.getPosition() != null) { positions.add(parameter.getPosition()); if (parameter.getPosition() > max) { max = parameter.getPosition(); } } } if (positions.isEmpty()) return null; Object[] positionalParameters = new Object[max]; positions.forEach(position -> positionalParameters[position - 1] = jpaQuery.getParameterValue(position)); return positionalParameters; }
@Override @Transactional(readOnly = true, propagation = Propagation.REQUIRED) public <T> List<T> runLikeQuery(Class<T> type, Map<String, Object> likeParam, Map.Entry<String, String> orderBy, EntityManager em) { String query = StringUtils.buildLikeQuery(type.getSimpleName(), likeParam, orderBy); TypedQuery<T> queryObj = em.createQuery(query, type); // 给参数赋值 Set<Parameter<?>> parmSet = queryObj.getParameters(); for ( Parameter parm : parmSet) { String parmName = parm.getName(); Object parmValue = likeParam.get(parmName); queryObj.setParameter(parmName, parmValue); } // 执行查询 List<T> entityList = queryObj //.setFirstResult(firstResult) //.setMaxResults(CollectionUtils.determineCapacity(capacity)) .getResultList(); return entityList; }
@Override @Transactional(readOnly = true, propagation = Propagation.REQUIRED) public <T> List<T> runAccurateQuery(Class<T> type, Map<String, Object> likeParam, Map.Entry<String, String> orderBy, EntityManager em) { String query = StringUtils.buildQuery("obj", type.getSimpleName(), likeParam, orderBy); TypedQuery<T> queryObj = em.createQuery(query, type); // 给参数赋值 Set<Parameter<?>> parmSet = queryObj.getParameters(); for ( Parameter parm : parmSet) { String parmName = parm.getName(); Object parmValue = likeParam.get(parmName); queryObj.setParameter(parmName, parmValue); } // 执行查询 List<T> entityList = queryObj //.setFirstResult(firstResult) //.setMaxResults(CollectionUtils.determineCapacity(capacity)) .getResultList(); return entityList; }
public static void bind(Query query, Map<String, ?> namedArgs, Object... args) { if (args != null) { for (int i = 0; i < args.length; i++) { query.setParameter(i + 1, args[i]); } } if (namedArgs != null) { for (Parameter<?> param : query.getParameters()) { String name = param.getName(); if (U.notEmpty(name)) { U.must(namedArgs.containsKey(name), "A named argument wasn't specified for the named JPQL parameter: %s", name); query.setParameter(name, Cls.convert(namedArgs.get(name), param.getParameterType())); } } } }
@Test public void shouldSetParameters() { Filter filter = new Filter(); filter.addCondition("testKey", Operator.eq, "testValue"); filter.addCondition("testKey1", Operator.eq, "testValue1"); Path path = mock(Path.class); when(path.getJavaType()).thenReturn(String.class); filter.getConditions().get(0).setPath(path); filter.getConditions().get(1).setPath(path); Query query = mock(Query.class); Parameter param = mock(Parameter.class); when(param.getParameterType()).thenReturn(String.class); when(query.getParameter(anyString())).thenReturn(param); filter.setParameters(query); verify(query).setParameter("testKey", "testValue"); verify(query).setParameter("testKey1", "testValue1"); }
private void removeStaleEntries(Date date){ Parameter<Date> p1 = removeQuery.getParameter("date1", Date.class); removeQuery.setParameter(p1, date); for (SalesOrder so : removeQuery.getResultList()){ for (SalesOrderLine sol : so.getSalesOrderLineCollection()) em.remove(sol); em.remove(so); } }
@GET @Path("/region/{from}/{to}") @Produces({"application/xml", "application/json"}) public List<RegionTransitCumulativeSales> findRegionRange(@PathParam("from") String from, @PathParam("to") String to) throws ParseException { System.out.println("START findRegionRange (from="+from+" , to="+to+")"); long DIFF, TIME = System.currentTimeMillis(), START_TIME = System.currentTimeMillis(); Query q = getEntityManager().createQuery(REGION_SUM_QUERY); Parameter<Integer> p1 = q.getParameter("startId", Integer.class); q.setParameter(p1, Integer.parseInt(from)); Parameter<Integer> p2 = q.getParameter("endId", Integer.class); q.setParameter(p2, Integer.parseInt(to)); List<RegionTransitCumulativeSales> result = new ArrayList<RegionTransitCumulativeSales>(); List<Object[]> resultList = q.getResultList(); for (int i=0; i < resultList.size(); i++){ Object o[] = resultList.get(i); RegionTransitCumulativeSales t = new RegionTransitCumulativeSales(); t.setCost((Double)o[0]); t.setSales((Double) o[1]); t.setUnits((Long) o[2]); t.setRegion((Region) o[3]); result.add(t); } DIFF = (System.currentTimeMillis() - START_TIME); System.out.println(" TOTAL TIME = "+DIFF+"ms"); return result; }
@GET @Path("/state/{from}/{to}/{regionId}") @Produces({"application/xml", "application/json"}) public List<StateTransitCumulativeSales> findStateRange(@PathParam("from") String from, @PathParam("to") String to, @PathParam("regionId") Integer regionId) { System.out.println("START findRegionRange (from="+from+" , to="+to+")"); long DIFF, TIME = System.currentTimeMillis(), START_TIME = System.currentTimeMillis(); Query q = getEntityManager().createQuery(STATE_SUM_QUERY); Parameter<Integer> p1 = q.getParameter("startId", Integer.class); q.setParameter(p1, Integer.parseInt(from)); Parameter<Integer> p2 = q.getParameter("endId", Integer.class); q.setParameter(p2, Integer.parseInt(to)); Parameter<Integer> p3 = q.getParameter("regionId", Integer.class); q.setParameter(p3, regionId); List<StateTransitCumulativeSales> result = new ArrayList<StateTransitCumulativeSales>(); List<Object[]> resultList = q.getResultList(); for (int i=0; i < resultList.size(); i++){ Object o[] = resultList.get(i); StateTransitCumulativeSales t = new StateTransitCumulativeSales(); t.setCost((Double)o[0]); t.setSales((Double) o[1]); t.setUnits((Long) o[2]); t.setState((String) o[3]); result.add(t); } DIFF = (System.currentTimeMillis() - START_TIME); System.out.println(" TOTAL TIME = "+DIFF+"ms"); return result; }
@GET @Path("/international/{international}") @Produces({"application/xml", "application/json"}) public List<Region> findInternational(@PathParam("international") Integer international) { Query q = getEntityManager().createNamedQuery("Region.findByInternational"); Parameter<Integer> p = q.getParameter("international", Integer.class); q.setParameter(p, international); return q.getResultList(); }
@GET @Path("/date/{from}") @Produces({"application/xml", "application/json"}) public List<LiveSalesList> findFrom(@PathParam("from") Integer from) { Query q = getEntityManager().createNamedQuery("LiveSalesList.findFromOrderLineId"); Parameter<Integer> p = q.getParameter("orderLineId", Integer.class); q.setParameter(p, from); return q.getResultList(); }
private HashMap<String, Long> runProductTypeQuery(Date date1, Integer productTypeId){ long DIFF, TIME = System.currentTimeMillis(), START_TIME = System.currentTimeMillis(); Query baseQuery = em.createQuery(BASE_TYPE_QUERY); Calendar cal = Calendar.getInstance(); HashMap<String, Long> result = new HashMap<String, Long>(); { cal.setTime(date1); int dayMin = cal.getActualMinimum(Calendar.DAY_OF_MONTH); int dayMax = cal.getActualMaximum(Calendar.DAY_OF_MONTH); cal.set(Calendar.DAY_OF_MONTH, dayMin); Parameter<Date> p1 = baseQuery.getParameter("oldStartDate", Date.class); baseQuery.setParameter(p1, cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, dayMax); Parameter<Date> p2 = baseQuery.getParameter("oldEndDate", Date.class); baseQuery.setParameter(p2, cal.getTime()); Parameter<Integer> p3 = baseQuery.getParameter("productTypeId", Integer.class); baseQuery.setParameter(p3, productTypeId); List<Object[]> resultList = baseQuery.getResultList(); DIFF = (System.currentTimeMillis() - TIME); System.out.println(" Q TIME = "+DIFF+"ms"); for (int i=0; i < resultList.size(); i++){ Object o[] = resultList.get(i); result.put((String)o[1],(Long)o[0]); } } return result; }
private void setQueryParameters(Query query, SearchParameters sp) { // add default parameter if specified in the named query for (Parameter<?> p : query.getParameters()) { if (NAMED_PARAMETER_CURRENT_USER_ID.equals(p.getName())) { query.setParameter(NAMED_PARAMETER_CURRENT_USER_ID, UserContext.getId()); } else if (NAMED_PARAMETER_NOW.equals(p.getName())) { query.setParameter(NAMED_PARAMETER_NOW, Calendar.getInstance().getTime()); } } // add parameters for the named query for (String paramName : sp.getNamedQueryParameters().keySet()) { query.setParameter(paramName, sp.getNamedQueryParameter(paramName)); } }
private <C extends CommonAbstractCriteria, Q extends Query> Q createQuery(Q query, FilterResult<C> filterResult) { Q secureQuery = (Q)new SecureQuery(query, null, // TODO how to extract this? filterResult.getSelectedPaths(), super.getFlushMode()); if (filterResult.getParameters() != null && filterResult instanceof CriteriaFilterResult) { CriteriaFilterResult<C> criteriaResult = (CriteriaFilterResult<C>)filterResult; for (Parameter<?> parameter: criteriaResult.getCriteriaParameters()) { Object value = filterResult.getParameters().get(parameter.getName()); secureQuery.setParameter((Parameter<Object>)parameter, value); } } return secureQuery; }
/** * {@inheritDoc} */ public boolean visit(JpqlNamedInputParameter node, CriteriaHolder query) { Parameter<?> parameter = builder.parameter(String.class, node.jjtGetChild(0).getValue()); query.setValue(parameter); query.addParameter(parameter); return true; }
/** * Sets the query bind named parameters */ public void setQueryParameters(final javax.persistence.Query query, final Object parameters) { if (parameters != null) { if (parameters instanceof Map<?, ?>) { for (final Parameter<?> param : query.getParameters()) { final Map<?, ?> map = (Map<?, ?>) parameters; final Object value = map.get(param.getName()); if (value instanceof Collection<?>) { final Collection<Object> values = new ArrayList<>(((Collection<?>) value).size()); for (final Object object : (Collection<?>) value) { if (object instanceof EntityReference) { values.add(fetchDao.fetch((Entity) object)); } else { values.add(object); } } query.setParameter(param.getName(), values); } else if (value instanceof EntityReference) { query.setParameter(param.getName(), fetchDao.fetch((Entity) value)); } else { query.setParameter(param.getName(), value); } } } else { query.setParameter(1, parameters); } } }
private void setQueryParameters(final Query query, final SearchParameters sp) { // add default parameter if specified in the named query for (Parameter<?> p : query.getParameters()) { if (NAMED_PARAMETER_NOW.equals(p.getName())) { query.setParameter(NAMED_PARAMETER_NOW, Calendar.getInstance().getTime()); } } // add parameters for the named query for (String paramName : sp.getNamedQueryParameters().keySet()) { query.setParameter(paramName, sp.getNamedQueryParameter(paramName)); } }
/** * JPA规范方法 {@inheritDoc} */ public <T> NativeQuery<X> setParameter(Parameter<T> param, T value) { if (param.getPosition() != null) { setParameter(param.getPosition(), value); } else if (StringUtils.isNotEmpty(param.getName())) { setParameter(param.getName(), value); } return this; }
/** * JPA规范方法 {@inheritDoc} */ public Set<Parameter<?>> getParameters() { Set<Parameter<?>> result = new HashSet<Parameter<?>>(); for (ParameterMetadata jp : config.getParams(this.db).values()) { result.add(jp.param); } return result; }
/** * JPA规范方法 {@inheritDoc} * * @throws NoSuchElementException * 该参数未在查询语句中定义 */ public Parameter<?> getParameter(String name) { ParameterMetadata param = config.getParams(db).get(name); if (param == null) { throw new NoSuchElementException(name); } return param.param; }
/** * JPA规范方法 {@inheritDoc} * * @throws NoSuchElementException * 该参数未在查询语句中定义 */ public <X> Parameter<X> getParameter(String name, Class<X> type) { ParameterMetadata param = config.getParams(db).get(name); if (param == null || param.param.getParameterType() != type) { throw new NoSuchElementException(name); } return param.param; }
/** * JPA规范方法，获得指定的参数 {@inheritDoc} * * @throws NoSuchElementException * 该参数未在查询语句中定义 */ public Parameter<?> getParameter(int position) { ParameterMetadata param = config.getParams(db).get(position); if (param == null) { throw new NoSuchElementException(String.valueOf(position)); } return param.param; }
/** * JPA规范方法，获得指定的参数 {@inheritDoc} * * @throws NoSuchElementException * 该参数未在查询语句中定义 */ public <X> Parameter<X> getParameter(int position, Class<X> type) { ParameterMetadata param = config.getParams(db).get(position); if (param == null || param.param.getParameterType() != type) { throw new NoSuchElementException(String.valueOf(position)); } return param.param; }
/** * JPA规范方法，得到参数的值 {@inheritDoc} */ public <T> T getParameterValue(Parameter<T> param) { if (param.getPosition() != null && param.getPosition() > -1) { return (T) getParameterValue(param.getPosition()); } else { return (T) getParameterValue(param.getName()); } }
/** * @see javax.persistence.Query#setParameter(javax.persistence.Parameter, * java.util.Calendar, javax.persistence.TemporalType) */ @Override public JpaBasicQueryPO setParameter(final Parameter<Calendar> param, final Calendar value, final TemporalType temporalType) { getQuery().setParameter(param, value, temporalType); return this; }
