Joget DX Beta 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 7 Current »

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 org.apache.commons.collections.SequencedHashMap;
import java.util.*;

public FormRowSet test() {
	FormRowSet f = new FormRowSet();
        f.setMultiRow(true);
        Class.forName("com.mysql.jdbc.Driver").newInstance();
	con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF-8", "database username", "database password");

	if(!con.isClosed()){
		String recordId = "#requestParam.id#"; // Get the url parameter
		String sql = "SELECT * FROM test_table WHERE id=?"; // Here you can query from one or multiple tables using JOIN etc
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, recordId);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
		FormRow r1 = new FormRow();
			r1.put("gridColumn1", rs.getString(1));
			r1.put("gridColumn1", rs.getString(2));
			r1.put("gridColumn1", rs.getString(3));
			f.add(r1);
	   }
	}
	return f;
}
return test();

Store Binder -> Bean Shell Form Binder

import org.joget.apps.form.model.*;
import org.joget.apps.form.lib.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import org.apache.commons.collections.SequencedHashMap;
import java.util.*;
import org.joget.commons.util.UuidGenerator;

public FormRowSet getGridRows() {
	return rows ; // this will return the grid rows
}

public saveGridRows(FormRowSet rows) {
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	con = DriverManager.getConnection("jdbc:mysql://localhost:3307/jwdb?characterEncoding=UTF-8", "database username", "database password");
	if(!con.isClosed()){

		String recordId = "#requestParam.id#";

		// If you need to query data from different table(s) and process it before saving it to database,
		// you can do it here


		UuidGenerator uuid = UuidGenerator.getInstance();

		Iterator i= rows.iterator(); // Iterating grid rows
		while (i.hasNext()) {
			FormRow row = (FormRow) i.next();
			String gridColumn1 = row.get("gridColumn1"); // reading grid column value
			String gridColumn2 = row.get("gridColumn2");
			String gridColumn3 = row.get("gridColumn3");
			String pId = uuid.getUuid(); // generating Primary Key
			String insertSql = "INSERT INTO table(id,col1,col2,col3) VALUES(?,?,?,?)";
			PreparedStatement stmtInsert = con.prepareStatement(insertSql);
			stmtInsert.setString(1, pId);
			stmtInsert.setString(2, gridColumn1);
			stmtInsert.setString(3, gridColumn2);
			stmtInsert.setString(4, gridColumn3);
			stmtInsert.executeUpdate();
		}
	} else {
		System.out.println("Connection Problem");
	}
	con.close();
}

FormRowSet rows = getGridRows(); // getting the grid rows
saveGridRows(rows); // processing & storing the grid rows


Grid Design

Correct Grid design should look like this:

 

part of load binder beanshell script :

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);
  • No labels