Compare two table columns attributes data – Using SQL Queries

/**
* Create Source table for Compare CSV Files Data
* @param filePath
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void getFileContentSRC(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
CSVParser parser = new CSVParser(new FileReader(filePath), CSVFormat.DEFAULT.withSkipHeaderRecord());

/*sql = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_SRC’;EXCEPTION WHEN OTHERS THEN NULL;END; “;
ps = conn.prepareStatement(sql);
ps.executeUpdate(sql);*/

sql = “CREATE TABLE ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”(MSISDN VARCHAR2(20)) ” ;
try(PreparedStatement ps = conn.prepareStatement(sql)){
ps.executeUpdate(sql);
}
sql = “INSERT INTO ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”(MSISDN) VALUES (?)”;
try(PreparedStatement ps = conn.prepareStatement(sql)){
final int batchSize = 1000;
int count = 0;

for (CSVRecord record : parser) {
ps.setString(1, record.get(0));
ps.addBatch();

if(++count % batchSize == 0) {
ps.executeBatch();
}
}
parser.close();
ps.executeBatch(); // insert remaining records
ps.close();
//conn.close();
}
}
/**
* Create Destination Table for Compare CSV files Data
* @param filePath
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void getFileContentDST(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
CSVParser parser = new CSVParser(new FileReader(filePath), CSVFormat.DEFAULT.withSkipHeaderRecord());

/*sql = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_DST’;EXCEPTION WHEN OTHERS THEN NULL;END; “;
ps = conn.prepareStatement(sql);
ps.executeUpdate(sql);*/

sql = “CREATE TABLE ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”(MSISDN VARCHAR2(20)) ” ;
try(PreparedStatement ps = conn.prepareStatement(sql)){
ps.executeUpdate(sql);
}

sql = “INSERT INTO ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”(MSISDN) VALUES (?)”;
try(PreparedStatement ps = conn.prepareStatement(sql)){
final int batchSize = 1000;
int count = 0;

for (CSVRecord record : parser) {
ps.setString(1, record.get(0));
ps.addBatch();

if(++count % batchSize == 0) {
ps.executeBatch();
}
}
parser.close();
ps.executeBatch(); // insert remaining records*/
ps.close();
}
//conn.close();
}

/**
* Generate Log Files
* @param filePath
* @param conn
* @throws Exception
*/
public void generateLogFiles(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
//Statement ps=null;
String sql=””;
ResultSet resultSet = null;
sql = “SELECT ROWNUM AS ROWNR,’OK’ AS STATUS,S.MSISDN FROM ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+” S,ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+” D WHERE S.MSISDN=D.MSISDN ORDER BY ROWNR “;
try(Statement ps = conn.createStatement()){
resultSet = ps.executeQuery(sql);
ResultSetMetaData meta = resultSet.getMetaData() ;
int numberOfColumns = meta.getColumnCount() ;
String dataHeaders = “” + meta.getColumnName(1) + “” ;
for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
dataHeaders += “,” + meta.getColumnName(i) + “” ;
}
String[] tableHeader = dataHeaders.split(“,”) ;
CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator(“\n”);
String[] headers = format.getHeader();
String FILE_NAME = filePath;
File file = new File(FILE_NAME);
FileWriter fw = new FileWriter(file);
CSVPrinter printer = new CSVPrinter(fw, format.withFirstRecordAsHeader());
for (int i = 0; i < tableHeader.length; i++) {
printer.print(tableHeader[i]);
}
printer.println();
printer.printRecords(resultSet);
fw.close();
printer.close();
}
//conn.close();
}

/**
* Generate Bad Files
* @param filePath
* @param conn
* @throws Exception
*/
public void generateBadFiles(String filePath,Connection conn,String chunkId,String subsetId) throws Exception
{
String sql=””;
ResultSet resultSet = null;
sql = “SELECT ROWNUM AS ROWNR,’2627′ AS ERROR_CODE,’NOT_LOADED’ AS ERROR_REASON,MSISDN FROM ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+” WHERE MSISDN NOT IN (SELECT MSISDN FROM ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”) ORDER BY ROWNR”;
try(Statement ps = conn.createStatement()){
resultSet = ps.executeQuery(sql);
ResultSetMetaData meta = resultSet.getMetaData() ;
int numberOfColumns = meta.getColumnCount() ;
String dataHeaders = “” + meta.getColumnName(1) + “” ;
for (int i = 2 ; i < numberOfColumns + 1 ; i ++ ) {
dataHeaders += “,” + meta.getColumnName(i) + “” ;
}
String[] tableHeader = dataHeaders.split(“,”) ;
CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator(“\n”);
String[] headers = format.getHeader();
String FILE_NAME = filePath;
File file = new File(FILE_NAME);
FileWriter fw = new FileWriter(file);
CSVPrinter printer = new CSVPrinter(fw, format.withFirstRecordAsHeader());
for (int i = 0; i < tableHeader.length; i++) {
printer.print(tableHeader[i]);
}
printer.println();
printer.printRecords(resultSet);
fw.close();
printer.close();
//conn.close();
}
}

/**
* This will drop the compare tables Source and Destination tables
* @param conn
* @param chunkId
* @param subsetId
* @throws Exception
*/
public void dropComparisonTables(Connection conn,String chunkId,String subsetId) throws Exception
{

String sqlSource=””;
String sqlDestination=””;

sqlSource = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_SRC_”+chunkId+”_”+subsetId+”‘;EXCEPTION WHEN OTHERS THEN NULL;END; “;
try(PreparedStatement psSource = conn.prepareStatement(sqlSource)){
psSource.executeUpdate(sqlSource);
}

sqlDestination = “BEGIN EXECUTE IMMEDIATE ‘DROP TABLE ZZ_COMPARE_CSV_DST_”+chunkId+”_”+subsetId+”‘;EXCEPTION WHEN OTHERS THEN NULL;END; “;
try(PreparedStatement psDestination = conn.prepareStatement(sqlDestination)){
psDestination.executeUpdate(sqlDestination);
}
//conn.close();
}

About ashokabhat

I am a C,C ,JAVA,Adobe Flex,.NET Programmer Currently working as a Software Developer
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s