Creating a BeanShell Form Validator

Introduction

In this tutorial, you'll learn how to create a BeanShell Form Validator. This validator ensures that only values from another table are allowed. Simply put, it acts as a reverse duplicate value checker. You'll have two forms in your example app—one for inputting valid values (Form 1) and another (Form 2) where the validator will be added.

App Structure

Form 1 Fields

Form 1 Details
Form 2 Fields
Form 2 Details

Implementation

Now that we have clarified the structure of the sample app let's start implementing the BeanShell Validator. Here is the code used in the sample app:

import org.joget.apps.form.service.FormUtil;
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.apps.form.model.Element;
import org.joget.apps.form.model.Form;
import org.joget.apps.form.model.FormData;
import org.joget.commons.util.LogUtil;

public boolean validate(Element element, FormData formData, String[] values) {
    boolean result = true;
  
    //get field values from form data object
    Form form = FormUtil.findRootForm(element);
    String nameString = "name";
    Element nameField = FormUtil.findElement(nameString, form, formData);
  
    if (nameField!=null) {
        //get value of fields
        String nameValue = FormUtil.getElementPropertyValue(nameField, formData);
        Connection con = null;
        try {
            // retrieve connection from the default datasource
            DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource");
            con = ds.getConnection();
           
            // execute SQL query
            if(!con.isClosed()) {
                PreparedStatement stmt = con.prepareStatement("SELECT * FROM app_fd_test_tableInfo WHERE NOT EXISTS (SELECT 1 FROM app_fd_test_tableInfo WHERE c_field1=?)");
                stmt.setObject(1, nameValue);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    result = false;
                } else {
                    result = true;
                }
            }
        } catch(Exception e) {
            LogUtil.error("Sample app - Form 1", e, "Error loading user data in load binder");
        } finally {
            //always close the connection after used
            try {
                if(con != null) {
                    con.close();
                }
            } catch(SQLException e) {/* ignored */}
        }
    } else {
        result = false;
    }
  
    return result;
}

//call validate method with injected variable
return validate(element, formData, values);

Do note that if you would like to change this BeanShell code to meet your requirements, it is fairly simple. All you have to do is change the following lines:

String nameString = "name";: Change to Field ID you would like to validate
Element nameField = FormUtil.findElement(nameString, form, formData);: Change the variable of the field element (Optional)
String nameValue = FormUtil.getElementPropertyValue(nameField, formData);: Change the variable of the field value (Optional)
PreparedStatement stmt = con.prepareStatement("SELECT * FROM app_fd_test_tableInfo WHERE NOT EXISTS (SELECT 1 FROM app_fd_test_tableInfo WHERE c_field1=?)");: Change the SQL query to match your requirements