You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

You may want to store certain fields from your form to another tables with the use of Beanshell Form Binder. Figure 1 shows an example of form that with the first 3 fields intended to be store to another data source beside 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.

  • No labels