//*************************************************************************** // (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