【Javaプログラム入門】今回はOracleデータベースに接続してSQLを実行するプログラムに挑戦してみましょう！

package dbSample;

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

public class DatabaseExample {

	// JDBC URL, ユーザ名、パスワード
	static final String JDBC_URL = "jdbc:oracle:thin:@localhost:1521:XE";
	static final String USERNAME = "system";
	static final String PASSWORD = "test";

	public static void main(String[] args) {
		try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
			System.out.println("Oracleデータベースに接続しました");
			System.out.println("");

			insertSample(connection, 1, "女王ネコ", 7);
			insertSample(connection, 2, "アイドル犬", 10);
			System.out.println("データを2件登録しました");
			selectSample(connection);
			System.out.println("");

			updateSample(connection, 1, 8);
			System.out.println("1件目のデータを更新しました");
			selectSample(connection);
			System.out.println("");

			deleteSample(connection, 2);
			System.out.println("2件目のデータを削除しました");
			selectSample(connection);
			System.out.println("");

			mergeSample(connection, 2, "チワワちゃん", 1, 2, "チワワ姫", 2);
			System.out.println("2件目を登録しました");
			selectSample(connection);
			System.out.println("");

			mergeSample(connection, 2, "チワワちゃん", 1, 2, "チワワ姫", 2);
			System.out.println("2件目を更新しました");
			selectSample(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void selectSample(Connection connection) {
		// Select
		try (PreparedStatement statement = connection.prepareStatement("SELECT * FROM sample_table ORDER BY id");
			ResultSet resultSet = statement.executeQuery()) {
			while (resultSet.next()) {
				System.out.println(
					"ID: " + resultSet.getInt("id") + 
					", Name: " + resultSet.getString("name") + 
					", Age: " + resultSet.getInt("age")
				);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void insertSample(Connection connection, int id, String name, int age) {
		// Insert
		try (PreparedStatement insertStatement = connection.prepareStatement("INSERT INTO sample_table VALUES (?, ?, ?)")) {
			insertStatement.setInt(1, id);
			insertStatement.setString(2, name);
			insertStatement.setInt(3, age);
			insertStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void updateSample(Connection connection, int id, int age) {
		// Update
		try (PreparedStatement updateStatement = connection.prepareStatement("UPDATE sample_table SET age = ? WHERE id = ?")) {
			updateStatement.setInt(1, age);
			updateStatement.setInt(2, id);
			updateStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void deleteSample(Connection connection, int id) {
		// Delete
		try (PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM sample_table WHERE id = ?")) {
			deleteStatement.setInt(1, id);
			deleteStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void mergeSample(Connection connection, 
				int id1, String name1, int age1,
				int id2, String name2, int age2) {
		// Merge
		String mergeSql = "MERGE INTO sample_table USING dual ON (id = ?) " +
				  "WHEN MATCHED THEN UPDATE SET name = ?, age = ? " +
				  "WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (?, ?, ?)";
		try (PreparedStatement mergeStatement = connection.prepareStatement(mergeSql)) {
			mergeStatement.setInt(1, id1);
			mergeStatement.setString(2, name1);
			mergeStatement.setInt(3, age1);
			mergeStatement.setInt(4, id2);
			mergeStatement.setString(5, name2);
			mergeStatement.setInt(6, age2);
			mergeStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}