Business Logic in SQL vs. Business Logic in Domain Classes, Part 1

Writing an Oracle Stored Procedure to calculate payroll for all employees can involve the following steps: computing base salary, overtime pay, deductions for unpaid leave, and tax deductions. For each employee, it must reference the base-salary table (employees), work logs (work_logs), leave records (leave_records), and so on. The example below shows how to implement this process. In actual use, adjustments may be needed depending on your table structure and business requirements.


CREATE OR REPLACE PROCEDURE calculate_payroll AS 
BEGIN
    FOR rec IN (SELECT e.employee_id, e.base_salary
                FROM employees e) LOOP
        -- Calculate overtime pay
        DECLARE
            overtime_hours NUMBER;
            overtime_rate NUMBER := 1.5;  -- Overtime pay rate
            overtime_pay NUMBER;
        BEGIN
            SELECT SUM(over_hours)
            INTO overtime_hours
            FROM work_logs
            WHERE employee_id = rec.employee_id
            AND work_date BETWEEN trunc(sysdate, 'MM') AND last_day(sysdate);  -- Select only records for the current month
            
            IF overtime_hours IS NULL THEN
                overtime_hours := 0;
            END IF;
            
            overtime_pay := overtime_hours * (rec.base_salary / 160) * overtime_rate;  -- Based on 160 hours
        END;
        
        -- Calculate unpaid leave deduction
        DECLARE
            unpaid_leave_days NUMBER;
            unpaid_deduction NUMBER;
        BEGIN
            SELECT SUM(leave_days)
            INTO unpaid_leave_days
            FROM leave_records
            WHERE employee_id = rec.employee_id
            AND leave_type = 'Unpaid'
            AND leave_date BETWEEN trunc(sysdate, 'MM') AND last_day(sysdate);  -- Select only records for the current month
            
            IF unpaid_leave_days IS NULL THEN
                unpaid_leave_days := 0;
            END IF;
            
            unpaid_deduction := (rec.base_salary / 20) * unpaid_leave_days;  -- Based on 20 working days per month
        END;
        
        -- Calculate tax deduction (a simple flat tax rate is used here as an example)
        DECLARE
            tax_rate NUMBER := 0.1;  -- Tax rate 10%
            tax_deduction NUMBER;
        BEGIN
            tax_deduction := (rec.base_salary + overtime_pay - unpaid_deduction) * tax_rate;
        END;
        
        -- Update final salary
        UPDATE employees
        SET final_salary = rec.base_salary + overtime_pay - unpaid_deduction - tax_deduction
        WHERE employee_id = rec.employee_id;
    END LOOP;
    
    COMMIT;
END;
/

This Stored Procedure fetches each employee's base salary, then references the work logs and leave records to calculate overtime pay and unpaid-leave deductions. It then deducts tax to compute and update the final salary. The procedure is intended to run at the end of each month to calculate that month's payroll. In a real environment the tax-calculation logic can be far more complex and may require additional logic. Error-handling logic can also be added to make the procedure more robust.

To change the Stored Procedure so it calculates payroll for a single employee, we can slightly modify the previous example so that it receives the employee ID as a parameter. The example code below shows a procedure that takes employee_id as a parameter and calculates that employee's payroll.

CREATE OR REPLACE PROCEDURE calculate_individual_payroll(p_employee_id NUMBER) AS
    base_salary NUMBER;
    overtime_hours NUMBER;
    overtime_rate NUMBER := 1.5;  -- Overtime pay rate
    overtime_pay NUMBER;
    unpaid_leave_days NUMBER;
    unpaid_deduction NUMBER;
    tax_rate NUMBER := 0.1;  -- Tax rate 10%
    tax_deduction NUMBER;
    final_salary NUMBER;
BEGIN
    -- Look up base salary
    SELECT base_salary
    INTO base_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    
    -- Calculate overtime pay
    SELECT NVL(SUM(over_hours), 0)
    INTO overtime_hours
    FROM work_logs
    WHERE employee_id = p_employee_id
        AND work_date BETWEEN trunc(sysdate, 'MM') AND last_day(sysdate);  -- Select only records for the current month
    
    overtime_pay := overtime_hours * (base_salary / 160) * overtime_rate;  -- Based on 160 hours
    
    -- Calculate unpaid leave deduction
    SELECT NVL(SUM(leave_days), 0)
    INTO unpaid_leave_days
    FROM leave_records
    WHERE employee_id = p_employee_id
        AND leave_type = 'Unpaid'
        AND leave_date BETWEEN trunc(sysdate, 'MM') AND last_day(sysdate);  -- Select only records for the current month
    
    unpaid_deduction := (base_salary / 20) * unpaid_leave_days;  -- Based on 20 working days per month
    
    -- Calculate tax deduction
    tax_deduction := (base_salary + overtime_pay - unpaid_deduction) * tax_rate;
    
    -- Calculate final salary
    final_salary := base_salary + overtime_pay - unpaid_deduction - tax_deduction;
    
    -- Update final salary
    UPDATE employees
    SET final_salary = final_salary
    WHERE employee_id = p_employee_id;
    
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ID not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error calculating payroll for Employee ID: ' || TO_CHAR(p_employee_id));
END;
/

