callable java code and procedure
Fri Nov 15 2024 14:00:52 GMT+0000 (Coordinated Universal Time)
Saved by
@wtlab
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';
content_copyCOPY
Comments