Unleashing Database Power with JDBC CallableStatement - The SC Guide

ඉතින් කොහොමද යාලුවනේ? ඔයාලා හැමෝම හොඳින් ඉන්නවා කියලා හිතනවා. අද අපි කතා කරන්න යන්නේ database operations වලදී අපිට ගොඩක් වැදගත් වෙන, ඒ වගේම සමහර වෙලාවට පොඩ්ඩක් අමාරුයි කියලා හිතෙන, නමුත් හරිම ප්‍රයෝජනවත් වෙන දෙයක් ගැන – ඒ තමයි JDBC CallableStatement.

සාමාන්‍යයෙන් අපි Java Application එකකින් database එකකට connect වෙලා data ගන්නකොට, data update කරනකොට, හෝ delete කරනකොට Statement, PreparedStatement වගේ ඒවා පාවිච්චි කරනවා නේද? ඒවා හරිම පහසුයි. ඒත් සමහර වෙලාවට, අපිට database එකේ තියෙන Stored Procedures call කරන්න වෙනවා. එතකොට තමයි අපේ Hero වෙන CallableStatement picture එකට එන්නේ.

මේ article එකෙන් අපි බලමු CallableStatement කියන්නේ මොකක්ද, ඒකෙන් Stored Procedures call කරන්නේ කොහොමද, ඒ වගේම ඒකෙන් අපිට ලැබෙන වාසි මොනවද කියලා. වැඩේ එලටම කරගන්න පුළුවන් වෙයි!

1. CallableStatement කියන්නේ මොකක්ද?

සරලවම කිව්වොත්, CallableStatement කියන්නේ JDBC API එකේ තියෙන interface එකක්. මේකෙන් පුළුවන් database එකේ තියෙන Stored Procedures සහ Functions execute කරන්න. මතකද PreparedStatement එකෙන් අපි parameterized SQL queries execute කරා? ඒ වගේම CallableStatement එකෙන් Stored Procedures වලට parameters යවන්නත්, ඒවායින් එන return values handle කරන්නත් පුළුවන්.

CallableStatement, PreparedStatement එකටත් වඩා විශේෂයි. මොකද මේකෙන් IN (input) parameters, OUT (output) parameters සහ INOUT (input/output) parameters support කරනවා. ඒ වගේම Stored Procedures වලින් result sets return කරනවා නම් ඒවා handle කරන්නත් පුළුවන්.

2. Stored Procedures ඇයි පාවිච්චි කරන්නේ?

දැන් ඔයාලට හිතෙන්න පුළුවන්, ඇයි අපි මේ Stored Procedures පාවිච්චි කරන්නේ කියලා. SQL queries කෙලින්ම Java code එකේ ලිව්වම ඉවරනේ කියලා. ඒත් Stored Procedures වලින් අපිට ලැබෙන වාසි ගොඩක් තියෙනවා:

• Performance වැඩි වෙනවා: Stored Procedures database server එකේ compile වෙලා save වෙනවා. ඉතින් හැම පාරම query එක compile කරන්න ඕනේ නැහැ. ඒක නිසා execution speed එක වැඩි වෙනවා.
• Security වැඩි වෙනවා: Database එකට කෙලින්ම tables වලට access දෙන්නේ නැතිව, Stored Procedures හරහා access දෙන්න පුළුවන්. ඒකෙන් data security එක තව වැඩි වෙනවා.
• Code Reusability: එක Stored Procedure එකක් ලියලා, ඕනෑම application එකකින්, ඕනෑම programming language එකකින් ඒක call කරන්න පුළුවන්.
• Network Traffic අඩු වෙනවා: එක Stored Procedure call එකකින් ගොඩක් operations කරන්න පුළුවන් නිසා, client side එකේ ඉඳන් database එකට යන network calls ගණන අඩු වෙනවා.

3. සරල Procedure එකක් call කරමු (No Parameters)

