import java.sql.*;

public class UpdateEmpAndAccounts {
    public static void main(String[] args) {
        // Database credentials
        String jdbcURL = "jdbc:mysql://localhost:3306/nehal";
        String username = "root";
        String password = "nehal@123";

        // Stored procedure
        String storedProcedureCall = "{CALL UpdateEmpAndAccounts(?, ?, ?)}";

        // Input values
        int empId = 5; // Employee ID to update
        String newName = "John Updated"; // New name
        int newBalance = 1200; // New account balance (integer)

        try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
             CallableStatement callableStatement = connection.prepareCall(storedProcedureCall)) {

            // Set parameters for the stored procedure
            callableStatement.setInt(1, empId);
            callableStatement.setString(2, newName);
            callableStatement.setInt(3, newBalance);

            // Execute the stored procedure
            callableStatement.execute();

            System.out.println("Employee name and account balance updated successfully!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//run procedure code in mysql
DELIMITER $$

CREATE PROCEDURE UpdateEmpAndAccounts(
    IN empId INT,           -- Employee ID to identify the record
    IN newName VARCHAR(255), -- New name for the employee
    IN newBalance INT        -- New balance for the account
)
BEGIN
    -- Update the name in the emp table
    UPDATE emp
    SET name = newName
    WHERE id = empId;

    -- Update the balance in the accounts table
    UPDATE accounts
    SET balance = newBalance
    WHERE id = empId;
END $$

DELIMITER ;
//status check for procedure
SHOW PROCEDURE STATUS WHERE Db = 'nehal';