import java.io.*;
import java.sql.*;
import java.text.*;

public class ConcatenatingStringsForIUD {
  Connection conn;
  public ConcatenatingStringsForIUD() {
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
    }
    catch (SQLException e) {
     System.err.println(e.getMessage());
     e.printStackTrace();
    }
  }

  public static void main(String[] args) 
   throws Exception, IOException {
    ConcatenatingStringsForIUD iud = new ConcatenatingStringsForIUD();

    String last_name           = "O'Reilly";
    String first_name          = "Tim";
    String middle_name         = null;
    Date   birth_date          = Date.valueOf("1971-03-17");
    String mothers_maiden_name = "Oh! I don't know!";

    iud.executeIUD(
     "delete PERSON " + 
     "where  last_name = " + 
     iud.formatWithTicks(last_name) + " " +
     "and    first_name = " + 
     iud.formatWithTicks(first_name));

    iud.executeIUD(
     "insert into PERSON " + 
     "(person_id, last_name, first_name, middle_name, " + 
     "birth_date, mothers_maiden_name) values " +
     "(person_id.nextval, " + 
     iud.formatWithTicks(last_name) + ", " + 
     iud.formatWithTicks(first_name) + ", " + 
     iud.formatWithTicks(middle_name) + ", " + 
     iud.formatWithOracleDate(birth_date) + ", " + 
     iud.formatWithTicks(mothers_maiden_name) + ")");

    birth_date = Date.valueOf("1972-03-17");

    iud.executeIUD(
     "update PERSON " + 
     "set    birth_date = " + 
     iud.formatWithSql92Date(birth_date) + " " +
     "where  last_name = " + 
     iud.formatWithTicks(last_name) + " " +
     "and    first_name = " + 
     iud.formatWithTicks(first_name));
  }

  private String formatWithOracleDate(Date date) {
    if (date != null) {
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
      return "to_date('" + sdf.format(date) + "','YYYY-MM-DD HH24:MI:SS')";
    }
    else {
      return "NULL";
    }
  }

  private String formatWithSql92Date(Date date) {
    if (date != null) {
      SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
      return "{ts '" + sdf.format(date) + "'}";
    }
    else {
      return "NULL";
    }
  }

  private String formatWithTicks(String string) {
    if (string != null) {
      char[]       in  = string.toCharArray();
      StringBuffer out = new StringBuffer((int)(in.length * 1.1));
      if (in.length > 0)
        out.append("'");
      for (int i=0;i < in.length;i++) {
        out.append(in[i]);
        if (in[i] == '\'') 
          out.append(in[i]);
      }
      if (in.length > 0)
        out.append("'");
      return out.toString();
    }
    else {
      return "NULL";
    }
  }

  public void executeIUD(String sql) 
   throws IOException, SQLException {
    int       rslt = 0;
    Statement stmt = null;
    System.out.println(sql);
    try {
      stmt = conn.createStatement();
      rslt = stmt.executeUpdate(sql);
      System.out.println(Integer.toString(rslt) + " rows affected");
      System.out.println(" ");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }
    finally {
      if (stmt != null) 
      try { stmt.close(); } catch (SQLException ignore) { }
    }
  }

  protected void finalize() 
   throws Throwable {
    if (conn != null) 
      { try { conn.close(); } catch (SQLException ignore) { } }
    super.finalize();
  }
}