把表单字段数据存储到多个数据表


您可能希望使用Beanshell表单绑定器将表单中的某些字段存储到其他表格中。 图1  显示了除了原始表单数据表之外,前3个字段要存储在另一个数据源中的表单示例。

图1:带有字段的表单


然后,点击表单“属性”标签并导航到“高级”页面。选择“Bean Shell Form Binder”作为存储绑定器。

Figure 2: Choose Bean Shell Form Binder as the Store Binder

 

使用您自己的代码配置Bean Shell Form Binder,按照预期存储字段,如下图所示。

在这个例子中使用的代码:

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 storeData(Element element, FormRowSet rows, FormData formData) {

    //check for empty data

    if (rows == null || rows.isEmpty()) {

        return rows;

    }

    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 reuse workflow form binder to store data

public void normalStoring(Element element, FormRowSet rows, FormData formData) {

    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("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) {

        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);

 

图3:用必要的代码填充Bean Shell


如果编码正确编写和测试,你会得到这个结果:

图4:填写并提交测试表单


检查数据库中的数据。

图5:数据正确存储在表中

文档更新时间: 2018-11-06 06:38   作者:钟锡伟