List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java
public boolean validateCollectCharges(String headerId, String unitssId, String fileId) throws Exception { SQLQuery query = null; if (!fileId.isEmpty()) { query = getCurrentSession().createSQLQuery("select if( count(*)>0 ,true ,false) as result from " + " lcl_bl_ac ac where ac.file_number_id =:fileId and ac.ar_bill_to_party='A'"); query.setLong("fileId", Long.parseLong(fileId)); } else {//from w w w.j a v a 2s. com List<Long> actualDrList = new ExportUnitQueryUtils().getAllPickedCargoBkg(Long.parseLong(headerId), Long.parseLong(unitssId)); query = getCurrentSession().createSQLQuery("select if( count(*)>0 ,true ,false) as result from " + " lcl_bl_ac ac where ac.file_number_id in(:fileList) and ac.ar_bill_to_party='A'"); query.setParameterList("fileList", actualDrList); } return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult(); }
From source file:com.mothsoft.alexis.dao.DocumentDaoImpl.java
License:Apache License
@SuppressWarnings("unchecked") public Graph getRelatedTerms(final String queryString, final Long userId, final int howMany) { final StopWatch stopWatch = new StopWatch(); stopWatch.start();//from w ww .ja va2 s .c o m final FullTextQuery fullTextQuery = this.buildFullTextQuery(queryString, userId, NO_DATE, NO_DATE, false, DocumentState.MATCHED_TO_TOPICS, FullTextQuery.ID); // find the specified number of terms from the most recent 100 documents // that match the query final Sort sort = new Sort(new SortField("creationDate", SortField.LONG, true)); fullTextQuery.setSort(sort); fullTextQuery.setFirstResult(0); fullTextQuery.setMaxResults(100); final List<Long> documentIds = new ArrayList<Long>(100); final List<Long> termIds = new ArrayList<Long>(100); final List<Object[]> results = fullTextQuery.list(); for (final Object[] ith : results) { final Long id = (Long) ith[0]; documentIds.add(id); } final Map<String, Node> nodes = new LinkedHashMap<String, Node>(); final Node root = new Node(queryString, Boolean.TRUE); nodes.put(queryString, root); final Map<String, Edge> edges = new HashMap<String, Edge>(); if (!documentIds.isEmpty()) { final Session session = (Session) this.em.getDelegate(); final org.hibernate.SQLQuery termsQuery = session.createSQLQuery("SELECT term.id " + " FROM document_term dt INNER JOIN term on term.id = dt.term_id " + " WHERE dt.document_id IN (:documentIds) GROUP BY term.id ORDER BY SUM(dt.tf_idf) DESC"); termsQuery.setParameterList("documentIds", documentIds); termsQuery.setMaxResults(100); termIds.addAll((List<Long>) termsQuery.list()); } if (!documentIds.isEmpty() && !termIds.isEmpty()) { final Session session = (Session) this.em.getDelegate(); final org.hibernate.SQLQuery associationsQuery = session.createSQLQuery( "SELECT CONCAT(a.term_value) term_a_value, CONCAT(b.term_value) term_b_value, SUM(da.association_weight) sum_weight " + " FROM document_association da " + " INNER JOIN term a ON da.term_a_id = a.id " + " AND a.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) " + " AND length(a.term_value) > 2 " + " INNER JOIN term b ON da.term_b_id = b.id " + " AND b.part_of_speech NOT IN (1, 3, 18, 19, 25, 39, 40) " + " AND length(b.term_value) > 2 " + " WHERE da.document_id IN (:documentIds) AND (da.term_a_id IN (:termIds) OR da.term_b_id IN (:termIds)) " + " GROUP BY a.id, b.id ORDER BY sum_weight DESC"); associationsQuery.setParameterList("documentIds", documentIds); associationsQuery.setParameterList("termIds", termIds); associationsQuery.setMaxResults(howMany); final List<Object[]> relatedTermsResults = associationsQuery.list(); final Set<String> aNodeKeys = new HashSet<String>(); final Set<String> bNodeKeys = new HashSet<String>(); for (final Object[] ith : relatedTermsResults) { final String a = (String) ith[0]; final String b = (String) ith[1]; if (!nodes.containsKey(a)) { final Node node = new Node(a); nodes.put(a, node); } if (!nodes.containsKey(b)) { final Node node = new Node(b); nodes.put(b, node); } if (a.equals(b)) { continue; } final String edgeKey = a + "||" + b; final String edgeKeyInverse = b + "||" + a; if (!edges.containsKey(edgeKey) && !edges.containsKey(edgeKeyInverse)) { final Node nodeA = nodes.get(a); final Node nodeB = nodes.get(b); aNodeKeys.add(a); bNodeKeys.add(b); final Edge edge = new Edge(nodeA, nodeB); edges.put(edgeKey, edge); } } // "orphan" handling, any b that is not also an a needs an edge from // root final Set<String> orphanKeys = new HashSet<String>(); orphanKeys.addAll(bNodeKeys); orphanKeys.removeAll(aNodeKeys); for (final String orphanKey : orphanKeys) { final Node orphan = nodes.get(orphanKey); final Edge orphanToParent = new Edge(root, orphan); edges.put(root.getName() + "||" + orphan.getName(), orphanToParent); } } final List<Node> nodeList = new ArrayList<Node>(nodes.size()); // keep root as first element nodes.remove(root.getName()); nodeList.add(root); nodeList.addAll(nodes.values()); final Graph graph = new Graph(nodeList, new ArrayList<Edge>(edges.values())); stopWatch.stop(); logger.info("Related terms search took: " + stopWatch.toString()); return graph; }
From source file:com.sapienter.jbilling.server.metafields.db.MetaFieldDAS.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Integer> getByFieldType(Integer entityId, MetaFieldType[] types) { if (null == entityId || null == types || types.length == 0) { throw new IllegalArgumentException("entity and types must be defined"); }/*from ww w. ja v a 2 s . c o m*/ String strTypes[] = toStringArray(types); SQLQuery query = getSession().createSQLQuery(getByFieldTypes); query.setParameter("entity", entityId); query.setParameterList("types", strTypes); return query.list(); }
From source file:com.sapienter.jbilling.server.metafields.db.MetaFieldDAS.java
License:Open Source License
public List<Integer> findByValueAndField(DataType type, Object value, Boolean sensitive, List<Integer> fields) { if (null == type || null == value || null == fields) { throw new IllegalArgumentException("arguments type/value/fields can not be null"); }// w w w. j a va2 s .c om StringBuilder queryBuilder = getFindByValueQueryBuilder(type, value, sensitive); queryBuilder.append(" and meta_field_name_id in (:fields)"); SQLQuery query = getSession().createSQLQuery(queryBuilder.toString()); query.setParameterList("fields", fields); return query.list(); }
From source file:com.sapienter.jbilling.server.metafields.db.MetaFieldDAS.java
License:Open Source License
public List<Integer> getValuesByCustomerAndFields(Integer customerId, List<Integer> fields) { if (null == customerId || null == fields || fields.size() == 0) { throw new IllegalArgumentException(" customer and fields can not be null"); }//from www . jav a 2s .co m SQLQuery query = getSession().createSQLQuery(getValuesByCustomerFields); query.setParameter("customer", customerId); query.setParameterList("fields", fields); query.setDate("startDate", new Date()); return query.list(); }
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
@SuppressWarnings({ "unchecked" })
public List<Modification> getModificationsForPipelineRange(final String pipelineName, final Integer fromCounter,
final Integer toCounter) {
return (List<Modification>) getHibernateTemplate().execute((HibernateCallback) session -> {
final List<Long> fromInclusiveModificationList = fromInclusiveModificationsForPipelineRange(session,
pipelineName, fromCounter, toCounter);
final Set<Long> fromModifications = new TreeSet<>(
fromInclusiveModificationsForPipelineRange(session, pipelineName, fromCounter, fromCounter));
final Set<Long> fromExclusiveModificationList = new HashSet<>();
for (Long modification : fromInclusiveModificationList) {
if (fromModifications.contains(modification)) {
fromModifications.remove(modification);
} else {
fromExclusiveModificationList.add(modification);
}//ww w . j a v a 2 s . co m
}
SQLQuery query = session.createSQLQuery(
"SELECT * FROM modifications WHERE id IN (:ids) ORDER BY materialId ASC, id DESC");
query.addEntity(Modification.class);
query.setParameterList("ids", fromExclusiveModificationList.isEmpty() ? fromInclusiveModificationList
: fromExclusiveModificationList);
return query.list();
});
}
From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java
License:Apache License
private List<Long> fromInclusiveModificationsForPipelineRange(Session session, String pipelineName, Integer fromCounter, Integer toCounter) { String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName, fromCounter, toCounter);//from w w w . ja v a 2s . c o m SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql); final List ids = pipelineIdsQuery.list(); if (ids.isEmpty()) { return new ArrayList<>(); } String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max" + " FROM modifications mods1 " + " INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId " + " WHERE pmr.pipelineId IN (:ids) " + " GROUP BY mods1.materialId"; SQLQuery query = session .createSQLQuery("SELECT mods.id " + " FROM modifications mods" + " INNER JOIN (" + minMaxQuery + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max" + " ORDER BY mods.materialId ASC, mods.id DESC"); query.addScalar("id", new LongType()); query.setParameterList("ids", ids); return query.list(); }
From source file:com.viettel.logistic.wms.dao.GoodsDAO.java
License:Open Source License
@Transactional public ResultDTO synchListGoods(List<GoodsDTO> lstGoods, Session session) { ResultDTO result = new ResultDTO(); String message = ParamUtils.SUCCESS; result.setMessage(ParamUtils.SUCCESS); StringBuffer sql = new StringBuffer(); sql.append(" UPDATE Goods a "); sql.append(// ww w . java2s . com " SET a.name =?, a.status=?, a.goods_group=?, a.unit_type=?, a.is_serial=? ,a.is_serial_strip=?, a.origin_price=to_number(?), a.origin_size=?, a.weight=?, a.volume_origin=?, a.volume_real=?, a.description=?, a.goods_type=? "); sql.append(" WHERE a.code IN (:idx0) AND a.cust_id IN (:idx1)"); SQLQuery query = getSession().createSQLQuery(sql.toString()); for (GoodsDTO lstGood : lstGoods) { List params = new ArrayList(); ArrayList<String> arrCode = new ArrayList<>(); ArrayList<Integer> arrName = new ArrayList<>(); arrCode.add(lstGood.getCode()); arrName.add(Integer.parseInt(lstGood.getCustId())); params.add(lstGood.getName()); params.add(lstGood.getStatus()); params.add(lstGood.getGoodsGroup()); params.add(lstGood.getUnitType()); params.add(lstGood.getIsSerial()); params.add(lstGood.getIsSerialStrip()); params.add(DataUtil.getStringNullOrZero(lstGood.getOriginPrice())); params.add(DataUtil.getStringNullOrZero(lstGood.getOriginSize())); params.add(DataUtil.getStringNullOrZero(lstGood.getWeight())); params.add(DataUtil.getStringNullOrZero(lstGood.getVolumeOrigin())); params.add(DataUtil.getStringNullOrZero(lstGood.getVolumeReal())); params.add(DataUtil.getStringNullOrZero(lstGood.getDescription())); params.add(lstGood.getGoodsType()); for (int idx = 0; idx < params.size(); idx++) { query.setParameter(idx, params.get(idx)); } query.setParameterList("idx0", arrCode); query.setParameterList("idx1", arrName); try { int i = query.executeUpdate(); if (i == 0) { List paramsInsert = new ArrayList(); StringBuffer sqlInsert = new StringBuffer(); SQLQuery queryInsert; sqlInsert.append( " INSERT INTO Goods (goods_id,cust_id,code,name,status,goods_group,unit_type,is_serial,is_serial_strip,origin_price,origin_size,weight,volume_origin,volume_real,description,goods_type) "); sqlInsert.append( " VALUES (GOODS_SEQ.nextval,TO_NUMBER(?),?,?,?,?,?,?,?,TO_NUMBER(?),?,?,?,?,?,?) "); paramsInsert.clear(); paramsInsert.add(lstGood.getCustId()); paramsInsert.add(lstGood.getCode()); paramsInsert.add(lstGood.getName()); paramsInsert.add(lstGood.getStatus()); paramsInsert.add(lstGood.getGoodsGroup()); paramsInsert.add(lstGood.getUnitType()); paramsInsert.add(lstGood.getIsSerial()); paramsInsert.add(lstGood.getIsSerialStrip()); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getOriginPrice())); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getOriginSize())); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getWeight())); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getVolumeOrigin())); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getVolumeReal())); paramsInsert.add(DataUtil.getStringNullOrZero(lstGood.getDescription())); paramsInsert.add(lstGood.getGoodsType()); queryInsert = getSession().createSQLQuery(sqlInsert.toString()); for (int idx = 0; idx < paramsInsert.size(); idx++) { queryInsert.setParameter(idx, paramsInsert.get(idx)); } try { int is = queryInsert.executeUpdate(); } catch (Exception e) { e.printStackTrace(); message = ParamUtils.FAIL; result.setMessage(message); return result; } } } catch (Exception e) { e.printStackTrace(); message = ParamUtils.FAIL; result.setMessage(message); return result; } } result.setMessage(message); return result; }
From source file:com.viettel.logistic.wms.dao.MapStaffGoodsDAO.java
License:Open Source License
public List<MapStaffGoodsDTO> getListStaffByGoods(String codeList, String custId) { StringBuilder sql = new StringBuilder(); List lstParams = new ArrayList(); sql.append(" SELECT DISTINCT m.staff_id staffId,"); sql.append(" m.staff_code staffCode,"); sql.append(" m.staff_name staffName,"); sql.append(" m.staff_type staffType,"); sql.append(" m.staff_email staffEmail"); sql.append(" FROM map_staff_goods m, goods g"); sql.append(" WHERE m.goods_id = g.goods_id "); sql.append(" AND g.status = 1"); sql.append(" AND m.staff_email IS NOT NULL"); sql.append(" AND g.cust_id = ?"); lstParams.add(custId);//from ww w.ja v a 2s . c om sql.append(" AND m.goods_code IN (:idx)"); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.setResultTransformer(Transformers.aliasToBean(MapStaffGoodsDTO.class)); query.addScalar("staffId", new StringType()); query.addScalar("staffCode", new StringType()); query.addScalar("staffName", new StringType()); query.addScalar("staffType", new StringType()); query.addScalar("staffEmail", new StringType()); for (int i = 0; i < lstParams.size(); i++) { query.setParameter(i, lstParams.get(i)); } List<String> lst = Splitter.on(",").trimResults().omitEmptyStrings().splitToList(codeList); query.setParameterList("idx", lst); return query.list(); }
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java
License:Open Source License
public List<StockTransGoodsDTO> getListStockTransGoods2Report(String lstStockTransCodes) { StringBuilder sqlGetStockTransGoods = new StringBuilder(); sqlGetStockTransGoods.append("SELECT st.stock_trans_type stockTransType, "); sqlGetStockTransGoods.append(" st.stock_trans_status stockTransStatus, "); sqlGetStockTransGoods.append(" st.order_code orderCode, "); sqlGetStockTransGoods.append(" st.order_action_code orderActionCode, "); sqlGetStockTransGoods.append(" st.stock_trans_code stockTransCode, "); sqlGetStockTransGoods.append(" st.syn_trans_code synTransCode, "); sqlGetStockTransGoods/*from ww w . j ava2s .co m*/ .append(" TO_CHAR (st.real_stock_trans_date, 'dd/MM/yyyy') realStockTransDate, "); sqlGetStockTransGoods.append(" TO_CHAR (st.stock_trans_date, 'dd/MM/yyyy') stockTransDate, "); sqlGetStockTransGoods.append(" st.trans_user_name transUserName, "); sqlGetStockTransGoods.append(" st.notes notes, "); sqlGetStockTransGoods.append(" st.order_id_list orderId,"); sqlGetStockTransGoods.append(" st.owner_id stockId, "); sqlGetStockTransGoods.append(" a.code stockCode, "); sqlGetStockTransGoods.append(" o.receive_name receiveName, "); sqlGetStockTransGoods.append(" std.goods_code goodsCode, "); sqlGetStockTransGoods.append(" std.goods_name goodsName, "); sqlGetStockTransGoods.append(" g.unit_type goodsUnitType, "); sqlGetStockTransGoods.append(" std.goods_unit_type_name goodsUnitTypeName, "); sqlGetStockTransGoods.append(" std.amount_real amountReal "); sqlGetStockTransGoods.append(" FROM stock_trans st "); sqlGetStockTransGoods.append(" JOIN "); sqlGetStockTransGoods.append(" stock_trans_detail std "); sqlGetStockTransGoods.append(" ON std.stock_trans_id = st.stock_trans_id "); sqlGetStockTransGoods.append(" JOIN goods g "); sqlGetStockTransGoods.append(" ON std.goods_id = g.goods_id"); sqlGetStockTransGoods.append(" JOIN "); sqlGetStockTransGoods.append(" stock a "); sqlGetStockTransGoods.append(" ON a.stock_id = st.owner_id "); sqlGetStockTransGoods.append(" LEFT JOIN "); sqlGetStockTransGoods.append(" cms_owner.orders o "); sqlGetStockTransGoods.append(" ON o.order_id = st.order_id_list "); sqlGetStockTransGoods.append(" WHERE st.stock_trans_code in ( :idx0 ) "); sqlGetStockTransGoods.append("GROUP BY st.owner_id, "); sqlGetStockTransGoods.append(" st.order_id_list, "); sqlGetStockTransGoods.append(" o.receive_name, "); sqlGetStockTransGoods.append(" a.code, "); sqlGetStockTransGoods.append(" st.order_code, "); sqlGetStockTransGoods.append(" st.order_action_code, "); sqlGetStockTransGoods.append(" st.stock_trans_code, "); sqlGetStockTransGoods.append(" st.stock_trans_type, "); sqlGetStockTransGoods.append(" st.syn_trans_code, "); sqlGetStockTransGoods.append(" st.real_stock_trans_date, "); sqlGetStockTransGoods.append(" st.stock_trans_date, "); sqlGetStockTransGoods.append(" std.goods_code, "); sqlGetStockTransGoods.append(" std.goods_name, "); sqlGetStockTransGoods.append(" g.unit_type, "); sqlGetStockTransGoods.append(" std.goods_unit_type_name, "); sqlGetStockTransGoods.append(" std.amount_real, "); sqlGetStockTransGoods.append(" st.trans_user_name, "); sqlGetStockTransGoods.append(" st.notes, "); sqlGetStockTransGoods.append(" st.stock_trans_status "); sqlGetStockTransGoods.append("ORDER BY st.stock_trans_date desc, stockTransCode, goodsName "); List lstParams = new ArrayList<>(); if (lstStockTransCodes.contains(ParamUtils.SPLITTER)) { lstParams.add(lstStockTransCodes.split(ParamUtils.SPLITTER)); } else { lstParams.add(lstStockTransCodes); } SQLQuery query = getSession().createSQLQuery(sqlGetStockTransGoods.toString()); query.setResultTransformer(Transformers.aliasToBean(StockTransGoodsDTO.class)); query.addScalar("goodsName", new StringType()); query.addScalar("goodsCode", new StringType()); query.addScalar("goodsUnitType", new StringType()); query.addScalar("goodsUnitTypeName", new StringType()); query.addScalar("amountReal", new DoubleType()); query.addScalar("stockTransType", new StringType()); query.addScalar("orderCode", new StringType()); query.addScalar("orderActionCode", new StringType()); query.addScalar("stockTransCode", new StringType()); query.addScalar("synTransCode", new StringType()); query.addScalar("realStockTransDate", new StringType()); query.addScalar("stockTransDate", new StringType()); query.addScalar("transUserName", new StringType()); query.addScalar("notes", new StringType()); query.addScalar("orderId", new StringType()); query.addScalar("stockId", new StringType()); query.addScalar("stockCode", new StringType()); query.addScalar("receiveName", new StringType()); query.addScalar("stockTransStatus", new StringType()); for (int i = 0; i < lstParams.size(); i++) { if (lstParams.get(i) instanceof String[]) { query.setParameterList("idx" + String.valueOf(i), (Object[]) lstParams.get(i)); } else { query.setParameter("idx" + String.valueOf(i), lstParams.get(i)); } } return query.list(); }