This Stored Procedure takes a specific employee ID (p_employee_id) as an argument, calculates that employee's base salary, overtime pay, unpaid-leave deduction, and tax, and then updates the final salary. It performs the payroll calculation for the current month; in actual use, you would need to include validation steps (such as checking that the employee ID is valid) and error-handling logic. You may also need to adjust the tax-calculation method or other calculation logic to match your real business requirements.

The Stored Procedure provided above essentially automates the process of calculating one employee's payroll in an Oracle database. The calculation includes base salary, overtime pay, deductions for unpaid leave, and tax deductions. Each calculation step retrieves the required information from the relevant tables in the database.

How to run it:

After creating the Stored Procedure in the database, you can execute it as follows.

BEGIN
    calculate_individual_payroll(p_employee_id => 123); -- 123 is an example employee ID.
END;
/

Here, p_employee_id receives the ID of the employee whose payroll you want to calculate. The calculation is performed based on the payroll information of the employee matching this parameter.

Important considerations:

Data accuracy: Before using the Stored Procedure in a production environment, verify that it accurately reflects the schema and actual data of the database you intend to use. For example, check that the structure and fields of the employees, work_logs, and leave_records tables match the example code.
Exception handling: The Stored Procedure provides basic exception handling, including NO_DATA_FOUND and OTHERS. In a real production environment, however, the exception-handling logic should be extended to cope with a wider range of exceptional situations.
Performance optimization: Performance issues can arise when processing large datasets. Depending on your needs, you may want to consider adding indexes, optimizing queries, or using batch processing.
Security considerations: When handling sensitive data such as employee salary information, it is important to apply appropriate security measures at both the database and application levels. Apply user-permission management, data encryption, and SQL-injection prevention techniques.
Testing: Before applying it to real data, run thorough tests in a test environment to confirm that it behaves exactly as expected.

This procedure can be a powerful tool for automating a specific employee's payroll calculation, but in a real environment additional development and testing are needed to satisfy complex business rules, various exceptional cases, and performance and security requirements.

When converting the payroll-calculation logic into Java code, it is best to implement it on the basis of domain classes. To do so, we define domain classes for the employee (Employee), work log (WorkLog), and leave record (LeaveRecord), and create a service class (PayrollService) responsible for calculating payroll.

The example code below implements simple domain classes and the payroll-calculation logic. In a real environment you could use technologies such as JPA, JDBC, or MyBatis for database integration; here it is implemented in a simplified form to make the code easier to understand.

Domain class definitions


public class Employee {
    private Long id;
    private Double baseSalary;
    private Double finalSalary; // Stores the calculated final salary

    // Constructors, Getters and Setters
}

public class WorkLog {
    private Long employeeId;
    private Double overHours; // Overtime hours for the month

    // Constructors, Getters and Setters
}
    
public class LeaveRecord {
    private Long employeeId;
    private String leaveType; // "Paid" or "Unpaid"
    private Double leaveDays; // Leave days for the month

    // Constructors, Getters and Setters
}

Payroll calculation service class

import java.util.List;

public class PayrollService {
    public void calculatePayrollForEmployee(Long employeeId) {
        Employee employee = findEmployeeById(employeeId);
        List<WorkLog> workLogs = findWorkLogsForEmployee(employeeId);
        List<LeaveRecord> leaveRecords = findLeaveRecordsForEmployee(employeeId);

        double baseSalary = employee.getBaseSalary();
        double overtimePay = calculateOvertimePay(workLogs, baseSalary);
        double unpaidDeduction = calculateUnpaidDeduction(leaveRecords, baseSalary);
        double taxDeduction = calculateTaxDeduction(baseSalary + overtimePay - unpaidDeduction);

        double finalSalary = baseSalary + overtimePay - unpaidDeduction - taxDeduction;
        employee.setFinalSalary(finalSalary);

        // Update employee's final salary in database (not shown here)
    }

