Page History

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