Bean Shell Form Binder with SQL Query
Hi Team
I have created a workflow for an absence request with Joget Community Edition, which is to be validated by the "approver" defined as a group (grp-001) in the organization. Joget correctly assigns the absence request to the defined "approver". However, I would like to send a notification to the approver by email immediately after the absence request has been completed. But I can't call the mail of the approver via "Hash Variable". That's why I have created a hidden field in the absence form and want to store the approver's mail in it with the following Bean Shell Form Binder skript.
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.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.commons.util.LogUtil;
public FormRowSet load(email) {
FormRowSet rows = new FormRowSet();
if (rows != null && !rows.isEmpfy(){
Connection con = null;
try {
// retrieve connection from the default datasource
DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource");
con = ds.getConnection();
// execute SQL query
if(!con.isClosed()) {
PreparedStatement stmt = con.prepareStatement("select u.email from dir_user_group g left join dir_user u on g.userId = u.username where g.groupId = 'grp-001'");
stmt.setObject(1, email);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
FormRow row = new FormRow();
System.out.println(rs.getObject("email") );
rows.add(row);
break;
}
}
} catch(Exception e) {
LogUtil.error("Sample app - Form 1", e, "Error loading user data in load binder");
} finally {
//always close the connection after used
try {
if(con != null) {
con.close();
}
} catch(SQLException e) {/* ignored */}
}
}
return rows;
}
//call load method with injected variable
return load(email);
But since I have no experience with the Bean Shell Form Binder, unfortunately it did not work.
However, the SQL query returns the correct mail in the database.
Can you help me further?