3. Write a JDBC application which will interact with Database and perform the following task.
a. Create a store procedure which will insert one record into employee table.
b. Create a store procedure which will retrieve salary for given employee id.
c. Write a java application which will call the above procedure and display appropriate information
on screen.
STORED PROCEDURE
-- Step 1: Create the table
CREATE TABLE employee1 (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
salary DOUBLE
);
-- Step 2: Create procedure to insert an employee
DELIMITER //
CREATE PROCEDURE insert_employee1(
IN p_emp_id INT,
IN p_emp_name VARCHAR(100),
IN p_salary DOUBLE
)
BEGIN
INSERT INTO employee1(emp_id, emp_name, salary)
VALUES (p_emp_id, p_emp_name, p_salary);
END //
DELIMITER ;
-- Step 3: Create procedure to get salary by employee ID
DELIMITER //
CREATE PROCEDURE get_salary_by_id1(
IN p_emp_id INT,
OUT p_salary DOUBLE
)
BEGIN
SELECT salary INTO p_salary
FROM employee1
WHERE emp_id = p_emp_id;
END //
DELIMITER ;
Employee Procedure App
import java.sql.*;
public class EmployeeProcedureApp {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/storedprocedure"; // Replace with your DB name
String dbUser = "root"; // Replace with your DB username
String dbPassword = "root"; // Replace with your DB password
Connection conn = null;
try {
// 1. Load JDBC Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. Connect to the database
conn = DriverManager.getConnection(jdbcURL, dbUser, dbPassword);
System.out.println("✅ Connected to the database.");
// 3. Call insert_employee1 procedure
CallableStatement insertStmt = conn.prepareCall("{call insert_employee1(?, ?, ?)}");
insertStmt.setInt(1, 101);
insertStmt.setString(2, "John Doe");
insertStmt.setDouble(3, 55000.0);
int rowsInserted = insertStmt.executeUpdate();
if (rowsInserted > 0) {
System.out.println("✅ Employee inserted successfully.");
}
// 4. Call get_salary_by_id procedure
CallableStatement getSalaryStmt = conn.prepareCall("{call get_salary_by_id(?, ?)}");
getSalaryStmt.setInt(1, 101); // input parameter
getSalaryStmt.registerOutParameter(2, Types.DOUBLE); // output parameter
getSalaryStmt.execute(); // use execute() instead of executeUpdate() for OUT param
double salary = getSalaryStmt.getDouble(2);
System.out.println("💰 Retrieved salary for emp_id 101: ₹" + salary);
} catch (Exception e) {
System.err.println("❌ Error: " + e.getMessage());
e.printStackTrace();
} finally {
// 5. Close connection
try {
if (conn != null && !conn.isClosed()) {
conn.close();
System.out.println("🔒 Connection closed.");
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}