package org.oklab;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * <p><code>java.sql.PreparedStatement</code>で、SQLのmerge文を実行する
 * このプログラムを動かすにはデータベースの設定とテーブルが必要です。</p>
 *
 * <p>サンプルSQL</p>
 * <pre>
 * create table hogehoge (
 *   key number,
 *   value varchar2(2));
 * create table hoge2 (
 *   key number,
 *   value varchar2(2));
 * </pre>
 *
 * @author satoshiokita
 * @version 1.0
 */
public class MergeByPreparedStatement {
    
    public static void main(String [] args) {
        MergeByPreparedStatement instance = new MergeByPreparedStatement();
        instance.mergeTest();
    }

    /**
     * 接続テストとmerge文のテストを行う。
     */
    public void mergeTest() {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            
            // 接続テスト
            conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@43.9.17.16:1521:RHL9","okita2","okita2");
            PreparedStatement stmt = 
                conn.prepareStatement("select sysdate from dual");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }

            // merge文のテスト
            System.out.println("merge test start");
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(
  "merge into hogehoge x "
+ "using (select key from hoge2 where key = ?) z on (x.key=z.key) "
+ "when matched then " 
+ "update set x.value = ? "
+ "when not matched then "
+ "insert (key, value) values (?,?)");
            stmt.setInt(1, 1);
            stmt.setString(2,"xx");
            stmt.setInt(3, 1);
            stmt.setString(4,"oo");

            stmt.execute();
            conn.commit();

            rs.close();
            stmt.close();
            conn.close();
            System.out.println("merge test end");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                } 
            } catch (SQLException e) {
                System.out.println("!!!");
            }
        }
    }
}