Autocomplete Multiselect Box - Data from querying database table
Confluence User - 14 Mar, 2019
Code for reference only. Feedback or enhancement is welcome. =)
- Autocomplete for Multi Select Box.
- Bean Shell Form Binder.
- Populate the Multi Select Box with data from another form/table.
- Limited to Top 1000 records only. Please change this to fewer records if there is performance issue while loading the form.
import java.util.Map;
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.apps.form.service.FormUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.joget.commons.util.LogUtil;
import java.sql.SQLException;
public FormRowSet load(Element element, String primaryKey, FormData formData) {
FormRowSet rows = new FormRowSet();
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 c_arm_form_id, c_arm_form_name from app_fd_arm_form_details limit 1000");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
FormRow option = new FormRow();
option.setProperty(FormUtil.PROPERTY_VALUE, rs.getString("c_arm_form_id"));
option.setProperty(FormUtil.PROPERTY_LABEL, rs.getString("c_arm_form_name"));
rows.add(option);
}
}
} catch(Exception e) {
LogUtil.error("Autocomplete form picker", e, "Error loading autocomplete data in form 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(element, primaryKey, formData);
form;autocomplete;typeahead;multiselectbox;beanshellformbinder;autocompletetextfield