💾 Custom Approval History in Joget: Direct SQL Insertion via BeanShell Tool

1. Overview

While Joget's Form Binders and built-in audit trails handle standard data storage, complex enterprise applications often require specialized auditing and history tracking. This is particularly true when you need a dedicated, sequentially numbered Approval History table that links to multiple main processes for easy reporting.

This post details an advanced, expert technique using a Java/BeanShell Process Tool to perform a direct SQL INSERT into a custom database table (app_fd_approval_history). This method grants granular control over the primary key (id) sequence and allows you to log specific metadata (user, status, date) mid-workflow, decoupled from the main form's binder.

---

2. How It Works

The script operates as a robust, transactional database logger configured as a workflow tool.

• ⚙️ Database Connection: It securely retrieves the Joget application's main database connection via the setupDataSource Spring bean using AppUtil.getApplicationContext().getBean("setupDataSource").
• ⚙️ Manual ID Generation: It executes SELECT MAX(CAST(id AS SIGNED)) to find the highest existing sequential ID. Crucially, it casts the id (often stored as a string in Joget forms) to a signed integer to ensure correct numerical ordering, and then calculates the nextId.
• ⚙️ Dynamic Data Capture: It utilizes Joget Hash Variables (e.g., #form.new_request.createdByName#) to dynamically capture real-time form data, user information, and timestamps directly from the workflow instance before preparing the data for insertion.
• ⚙️ Execution & Cleanup: It uses a PreparedStatement to safely execute the INSERT query. The finally block is critical, ensuring the database connection is closed (conn.close()) even if an exception occurs, preventing connection leaks.

---

3. Full Code

This code is configured as a BeanShell within a Joget workflow, typically placed immediately after a submission or approval step where the status transition occurs.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

public void insertNewData() {
    Connection conn = null;
    try {
        LogUtil.info("ApprovalHistoryTool", "Starting Approval History Creation Tool");

        // 1. Get the primary application data source
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        conn = ds.getConnection();

        // 2. Determine the next sequential ID manually
        String maxIdQuery = "SELECT MAX(CAST(id AS SIGNED)) FROM app_fd_approval_history";
        PreparedStatement maxIdStmt = conn.prepareStatement(maxIdQuery);
        ResultSet resultSet = maxIdStmt.executeQuery();

        int nextId = 1; 
        if (resultSet.next()) {
            nextId = resultSet.getInt(1) + 1;
            LogUtil.info("ApprovalHistoryTool", "Next ID to be inserted: " + nextId);
        }
        maxIdStmt.close();

        // 3. Prepare the INSERT statement
        String insertQuery = "INSERT INTO app_fd_approval_history" +
                "(id, c_name, c_status, c_date, c_remark, c_request_for, c_parent_id)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";

        // 4. Capture dynamic data using Joget Hash Variables
        // IMPORTANT: Replace the placeholder values (#form.new_request...) with your actual form IDs.
        String name = "";
        String status = "";
        String date = "";

        // Conditional logic based on a form field value (Request Type)
        if ("New".equals("#form.new_request.request_type#")) {
            name = "#form.new_request.createdByName#";
            status = "Request Submitted";
            date = "#form.new_request.dateCreated#";
        } else if ("Update".equals("#form.new_request.request_type#")) {
            name = "#form.new_request.modifiedByName#";
            status = "Update Request";
            date = "#form.new_request.dateModified#";
        }

        String remark = "#form.new_request.reason#";
        String requestFor = ""; 
        String foreignKey = "#form.new_request.id#"; // Links to the main form record

        PreparedStatement insertStmt = conn.prepareStatement(insertQuery);

        // Set values using the PreparedStatement (prevents SQL injection)
        insertStmt.setInt(1, nextId);
        insertStmt.setString(2, name);
        insertStmt.setString(3, status);
        insertStmt.setString(4, date);
        insertStmt.setString(5, remark);
        insertStmt.setString(6, requestFor);
        insertStmt.setString(7, foreignKey);
        LogUtil.info("ApprovalHistoryTool", "Linking history to parent ID: " + foreignKey);

        // 5. Execute and close
        insertStmt.executeUpdate();
        insertStmt.close();

        LogUtil.info("ApprovalHistoryTool", "Data inserted successfully in Approval History Table.");
    } catch (Exception e) {
        LogUtil.error("ApprovalHistoryTool", "Error inserting data: " + e.getMessage(), e);
    } finally {
        // 6. Ensure connection is always closed
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            LogUtil.error("ApprovalHistoryTool", "Error closing connection: " + e.getMessage(), e);
        }
    }
}

insertNewData();

`

4. Example Use Cases

• ✅ Complete Audit Trail: Creating a persistent, uneditable log of every approval, rejection, and modification action for compliance or security auditing.
• ✅ Custom Reporting: Facilitating streamlined reporting on approval cycle times, user actions, and status changes using a normalized, dedicated history table structure.
• ✅ Sequential Primary Keys: Implementing simple, strictly sequential, numeric ids for history records, which are far easier for end-users and reports to reference than default UUIDs.

5. Customization Tips

• 💡 Hash Variable Validation: Always verify that your Joget Hash Variables are correctly returning the required values (e.g., using LogUtil.info()) before trusting them in a database insertion.
• 💡 Error Logging: Expand the catch block to include logic that notifies an administrator (e.g., sends an email) if the database insertion fails, rather than just logging the error.
• 💡 Time Zone Control: Dates retrieved via Hash Variables may require explicit formatting. Use Java's SimpleDateFormat inside the script to format the date string before insertion, ensuring consistency across different geographic locations.

6. Key Benefits

• 🚀 Granular Control: Provides developers with complete control over SQL execution, allowing for custom data structures and transactional integrity.
• 🚀 Decoupled Logging: Separates the audit trail from the main form data, making both the main table and the history table cleaner and more performant.
• 🚀 Security & Consistency: Use of PreparedStatement protects against basic SQL injection and guarantees the field values are treated as data, not code.

7. Security Note

🔒 SQL Injection Prevention: The use of PreparedStatement with placeholder ? marks is the most critical security feature in this script. It prevents external data (even malicious data from a form field) from altering the structure of the SQL command.
🔒 Resource Leaks: The meticulous implementation of the finally block to close the Connection is non-negotiable. Failing to close connections will lead to resource exhaustion and potential database failure under load.

---

8. Final Thoughts

Mastering direct database interaction via BeanShell Tools gives you expert-level control over your Joget application's data layer. This technique is indispensable for building robust, compliance-ready solutions that require custom, sequential auditing and complex data relationships.