公司内部SQL联合查询

String prodTableId = assignWorkTB.get(0).getCellBeanValue("CR_PROD_TABLE_ID");ClassPOJO classPOJO = new ClassPOJO("TN_PROD_ASSIGN_WORK a INNER JOIN TN_PROD_ASSIGN_WORK_OPERATION b ON a.CN_ID = b.CR_PROD_ASSIGN_WORK_ID " +"INNER JOIN TN_ROUTE_TREE_OPERATION c ON b.CR_ROUTE_TREE_OPERATION_ID = c.CN_ID INNER JOIN TN_RECEIPTS_LIST_LINE d ON b.CN_ID = d.CR_ORDER_LINE_ID " +"INNER JOIN TN_IQC_LINE e ON d.CN_ID = e.CR_SOURCE_LINE_ID INNER JOIN TN_INSTOCK_LINE f ON e.CN_ID = f.CR_IQC_LINE_ID");classPOJO.addAttribute(new ClassAttributePOJO("c.CN_ID AS wipId, f.CN_INSTOCK_QTY AS instockQty"));CondSetBean condSetBean = new CondSetBeanJustAnd();condSetBean.addCondBean(new CondBeanEqual("a.CR_PROD_TABLE_ID", prodTableId));CondBeanIn cbi = new CondBeanIn("b.CR_ROUTE_TREE_OPERATION_ID");condSetBean.addCondBean(cbi);for (int i = 0; i < wipTB.size(); i++) {cbi.addValue(wipTB.get(i).getCellBeanValue(TriangleDefinition.COLUMN_NAME_CN_ID));}TableBean newWipTB = this.baseDAO.queryForTableBean(classPOJO, condSetBean);

另外一种

if (matrixTableBean != null && matrixTableBean.size() > 0) {dataBean.addFormBean(matrixDataBean.getFormBean(TriangleDefinition.FORM_BEAN_KEY_SPLIT_PAGE_INFO));// 读取交易记录的定性|Domain|Relation|ExceptionCondSetBean csbAttribute = new CondSetBeanJustAnd();CondBeanIn cbiMatrixIdForAttr = new CondBeanIn("CR_TRADING_RECORD_MATRIX_ID");csbAttribute.addCondBean(cbiMatrixIdForAttr);StringBuffer inMatrixSQL = new StringBuffer();for (int i = 0; i < matrixTableBean.size(); i++) {RowBean rowBean = matrixTableBean.get(i);String matrixId = rowBean.getCellBeanValue(TriangleDefinition.COLUMN_NAME_CN_ID);cbiMatrixIdForAttr.addValue(matrixId);if (i > 0) {inMatrixSQL.append(",");}inMatrixSQL.append("'" + matrixId + "'");}// 读取属性要加入排序.以便后续生成数据Table的时候有固定的顺序OrderBy obAttribute = new OrderBy();obAttribute.addSort(new Sort("CR_ATTRIBUTE_TYPE_ID"));obAttribute.addSort(new Sort("CN_ATTRIBUTE_ID"));// DomainTableBean domainTableBean = this.baseDAO.queryForTableBean(new ClassPOJO(domainTableName), csbAttribute, obAttribute);// 定性TableBean qualitativeTableBean = this.baseDAO.queryForTableBean(new ClassPOJO(qualitativeTableName), csbAttribute, obAttribute);// 异常数量ClassPOJO countExceptionClassPOJO = new ClassPOJO(hmAccountInfo.get("public_RecordDBTableName"));countExceptionClassPOJO.addAttribute(new ClassAttributePOJO("CR_TRADING_RECORD_MATRIX_ID"));countExceptionClassPOJO.addAttribute(new ClassAttributePOJO("COUNT(CN_ID)"));StringBuffer countExceptionSQL = new StringBuffer();countExceptionSQL.append("SELECT CR_TRADING_RECORD_MATRIX_ID AS matrixId,COUNT(CN_ID) AS number FROM ");countExceptionSQL.append(hmAccountInfo.get("public_RecordDBTableName"));countExceptionSQL.append(" WHERE CR_TRADING_RECORD_MATRIX_ID IN (");countExceptionSQL.append(inMatrixSQL);countExceptionSQL.append(") GROUP BY CR_TRADING_RECORD_MATRIX_ID");TableBean exceptionTableBean = this.baseDAO.queryForTableBeanOnSQL(countExceptionSQL.toString());