Kindly consider below scenario,
Base Functionality
Reading CSV file and insert in database after replacing values with web macro.
Reading values from csv @ first header information NO,NAME next to that read one by one values from csv and put into web macro context like below context.put("1","RAJARAJAN") next web macro replace $(NO) ==>1 and $(NAME)==>RAJARAJAN values taken from csv once web macro replacement done next add in statement batch once it reached 1000 execute the batch.
Code is running as per functionality but it takes 4 minutes to parse 50,000 records need performance improvement or need to change logic ....kindly let me know if any doubts. Any change to drastic performance...
Note: I use web macro because to replace $(NO) in merge query
Bala.csv
NO?NAME ==================>Header Information
1?RAJARAJAN
2?ARUN
3?ARUNKUMAR
Connection con=null;
Statement stmt=null;
Connection con1=null;
int counter=0;
try{
WebMacro wm = new WM();
Context context = wm.getContext();
String strFilePath = "/home/vbalamurugan/3A/Bala.csv";
String msg="merge into temp2 A using
(select '$(NO)' NO,'$(NAME)' NAME from dual)B on(A.NO=B.NO)
when not matched then insert (NO,NAME)
values(B.NO,B.NAME) when matched then
update set A.NAME='Attai' where A.NO='$(NO)'";
String[]rowsAsTokens;
con=getOracleConnection("localhost","raymedi_hq","raymedi_hq","XE");
con.setAutoCommit(false);
stmt=con.createStatement();
File file = new File(strFilePath);
Scanner scanner = new Scanner(file);
try {
String headerField;
String header[];
headerField=scanner.nextLine();
header=headerField.split("\\?");
long start=System.currentTimeMillis();
while(scanner.hasNext()) {
String scan[]=scanner.nextLine().split("\\?");
for(int i=0;i<scan.length;i++){
context.put(header[i],scan[i]);
}
if(context.size()>0){
String m=replacingWebMacroStatement(msg,wm,context);
if(counter>1000){
stmt.executeBatch();
stmt.clearBatch();
counter=0;
}else{
stmt.addBatch(m);
counter++;
}
}
}
long b=System.currentTimeMillis()-start;
System.out.println("=======Total Time Taken"+b);
}catch(Exception e){
e.printStackTrace();
}
finally {
scanner.close();
}
stmt.executeBatch();
stmt.clearBatch();
stmt.close();
}catch(Exception e){
e.printStackTrace();
con.rollback();
}finally{
con.commit();
}
// Method For replace webmacro with $
public static String
replacingWebMacroStatement(String Query, WebMacro wm,
Context context) throws Exception {
Template template = new StringTemplate(wm.getBroker(), Query);
template.parse();
String macro_replaced = template.evaluateAsString(context);
return macro_replaced;
}
// for getting oracle connection
public static Connection
getOracleConnection(String IPaddress,
String username,String password,String Tns)throws SQLException{
Connection connection = null;
try{
String baseconnectionurl ="jdbc:oracle:thin:@"+IPaddress+":1521:"+Tns;
String driver = "oracle.jdbc.driver.OracleDriver";
String user = username;
String pass = password;
Class.forName(driver);
connection=DriverManager.getConnection(baseconnectionurl,user,pass);
}catch(Exception e){
e.printStackTrace();
}
return connection;
}
