//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
// 
// The following sample of source code ("Sample") is owned by International 
// Business Machines Corporation or one of its subsidiaries ("IBM") and is 
// copyrighted and licensed, not sold. You may use, copy, modify, and 
// distribute the Sample in any form without payment to IBM, for the purpose of 
// assisting you in the development of your applications.
// 
// The Sample code is provided to you on an "AS IS" basis, without warranty of 
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
// not allow for the exclusion or limitation of implied warranties, so the above 
// limitations or exclusions may not apply to you. IBM shall not be liable for 
// any damages you suffer as a result of using, copying, modifying or 
// distributing the Sample, even if IBM has been advised of the possibility of 
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: TbMerge.java
//
// SAMPLE: How to use the MERGE statement
//
// SQL Statements USED:
//         SELECT
//         UPDATE
//         DELETE
//         INSERT
//         MERGE
//
// Classes used from Util.java are:
//         Db
//         Data
//         JdbcException
//
//                           
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
//     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/

import java.lang.*;
import java.sql.*;

public class TbMerge
{
  public static void main(String argv[])
  {
    try
    {
      Db db = new Db(argv);

      System.out.println();
      System.out.println(
        "  THIS SAMPLE SHOWS HOW TO USE THE 'MERGE' STATEMENT\n");

      // connect to the 'sample' database
      db.connect();

      // create the 'empsamp' table
      CreateTable(db.con);

      // make changes to the 'empsamp' table
      ChangeTable(db.con);

      // apply the changes from table 'empsamp' table to the
      // 'staff' table
      MergeTables(db.con);

      // drop the 'empsamp' table
      Statement stmt = db.con.createStatement();
      stmt.executeUpdate("DROP TABLE empsamp");
      stmt.close();

      // disconnect from the 'sample' database
      db.disconnect();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    } 
  } // main