    private double calculateOvertimePay(List<WorkLog> workLogs, double baseSalary) {
        double overtimeHours = workLogs.stream().mapToDouble(WorkLog::getOverHours).sum();
        return overtimeHours * (baseSalary / 160) * 1.5; // Example: 1.5x overtime rate applied
    }

    private double calculateUnpaidDeduction(List<LeaveRecord> leaveRecords, double baseSalary) {
        double unpaidLeaveDays = leaveRecords.stream()
            .filter(lr -> "Unpaid".equals(lr.getLeaveType()))
            .mapToDouble(LeaveRecord::getLeaveDays)
            .sum();
        return (baseSalary / 20) * unpaidLeaveDays; // Based on 20 working days per month
    }

    private double calculateTaxDeduction(double taxableIncome) {
        return taxableIncome * 0.1; // Example: 10% tax rate applied
    }

    // These methods would interact with the database to retrieve the relevant data
    private Employee findEmployeeById(Long employeeId) { /* ... */ }
    private List<WorkLog> findWorkLogsForEmployee(Long employeeId) { /* ... */ }
    private List<LeaveRecord> findLeaveRecordsForEmployee(Long employeeId) { /* ... */ }
}

In this code example, the fields, constructors, getters, and setters of each class are omitted; to use it in practice, they must be implemented appropriately. Database-access methods such as findEmployeeById, findWorkLogsForEmployee, and findLeaveRecordsForEmployee must also be implemented with real database-integration logic.

Including the payroll-calculation logic inside the Employee entity is a pattern often seen in Domain-Driven Design (DDD). This approach strengthens encapsulation by letting the entity perform business logic using its own data. However, in this case the Employee class must also hold references to work logs (WorkLog) and leave records (LeaveRecord), which can increase coupling between entities. It should therefore be considered carefully.

Below is an example that includes the payroll-calculation logic inside the Employee entity. This example assumes that Employee directly manages its own lists of work logs and leave records.

import java.util.List;

public class Employee {
    private Long id;
    private Double baseSalary;
    private Double finalSalary;
    private List<WorkLog> workLogs;
    private List<LeaveRecord> leaveRecords;

    // Constructors, getters and setters omitted

    // Payroll calculation
    public void calculatePayroll() {
        double overtimePay = calculateOvertimePay();
        double unpaidDeduction = calculateUnpaidDeduction();
        double taxDeduction = calculateTaxDeduction(baseSalary + overtimePay - unpaidDeduction);

        finalSalary = baseSalary + overtimePay - unpaidDeduction - taxDeduction;
    }

    private double calculateOvertimePay() {
        double overtimeHours = workLogs.stream().mapToDouble(WorkLog::getOverHours).sum();
        return overtimeHours * (baseSalary / 160) * 1.5; // Example: 1.5x overtime rate applied
    }

    private double calculateUnpaidDeduction() {
        double unpaidLeaveDays = leaveRecords.stream()
            .filter(lr -> "Unpaid".equals(lr.getLeaveType()))
            .mapToDouble(LeaveRecord::getLeaveDays)
            .sum();
        return (baseSalary / 20) * unpaidLeaveDays; // Based on 20 working days per month
    }

    private double calculateTaxDeduction(double taxableIncome) {
        return taxableIncome * 0.1; // Example: 10% tax rate applied
    }

    // Accessor methods for workLogs and leaveRecords omitted
}

This example lets each Employee instance manage its own lists of work logs (workLogs) and leave records (leaveRecords) and use that information to calculate its own payroll. The advantage of this approach is stronger encapsulation, since the business logic is tightly bound to the data. The downsides are a more complex model and increased coupling between entities. In addition, how work-log and leave-record data are accessed and managed will depend on the actual project's architecture and technology stack.

Using this approach, let's look at how to create a service that runs the payroll-calculation logic for all employees and write a few unit tests for that service. Here we will name the service class PayrollCalculatorService and write the unit tests using JUnit.

All-employee payroll calculation service

import java.util.List;

public class PayrollCalculatorService {
    private EmployeeRepository employeeRepository; // Repository for data access

    // Constructor or setter for dependency injection (omitted)

    public void calculatePayrollForAllEmployees() {
        List<Employee> employees = employeeRepository.findAll();

        for (Employee employee : employees) {
            employee.calculatePayroll(); // Call the method defined in the Employee class
            employeeRepository.save(employee); // Save the calculated payroll
        }
    }
}

