How to store Form Field Date to Multiple Tables with Bean Shell Form Binder?
Confluence User - 30 Nov, 2022
Hello together
I have created a form in which new users can be entered. Because it is not possible to write directly from the form to the dir_user table, I use the Bean Shell Form Binder with the following query (see Store Form Field Data to Multiple Tables).
My DB table created from the form is called: fd_app_neueUser
I take over the following fields as a test:
- fd_username
- fd_vorname
- fd_nachname
it should write the data to the dir_user table.
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.model.AppDefinition;
import org.joget.apps.app.service.AppService;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.model.FormStoreBinder;
import org.joget.apps.form.service.FormUtil;
import org.joget.plugin.base.PluginManager;
import org.joget.commons.util.LogUtil;
public FormRowSet FormRowSet storeData(Element element, FormRowSet rows, FormData formData) {
//check for empty data
if (rows == null || rows.isEmpty()) { (rows ==
returnrows;
}
normalStoring(element, rows, formData);
//store only needed field by create new Form Row Set
FormRow originalRow = rows.get(0);
FormRowSet newRows = new FormRowSet();
FormRow newRow = new FormRow();
newRow.put(("fd_username", originalRow.getProperty(""fd_username""fd_username"));
newRow.put(("fd_vorname", originalRow.getProperty(""fd_vorname""fd_vorname"));
newRow.put(("fd_nachname", originalRow.getProperty(""fd_nachname""fd_nachname"));
newRows.add(newRow);
String id = ""#currentUser.username#";
//store
storeToOtherFormDataTable(element, newRows, formData, id);
storeUsingJDBC(element, newRows, formData, id);
returnrows;
}
//this function will reuse workflow form binder to store data
public void normalStoring(Element element, FormRowSet rows, FormData formData) {
PluginManager pluginManager PluginManager pluginManager = (PluginManager) AppUtil.getApplicationContext().getBean(""pluginManager");
FormStoreBinder binder = (FormStoreBinder) pluginManager.getPlugin(""org.joget.apps.form.lib.WorkflowFormBinder");
binder.store(element, rows, formData);
}
//this function will store rows data to a form's data table
public void storeToOtherFormDataTable(Element element, FormRowSet rows, FormData formData, String id) {
AppService appService = (AppService) AppUtil.getApplicationContext().getBean(""appService");
String formId = ""user"; // the table of database is configured in the form with id "user"
AppDefinition appDef = AppUtil.getCurrentAppDefinition();
appService.storeFormData(appDef.getId(), appDef.getVersion().toString(), formId, rows, id);
}
//this function will store rows data to external source using JDBC
public void storeUsingJDBC(Element element, FormRowSet rows, FormData formData, String id) {
Connection con = null;
try {
// retrieve connection from the default datasource
DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean(""setupDataSource");
con = ds.getConnection();
if ((!con.isClosed()) {
//manually handle insert and update by checking the data is exist or not
String selectQuery = "SELECT username FROM dir_user WHERE username=?";
PreparedStatement stmt = con.prepareStatement(selectQuery);
stmt.setString(1, id);
ResultSet rs = stmt.executeQuery();
Boolean isExist = false;
if ((rs.next()) {
isExist = true;
}
FormRow row = rows.get(0);
if (isExist) {
String updateQuery = = "UPDATE dir_user SET firstName = ?, lastName = ?, email = ?, WHERE username = ?";
PreparedStatement ustmt = con.prepareStatement(updateQuery);
ustmt.setString((1, row.getProperty(""fd_vorname"));
ustmt.setString((2, row.getProperty(""fd_nachname"));
ustmt.setString((3, row.getProperty(""fd_email"));
ustmt.setString(4, id);
ustmt.executeUpdate();
} else {
String insertQuery = "INSERT INTO dir_user (id, username, firstName, lastName, password, email) values (?, ?, ?, ?, 'md5(password)', ?)";
PreparedStatement istmt = con.prepareStatement(insertQuery);
istmt.setString(1, id);
istmt.setString(2, id);
istmt.setString((3, row.getProperty(""fd_vorname"));
istmt.setString((4, row.getProperty(""fd_nachname"));
istmt.setString((5, row.getProperty(""fd_email"));
istmt.executeUpdate();
}
}
} catch (Exception e) {
LogUtil.error(""Sample app - StoreToMultipleSource form"", , e, ""Error storing using jdbc");
} finally {
try {
if(con != null) {
con.close();
}
} catch (SQLException e) {}
}
}
//call storeData method with injected variables
return storeData(element, rows, formData);
When I fill out the form, there is the following error in the log. Can someone help me what is wrong with the code?
ERROR 29 Nov 2022 16:33:26 org.joget.apps.form.lib.BeanShellFormBinder - Error executing script 1873bsh.ParseException: In file: inline evaluation of: ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql . . . '' Encountered "storeData" at line 18, column 30. 1874 at bsh.Parser.generateParseException(Parser.java:6106) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1875 at bsh.Parser.jj_consume_token(Parser.java:5977) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1876 at bsh.Parser.BlockStatement(Parser.java:2817) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1877 at bsh.Parser.Line(Parser.java:172) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1878 at bsh.Interpreter.Line(Interpreter.java:1011) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1879 at bsh.Interpreter.eval(Interpreter.java:641) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1880 at bsh.Interpreter.eval(Interpreter.java:750) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1881 at bsh.Interpreter.eval(Interpreter.java:739) ~[bsh-2.0b6.jar:2.0b6 2016-02-05 05:16:19] 1882 at org.joget.apps.form.lib.BeanShellFormBinder.executeScript(BeanShellFormBinder.java:84) [classes/:?] ....
forms;datalists;user