  // This method creates the 'empsamp' table and inserts some values into it
  public static void CreateTable(Connection conn)
  {
    try
    {
      System.out.println(
        "\n  -----------------------------------------------------------" +
        "\n  USE THE SQL STATEMENT:\n" +
        "    CREATE TABLE\n" +
        "  TO CREATE A TABLE IN THE SAMPLE DATABASE.\n");

      // create the table
      System.out.println(
        "\n  Create a table 'EMPSAMP' with attributes:" +
        "\n    ID SMALLINT NOT NULL," +
        "\n    NAME VARCHAR(9)," +
        "\n    DEPT SMALLINT," +
        "\n    JOB CHAR(5)," +
        "\n    YEARS SMALLINT," +
        "\n    SALARY DEC(7,2)," +
        "\n    COMM DEC(7,2)," +
        "\n    PRIMARY KEY(ID)");  
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empsamp(" + 
        "  ID SMALLINT NOT NULL," +
        "  NAME VARCHAR(9)," +
        "  DEPT SMALLINT," +
        "  JOB CHAR(5)," +
        "  YEARS SMALLINT," +
        "  SALARY DEC(7,2)," +
        "  COMM DEC(7,2)," +
        "  PRIMARY KEY(ID))");  

      // insert some values into the table
      System.out.println("\n  Insert values into EMPSAMP");
      System.out.println("\n  Invoke the statement:\n" +
                         "\n    INSERT INTO empsamp " +
                         "SELECT * FROM staff WHERE ID >= 310");
      stmt.executeUpdate("INSERT INTO empsamp" +
                         "  SELECT * FROM staff" + 
                         "    WHERE ID >= 310");
      stmt.close();

      // display the final content of the 'empsamp' table
      TbContentDisplay(conn, "empsamp");

      // commit the transaction
      conn.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, conn);
      jdbcExc.handle();
    } 
  } // CreateTable

  // This method makes changes to the 'empsamp' table
  public static void ChangeTable(Connection conn)
  {
    try
    {
      System.out.println(
        "\n  -----------------------------------------------------------" +
        "\n  USE THE SQL STATEMENTS:\n" +
        "    UPDATE\n" +
        "    INSERT\n" +
        "  TO MAKE CHANGES TO THE 'empsamp' TABLE.\n");
  
      // display the initial contents of the 'empsamp' table
      TbContentDisplay(conn, "empsamp");

      // make changes and insert values into the 'empsamp' table
      System.out.println(
        "\n  Invoke the statement\n\n" +
        "    INSERT INTO empsamp(id, name, dept, job, salary)\n" +
        "      VALUES(380, 'Pearce', 38, 'Clerk', 13217.50),\n" +
        "            (390, 'Hachey', 38, 'Mgr', 21270.00),\n" +
        "            (400, 'Wagland', 38, 'Clerk', 14575.00)\n");

      System.out.println(
        "\n  Invoke the statements:\n" +
        "\n    UPDATE empsamp SET job = 'Mgr' WHERE id = 310" +
        "\n    UPDATE empsamp SET job = 'Sales', salary = 15000.00" + 
        " WHERE id = 350" +
        "\n    UPDATE empsamp SET name = '-' WHERE id = 320");

      Statement stmt = conn.createStatement();
      stmt.executeUpdate(
        "INSERT INTO empsamp(id, name, dept, job, salary)" +
        "  VALUES(380, 'Pearce', 38, 'Clerk', 13217.50)," +
        "        (390, 'Hachey', 38, 'Mgr', 21270.00)," +
        "        (400, 'Wagland', 38, 'Clerk', 14575.00)");

      stmt.executeUpdate("UPDATE empsamp SET job = 'Mgr' WHERE id = 310");
      stmt.executeUpdate("UPDATE empsamp " + 
                         "  SET job = 'Sales', salary = 15000.00" + 
                         "  WHERE id = 350");
      stmt.executeUpdate("UPDATE empsamp SET name = '-' WHERE id = 320");
      stmt.close();

      // display the content of the final 'empsamp' table
      TbContentDisplay(conn, "empsamp");

      // commit the transaction
      conn.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, conn);
      jdbcExc.handle();
    } 
  } // ChangeTable

  // This method applies the changes from the 'empsamp' table
  // to the 'staff' table using the MERGE statement
  public static void MergeTables(Connection conn)
  {
    try
    {
      System.out.println(
        "\n  -----------------------------------------------------------" +
        "\n  USE THE SQL STATEMENT:\n" +
        "    MERGE\n" +
        "  TO APPLY CHANGES FROM TABLE 'empsamp' TO TABLE 'staff'\n");

      // display the initial contents of the 'staff' table
      TbContentDisplay(conn, "staff");

      // apply changes from the 'empsamp' table to the 'staff' table 
      // with the MERGE statement
      System.out.println(
        "\n  Merge tables" +
        "\n  Invoke the statement:\n" +
        "\n    MERGE INTO staff S" +
        "\n      USING (SELECT * FROM empsamp) E" +
        "\n        ON (S.id = E.id)" +
        "\n          WHEN MATCHED AND E.name != '-' THEN" +
        "\n            UPDATE SET (name, dept, job, years, salary, comm) =" +
        "\n                       (E.name, E.dept, E.job, E.years," +
        " E.salary, E.comm)" +
        "\n          WHEN NOT MATCHED THEN" +
        "\n            INSERT (id, name, dept, job, years, salary, comm)" +
        "\n              VALUES (E.id, E.name, E.dept, E.job, E.years," +
        " E.salary, E.comm)" +
        "\n          ELSE" +
        "\n            IGNORE\n");

      Statement stmt = conn.createStatement();
      stmt.executeUpdate(  
        "MERGE INTO staff S" +
        "  USING (SELECT * FROM empsamp) E" +
        "    ON (S.id = E.id)" +
        "      WHEN MATCHED AND E.name != '-' THEN" +
        "        UPDATE SET (name, dept, job, years, salary, comm) =" +
        "        (E.name, E.dept, E.job, E.years, E.salary, E.comm)" +
        "      WHEN NOT MATCHED THEN" +
        "        INSERT (id, name, dept, job, years, salary, comm)" +
        "          VALUES (E.id, E.name, E.dept, E.job, E.years," + 
        "                  E.salary, E.comm)" +
        "      ELSE" +
        "        IGNORE");

      stmt.close();

      // display the contents of the final 'staff' table
      TbContentDisplay(conn, "staff");

      // rollback the transaction
      conn.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, conn);
      jdbcExc.handle();
    } 
  } // MergeTables

  // helping function: Display the contents of the 'staff' or 'empsamp' table
  public static void TbContentDisplay(Connection conn, String tablename) 
  {
    try
    {
      Integer id = new Integer(0);
      String name = null;
      Integer dept = new Integer(0);
      String job = null;
      Integer years = new Integer(0);
      Double salary = new Double(0.0);
      Double comm = new Double(0.0);

      System.out.println();
      System.out.println(
        "  SELECT * FROM " + tablename + " WHERE id >= 310\n" +
        "    ID  NAME     DEPT JOB   YEARS SALARY   COMM\n" +
        "    --- -------- ---- ----- ----- -------- --------");

      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery(
        "SELECT * FROM " + tablename + " WHERE id >= 310");

      while (rs.next())
      {
        id = Integer.valueOf(rs.getString(1));
        name = rs.getString(2);
        dept = Integer.valueOf(rs.getString(3));
        job = rs.getString(4);
        if (rs.getString(5) == null)
        {
          years = null;
        }
        else
        {
          years = Integer.valueOf(rs.getString(5));
        }
        salary = Double.valueOf(Double.toString(rs.getDouble(6)));
        if (rs.getDouble(7) == 0.0)
        {
          comm = null;
        }
        else
        {
          comm = Double.valueOf(Double.toString(rs.getDouble(7)));
        }

        System.out.print("    "+Data.format(id, 3) +
                         " " + Data.format(name, 8) +
                         " " + Data.format(dept, 4));
        if (job != null)
        {
          System.out.print(" " + Data.format(job, 5));
        }
        else
        {
          System.out.print("     -");
        }
        if (years != null)
        {
          System.out.print(" " + Data.format(years, 5));
        }
        else
        {
          System.out.print("     -");
        }

        System.out.print(" " + Data.format(salary, 7, 2));
        if (comm != null)
        {
          System.out.print(" " + Data.format(comm, 7, 2));
        }
        else
        {
          System.out.print("       -");
        }
        System.out.println();
      }
      rs.close();
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, conn);
      jdbcExc.handle();
    }
  } // TbContentDisplay

}  // TbMerge