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

App Structure

Figure 1:  Form 1 Fields


Figure 2: Form 1 Details


Figure 3: Form 2 Fields


Figure 4: 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:

BeanShell
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:

  • Line 18: Change to Field ID you would like to validate
  • Line 19: Change the variable of the field element (Optional)
  • Line 23: Change the variable of the field value (Optional)
  • Line 32: Change the SQL query to match your requirements 

Runtime

Figure 5: Form 1 List


Figure 6: Invalid Input

Since the value "Test" is not present in the Form 1 List, the validator will flag this submission and reject it.


Figure 7: Valid Input


Download the Sample App

  • No labels