import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
import javax.sql.DataSource;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class FindInActiveCustomer {
private final static String RAPID_CUSTOMER_QUERY = "SELECT TOP 100 A.GFCID,A.CAGID,B.ACTIVE_IND FROM VA_CUS_CUSTOMER A left join VA_CUS_RISK_ATTRIBUTE B on A.CUSTOMER_ID=B.CUSTOMER_ID WHERE B.CUSTOMER_ID IS NULL AND A.GFCID LIKE '000001%' AND A.CAGID=A.GFCID ORDER BY GFCID ASC";
private final String RISKODS_CUSTOMER_QUERY = "SELECT C.CUSTOMER_ID, CUSTOMER_NAME, CAGID, GFPID, GFCID, "
+ "DECODE(CAGID, GFCID, CUSTOMER_NAME, (SELECT R.CUSTOMER_NAME FROM V_CUS_CUSTOMER R WHERE R.CUSTOMER_ID = C.CAGID)) AS RSHIP_NAME, "
+ " APPROVAL_RISK_RATING_CD, APPROVAL_RISK_RATING_DT, LIMIT_RATING_CD, NVL(LIMIT_RATING_DT,APPROVAL_RISK_RATING_DT) LIMIT_RATING_DT, RCA_DT,"
+ " INDEPENDENT_RISK_UNIT_CD,(SELECT UNIT_DESC FROM V_UNIT WHERE INDEPENDENT_RISK_UNIT_CD = UNIT_CD) AS IND_RISK_UNIT_DESC,"
+ " (SELECT CHOICE_LONG_DESC"
+ " FROM V_CUS_MANAGER M, V_REF_CHOICE R "
+ " WHERE M.MANAGER_GEID=CHOICE_CD AND REF_CHOICE_GRP_CD = 'MANAGER_GEID' "
+ " AND C.CUSTOMER_ID = M.CUSTOMER_ID AND MANAGER_TYPE_CD = 2) AS SEN_RISK_MANAGER"
+ " FROM V_CUS_CUSTOMER C, V_CUS_CONFIDENTIAL_RATING, V_CUS_RISK_ATTRIBUTE "
+ " WHERE C.CUSTOMER_ID = V_CUS_CONFIDENTIAL_RATING.CUSTOMER_ID AND "
+ " C.CUSTOMER_ID = V_CUS_RISK_ATTRIBUTE.CUSTOMER_ID ";
//+ " AND C.GFCID = :gfcId";
private static DataSource rapidDataSource;
private static DataSource odsDataSource;
@BeforeClass
public static void beforeClass(){
ApplicationContext context =
new ClassPathXmlApplicationContext("db-context2.xml");
rapidDataSource=(DataSource) context.getBean("rapidDataSource");
odsDataSource=(DataSource) context.getBean("odsDataSource");
}
@Test
public void find() throws SQLException{
Connection conn1 = rapidDataSource.getConnection();
PreparedStatement ps1 = conn1.prepareStatement(RAPID_CUSTOMER_QUERY);
ResultSet rs1= ps1.executeQuery();
rs1.setFetchSize(10);
String element=null;
while(rs1.next()){
element=rs1.getString(1);
System.out.println(" "+element);
//new FindInActiveCustomer().find_RISKODS_(element);
}
}
public void find_RISKODS_(String element) throws SQLException{
Connection conn = odsDataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(RISKODS_CUSTOMER_QUERY);
ResultSet rs = ps.executeQuery();
rs.setFetchSize(10);
String element2=null;
while(rs.next()){
element2=rs.getString(1);
//System.out.print(" "+rs.getString(1));
if(element.equals(element2));
System.out.print("The Same -----"+element);
}
}
}