Store Form Field Data to Multiple Tables

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