Example usage for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator

List of usage examples for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator

Introduction

In this page you can find the example usage for org.springframework.jdbc.core PreparedStatementCreator PreparedStatementCreator.

Prototype

PreparedStatementCreator

Source Link

Usage

From source file:org.kuali.kpme.core.block.dao.CalendarBlockDaoJdbcImpl.java

@Override
public List<CalendarBlock> getAllCalendarBlocks() {
    List<CalendarBlock> calendarBlocks = new ArrayList<CalendarBlock>();
    PreparedStatementCreator psc = new PreparedStatementCreator() {

        @Override//  w  ww  .jav a2 s.  c o m
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            String query = "SELECT begin_ts, end_ts, lm_leave_block_id as c_block_id, 'Leave' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, principal_id_modified as user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code, "
                    + "'N' as lunch_deleted, null as overtime_pref, null as hours, leave_amount as amount "
                    + "FROM lm_leave_block_t " + "UNION "
                    + "SELECT begin_ts, end_ts, tk_time_block_id as c_block_id, 'Time' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code, "
                    + "lunch_deleted, ovt_pref as overtime_pref, hours, amount " + "FROM tk_time_block_t;";

            return conn.prepareStatement(query);
        }

    };
    calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
    return calendarBlocks;
}

From source file:org.kuali.kpme.core.block.dao.CalendarBlockDaoJdbcImpl.java

@Override
public List<CalendarBlock> getActiveCalendarBlocksForDate(LocalDate asOfDate) {
    PreparedStatementCreator psc = new PreparedStatementCreator() {
        /**/*from ww  w  .  j  a  v a  2 s . co m*/
         * TODO: For use, our effective dating strategy must be included in the query below.
         */
        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            String query = "SELECT begin_ts, end_ts, tk_time_block_id as c_block_id, 'Time' as c_block_type, document_id, job_number, obj_id, ver_nbr, principal_id, user_principal_id, timestamp, task, grp_key_cd, work_area, earn_code "
                    + "FROM tk_time_block_t";

            return conn.prepareStatement(query);
        }

    };

    List<CalendarBlock> calendarBlocks = this.getJdbcTemplate().query(psc, new CalendarBlockRowMapper());
    return calendarBlocks;
}

From source file:org.kuali.kpme.core.block.dao.CalendarBlockDaoJdbcImpl.java

@Override
public DateTime getLatestEndTimestampForAssignment(Assignment assignment, String calendarBlockType) {

    PreparedStatementCreator timeBlockPSC = new PreparedStatementCreator() {

        @Override//from  w  w w . jav  a2  s . co  m
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT max(end_ts) ");
            sql.append("FROM tk_time_block_t ");
            sql.append("WHERE principal_id = ? AND job_number=? AND task=? AND work_area=?");

            String query = sql.toString();

            return conn.prepareStatement(query);
        }
    };

    PreparedStatementCreator leaveBlockPSC = new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT max(end_ts) ");
            sql.append("FROM lm_leave_block_t ");
            sql.append("WHERE principal_id = ? AND job_number=? AND task=? AND work_area=?");

            String query = sql.toString();

            return conn.prepareStatement(query);
        }
    };

    try {
        PreparedStatement statement = null;
        if (StringUtils.equals(calendarBlockType, "Time")) {
            statement = timeBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
        } else if (StringUtils.equals(calendarBlockType, "Leave")) {
            statement = leaveBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
        } else {
            throw new IllegalArgumentException("calendarBlockType must be one of 'Time' or 'Leave'");
        }
        if (statement != null) {
            statement.setString(1, assignment.getPrincipalId());
            statement.setString(2, assignment.getJobNumber().toString());
            statement.setString(3, assignment.getTask().toString());
            statement.setString(4, assignment.getWorkArea().toString());
        }

        ResultSet rs = statement.executeQuery();
        if (rs != null) {
            boolean empty = !rs.first();
            Timestamp maxDate = rs.getTimestamp("max(end_ts)");
            if (maxDate == null) {
                return null;
            } else {
                return new DateTime(maxDate.getTime());
            }
        }
    } catch (SQLException e) {
        LOG.warn("error creating or executing sql statement");
        throw new RuntimeException();
    }
    return null;
}

From source file:org.kuali.kpme.core.block.dao.CalendarBlockDaoJdbcImpl.java

