writes file content to a Database CLOB

This utility tool writes file content to a Database CLOB .
The tool reads the list of files , creates CLOB of the files and
updates the existing CLOB in the database with the CLOB created newly.

This tool is especially useful in WebDepot architecture where XSLs(stored as CLOB )
need to be updated in the DB.

This tool can also be used for converting XML files as CLOBS and updating in the DB.

The SQL query can be appropriately modified to suit user's needs

Change the below values appropriately to conenct to the DB of your choice:
String url = "jdbc:oracle:thin:@THDEV02.NAM.NSROOT.NET:1521:UAUTHDEV";
String user = "uauth_app4";
String password = "uapdev04";

Create a file : c:\\ListofFilesToUpdateCLOBInDB.txt that contains the list of files to
be converted as CLOB object and updated in the Database .
Sample ListofFilesToUpdateCLOBInDB file will contain:
C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_MENU.xsl
C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_HEADER_REG.xsl

Query to update or insert can be modified as per user's needs.
Sample update query :
prepStm = conn.prepareStatement ( "UPDATE "+tableName+" SET "+ CLOBColumnName+" = ? WHERE "+updateCondition+" ='"+xslName+"'" );

package utility;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.StringReader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import oracle.sql.CLOB;

/**
 * 
 * This utility tool writes file content to a Database CLOB .
 * The tool reads the list of files , creates CLOB of the files and 
 * updates the existing CLOB in the database with the CLOB created newly.
 * 
 * This tool is especially useful in WebDepot architecture where XSLs(stored as CLOB )
 * need to be updated in the DB.
 * 
 * This tool can also be used for converting XML files as CLOBS and updating in the DB.
 * 
 * The SQL query can be appropriately modified to suit user's needs 
 * 
 * 
 */
public class WriteFileToDBClob {

 
 String FileName = null;
 String FileContent = null;
 String xslName = null;
 static oracle.jdbc.OracleConnection conn = null;
 
 public static void main(String[] args) {
  try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
  }
  catch(ClassNotFoundException e){
    System.out.println("Could not load the JDBC driver. " + e);
  }
  /*
   * Connect to the corresponding DB
   */
  String url = "jdbc:oracle:thin:@THDEV02.NAM.NSROOT.NET:1521:UAUTHDEV";
  String user = "uauth_app4";
  String password = "uapdev04";
  try{
    conn = (oracle.jdbc.OracleConnection)
           java.sql.DriverManager.getConnection(url, user, password);
    System.out.println("connected");
  }
  catch(SQLException e){
    System.out.println("Connection attempt failed. " + e);
    e.printStackTrace();
  }
  catch(Exception genex){
   genex.printStackTrace();
  }
  WriteFileToDBClob writeFileToDBClob= new WriteFileToDBClob();
  writeFileToDBClob.readUpdateFileList();
 
 }
 
 public void readUpdateFileList(){
   try{
    BufferedReader reader= new BufferedReader(new FileReader("c:\\ListofFilesToUpdateCLOBInDB.txt"));
    /* The list of files should be in this format :
     C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_MENU.xsl
     C:\Data\views\sp97924_wd_2007_09_shw\project\ct\webconfig\wd_conf\DATAFILES\PAGEXSL\enUS\PLCN\PLCN_JJILL_HEADER_REG.xsl
    */
    while((FileName = reader.readLine())!=null) {
     if(!(FileName.indexOf("%20")>-1)){
      readFile();
      updateFileClobInDB();
     }
     else {
      System.out.println("This file name contains a space which cannot be processed:"+FileName+"\n");
     }
    }
   }
  catch(Exception e){
   e.printStackTrace();
  }
  finally{
   conn = null;
  }
 }
 
 public void readFile(){
  try{
    File f = new File(FileName);
    xslName = f.getName().substring(0,f.getName().length()-4);
    BufferedReader reader = new BufferedReader(new FileReader(f));
    String line = null;
    int count = 0;
    StringBuffer sb = new StringBuffer();
    String replaceString = null;
    Map temp = new HashMap(0);
    while((line = reader.readLine()) != null)
    {
     sb.append(line+"\r\n");
    }
    FileContent = sb.toString();
  }catch(Exception e){
   System.out.println(e);
   e.printStackTrace();
  }
 }
 
 public void updateFileClobInDB(){
  boolean autoCommit = false;
  PreparedStatement prepStm=null;
  try {
   autoCommit = conn.getAutoCommit();
   // autocommit MUST be false for this CLOB
   conn.setAutoCommit(false);
   CLOB newClob = getCLOB(FileContent, conn);
   FileContent = null ;
   // update clob-data in database 
   String tableName = "wd_pagexsl";
   String updateCondition = "template";
   String CLOBColumnName = "XSL";
   prepStm = conn.prepareStatement ( "UPDATE "+tableName+"  SET "+ CLOBColumnName+" = ? WHERE  "+updateCondition+" ='"+xslName+"'" );
   prepStm.setClob ( 1, newClob );
   prepStm.executeUpdate();
   conn.commit(); // commit after each update. You can change this if you prefer committing after all updates.
  } catch(SQLException sqle){
   sqle.printStackTrace();
  }
 }
 private static CLOB getCLOB(String fileContent, Connection conn) throws SQLException{
    CLOB tempClob = null;
    try{
      // If the temporary CLOB has not yet been created, create new
      tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION); 

      // Open the temporary CLOB in readwrite mode to enable writing
      tempClob.open(CLOB.MODE_READWRITE); 
      // Get the output stream to write
      Writer tempClobWriter = tempClob.getCharacterOutputStream(); 
      // Write the data into the temporary CLOB
      tempClobWriter.write(fileContent); 

      // Flush and close the stream
      tempClobWriter.flush();
      tempClobWriter.close(); 

      // Close the temporary CLOB 
      tempClob.close();    
    } catch(SQLException sqlexp){
      tempClob.freeTemporary(); 
      sqlexp.printStackTrace();
    } catch(Exception exp){
      tempClob.freeTemporary(); 
      exp.printStackTrace();
    }
    return tempClob; 
  } 
}