List of usage examples for org.springframework.jdbc.core PreparedStatementCallback PreparedStatementCallback
PreparedStatementCallback
From source file:cc.tooyoung.common.db.JdbcTemplate.java
@SuppressWarnings("unchecked") public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException { if (ApiLogger.isTraceEnabled()) { ApiLogger.trace("Executing SQL batch update [" + sql + "]"); }//from w w w. j a v a 2s . c om return (int[]) execute(sql, new PreparedStatementCallback() { public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { try { int batchSize = pss.getBatchSize(); InterruptibleBatchPreparedStatementSetter ipss = (pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter) pss : null); if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) { for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); if (ipss != null && ipss.isBatchExhausted(i)) { break; } ps.addBatch(); } return ps.executeBatch(); } else { List rowsAffected = new ArrayList(); for (int i = 0; i < batchSize; i++) { pss.setValues(ps, i); if (ipss != null && ipss.isBatchExhausted(i)) { break; } rowsAffected.add(new Integer(ps.executeUpdate())); } int[] rowsAffectedArray = new int[rowsAffected.size()]; for (int i = 0; i < rowsAffectedArray.length; i++) { rowsAffectedArray[i] = ((Integer) rowsAffected.get(i)).intValue(); } return rowsAffectedArray; } } finally { if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); } } } }, true); }
From source file:com.disney.opa.dao.impl.ProductDaoImpl.java
public void update(Product product) throws PADataAccessException { if (product == null) { throw new PADataAccessException("Product object is null."); }//w ww .jav a2 s. c om //OPA 6.2, check if the parent template has template based permission 100 to allow the packaging prduct to have its own associate //if it does, then we need to update the associate id for child packaging component once update is called on parent since //updating parent product also updates child product associate id to parent associate id and that is not a desired situation final PermissionDefinition[] permissions = permissionSetDao.getPermissionDefinitions( product.getProductTemplateID(), permissionSetDao.ALLOW_PACKAGING_COMPONENT_FOR_ASSOCIATE); boolean doNotUpdateAssociateId = (permissions != null && permissions.length > 0) ? true : false; List<Product> undoAssociateIdProducts = null; //OPA 7.0: Undo Associate User Id for packaging and PI components if (doNotUpdateAssociateId) { Product[] childProducts = this.getChildProducts(product.getID(), 1); undoAssociateIdProducts = childProductsByType(childProducts, Product.PRODUCT_TYPE_PACKAGE_COMPONENT); List<Product> undoPIProductIds = childProductsByType(childProducts, Product.PRODUCT_TYPE_PI_COMPONENT); if (undoPIProductIds != null) { undoAssociateIdProducts.addAll(undoPIProductIds); } } else { Product[] childProducts = this.getChildProducts(product.getID(), 1); undoAssociateIdProducts = childProductsByType(childProducts, Product.PRODUCT_TYPE_PI_COMPONENT); } int productID = product.getID(); String productName = product.getName(); if (log.isDebugEnabled()) { log.debug("Method update(" + product + ") with id = " + productID + " ... begin"); } List<String> sqlList = new ArrayList<String>(); //Add subcategory prefix and property suffix for NA Stationery Products PermissionDefinition[] permission = permissionSetDao.getPermissionDefinitions( product.getProductTemplateID(), permissionSetDao.PRODUCT_NAMING_CONVENTION); if (product.getName() != "" && permission != null && permission.length > 0) { productName = getRevisedProductName(product); product.setName(productName); } // Update product header String updateProductSQL = buildUpdateProductSQL(product); sqlList.add(updateProductSQL); Attribute[] attributes = product.getAllAttributes(); if (attributes != null && attributes.length > 0) { for (int i = 0; i < attributes.length; i++) { Attribute attribute = (Attribute) attributes[i]; AttributeDefinition definition = (AttributeDefinition) (attribute.getAttributeDefinition()); if (attribute.isDirty()) { // First, delete existing attribute values String deleteAttributeSQL = MessageFormat.format(SP_DELETE_ATTRIBUTE_VALUES, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()) }); sqlList.add(deleteAttributeSQL); int dataTypeID = attribute.getAttributeDefinition().getDataTypeID(); // Then insert new values if (dataTypeID == AttributeConstants.DATATYPE_IS_STRING) { String[] values = attribute.getStringValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertStringSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(values[j]) }); sqlList.add(insertStringSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_INTEGER) { Integer[] values = attribute.getIntegerValues(); // Portfolio attribute, store both TextValue and Integer Value; if (values != null) { if (definition.getID() == AttributeConstants.ATT_PORTFOLIO) { final Map<Integer, String> portfolioMap = this .getStyleGuides(product.getProductTemplateID(), null, null); for (int j = 0; j < values.length; j++) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString( portfolioMap.get(values[j].intValue())), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } else if (definition.getID() == AttributeConstants.ATT_ON_BEHALF_COMPANY) { Company[] companyArray = companyDao .getCompanyAssociation(product.getLicenseeCompanyID(), 0); if (companyArray != null && values != null && values.length == 1) { for (int j = 0; j < values.length; j++) { for (int k = 0; k < companyArray.length; k++) { if (values[j] == companyArray[k].getID()) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(companyArray[k].getName()), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } } } } else { for (int j = 0; j < values.length; j++) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DOUBLE) { Double[] values = attribute.getDoubleValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertDoubleSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DOUBLE, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].doubleValue()) }); sqlList.add(insertDoubleSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DATE || dataTypeID == AttributeConstants.DATATYPE_IS_MMYYYY) { Date[] values = attribute.getDateValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertDateSQL = MessageFormat.format(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DATE, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeDate(values[j]) }); sqlList.add(insertDateSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_OPTION) { Option[] values = attribute.getOptionValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertOptionSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_OPTION, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(definition.getOptionSourceID()), String.valueOf(values[j].getID()) }); sqlList.add(insertOptionSQL); } } } } } } // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); //OPA 7.3: keep this call to add all the other sql statements //JDBCUtil.executeUpdateDeleteInsert(sqlList); for (String sql : sqlList) { namedJdbcTemplate.execute(sql, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); } String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); namedJdbcTemplate.execute(summarySQL, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); //since the associate Id was updated when the parent product was updated (ex, packaging component //need to have own associate id), the associate id needs to be reverted back (see above for more comments, OPA 6.2) if (undoAssociateIdProducts != null && undoAssociateIdProducts.size() > 0) { for (Product tempProduct : undoAssociateIdProducts) { updateUsers(tempProduct, JDBCUtil.IGNORED, tempProduct.getAssociateUserID(), JDBCUtil.IGNORED); } } if (log.isDebugEnabled()) { log.debug("Method update(" + product + ") with id = " + product.getID() + " ... end"); } }
From source file:com.disney.opa.dao.impl.ProductDaoImpl.java
public void updateUsers(Product product, int licenseeID, int associateID, int leadID) throws PADataAccessException { if (product == null) { throw new PADataAccessException("Product object is null."); }/*from w w w . j a v a 2 s . c o m*/ if (licenseeID < 0 && associateID < 0 && leadID < 0) { throw new PADataAccessException("Nothing to update on Product."); } if (log.isDebugEnabled()) { log.debug("Method updateUsers(" + product.getID() + ", " + licenseeID + ", " + associateID + ", " + leadID + ") ... begin"); } String productIDString = String.valueOf(product.getID()); String licenseeIDString = null; if (licenseeID != JDBCUtil.IGNORED) { licenseeIDString = String.valueOf(licenseeID); } String associateIDString = null; if (associateID != JDBCUtil.IGNORED) { associateIDString = String.valueOf(associateID); } String leadIDString = null; if (leadID != JDBCUtil.IGNORED) { leadIDString = String.valueOf(leadID); } List<String> sqlList = new ArrayList<String>(); String updProductUsers = MessageFormat.format(SP_UPD_PRODUCT_USERS, new Object[] { productIDString, licenseeIDString, associateIDString, leadIDString }); sqlList.add(updProductUsers); String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { productIDString }); sqlList.add(summarySQL); //JDBCUtil.executeUpdateDeleteInsert(sqlList); for (String sql : sqlList) { namedJdbcTemplate.execute(sql, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); } if (log.isDebugEnabled()) { log.debug("Method updateUsers(" + product.getID() + ", " + licenseeID + ", " + associateID + ", " + leadIDString + ") ... end"); } }
From source file:org.kuali.rice.kew.documentoperation.web.DocumentContentOperationAction.java
private String getDocumentContent(final String documentId) { final DataSource dataSource = KEWServiceLocator.getDataSource(); JdbcTemplate template = new JdbcTemplate(dataSource); String docContent = template.execute(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { String sql = "SELECT doc_cntnt_txt FROM krew_doc_hdr_cntnt_t WHERE doc_hdr_id = ?"; PreparedStatement statement = connection.prepareStatement(sql); return statement; }//from w w w. j a va2 s . c om }, new PreparedStatementCallback<String>() { public String doInPreparedStatement(PreparedStatement statement) throws SQLException, DataAccessException { String docContent = ""; statement.setString(1, documentId); ResultSet rs = statement.executeQuery(); try { while (rs.next()) { docContent = rs.getString("doc_cntnt_txt"); } } finally { if (rs != null) { rs.close(); } } return docContent; } }); return docContent; }
From source file:org.kuali.rice.kew.documentoperation.web.DocumentContentOperationAction.java
private void saveDocumentContent(final String documentId, final String docContent) { if (StringUtils.isBlank(documentId) || StringUtils.isBlank(docContent)) { LOG.info("The document Id or the doc content was blank"); return;/*from w w w. j a v a2 s .co m*/ } final DataSource dataSource = KEWServiceLocator.getDataSource(); JdbcTemplate template = new JdbcTemplate(dataSource); template.execute(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { String sql = "UPDATE krew_doc_hdr_cntnt_t SET doc_cntnt_txt = ? WHERE doc_hdr_id = ?"; PreparedStatement statement = connection.prepareStatement(sql); return statement; } }, new PreparedStatementCallback<String>() { public String doInPreparedStatement(PreparedStatement statement) throws SQLException, DataAccessException { statement.setString(1, docContent); statement.setString(2, documentId); ResultSet rs = statement.executeQuery(); if (rs != null) { rs.close(); } return ""; } }); }
From source file:org.kuali.rice.kim.impl.role.RoleDaoJdbc.java
@Override public List<RoleMemberBo> getRoleMembersForRoleIds(Collection<String> roleIds, String memberTypeCode, Map<String, String> qualification) { JdbcTemplate template = new JdbcTemplate(dataSource); final List<String> roleIDs = new ArrayList<String>(roleIds); final String memberTypeCd = memberTypeCode; final Map<String, String> qual = qualification; final List<RoleMemberBo> roleMemberBos = new ArrayList<RoleMemberBo>(); template.execute(new PreparedStatementCreator() { /*// ww w.ja v a2 s .co m SAMPLE QUERY SELECT A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, CO.OBJ_ID AS ATTR_DEFN_OBJ_ID, CO.VER_NBR as ATTR_DEFN_VER_NBR, CO.NM AS ATTR_NAME, CO.LBL as ATTR_DEFN_LBL, CO.ACTV_IND as ATTR_DEFN_ACTV_IND, CO.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, CO.CMPNT_NM AS ATTR_DEFN_CMPNT_NM FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID JOIN KRIM_ATTR_DEFN_T CO ON BO.KIM_ATTR_DEFN_ID = CO.KIM_ATTR_DEFN_ID WHERE A0.ROLE_ID in ('100000') UNION ALL SELECT D0.ROLE_MBR_ID AS ROLE_MBR_ID,D0.ROLE_ID AS ROLE_ID,D0.MBR_ID AS MBR_ID,D0.MBR_TYP_CD AS MBR_TYP_CD,D0.VER_NBR AS ROLE_MBR_VER_NBR,D0.OBJ_ID AS ROLE_MBR_OBJ_ID,D0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,D0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, '' AS KIM_TYP_ID, '' AS KIM_ATTR_DEFN_ID, '' AS ATTR_VAL, '' AS ATTR_DATA_ID, '' AS ATTR_DATA_OBJ_ID, NULL AS ATTR_DATA_VER_NBR, '' AS ATTR_DEFN_OBJ_ID, NULL as ATTR_DEFN_VER_NBR, '' AS ATTR_NAME, '' as ATTR_DEFN_LBL, '' as ATTR_DEFN_ACTV_IND, '' AS ATTR_DEFN_NMSPC_CD, '' AS ATTR_DEFN_CMPNT_NM FROM KRIM_ROLE_MBR_T D0 WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0) AND D0.ROLE_ID IN ('100000') */ @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { /* The query returns multiple lines for each role by joining a role with each of its members. This allows us to get all the role member and role data in a single query (even though we are duplicating the role information across the role members). The cost of this comes out to be cheaper than firing indiviudual queries for each role in cases where there are over 500 roles */ StringBuilder sql1 = new StringBuilder("SELECT " + " A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, " + " BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, " + " C0.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, C0.OBJ_ID AS ATTR_DEFN_OBJ_ID, C0.VER_NBR as ATTR_DEFN_VER_NBR, C0.NM AS ATTR_NAME, C0.LBL as ATTR_DEFN_LBL, C0.ACTV_IND as ATTR_DEFN_ACTV_IND, C0.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, C0.CMPNT_NM AS ATTR_DEFN_CMPNT_NM " + " FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID " + " JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID "); StringBuilder sql2 = new StringBuilder("SELECT" + " D0.ROLE_MBR_ID AS ROLE_MBR_ID,D0.ROLE_ID AS ROLE_ID,D0.MBR_ID AS MBR_ID,D0.MBR_TYP_CD AS MBR_TYP_CD,D0.VER_NBR AS ROLE_MBR_VER_NBR,D0.OBJ_ID AS ROLE_MBR_OBJ_ID,D0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,D0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, " + " '' AS KIM_TYP_ID, '' AS KIM_ATTR_DEFN_ID, '' AS ATTR_VAL, '' AS ATTR_DATA_ID, '' AS ATTR_DATA_OBJ_ID, NULL AS ATTR_DATA_VER_NBR," + " '' AS KIM_ATTR_DEFN_ID,'' AS ATTR_DEFN_OBJ_ID, NULL as ATTR_DEFN_VER_NBR, '' AS ATTR_NAME, '' as ATTR_DEFN_LBL, '' as ATTR_DEFN_ACTV_IND, '' AS ATTR_DEFN_NMSPC_CD, '' AS ATTR_DEFN_CMPNT_NM " + " FROM KRIM_ROLE_MBR_T D0 " + " WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0)"); StringBuilder criteria = new StringBuilder(); List<String> params1 = new ArrayList<String>(); List<String> params2 = new ArrayList<String>(); if (roleIDs != null && !roleIDs.isEmpty()) { criteria.append("A0.ROLE_ID IN ("); for (String roleId : roleIDs) { criteria.append("?,"); params1.add(roleId); params2.add(roleId); } criteria.deleteCharAt(criteria.length() - 1); criteria.append(")"); } if (memberTypeCd != null) { if (criteria.length() > 0) { criteria.append(" AND "); } criteria.append("A0.MBR_TYP_CD = ?"); params1.add(memberTypeCd); params2.add(memberTypeCd); } // Assuming that at least a role id or role member type code is specified if (criteria.length() > 0) { sql1.append(" WHERE "); sql2.append(" AND "); sql1.append(criteria); sql2.append(criteria.toString().replaceAll("A0", "D0")); } if (qual != null && CollectionUtils.isNotEmpty(qual.keySet())) { // If Qualifiers present then sql2 should not be returning any result as it finds // rolemembers with now attributes sql2 = new StringBuilder(); if (criteria.length() > 0) { sql1.append(" AND "); } else { sql1.append(" WHERE "); } sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE "); int conditionCount = 0; for (Map.Entry<String, String> qualifier : qual.entrySet()) { if (StringUtils.isNotEmpty(qualifier.getValue())) { // advance the number of times we have found a non-null qualifier conditionCount++; // add '(' if encountering a non-null qualifier for the first time if (conditionCount == 1) { sql1.append("("); } // add the qualifier template with the parameters String value = (qualifier.getValue()).replace('*', '%'); sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ?) "); params1.add(value); params1.add(qualifier.getKey()); } sql1.append("OR"); } // remove the last OR sql1.delete(sql1.length() - 2, sql1.length()); // add ') AND' if we encountered a non-null qualifier sub-query above if (conditionCount != 0) { sql1.append(") AND"); } sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID)"); } StringBuilder sql = new StringBuilder(sql1.toString()); if (sql2.length() > 0) { sql.append(" UNION ALL "); sql.append(sql2.toString()); } sql.append(" ORDER BY ROLE_MBR_ID "); PreparedStatement statement = connection.prepareStatement(sql.toString()); int i = 1; for (String param : params1) { statement.setString(i, param); i++; } if (sql2.length() > 0) { for (String param : params2) { statement.setString(i, param); i++; } } return statement; } }, new PreparedStatementCallback<List<RoleMemberBo>>() { @Override public List<RoleMemberBo> doInPreparedStatement(PreparedStatement statement) throws SQLException, DataAccessException { ResultSet rs = statement.executeQuery(); try { RoleMemberBo lastRoleMember = null; while (rs.next()) { boolean processRolemember = true; String roleId = rs.getString("ROLE_ID"); String id = rs.getString("ROLE_MBR_ID"); String memberId = rs.getString("MBR_ID"); MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD")); DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null : new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT")); DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null : new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT")); // Since we are joining role members and attributes we would have multiple role member rows // but one row per attribute so check if its the first time we are seeing the role member if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) { RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType, activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build(); Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR"); String roleObjId = rs.getString("ROLE_MBR_OBJ_ID"); RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember); roleMemberBo.setVersionNumber(roleVersionNbr); roleMemberBo.setObjectId(roleObjId); List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>(); roleMemberBo.setAttributeDetails(roleMemAttrBos); if (roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))) { roleMemberBos.add(roleMemberBo); } else { processRolemember = false; } lastRoleMember = roleMemberBo; } String kimTypeId = rs.getString("KIM_TYP_ID"); if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) { KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId); // Create RoleMemberAttributeDataBo for this row RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo(); KimAttribute.Builder attrBuilder = KimAttribute.Builder.create( rs.getString("ATTR_DEFN_CMPNT_NM"), rs.getString("ATTR_NAME"), rs.getString("ATTR_DEFN_NMSPC_CD")); attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND"))); attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL")); attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID")); attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID")); attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR")); roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID")); roleMemAttrDataBo.setAssignedToId(id); roleMemAttrDataBo.setKimTypeId(kimTypeId); roleMemAttrDataBo.setKimType(KimTypeBo.from(theType)); roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId()); roleMemAttrDataBo.setAttributeValue(rs.getString("ATTR_VAL")); roleMemAttrDataBo.setVersionNumber(attrBuilder.getVersionNumber()); roleMemAttrDataBo.setObjectId(attrBuilder.getObjectId()); roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build())); lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo); } } } finally { if (rs != null) { rs.close(); } } return roleMemberBos; } }); return roleMemberBos; }
From source file:org.kuali.rice.kim.impl.role.RoleDaoOjb.java
public List<RoleMemberBo> getRoleMembersForRoleIds(Collection<String> roleIds, String memberTypeCode, Map<String, String> qualification) { JdbcTemplate template = new JdbcTemplate(dataSource); final List<String> roleIDs = new ArrayList<String>(roleIds); final String memberTypeCd = memberTypeCode; final Map<String, String> qual = qualification; final List<RoleMemberBo> roleMemberBos = new ArrayList<RoleMemberBo>(); List<RoleMemberBo> results = template.execute(new PreparedStatementCreator() { /*//from w ww. j a v a 2 s. c o m SAMPLE QUERY SELECT A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, CO.OBJ_ID AS ATTR_DEFN_OBJ_ID, CO.VER_NBR as ATTR_DEFN_VER_NBR, CO.NM AS ATTR_NAME, CO.LBL as ATTR_DEFN_LBL, CO.ACTV_IND as ATTR_DEFN_ACTV_IND, CO.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, CO.CMPNT_NM AS ATTR_DEFN_CMPNT_NM FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID JOIN KRIM_ATTR_DEFN_T CO ON BO.KIM_ATTR_DEFN_ID = CO.KIM_ATTR_DEFN_ID WHERE A0.ROLE_ID in ('100000') UNION ALL SELECT D0.ROLE_MBR_ID AS ROLE_MBR_ID,D0.ROLE_ID AS ROLE_ID,D0.MBR_ID AS MBR_ID,D0.MBR_TYP_CD AS MBR_TYP_CD,D0.VER_NBR AS ROLE_MBR_VER_NBR,D0.OBJ_ID AS ROLE_MBR_OBJ_ID,D0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,D0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, '' AS KIM_TYP_ID, '' AS KIM_ATTR_DEFN_ID, '' AS ATTR_VAL, '' AS ATTR_DATA_ID, '' AS ATTR_DATA_OBJ_ID, NULL AS ATTR_DATA_VER_NBR, '' AS ATTR_DEFN_OBJ_ID, NULL as ATTR_DEFN_VER_NBR, '' AS ATTR_NAME, '' as ATTR_DEFN_LBL, '' as ATTR_DEFN_ACTV_IND, '' AS ATTR_DEFN_NMSPC_CD, '' AS ATTR_DEFN_CMPNT_NM FROM KRIM_ROLE_MBR_T D0 WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0) AND D0.ROLE_ID IN ('100000') */ public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { /* The query returns multiple lines for each role by joining a role with each of its members. This allows us to get all the role member and role data in a single query (even though we are duplicating the role information across the role members). The cost of this comes out to be cheaper than firing indiviudual queries for each role in cases where there are over 500 roles */ StringBuffer sql1 = new StringBuffer("SELECT " + " A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, " + " BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, " + " C0.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, C0.OBJ_ID AS ATTR_DEFN_OBJ_ID, C0.VER_NBR as ATTR_DEFN_VER_NBR, C0.NM AS ATTR_NAME, C0.LBL as ATTR_DEFN_LBL, C0.ACTV_IND as ATTR_DEFN_ACTV_IND, C0.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, C0.CMPNT_NM AS ATTR_DEFN_CMPNT_NM " + " FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID " + " JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID "); StringBuffer sql2 = new StringBuffer("SELECT" + " D0.ROLE_MBR_ID AS ROLE_MBR_ID,D0.ROLE_ID AS ROLE_ID,D0.MBR_ID AS MBR_ID,D0.MBR_TYP_CD AS MBR_TYP_CD,D0.VER_NBR AS ROLE_MBR_VER_NBR,D0.OBJ_ID AS ROLE_MBR_OBJ_ID,D0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,D0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, " + " '' AS KIM_TYP_ID, '' AS KIM_ATTR_DEFN_ID, '' AS ATTR_VAL, '' AS ATTR_DATA_ID, '' AS ATTR_DATA_OBJ_ID, NULL AS ATTR_DATA_VER_NBR," + " '' AS KIM_ATTR_DEFN_ID,'' AS ATTR_DEFN_OBJ_ID, NULL as ATTR_DEFN_VER_NBR, '' AS ATTR_NAME, '' as ATTR_DEFN_LBL, '' as ATTR_DEFN_ACTV_IND, '' AS ATTR_DEFN_NMSPC_CD, '' AS ATTR_DEFN_CMPNT_NM " + " FROM KRIM_ROLE_MBR_T D0 " + " WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0)"); StringBuffer criteria = new StringBuffer(); List<String> params1 = new ArrayList<String>(); List<String> params2 = new ArrayList<String>(); if (roleIDs != null && !roleIDs.isEmpty()) { criteria.append("A0.ROLE_ID IN ("); for (String roleId : roleIDs) { criteria.append("?,"); params1.add(roleId); params2.add(roleId); } criteria.deleteCharAt(criteria.length() - 1); criteria.append(")"); } if (memberTypeCd != null) { if (criteria.length() > 0) { criteria.append(" AND "); } criteria.append("A0.MBR_TYP_CD = ?"); params1.add(memberTypeCd); params2.add(memberTypeCd); } // Assuming that at least a role id or role member type code is specified if (criteria.length() > 0) { sql1.append(" WHERE "); sql2.append(" AND "); sql1.append(criteria); sql2.append(criteria.toString().replaceAll("A0", "D0")); } if (qual != null && CollectionUtils.isNotEmpty(qual.keySet())) { // If Qualifiers present then sql2 should not be returning any result as it finds // rolemembers with now attributes sql2 = new StringBuffer(); if (criteria.length() > 0) { sql1.append(" AND "); } else { sql1.append(" WHERE "); } sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE ("); for (Map.Entry<String, String> qualifier : qual.entrySet()) { if (StringUtils.isNotEmpty(qualifier.getValue())) { String value = (qualifier.getValue()).replace('*', '%'); sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ? ) "); params1.add(value); params1.add(qualifier.getKey()); } sql1.append("OR"); } sql1.delete(sql1.length() - 2, sql1.length()); sql1.append(") AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )"); } StringBuffer sql = new StringBuffer(sql1.toString()); if (sql2.length() > 0) { sql.append(" UNION ALL "); sql.append(sql2.toString()); } sql.append(" ORDER BY ROLE_MBR_ID "); PreparedStatement statement = connection.prepareStatement(sql.toString()); int i = 1; for (String param : params1) { statement.setString(i, param); i++; } if (sql2.length() > 0) { for (String param : params2) { statement.setString(i, param); i++; } } return statement; } }, new PreparedStatementCallback<List<RoleMemberBo>>() { public List<RoleMemberBo> doInPreparedStatement(PreparedStatement statement) throws SQLException, DataAccessException { ResultSet rs = statement.executeQuery(); try { RoleMemberBo lastRoleMember = null; while (rs.next()) { boolean processRolemember = true; String roleId = rs.getString("ROLE_ID"); String id = rs.getString("ROLE_MBR_ID"); String memberId = rs.getString("MBR_ID"); MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD")); DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null : new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT")); DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null : new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT")); // Since we are joining role members and attributes we would have multiple role member rows // but one row per attribute so check if its the first time we are seeing the role member if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) { RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType, activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build(); Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR"); String roleObjId = rs.getString("ROLE_MBR_OBJ_ID"); RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember); roleMemberBo.setVersionNumber(roleVersionNbr); roleMemberBo.setObjectId(roleObjId); List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>(); roleMemberBo.setAttributeDetails(roleMemAttrBos); if (roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))) { roleMemberBos.add(roleMemberBo); } else { processRolemember = false; } lastRoleMember = roleMemberBo; } String kimTypeId = rs.getString("KIM_TYP_ID"); String attrKey = rs.getString("KIM_ATTR_DEFN_ID"); String attrVal = rs.getString("ATTR_VAL"); if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) { KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId); // Create RoleMemberAttributeDataBo for this row RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo(); KimAttribute.Builder attrBuilder = KimAttribute.Builder.create( rs.getString("ATTR_DEFN_CMPNT_NM"), rs.getString("ATTR_NAME"), rs.getString("ATTR_DEFN_NMSPC_CD")); attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND"))); attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL")); attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID")); attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID")); attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR")); roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID")); roleMemAttrDataBo.setAssignedToId(id); roleMemAttrDataBo.setKimTypeId(kimTypeId); roleMemAttrDataBo.setKimType(KimTypeBo.from(theType)); roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId()); roleMemAttrDataBo.setAttributeValue(attrVal); roleMemAttrDataBo.setVersionNumber(rs.getLong("ATTR_DATA_VER_NBR")); roleMemAttrDataBo.setObjectId(rs.getString("ATTR_DATA_OBJ_ID")); roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build())); lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo); } } } finally { if (rs != null) { rs.close(); } } return roleMemberBos; } }); return roleMemberBos; }
From source file:org.paxml.tag.sql.SqlTag.java
protected Object executeSql(String sql, Context context) { return executeSql(sql, new ISqlExecutor() { @Override//ww w .j a va 2 s .c om public Object update(final String sql) { if (param != null) { NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(jdbcTemplate); return t.execute(sql, param, new PreparedStatementCallback<Void>() { @Override public Void doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { ps.executeUpdate(); return null; } }); } else { jdbcTemplate.execute(sql); } return null; } @Override public Object query(String sql, boolean close) { if (param != null) { NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(jdbcTemplate); return t.queryForList(sql, param); } else { return jdbcTemplate.queryForList(sql); } } }); }
From source file:org.springframework.batch.item.database.JdbcBatchItemWriter.java
@SuppressWarnings("unchecked") @Override// www .j a v a2 s . c om public void write(final List<? extends T> items) throws Exception { if (!items.isEmpty()) { if (logger.isDebugEnabled()) { logger.debug("Executing batch with " + items.size() + " items."); } int[] updateCounts; if (usingNamedParameters) { if (items.get(0) instanceof Map && this.itemSqlParameterSourceProvider == null) { updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, items.toArray(new Map[items.size()])); } else { SqlParameterSource[] batchArgs = new SqlParameterSource[items.size()]; int i = 0; for (T item : items) { batchArgs[i++] = itemSqlParameterSourceProvider.createSqlParameterSource(item); } updateCounts = namedParameterJdbcTemplate.batchUpdate(sql, batchArgs); } } else { updateCounts = namedParameterJdbcTemplate.getJdbcOperations().execute(sql, new PreparedStatementCallback<int[]>() { @Override public int[] doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { for (T item : items) { itemPreparedStatementSetter.setValues(item, ps); ps.addBatch(); } return ps.executeBatch(); } }); } if (assertUpdates) { for (int i = 0; i < updateCounts.length; i++) { int value = updateCounts[i]; if (value == 0) { throw new EmptyResultDataAccessException("Item " + i + " of " + updateCounts.length + " did not update any rows: [" + items.get(i) + "]", 1); } } } } }
From source file:org.springframework.jdbc.core.JdbcTemplate.java
/** * Query using a prepared statement, allowing for a PreparedStatementCreator * and a PreparedStatementSetter. Most other query methods use this method, * but application code will always work with either a creator or a setter. * @param psc Callback handler that can create a PreparedStatement given a * Connection/*from www .j a v a 2s.c o m*/ * @param pss object that knows how to set values on the prepared statement. * If this is null, the SQL will be assumed to contain no bind parameters. * @param rse object that will extract results. * @return an arbitrary result object, as returned by the ResultSetExtractor * @throws DataAccessException if there is any problem */ protected Object query(PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor rse) throws DataAccessException { if (logger.isDebugEnabled()) { String sql = getSql(psc); logger.debug("Executing SQL query" + (sql != null ? " [" + sql + "]" : "")); } return execute(psc, new PreparedStatementCallback() { public Object doInPreparedStatement(PreparedStatement ps) throws SQLException { ResultSet rs = null; try { if (pss != null) { pss.setValues(ps); } if (getFetchSize() > 0) { ps.setFetchSize(getFetchSize()); } rs = ps.executeQuery(); ResultSet rsToUse = rs; if (nativeJdbcExtractor != null) { rsToUse = nativeJdbcExtractor.getNativeResultSet(rs); } //??pojo return rse.extractData(rsToUse); } finally { JdbcUtils.closeResultSet(rs); if (pss instanceof ParameterDisposer) { ((ParameterDisposer) pss).cleanupParameters(); } } } }); }