Joget DX Available for Download

Check out Joget DX, the next generation successor to Joget Workflow for faster, simpler digital transformation.

Skip to end of metadata
Go to start of metadata

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

Compare with Current View Page History

« Previous Version 5 Next »

This post explains how to load / read data from Form Grid using Bean Shell Form Binder. This can be handy if you need to load / store grid data from multiple tables.

Load Binder -> Bean Shell Form Binder

import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import java.util.*;

FormRowSet f = new FormRowSet();
f.setMultiRow(true);

//Get Joget's current datasource configs
DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");

con = ds.getConnection();

if (!con.isClosed()) {
	//Get the URL parameter
	String recordId = "#requestParam.id#";
 
	//Here you can query from one or multiple tables using JOIN etc
	String sql = "SELECT * FROM your_table_name WHERE id=?";
	PreparedStatement stmt = con.prepareStatement(sql);
	stmt.setString(1, recordId);
 
	//Execute the SELECT SQL statement
	ResultSet rs = stmt.executeQuery();
		
	//Get value from columns of record(s)
	while (rs.next()) {
		FormRow r1 = new FormRow();
		r1.put("gridColumn1", rs.getString(1));
		r1.put("gridColumn2", rs.getString(2));
		r1.put("gridColumn3", rs.getString(3));
		f.add(r1);
	}
}

return f;


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.service.AppUtil;
import org.joget.apps.form.model.Form;
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.service.FormUtil;
import org.joget.commons.util.UuidGenerator;

public saveGridRows(Element element, FormRowSet rows, FormData formData) {
    
    String recordId = null;
    Connection con = null;
    
    try {
        //Get Joget's current datasource configs
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");

        con = ds.getConnection();
        
        if(!con.isClosed()) {
            //To generate new record IDs for storing into child table
            UuidGenerator uuid = UuidGenerator.getInstance();
            
            //Iterate to add new records
            Iterator i= rows.iterator();
            while (i.hasNext()) {
                FormRow row = (FormRow) i.next();
                
                String pId = uuid.getUuid();
                String gridColumn1 = row.get("gridColumn1");
                String gridColumn2 = row.get("gridColumn2");
                String gridColumn3 = row.get("gridColumn3");
                
                String insertSql = "INSERT INTO your_table_name (id,gridColumn1,gridColumn2,gridColumn3) VALUES (?,?,?,?);";
                
                PreparedStatement stmtInsert = con.prepareStatement(insertSql);
                
                stmtInsert.setString(1, pId);
                stmtInsert.setString(2, gridColumn1);
                stmtInsert.setString(3, gridColumn2);
                stmtInsert.setString(4, gridColumn3);
                
                //Execute SQL statement
                stmtInsert.executeUpdate();
            }
        }
    } catch (Exception ex) {
        LogUtil.error("Your App/Plugin Name", ex, "Error storing using jdbc");
    } finally {
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception ex) {
            LogUtil.error("Your App/Plugin Name", ex, "Error closing the jdbc connection");
        }
    }
}

//Process and store grid rows
saveGridRows(element, rows, formData);


Grid Design

Correct Grid design should look like this:

 

Example of a simple load binder beanshell script :

import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;

FormRowSet f = new FormRowSet();
f.setMultiRow(true);

FormRow r1 = new FormRow();
r1.put("gridColumn1", your_value);
r1.put("gridColumn2", your_value);
r1.put("gridColumn3", your_value);
f.add(r1);

FormRow r2 = new FormRow();
r2.put("gridColumn1", your_value);
r2.put("gridColumn2", your_value);
r2.put("gridColumn3", your_value);
f.add(r2);
 
return f;

Related Tutorial

 

 

 

  • No labels