I wrote a very simple SQL statement (CheckBox component), but I don't know why first version does not work (with hash value), but the second version does work fine (without hash value, only one user)

 

 

Version 1

import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import org.apache.commons.collections.SequencedHashMap;
import java.util.*;
import javax.sql.DataSource;
public FormRowSet getFormRowSet() {
 FormRowSet results = new FormRowSet();
Class.forName("com.mysql.jdbc.Driver").newInstance();
 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/XXXX?characterEncoding=UTF-8", "XXXX", "XXXX");

 if(!con.isClosed()){

 String sql = "select email from `jwdb`.`dir_user` where id = (Select userId from `jwdb`.`dir_employment` where id = (Select t1.hod from `jwdb`.`dir_department` as t1 left join `jwdb`.`dir_employment` as t2 on t1.organizationId = t2.organizationId and t1.id = t2.departmentId where t2.userId = '#currentUser.id#')) Union All select email from `jwdb`.`dir_user` where id = (Select userId from `jwdb`.`dir_employment` where id = (Select t3.hod from `jwdb`.`dir_department` as t3 where t3.Id = (Select t1.parentId from `jwdb`.`dir_department` as t1 left join `jwdb`.`dir_employment` as t2 on t1.organizationId = t2.organizationId and t1.id = t2.departmentId where t2.userId = '#currentUser.id#'))) Limit 1;";
PreparedStatement stmt = con.prepareStatement(sql);
 //stmt.setString(1, recordId);
 ResultSet rs = stmt.executeQuery();
 
 while (rs.next()) {
 FormRow row = new FormRow();
 row.put(FormUtil.PROPERTY_VALUE, rs.getString(1));
 row.put(FormUtil.PROPERTY_LABEL, rs.getString(1));
 row.put(FormUtil.PROPERTY_SELECTED, "true");
 results.add(row);
 }
 }
return results;
}
return getFormRowSet();

 

Version 2

import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import org.apache.commons.collections.SequencedHashMap;
import java.util.*;
import javax.sql.DataSource;
public FormRowSet getFormRowSet() {
 FormRowSet results = new FormRowSet();
Class.forName("com.mysql.jdbc.Driver").newInstance();
 con = DriverManager.getConnection("jdbc:mysql://localhost:3306/XXXX?characterEncoding=UTF-8", "XXXX", "XXXX");

 if(!con.isClosed()){

 String sql = "select email from `jwdb`.`dir_user` where id = (Select userId from `jwdb`.`dir_employment` where id = (Select t1.hod from `jwdb`.`dir_department` as t1 left join `jwdb`.`dir_employment` as t2 on t1.organizationId = t2.organizationId and t1.id = t2.departmentId where t2.userId = 'user1')) Union All select email from `jwdb`.`dir_user` where id = (Select userId from `jwdb`.`dir_employment` where id = (Select t3.hod from `jwdb`.`dir_department` as t3 where t3.Id = (Select t1.parentId from `jwdb`.`dir_department` as t1 left join `jwdb`.`dir_employment` as t2 on t1.organizationId = t2.organizationId and t1.id = t2.departmentId where t2.userId = 'user1'))) Limit 1;";
PreparedStatement stmt = con.prepareStatement(sql);
 //stmt.setString(1, recordId);
 ResultSet rs = stmt.executeQuery();
 
 while (rs.next()) {
 FormRow row = new FormRow();
 row.put(FormUtil.PROPERTY_VALUE, rs.getString(1));
 row.put(FormUtil.PROPERTY_LABEL, rs.getString(1));
 row.put(FormUtil.PROPERTY_SELECTED, "true");
 results.add(row);
 }
 }
return results;
}
return getFormRowSet();

 

 

 

Maybe you have an idea why this happens?

 

Regards

McStar

  • No labels