Tag Archive Bean Shell

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.apps.form.service.FormUtil;
import org.joget.commons.util.LogUtil;
 
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()) {
            String sql = "SELECT PROVINCE_ID AS value, TRIM(PROVINCE_NAME) AS label FROM province ORDER BY PROVINCE_NAME ASC";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
 
            FormRow option = new FormRow();
 
            option.setProperty(FormUtil.PROPERTY_LABEL, "please select");
            //option.setProperty(FormUtil.PROPERTY_VALUE, (String) rs.getObject("value").toString());
 
            rows.add(option);
 
            while (rs.next()) {
                FormRow option = new FormRow();
 
                option.setProperty(FormUtil.PROPERTY_LABEL, (String) rs.getObject("label"));
                option.setProperty(FormUtil.PROPERTY_VALUE, (String) rs.getObject("value").toString());
 
                rows.add(option);
            }
        }
    } catch (Exception e) {
        LogUtil.error("Sample app - Form 1", e, "Form Options 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);