public FormRowSet load(Element element, String username, FormData formData) { FormRowSet rows = new FormRowSet(); String category = #RequestParam.category#; if(category.equals("Breakfast")){ if (username != null && !username.isEmpty()) { Connection con = null; try { // retrieve connection from the default datasource DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource"); con = ds.getConnection(); String firstname = #currentUser.firstName#; String badgeID; // execute SQL query if(!con.isClosed()) {
PreparedStatement stmt = con.prepareStatement("SELECT distinct employeeCode FROM dir_employment JOIN dir_user ON dir_user.id = dir_employment.userId WHERE dir_user.firstName = ? "); stmt.setObject(1, firstname); ResultSet rs = stmt.executeQuery(); while (rs.next()) { badgeID = rs.getString("employeeCode"); } if (badgeID != null || !badgeID.isEmpty()){ PreparedStatement stmt1 = con.prepareStatement("SELECT * FROM app_fd_underweightstaff where c_badgeid = ? "); stmt1.setObject(1, badgeID); ResultSet rs1 = stmt1.executeQuery(); Boolean exist = false; while (rs1.next()) { exist = true; } FormRow r = new FormRow(); if(exist){ r.put("staffcategory", "RM 1"); }else { r.put("staffcategory", "RM 2"); } }else{ String id = FormUtil.getElementParameterName(element); formData.addFormError(id, "the badgeID is empty"); }
} } 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 */} } }
}else{ String id = FormUtil.getElementParameterName(element); formData.addFormError(id, "no category"); } return rows;
}
//call load method with injected variable return load(element, primaryKey, formData);
Hi, guys
I face some problem with my code, the staff category did not been fill by any value. Here are the code. Is there any problem?
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(Element element, String username, FormData formData) {
FormRowSet rows = new FormRowSet();
String category = #RequestParam.category#;
if(category.equals("Breakfast")){
if (username != null && !username.isEmpty()) {
Connection con = null;
try {
// retrieve connection from the default datasource
DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource");
con = ds.getConnection();
String firstname = #currentUser.firstName#;
String badgeID;
// execute SQL query
if(!con.isClosed()) {
PreparedStatement stmt = con.prepareStatement("SELECT distinct employeeCode FROM dir_employment JOIN dir_user ON dir_user.id = dir_employment.userId WHERE dir_user.firstName = ? ");
stmt.setObject(1, firstname);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
badgeID = rs.getString("employeeCode");
}
if (badgeID != null || !badgeID.isEmpty()){
PreparedStatement stmt1 = con.prepareStatement("SELECT * FROM app_fd_underweightstaff where c_badgeid = ? ");
stmt1.setObject(1, badgeID);
ResultSet rs1 = stmt1.executeQuery();
Boolean exist = false;
while (rs1.next()) {
exist = true;
}
FormRow r = new FormRow();
if(exist){
r.put("staffcategory", "RM 1");
}else {
r.put("staffcategory", "RM 2");
}
}else{
String id = FormUtil.getElementParameterName(element);
formData.addFormError(id, "the badgeID is empty");
}
}
} 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 */}
}
}
}else{
String id = FormUtil.getElementParameterName(element);
formData.addFormError(id, "no category");
}
return rows;
}
//call load method with injected variable
return load(element, primaryKey, formData);