මුලින්ම අපි බලමු කිසිම parameter එකක් නැති Stored Procedure එකක් Java වලින් call කරන්නේ කොහොමද කියලා. මේකට අපි MySQL database එකක් පාවිච්චි කරමු. ඔයාට ඕනෑම database එකක් පාවිච්චි කරන්න පුළුවන්.

Database Stored Procedure එක:

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT employee_id, first_name, last_name, email FROM employees;
END //
DELIMITER ;

මේ GetAllEmployees කියන procedure එක employees table එකේ තියෙන හැම employee කෙනෙක්ගේම විස්තර return කරනවා. දැන් බලමු මේක Java වලින් call කරන්නේ කොහොමද කියලා.

Java Code:

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

public class CallableStatementExample {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             CallableStatement callableStatement = conn.prepareCall("{call GetAllEmployees()}")) {

            boolean hadResults = callableStatement.execute();

            // Process results if any
            if (hadResults) {
                try (ResultSet rs = callableStatement.getResultSet()) {
                    while (rs.next()) {
                        System.out.println("Employee ID: " + rs.getInt("employee_id") +
                                           ", Name: " + rs.getString("first_name") + " " + rs.getString("last_name") +
                                           ", Email: " + rs.getString("email"));
                    }
                }
            }

            System.out.println("\nSuccessfully called GetAllEmployees procedure!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

මේ code එකේදී අපි conn.prepareCall("{call GetAllEmployees()}") පාවිච්චි කරනවා. මෙතනදී {call procedureName()} කියන format එක පාවිච්චි කරන්න ඕනේ. execute() method එකෙන් procedure එක run කරනවා. ඒ වගේම getResultSet() එකෙන් procedure එකෙන් return වෙන result set එක ගන්නවා.

4. IN සහ OUT Parameters එක්ක වැඩ කරමු

දැන් අපි බලමු Stored Procedure එකකට parameters යවලා, ඒකෙන් data ගන්න හැටි. මේකට අපි IN සහ OUT parameters පාවිච්චි කරමු.

Database Stored Procedure එක:

අපි හිතමු අපිට employee කෙනෙක්ගේ ID එක දීලා, එයාගේ නම සහ email address එක ගන්න ඕනේ කියලා.

DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(
    IN emp_id INT,
    OUT emp_first_name VARCHAR(50),
    OUT emp_last_name VARCHAR(50),
    OUT emp_email VARCHAR(100)
)
BEGIN
    SELECT first_name, last_name, email
    INTO emp_first_name, emp_last_name, emp_email
    FROM employees
    WHERE employee_id = emp_id;
END //
DELIMITER ;

මේ procedure එකේ emp_id කියන්නේ IN parameter එකක්. ඒ කියන්නේ අපි මේකට අගයක් යවනවා. emp_first_name, emp_last_name, emp_email කියන්නේ OUT parameters. ඒ කියන්නේ procedure එකෙන් අපිට මේවායේ අගයන් return කරනවා.

Java Code:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class CallableStatementWithParamsExample {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        int employeeIdToSearch = 101; // Example employee ID

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             CallableStatement callableStatement = conn.prepareCall("{call GetEmployeeDetails(?, ?, ?, ?)}")) {

            // Set the IN parameter
            callableStatement.setInt(1, employeeIdToSearch);

            // Register the OUT parameters
            callableStatement.registerOutParameter(2, Types.VARCHAR); // for emp_first_name
            callableStatement.registerOutParameter(3, Types.VARCHAR); // for emp_last_name
            callableStatement.registerOutParameter(4, Types.VARCHAR); // for emp_email

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

            // Retrieve the values from OUT parameters
            String firstName = callableStatement.getString(2);
            String lastName = callableStatement.getString(3);
            String email = callableStatement.getString(4);

            if (firstName != null) {
                System.out.println("Employee Details for ID " + employeeIdToSearch + ":");
                System.out.println("First Name: " + firstName);
                System.out.println("Last Name: " + lastName);
                System.out.println("Email: " + email);
            } else {
                System.out.println("No employee found with ID " + employeeIdToSearch);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

මේ code එකේදී {call GetEmployeeDetails(?, ?, ?, ?)} කියන විදියට procedure එක call කරන්න අපි template එකක් හදාගන්නවා. හැම ? ලකුණක්ම parameter එකක් නියෝජනය කරනවා. setInt(1, employeeIdToSearch) වලින් අපි පළවෙනි parameter එකට අගය දෙනවා.

ඊට පස්සේ, registerOutParameter() method එක පාවිච්චි කරලා, දෙවෙනි, තුන්වෙනි සහ හතරවෙනි parameters OUT parameters විදියට register කරනවා. මේකෙදි අදාළ data type එක (උදා: Types.VARCHAR) දෙන්න ඕනේ. Procedure එක execute කරාට පස්සේ, getString() වගේ methods පාවිච්චි කරලා OUT parameters වල අගයන් ලබාගන්න පුළුවන්.

5. INOUT Parameters සහ Return Values

සමහර වෙලාවට අපිට INOUT parameters පාවිච්චි කරන්න වෙනවා. ඒ කියන්නේ parameter එකකට අපි අගයක් දීලා යවලා, procedure එක ඇතුලේදී ඒ අගය modify කරලා ඒකම ආපහු ගන්නවා. ඒ වගේම Stored Function එකකින් return වෙන values ගන්නත් පුළුවන්.

Database Stored Function (Return Value) එක:

අපි හිතමු employee කෙනෙක්ගේ email එක දීලා එයාගේ ID එක ගන්න function එකක් තියෙනවා කියලා.

DELIMITER //
CREATE FUNCTION GetEmployeeIdByEmail(emp_email VARCHAR(100))
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE emp_id INT;
    SELECT employee_id INTO emp_id FROM employees WHERE email = emp_email;
    RETURN emp_id;
END //
DELIMITER ;

Java Code (Function Return Value):

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class CallableStatementFunctionExample {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        String emailToSearch = "[email protected]"; // Example email

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             // Note the {? = call ...} syntax for functions
             CallableStatement callableStatement = conn.prepareCall("{? = call GetEmployeeIdByEmail(?)}")) {

            // Register the return parameter (first parameter, index 1)
            callableStatement.registerOutParameter(1, Types.INTEGER);

            // Set the IN parameter (second parameter, index 2)
            callableStatement.setString(2, emailToSearch);

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

            // Retrieve the return value
            int employeeId = callableStatement.getInt(1);

            if (employeeId != 0) { // Assuming 0 means not found
                System.out.println("Employee ID for " + emailToSearch + ": " + employeeId);
            } else {
                System.out.println("No employee found with email " + emailToSearch);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Stored Functions call කරනකොට syntax එක පොඩ්ඩක් වෙනස් වෙනවා: {? = call functionName(?)}. මෙතනදී පළවෙනි ? එකෙන් return value එක නියෝජනය කරනවා. ඉතිරි ? ලකුණු IN/OUT/INOUT parameters සඳහා පාවිච්චි වෙනවා.

අවසන් වශයෙන්

CallableStatement කියන්නේ JDBC වල තියෙන ගොඩක් powerful feature එකක්. Database එකේ තියෙන Stored Procedures සහ Functions පාවිච්චි කරලා complex business logic execute කරන්න මේකෙන් පුළුවන්. Performance වැඩි කරගන්න, security එක වැඩි කරගන්න, ඒ වගේම code reusability ලබාගන්න Stored Procedures හරිම වැදගත්.

ඔයාලාත් මේ concepts practice කරලා බලන්න. Database එකක Stored Procedures හදලා Java වලින් call කරලා බලන්න. එතකොට තමයි හරියටම තේරෙන්නේ වැඩේ කොහොමද වෙන්නේ කියලා.

මේ article එක ඔයාලට ප්‍රයෝජනවත් වෙන්න ඇති කියලා හිතනවා. මොනවා හරි ප්‍රශ්න තියෙනවා නම්, නැත්නම් මේ ගැන ඔයාලගේ අදහස් මොනවද කියලා පහළින් comment කරන්න අමතක කරන්න එපා. අපි තව අලුත් article එකකින් හමුවෙමු! Happy Coding!