@Override
public DateTime getLatestEndTimestampForEarnCode(String earnCode, String calendarBlockType) {

    PreparedStatementCreator timeBlockPSC = new PreparedStatementCreator() {

        @Override//from   ww  w  .j  a  v a 2 s  .  com
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT max(end_ts) ");
            sql.append("FROM tk_time_block_t ");
            sql.append("WHERE earn_code = ?");

            String query = sql.toString();

            return conn.prepareStatement(query);
        }
    };

    PreparedStatementCreator leaveBlockPSC = new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT max(end_ts) ");
            sql.append("FROM lm_leave_block_t ");
            sql.append("WHERE earn_code = ?");

            String query = sql.toString();

            return conn.prepareStatement(query);
        }
    };
    try {
        PreparedStatement statement = null;
        if (StringUtils.equals(calendarBlockType, "Time")) {
            statement = timeBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
        } else if (StringUtils.equals(calendarBlockType, "Leave")) {
            statement = leaveBlockPSC.createPreparedStatement(this.getDataSource().getConnection());
        } else {
            throw new IllegalArgumentException("calendarBlockType must be one of 'Time' or 'Leave'");
        }
        if (statement != null) {
            statement.setString(1, earnCode);
        }

        ResultSet rs = statement.executeQuery();
        if (rs != null) {
            boolean empty = !rs.first();
            Timestamp maxDate = rs.getTimestamp("max(end_ts)");
            if (maxDate == null) {
                return null;
            } else {
                return new DateTime(maxDate.getTime());
            }
        }
    } catch (SQLException e) {
        LOG.warn("error creating or executing sql statement");
        throw new RuntimeException();
    }
    return null;
}

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;
        }/* ww w  .j  ava 2  s  . co  m*/
    }, 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() {

        /*/* w  w  w.jav a  2s . 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')
        */

        @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  www  .  j  a va  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.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java

@Override
public Integer saveAgency(final Agency agency) {

    log.debug("Inserting row into Agency table");

    final String agencyInsertStatement = "INSERT into AGENCY (AgencyName, AgencyORI) values (?,?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps = connection.prepareStatement(agencyInsertStatement,
                    new String[] { "AgencyName", "AgencyORI" });
            ps.setString(1, agency.getAgencyName());
            ps.setString(2, agency.getAgencyOri());
            return ps;
        }//from  w ww.  jav a 2  s. c o  m
    }, keyHolder);

    return keyHolder.getKey().intValue();
}

From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java

@Override
public Integer saveIncident(final Incident inboundIncident) {
    log.debug("Inserting row into Incident table: " + inboundIncident.toString());

    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

            String incidentInsertStatement = "";
            String[] insertArgs = null;

            if (inboundIncident.getIncidentID() == null) {
                incidentInsertStatement = "INSERT into INCIDENT (ReportingAgencyID, IncidentCaseNumber,"
                        + "IncidentLocationLatitude, IncidentLocationLongitude, IncidentLocationStreetAddress,IncidentLocationTown,IncidentDate,IncidentTime,ReportingSystem,RecordType) values (?,?,?,?,?,?,?,?,?,?)";

                insertArgs = new String[] { "ReportingAgencyID",
                        "IncidentCaseNumber" + "IncidentLocationLatitude", "IncidentLocationLongitude",
                        "IncidentLocationStreetAddress", "IncidentLocationTown", "IncidentDate", "IncidentTime",
                        "ReportingSystem", "RecordType" };
            } else {
                incidentInsertStatement = "INSERT into INCIDENT (ReportingAgencyID, IncidentCaseNumber,"
                        + "IncidentLocationLatitude, IncidentLocationLongitude, IncidentLocationStreetAddress,IncidentLocationTown,IncidentDate,IncidentTime,ReportingSystem,RecordType, IncidentID) values (?,?,?,?,?,?,?,?,?,?,?)";

                insertArgs = new String[] { "ReportingAgencyID",
                        "IncidentCaseNumber" + "IncidentLocationLatitude", "IncidentLocationLongitude",
                        "IncidentLocationStreetAddress", "IncidentLocationTown", "IncidentDate", "IncidentTime",
                        "ReportingSystem", "RecordType", "IncidentID" };
            }//w  w w . j a  v  a  2  s  . c o  m

            PreparedStatement ps = connection.prepareStatement(incidentInsertStatement, insertArgs);
            if (inboundIncident.getReportingAgencyID() != null) {
                ps.setInt(1, inboundIncident.getReportingAgencyID());
            } else {
                ps.setNull(1, java.sql.Types.NULL);
            }

            ps.setString(2, inboundIncident.getIncidentCaseNumber());
            ps.setBigDecimal(3, inboundIncident.getIncidentLocationLatitude());
            ps.setBigDecimal(4, inboundIncident.getIncidentLocationLongitude());
            ps.setString(5, inboundIncident.getIncidentLocationStreetAddress());
            ps.setString(6, inboundIncident.getIncidentLocationTown());
            ps.setDate(7, new java.sql.Date(inboundIncident.getIncidentDate().getTime()));
            ps.setTime(8, new java.sql.Time(inboundIncident.getIncidentDate().getTime()));
            ps.setString(9, inboundIncident.getReportingSystem());
            ps.setString(10, String.valueOf(inboundIncident.getRecordType()));

            if (inboundIncident.getIncidentID() != null) {
                ps.setInt(11, inboundIncident.getIncidentID());
            }

            return ps;
        }
    }, keyHolder);

    Integer returnValue = null;

    if (inboundIncident.getIncidentID() != null) {
        returnValue = inboundIncident.getIncidentID();
    } else {
        returnValue = keyHolder.getKey().intValue();
    }

    return returnValue;
}