This service class performs the payroll calculation for all employees. EmployeeRepository is used to look up employee information from the database and to save the calculated payroll data. In a real implementation it could be built with Spring Data JPA, MyBatis, or similar.

Writing unit tests

We write unit tests for PayrollCalculatorService using JUnit. Here we use Mockito to mock the dependencies and verify the correctness of the employee payroll-calculation logic.

import org.junit.jupiter.api.Test;
import org.mockito.Mockito;
import static org.mockito.Mockito.*;

import java.util.Arrays;
import java.util.List;

public class PayrollCalculatorServiceTest {

    @Test
    public void testCalculatePayrollForAllEmployees() {
        // Given
        EmployeeRepository mockRepository = Mockito.mock(EmployeeRepository.class);
        PayrollCalculatorService service = new PayrollCalculatorService();
        service.setEmployeeRepository(mockRepository); // Dependency injection

        Employee employee1 = new Employee(1L, 3000.0); // Sample employee
        Employee employee2 = new Employee(2L, 4000.0);
        List<Employee> employees = Arrays.asList(employee1, employee2);

        when(mockRepository.findAll()).thenReturn(employees);

        // When
        service.calculatePayrollForAllEmployees();

        // Then
        for (Employee employee : employees) {
            verify(mockRepository).save(employee); // Verify save was called for each employee
        }
    }
}

This test verifies that PayrollCalculatorService uses EmployeeRepository to calculate payroll for all employees and saves the results. Whether the calculatePayroll method of an actual Employee instance works correctly should be verified through unit tests of the Employee class itself. For simplicity, the code above omits the Employee constructor and the calculatePayroll implementation.

These examples provide a basic structure. In a real application, the implementation will vary depending on business requirements, database design, and the technology stack used in the project.

Let's compare the two approaches to handling business logic: the Transaction Script approach using Stored Procedures that embed logic in SQL, and the approach using domain classes in Java. By comparing them from several perspectives, we can understand the strengths and weaknesses of each and present a comparison table to help you choose the right one for your situation.

Comparison	Stored Procedure (Transaction Script)	Java (Domain Model)	Notes
Encapsulation of business logic	Low. Logic is tightly coupled to the database.	High. Business logic is encapsulated within objects.	The domain model encapsulates business logic within objects, providing a high level of abstraction and cohesion.
Readability	Can be low. Complex SQL and PL/SQL can be hard to read.	High. High-level languages such as Java are readable and easy to understand.	The domain model tends to express business logic clearly, making the intent of the code easy to understand.
Maintainability	Medium to low. Testing and deploying logic changes can be difficult.	High. Easy maintenance through unit tests and CI/CD.	Languages such as Java make refactoring, unit testing, and version control easy, and deploying changes is simpler.
Impact of changing database products	High. Tightly coupled to a specific database.	Low. Abstraction layers such as JDBC and JPA provide database independence.	The domain-model approach minimizes the impact on application logic when the database is replaced.
Testability	Low. Requires a database, and isolated tests are difficult.	High. Isolated tests are possible using unit tests and mock objects.	The domain model enables faster and more effective testing through isolated unit tests.

Choosing the right approach for the situation

When Stored Procedures (Transaction Script) are a good fit:

When the logic consists of simple CRUD operations that depend heavily on data processing.
When you need to optimize database performance and process all logic on the database server.
When a project is led by database experts who prefer processing inside the database.

When Java (Domain Model) is a good fit:

When the business logic is complex and modeling through an object-oriented approach is needed.
In projects where application maintainability and extensibility are important.
When you want to maintain database independence while leveraging a variety of data sources.
When the team is familiar with object-oriented programming and wants to leverage the tools and libraries of the Java (JVM) ecosystem.

Each approach has its own strengths and weaknesses, and the decision should take into account many factors, including project requirements, the team's technology stack and expertise, and the complexity of the application. For example, if the business logic is highly complex and you want to take full advantage of object-oriented design, the domain-model approach in Java may be the better fit. On the other hand, if data-processing logic is the focus and you need to maximize specific database features (such as complex SQL, transaction management, and optimization), using Stored Procedures may be more efficient.

From a testability standpoint, the Java-based approach also lets you build more robust test coverage through unit and integration tests. This improves application stability and helps you detect and fix errors early.

Ultimately, a project's success depends not only on the technology you choose, but also on how you use that technology, how well the team collaborates, and how the project is managed. It is therefore important to fully understand the strengths and weaknesses of each approach and choose the one best suited to the specific circumstances of your project.