package CHAPTER15;

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

public class C15_06_InsertMultiQuery {
	private static final String URL = "jdbc:mysql://localhost:3306/course";
	private static final String USER = "root";
	private static final String PASSWORD = "123456789";

	// 여러 회원 데이터를 삽입하는 메서드
	public static void insertMultipleMembers(String[][] membersData, Connection conn) throws SQLException {
		// INSERT SQL 쿼리 - 여러 개의 회원 데이터를 추가
		String query = "INSERT INTO members (username, password, email, phone, birthdate) VALUES (?, ?, ?, ?, ?)";

		PreparedStatement preparedStatement = conn.prepareStatement(query);

		for (String[] member : membersData) {
			preparedStatement.setString(1, member[0]); // username
			preparedStatement.setString(2, member[1]); // password
			preparedStatement.setString(3, member[2]); // email
			preparedStatement.setString(4, member[3]); // phone
			preparedStatement.setString(5, member[4]); // birthdate

			preparedStatement.addBatch(); // 배치에 추가
		}

		int[] rowsAffected = preparedStatement.executeBatch(); // 배치 실행
		System.out.println(rowsAffected.length + " members inserted successfully.");

	}

	// 모든 회원 정보를 조회하는 메서드
	public static void getAllMembers(Connection conn) throws SQLException {
		String query = "SELECT * FROM members";

		PreparedStatement preparedStatement = conn.prepareStatement(query);
		ResultSet resultSet = preparedStatement.executeQuery();

		System.out.println("\nAll Members:");
		while (resultSet.next()) {
			System.out.println("ID: " + resultSet.getInt("member_id") 
					+ ", Username: " + resultSet.getString("username")
					+ ", Email: " + resultSet.getString("email") 
					+ ", Phone: " + resultSet.getString("phone")
					+ ", Birthdate: " + resultSet.getDate("birthdate"));
		}
	}

	public static void main(String[] args) throws SQLException {
		try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
			
			// 여러 회원 데이터를 2차원 배열로 정의
			String[][] members = {
					{ "Alice_Smith", "alicePass2024", "alice.smith@example.com", "010-5678-4321", "1992-07-20" },
					{ "Bob_Jones", "bobSecure789", "bob.jones@example.com", "010-1111-2222", "1985-11-03" },
					{ "Charlie_Brown", "charlieBest!", "charlie.brown@example.com", "010-9999-8888", "1995-01-15" } };

			// 회원 데이터 삽입
			insertMultipleMembers(members, conn);

			// 모든 회원 정보 조회
			getAllMembers(conn);
		}
	}
}
