001/** 002 * Copyright 2005-2014 The Kuali Foundation 003 * 004 * Licensed under the Educational Community License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.opensource.org/licenses/ecl2.php 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016package org.kuali.rice.kim.impl.role; 017 018import java.sql.Connection; 019import java.sql.PreparedStatement; 020import java.sql.ResultSet; 021import java.sql.SQLException; 022import java.sql.Timestamp; 023import java.util.ArrayList; 024import java.util.Collection; 025import java.util.HashMap; 026import java.util.List; 027import java.util.Map; 028 029import javax.sql.DataSource; 030 031import org.apache.commons.collections.CollectionUtils; 032import org.apache.commons.lang.StringUtils; 033import org.joda.time.DateTime; 034import org.kuali.rice.core.api.membership.MemberType; 035import org.kuali.rice.core.api.util.Truth; 036import org.kuali.rice.kim.api.common.attribute.KimAttribute; 037import org.kuali.rice.kim.api.role.RoleMember; 038import org.kuali.rice.kim.api.services.KimApiServiceLocator; 039import org.kuali.rice.kim.api.type.KimType; 040import org.kuali.rice.kim.impl.common.attribute.KimAttributeBo; 041import org.kuali.rice.kim.impl.type.KimTypeBo; 042import org.springframework.dao.DataAccessException; 043import org.springframework.jdbc.core.JdbcTemplate; 044import org.springframework.jdbc.core.PreparedStatementCallback; 045import org.springframework.jdbc.core.PreparedStatementCreator; 046import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy; 047 048public class RoleDaoJdbc implements RoleDao { 049 050 protected DataSource dataSource; 051 052 @Override 053 public List<RoleMemberBo> getRoleMembersForRoleIds(Collection<String> roleIds, String memberTypeCode, 054 Map<String, String> qualification) { 055 JdbcTemplate template = new JdbcTemplate(dataSource); 056 final List<String> roleIDs = new ArrayList<String>(roleIds); 057 final String memberTypeCd = memberTypeCode; 058 final Map<String, String> qual = qualification; 059 final List<RoleMemberBo> roleMemberBos = new ArrayList<RoleMemberBo>(); 060 template.execute(new PreparedStatementCreator() { 061 062 /* 063 SAMPLE QUERY 064 065 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, 066 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, 067 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 068 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 069 WHERE A0.ROLE_ID in ('100000') 070 071 UNION ALL 072 073 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, 074 '' 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, 075 '' 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 076 FROM KRIM_ROLE_MBR_T D0 077 WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0) 078 AND D0.ROLE_ID IN ('100000') 079 */ 080 081 @Override 082 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 083 /* 084 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 085 and role data in a single query (even though we are duplicating the role information across the role members). The cost of this 086 comes out to be cheaper than firing indiviudual queries for each role in cases where there are over 500 roles 087 */ 088 StringBuilder sql1 = new StringBuilder("SELECT " 089 + " 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, " 090 + " 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, " 091 + " 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 " 092 + " FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID " 093 + " JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID "); 094 095 StringBuilder sql2 = new StringBuilder("SELECT" 096 + " 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, " 097 + " '' 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," 098 + " '' 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 " 099 + " FROM KRIM_ROLE_MBR_T D0 " 100 + " WHERE D0.ROLE_MBR_ID NOT IN (SELECT DISTINCT (E0.ROLE_MBR_ID) FROM KRIM_ROLE_MBR_ATTR_DATA_T E0)"); 101 102 StringBuilder criteria = new StringBuilder(); 103 104 List<String> params1 = new ArrayList<String>(); 105 List<String> params2 = new ArrayList<String>(); 106 107 if (roleIDs != null && !roleIDs.isEmpty()) { 108 criteria.append("A0.ROLE_ID IN ("); 109 110 for (String roleId : roleIDs) { 111 criteria.append("?,"); 112 params1.add(roleId); 113 params2.add(roleId); 114 } 115 criteria.deleteCharAt(criteria.length() - 1); 116 criteria.append(")"); 117 } 118 119 if (memberTypeCd != null) { 120 if (criteria.length() > 0) { 121 criteria.append(" AND "); 122 } 123 124 criteria.append("A0.MBR_TYP_CD = ?"); 125 params1.add(memberTypeCd); 126 params2.add(memberTypeCd); 127 } 128 129 // Assuming that at least a role id or role member type code is specified 130 if (criteria.length() > 0) { 131 sql1.append(" WHERE "); 132 sql2.append(" AND "); 133 sql1.append(criteria); 134 sql2.append(criteria.toString().replaceAll("A0", "D0")); 135 } 136 137 if (qual != null && CollectionUtils.isNotEmpty(qual.keySet())) { 138 139 // If Qualifiers present then sql2 should not be returning any result as it finds 140 // rolemembers with now attributes 141 sql2 = new StringBuilder(); 142 143 if (criteria.length() > 0) { 144 sql1.append(" AND "); 145 } else { 146 sql1.append(" WHERE "); 147 } 148 149 sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE ("); 150 for (Map.Entry<String, String> qualifier : qual.entrySet()) { 151 if (StringUtils.isNotEmpty(qualifier.getValue())) { 152 String value = (qualifier.getValue()).replace('*', '%'); 153 sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ? ) "); 154 params1.add(value); 155 params1.add(qualifier.getKey()); 156 } 157 sql1.append("OR"); 158 } 159 sql1.delete(sql1.length() - 2, sql1.length()); 160 sql1.append(") AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )"); 161 162 } 163 164 StringBuilder sql = new StringBuilder(sql1.toString()); 165 166 if (sql2.length() > 0) { 167 sql.append(" UNION ALL "); 168 sql.append(sql2.toString()); 169 } 170 171 sql.append(" ORDER BY ROLE_MBR_ID "); 172 173 PreparedStatement statement = connection.prepareStatement(sql.toString()); 174 int i = 1; 175 for (String param : params1) { 176 statement.setString(i, param); 177 i++; 178 } 179 180 if (sql2.length() > 0) { 181 for (String param : params2) { 182 statement.setString(i, param); 183 i++; 184 } 185 } 186 187 return statement; 188 } 189 }, new PreparedStatementCallback<List<RoleMemberBo>>() { 190 @Override 191 public List<RoleMemberBo> doInPreparedStatement( 192 PreparedStatement statement) throws SQLException, DataAccessException { 193 ResultSet rs = statement.executeQuery(); 194 try { 195 RoleMemberBo lastRoleMember = null; 196 while (rs.next()) { 197 boolean processRolemember = true; 198 199 String roleId = rs.getString("ROLE_ID"); 200 String id = rs.getString("ROLE_MBR_ID"); 201 String memberId = rs.getString("MBR_ID"); 202 203 MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD")); 204 DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT")); 205 DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT")); 206 207 // Since we are joining role members and attributes we would have multiple role member rows 208 // but one row per attribute so check if its the first time we are seeing the role member 209 if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) { 210 RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType, 211 activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build(); 212 Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR"); 213 String roleObjId = rs.getString("ROLE_MBR_OBJ_ID"); 214 215 RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember); 216 roleMemberBo.setVersionNumber(roleVersionNbr); 217 roleMemberBo.setObjectId(roleObjId); 218 List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>(); 219 220 roleMemberBo.setAttributeDetails(roleMemAttrBos); 221 if(roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))){ 222 roleMemberBos.add(roleMemberBo); 223 } else { 224 processRolemember = false; 225 } 226 227 lastRoleMember = roleMemberBo; 228 } 229 230 String kimTypeId = rs.getString("KIM_TYP_ID"); 231 if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) { 232 KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId); 233 // Create RoleMemberAttributeDataBo for this row 234 RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo(); 235 236 KimAttribute.Builder attrBuilder = KimAttribute.Builder.create( 237 rs.getString("ATTR_DEFN_CMPNT_NM") 238 , rs.getString("ATTR_NAME") 239 , rs.getString("ATTR_DEFN_NMSPC_CD")); 240 attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND"))); 241 attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL")); 242 attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID")); 243 attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID")); 244 attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR")); 245 246 roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID")); 247 roleMemAttrDataBo.setAssignedToId(id); 248 roleMemAttrDataBo.setKimTypeId(kimTypeId); 249 roleMemAttrDataBo.setKimType(KimTypeBo.from(theType)); 250 roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId()); 251 roleMemAttrDataBo.setAttributeValue(rs.getString("ATTR_VAL")); 252 roleMemAttrDataBo.setVersionNumber(attrBuilder.getVersionNumber()); 253 roleMemAttrDataBo.setObjectId(attrBuilder.getObjectId()); 254 255 roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build())); 256 lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo); 257 } 258 259 } 260 } finally { 261 if (rs != null) { 262 rs.close(); 263 } 264 } 265 return roleMemberBos; 266 } 267 } 268 ); 269 return roleMemberBos; 270 } 271 272 public void setDataSource(DataSource dataSource) { 273 this.dataSource = new TransactionAwareDataSourceProxy(dataSource); 274 } 275 276}