001/** 002 * Copyright 2005-2016 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 int conditionCount = 0; 151 for (Map.Entry<String, String> qualifier : qual.entrySet()) { 152 if (StringUtils.isNotEmpty(qualifier.getValue())) { 153 // advance the number of times we have found a non-null qualifier 154 conditionCount++; 155 156 // add '(' if encountering a non-null qualifier for the first time 157 if (conditionCount == 1) { 158 sql1.append("("); 159 } 160 161 // add the qualifier template with the parameters 162 String value = (qualifier.getValue()).replace('*', '%'); 163 sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ?) "); 164 params1.add(value); 165 params1.add(qualifier.getKey()); 166 } 167 168 sql1.append("OR"); 169 } 170 // remove the last OR 171 sql1.delete(sql1.length() - 2, sql1.length()); 172 // add ') AND' if we encountered a non-null qualifier sub-query above 173 if (conditionCount != 0) { 174 sql1.append(") AND"); 175 } 176 sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID)"); 177 } 178 179 StringBuilder sql = new StringBuilder(sql1.toString()); 180 181 if (sql2.length() > 0) { 182 sql.append(" UNION ALL "); 183 sql.append(sql2.toString()); 184 } 185 186 sql.append(" ORDER BY ROLE_MBR_ID "); 187 188 PreparedStatement statement = connection.prepareStatement(sql.toString()); 189 int i = 1; 190 for (String param : params1) { 191 statement.setString(i, param); 192 i++; 193 } 194 195 if (sql2.length() > 0) { 196 for (String param : params2) { 197 statement.setString(i, param); 198 i++; 199 } 200 } 201 202 return statement; 203 } 204 }, new PreparedStatementCallback<List<RoleMemberBo>>() { 205 @Override 206 public List<RoleMemberBo> doInPreparedStatement( 207 PreparedStatement statement) throws SQLException, DataAccessException { 208 ResultSet rs = statement.executeQuery(); 209 try { 210 RoleMemberBo lastRoleMember = null; 211 while (rs.next()) { 212 boolean processRolemember = true; 213 214 String roleId = rs.getString("ROLE_ID"); 215 String id = rs.getString("ROLE_MBR_ID"); 216 String memberId = rs.getString("MBR_ID"); 217 218 MemberType memberType = MemberType.fromCode(rs.getString("MBR_TYP_CD")); 219 DateTime activeFromDate = rs.getDate("ROLE_MBR_ACTV_FRM_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_FRM_DT")); 220 DateTime activeToDate = rs.getDate("ROLE_MBR_ACTV_TO_DT") == null ? null: new DateTime(rs.getDate("ROLE_MBR_ACTV_TO_DT")); 221 222 // Since we are joining role members and attributes we would have multiple role member rows 223 // but one row per attribute so check if its the first time we are seeing the role member 224 if (lastRoleMember == null || !id.equals(lastRoleMember.getId())) { 225 RoleMember roleMember = RoleMember.Builder.create(roleId, id, memberId, memberType, 226 activeFromDate, activeToDate, new HashMap<String, String>(), "", "").build(); 227 Long roleVersionNbr = rs.getLong("ROLE_MBR_VER_NBR"); 228 String roleObjId = rs.getString("ROLE_MBR_OBJ_ID"); 229 230 RoleMemberBo roleMemberBo = RoleMemberBo.from(roleMember); 231 roleMemberBo.setVersionNumber(roleVersionNbr); 232 roleMemberBo.setObjectId(roleObjId); 233 List<RoleMemberAttributeDataBo> roleMemAttrBos = new ArrayList<RoleMemberAttributeDataBo>(); 234 235 roleMemberBo.setAttributeDetails(roleMemAttrBos); 236 if(roleMemberBo.isActive(new Timestamp(System.currentTimeMillis()))){ 237 roleMemberBos.add(roleMemberBo); 238 } else { 239 processRolemember = false; 240 } 241 242 lastRoleMember = roleMemberBo; 243 } 244 245 String kimTypeId = rs.getString("KIM_TYP_ID"); 246 if (processRolemember && StringUtils.isNotEmpty(kimTypeId)) { 247 KimType theType = KimApiServiceLocator.getKimTypeInfoService().getKimType(kimTypeId); 248 // Create RoleMemberAttributeDataBo for this row 249 RoleMemberAttributeDataBo roleMemAttrDataBo = new RoleMemberAttributeDataBo(); 250 251 KimAttribute.Builder attrBuilder = KimAttribute.Builder.create( 252 rs.getString("ATTR_DEFN_CMPNT_NM") 253 , rs.getString("ATTR_NAME") 254 , rs.getString("ATTR_DEFN_NMSPC_CD")); 255 attrBuilder.setActive(Truth.strToBooleanIgnoreCase(rs.getString("ATTR_DEFN_ACTV_IND"))); 256 attrBuilder.setAttributeLabel(rs.getString("ATTR_DEFN_LBL")); 257 attrBuilder.setId(rs.getString("KIM_ATTR_DEFN_ID")); 258 attrBuilder.setObjectId(rs.getString("ATTR_DEFN_OBJ_ID")); 259 attrBuilder.setVersionNumber(rs.getLong("ATTR_DEFN_VER_NBR")); 260 261 roleMemAttrDataBo.setId(rs.getString("ATTR_DATA_ID")); 262 roleMemAttrDataBo.setAssignedToId(id); 263 roleMemAttrDataBo.setKimTypeId(kimTypeId); 264 roleMemAttrDataBo.setKimType(KimTypeBo.from(theType)); 265 roleMemAttrDataBo.setKimAttributeId(attrBuilder.getId()); 266 roleMemAttrDataBo.setAttributeValue(rs.getString("ATTR_VAL")); 267 roleMemAttrDataBo.setVersionNumber(attrBuilder.getVersionNumber()); 268 roleMemAttrDataBo.setObjectId(attrBuilder.getObjectId()); 269 270 roleMemAttrDataBo.setKimAttribute(KimAttributeBo.from(attrBuilder.build())); 271 lastRoleMember.getAttributeDetails().add(roleMemAttrDataBo); 272 } 273 274 } 275 } finally { 276 if (rs != null) { 277 rs.close(); 278 } 279 } 280 return roleMemberBos; 281 } 282 } 283 ); 284 return roleMemberBos; 285 } 286 287 public void setDataSource(DataSource dataSource) { 288 this.dataSource = new TransactionAwareDataSourceProxy(dataSource); 289 } 290 291}