Joget DX 8 Stable Released
The stable release for Joget DX 8 is now available, with a focus on UX and Governance.
You may want to store certain fields from your form to other tables with the use of the Beanshell Form Binder. Figure 1 shows an example of a form where the first 3 fields are to be stored in another data source in addition to the original form data table.
Figure1: Form with field to store
The quick and easy approach in addressing this requirement is to make use of the Beanshell Form Binder in the section's Store Binder. Edit the section.
Figure 2: Configure the section's properties to determine how data is being handled.
In the Store Binder, choose Bean Shell Form Binder as the Store Binder.
Figure 3: Choose Beanshell Form Binder as the Store Binder.
Configure the Bean Shell Form Binder with your own coding to store the fields intended as shown in the next figure. This is the code used in this example.
import org.joget.apps.app.service.*; import org.joget.apps.app.model.*; import org.joget.apps.form.model.*; import org.joget.apps.form.service.*; import java.sql.*; import java.util.*; public FormRowSet storeData() { 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("firstName", originalRow.getProperty("firstName")); newRow.put("lastName", originalRow.getProperty("lastName")); newRow.put("email", originalRow.getProperty("email")); newRows.add(newRow); String id = "#currentUser.username#"; //Store storeToOtherFormDataTable(element, newRows, formData, id); StoreUsingJDBC(element, newRows, formData, id); return rows; } //this function will put all the data gather from the element's childs to it's parent store binder public void normalStoring(Element element, FormRowSet rows, FormData formData) { if (rows != null && !rows.isEmpty()) { // find parent that have store binder Element parent = element.getParent(); while (parent.getStoreBinder() == null && parent.getParent() != null) { parent = parent.getParent(); } FormStoreBinder storeBinder = parent.getStoreBinder(); if (storeBinder != null) { FormRowSet parentRows = formData.getStoreBinderData(storeBinder); FormRow currentRow = rows.get(0); if (parentRows != null && parentRows.size() == 1 && rows.size() == 1) { FormRow parentRow = parentRows.get(0); parentRow.putAll(currentRow); } else { parentRows = new FormRowSet(); FormRow parentRow = new FormRow(); parentRow.putAll(currentRow); parentRows.add(parentRow); formData.setStoreBinderData(storeBinder, parentRows); } } } } //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) FormUtil.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 { Class.forName("com.mysql.jdbc.Driver").newInstance(); con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF-8", "root", ""); 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("firstName")); ustmt.setString(2, row.getProperty("lastName")); ustmt.setString(3, row.getProperty("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("firstName")); istmt.setString(4, row.getProperty("lastName")); istmt.setString(5, row.getProperty("email")); istmt.executeUpdate(); } } } catch (Exception ex) { System.err.println("Exception: " + ex.getMessage()); } finally { try { if(con != null) con.close(); } catch(SQLException e) {} } } return storeData();
Figure 4: Populate the Beanshell Form Binder with the necessary codes.
With the coding properly written and tested. You should be able to achieve the result as shown in the following figures.
Figure 5: Fill and submit the form.
Check the data in database.
.Figure 6: Data is stored correctly into the tables.