Sample Stored Procedure and Groovy Script for Full or Filter Reconciliation

The following is a sample groovy script for performing full or filter reconciliation.

The full reconciliation procedure is called as follows:

st = conn.prepareCall("{call EXECUTE_QUERY(?)}");

The filtered reconciliation procedure is called as follows:

st = conn.prepareCall("{call EXECUTE_QUERY_WITH_FILTER(?,?,?)}");

The get user role procedure is called as follows:

roleStmt = conn.prepareCall("{call GET_USERROLE(?,?)}");

The get user group procedure is called as follows:

groupStmt = conn.prepareCall("{call GET_USERGROUP(?,?)}");

The procedure for EXECUTE_QUERY is as follows:

create or replace PROCEDURE EXECUTE_QUERY
( user_cursor OUT TYPES.cursorType
) AS
BEGIN
OPEN user_cursor FOR
SELECT USERINFO.USERID, USERINFO.FIRSTNAME , USERINFO.LASTNAME, USERINFO.EMAIL ,USERINFO.DESCRIPTION,USERINFO.SALARY,USERINFO.JOININGDATE ,USERINFO.STATUS FROM USERINFO;
END EXECUTE_QUERY;

The procedure for EXECUTE_QUERY_WITH_FILTER is as follows:

create or replace PROCEDURE EXECUTE_QUERY_WITH_FILTER
( user_cursor OUT TYPES.cursorType, columnName IN VARCHAR2, columnValue IN VARCHAR2
) AS
BEGIN
open user_cursor for 'SELECT USERINFO.USERID, USERINFO.FIRSTNAME , USERINFO.LASTNAME, USERINFO.EMAIL ,USERINFO.DESCRIPTION,USERINFO.SALARY,USERINFO.JOININGDATE ,USERINFO.STATUS FROM USERINFO USERINFO where '|| columnName ||' = '''||columnValue||'''';
END EXECUTE_QUERY_WITH_FILTER;

The procedure for GET_USERROLE is as follows:

create or replace PROCEDURE GET_USERROLE
( user_cursor OUT TYPES.cursorType, userin IN VARCHAR2
) AS
BEGIN
OPEN user_cursor FOR
SELECT ROLEID,FROMDATE,TODATE from USER_ROLE where USERID=userin;
END GET_USERROLE;

The procedure for GET_USERGROUP is as follows:

create or replace PROCEDURE GET_USERGROUP
( user_cursor OUT TYPES.cursorType, userin IN VARCHAR2
) AS
BEGIN
OPEN user_cursor FOR
SELECT GROUPID from USER_GROUP where USERID=userin;
END GET_USERGROUP;

import org.identityconnectors.framework.common.objects.*;
import java.lang.reflect.*;
import java.lang.String;
import org.identityconnectors.common.security.GuardedString;
import java.text.*;
 
rs = null;
st = null;
try {
        if( filterString != "")
        {
                System.out.println("[Execute Query] Performing Recon with Filter. Filter is::"+ filterString+" And Filer Params are::"+filterParams);
                String[] filter = filterParams.get(0).split(":");
            st = conn.prepareCall("{call EXECUTE_QUERY_WITH_FILTER(?,?,?)}");
                st.setString(2, filter[0]);
                st.setString(3, filter[1]);
        }
        else
        {
                System.out.println("[Execute Query] Performing Full Recon.");
                st = conn.prepareCall("{call EXECUTE_QUERY(?)}");
}
        st.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        st.execute();
        rs = st.getObject(1);
        SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
 
        while (rs.next()) {
                cob = new ConnectorObjectBuilder();
                cob.setObjectClass(ObjectClass.ACCOUNT);
                Attribute fname= AttributeBuilder.build(new String("FIRSTNAME"),rs.getString(2));
                Attribute lname= AttributeBuilder.build(new String("LASTNAME"),rs.getString(3));
                Attribute uid= AttributeBuilder.build(new String("__UID__"),rs.getString(1));
                Attribute name= AttributeBuilder.build(new String("__NAME__"),rs.getString(1));
                Attribute email= AttributeBuilder.build(new String("EMAIL"),rs.getString(4));
                Attribute salary= AttributeBuilder.build(new String("SALARY"),rs.getBigDecimal(6));
                Attribute description= AttributeBuilder.build(new String("DESCRIPTION"),rs.getString(5));
                dbDate = rs.getDate(7);
                joinDateStr = null;
                if( null != dbDate)
                {
                        java.util.Date date= df.parse(dbDate.toString());
                        joinDateStr = targetFormat.format(date);
                }
                Attribute joindate= AttributeBuilder.build(new String("JOININGDATE"),joinDateStr);
                Attribute status= AttributeBuilder.build(new String("STATUS"),rs.getString(8));
                                cob.addAttribute(fname);
                cob.addAttribute(lname);
                cob.addAttribute(uid);
                cob.addAttribute(name);
                cob.addAttribute(email);
                cob.addAttribute(salary);
                cob.addAttribute(description);
                cob.addAttribute(joindate);
                cob.addAttribute(status);
                roleStmt = conn.prepareCall("{call GET_USERROLE(?,?)}");
                roleStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        roleStmt.setString(2, rs.getString(1));
        roleStmt.execute();
                roleResultSet = roleStmt.getObject(1);
                java.util.List<EmbeddedObject> eoList = new ArrayList<EmbeddedObject>();
                while (roleResultSet.next()) {
                        Attribute roleId= AttributeBuilder.build(new String("ROLEID"),roleResultSet.getString(1));
                                                dbDate = roleResultSet.getDate(2);
                        fromDateStr = null;
                        if( null != dbDate)
                        {
                                java.util.Date date= df.parse(dbDate.toString());
                                fromDateStr = targetFormat.format(date);
                        }
                        dbDate = roleResultSet.getDate(2);
                        toDateStr = null;
                        if( null != dbDate)
                        {
                                java.util.Date date= df.parse(dbDate.toString());
                                toDateStr = targetFormat.format(date);
                        }
                
                        Attribute fromdate= AttributeBuilder.build(new String("FROMDATE"),fromDateStr);
                        Attribute todate= AttributeBuilder.build(new String("TODATE"),toDateStr);
                                        EmbeddedObjectBuilder roleEA = new EmbeddedObjectBuilder();
                        roleEA.addAttribute(roleId);
                        roleEA.addAttribute(fromdate);
                        roleEA.addAttribute(todate);
                        roleEA.setObjectClass(new ObjectClass("USER_ROLE"));
                        eoList.add(roleEA.build());
                }  
                roleResultSet.close();
                roleStmt.close();
                EmbeddedObject[] roleEm = eoList.toArray(new EmbeddedObject[eoList.size()]);
cob.addAttribute(AttributeBuilder.build("USER_ROLE", (Object[]) roleEm));
                groupStmt = conn.prepareCall("{call GET_USERGROUP(?,?)}");
                groupStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        groupStmt.setString(2, rs.getString(1));
        groupStmt.execute();
                groupResultSet = groupStmt.getObject(1);
                java.util.List<EmbeddedObject> geoList = new ArrayList<EmbeddedObject>();
                while (groupResultSet.next()) {
                        Attribute groupId= AttributeBuilder.build(new String("GROUPID"),groupResultSet.getString(1));
                        EmbeddedObjectBuilder groupEA = new EmbeddedObjectBuilder();
                        groupEA.addAttribute(groupId);
                        groupEA.setObjectClass(new ObjectClass("USER_GROUP"));
                        geoList.add(groupEA.build());
                }  
                groupResultSet.close();
                groupStmt.close();
                EmbeddedObject[] groupEm = geoList.toArray(new EmbeddedObject[geoList.size()]);
cob.addAttribute(AttributeBuilder.build("USER_GROUP", (Object[]) groupEm));
        
                if(!handler.handle(cob.build())) return;
        } } finally {
        if( null != rs)
        rs.close();
        if( null != st)
        st.close();
}