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:
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 locNameString = "locationName";
Element locNameField = FormUtil.findElement(locNameString, form, formData);
if (locNameField!=null) {
//get value of fields
String locNameValue = FormUtil.getElementPropertyValue(locNameField, 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, locNameValue);
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 "C4501" 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
- APP_KB_beanshellvalidator.jwa
- There is also a second sample app that implements a similar concept to this article but has different requirements. You can download and check out the other sample app here: APP_KB_beanshellValidatorSample.jwa.