Bean Shell – PlusMagi's Blog By Pitt Phunsanit

joget:Form Options Binder

ตัวอย่างการดึงฐานข้อมูลมาแสดงเป็น select box โดยใช้ Bean Shell

ใน Form Binder คลิก selectbox ที่ต้องการ
Or Choose Options Binder เลือกเป็น Bean Shell Form Binder

คลิก next จะเปลี่ยนไปหน้า Edit Select Box > Configure Bean Shell Form Binder > Advanced Options ให้นำ code ไปใส่ไว้

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);

การเซ็ต value ของ option บางครั้งต้องแปลง integer เป็น string ด้วยเช่น option.setProperty(FormUtil.PROPERTY_VALUE, (String) rs.getObject(“value”).toString());

joget: Bean Shell Form Binder

ตัวอย่างการใช้ bean shell ดึงค่าจาก database มาแสดง

สร้าง table app_fd_prototype โดยใช้

--
-- Table structure for table `app_fd_prototype`
--

CREATE TABLE `app_fd_prototype` (
`id` varchar(255) NOT NULL,
`dateCreated` datetime DEFAULT NULL,
`dateModified` datetime DEFAULT NULL,
`c_Checkbox` longtext NOT NULL,
`c_DatePicker` longtext NOT NULL,
`c_FileUpload` longtext NOT NULL,
`c_HiddenField` longtext NOT NULL,
`c_PasswordField` longtext NOT NULL,
`c_Radio` longtext NOT NULL,
`c_SelectBox` longtext NOT NULL,
`c_TextArea` longtext NOT NULL,
`c_TextField` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `app_fd_prototype`
--

INSERT INTO `app_fd_prototype` (`id`, `dateCreated`, `dateModified`, `c_Checkbox`, `c_DatePicker`, `c_FileUpload`, `c_HiddenField`, `c_PasswordField`, `c_Radio`, `c_SelectBox`, `c_TextArea`, `c_TextField`) VALUES
('399895e8-ac15e1b1-2ddac962-1ca1ea8b', '2017-10-02 00:00:00', '2017-10-27 00:00:00', 'permanent', '10/09/2017', '04a972cda3440360b437a67d3a0dbb9d.jpg ', 'HiddenField	', 'PasswordField', 'male', 'programmer', 'โปรแกรมเมอร์ธรรมดาที่อยากเขียนสิ่งที่ไม่ธรรมดา ลูกค้า ผู้ชมเข้ามาดูต้องบอกว่า ดี แจ๋ง ง่าย แต่คนเขียนต้องไม่ลำบากกับชีวิต', 'pitt phunsanit');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `app_fd_prototype`
--
ALTER TABLE `app_fd_prototype`
ADD PRIMARY KEY (`id`);
COMMIT;

สร้างฟอร์มโดยใช้

{"className":"org.joget.apps.form.model.Form","properties":{"id":"BeanShellFormBinder","loadBinder":{"className":"org.joget.apps.form.lib.BeanShellFormBinder","properties":{"useAjax":"","script":"import java.sql.Connection;\r\nimport java.sql.PreparedStatement;\r\nimport java.sql.ResultSet;\r\nimport java.sql.SQLException;\r\nimport javax.sql.DataSource;\r\nimport org.joget.apps.app.service.AppUtil;\r\nimport org.joget.apps.form.model.Element;\r\nimport org.joget.apps.form.model.FormData;\r\nimport org.joget.apps.form.model.FormRow;\r\nimport org.joget.apps.form.model.FormRowSet;\r\nimport org.joget.commons.util.LogUtil;\r\n\r\npublic FormRowSet load(Element element, String primaryKey, FormData formData) {\r\n    FormRowSet rows = new FormRowSet();\r\n    if (primaryKey != null && !primaryKey.isEmpty()) {\r\n        Connection con = null;\r\n        try {\r\n            \/*retrieve connection from the default datasource*\/\r\n            DataSource ds = (DataSource)AppUtil.getApplicationContext().getBean(\"setupDataSource\");\r\n            con = ds.getConnection();\r\n\r\n            \/*execute SQL query*\/\r\n            if(!con.isClosed()) {\r\n                String sql = \"SELECT c_Checkbox, c_DatePicker, c_FileUpload, c_HiddenField, c_PasswordField, c_Radio, c_SelectBox, c_TextArea, c_TextField FROM app_fd_prototype WHERE id = ?\";\r\n                PreparedStatement stmt = con.prepareStatement(sql);\r\n                stmt.setObject(1, primaryKey);\r\n\r\n                ResultSet rs = stmt.executeQuery();\r\n                while (rs.next()) {\r\n                    FormRow row = new FormRow();\r\n \r\n                System.out.println(rs.getObject(\"c_Checkbox\"));\r\n\r\n                    row.put(\"Checkbox\", (rs.getObject(\"c_Checkbox\") != null) ? rs.getObject(\"c_Checkbox\").toString() : \"\");\r\n                    row.put(\"DatePicker\", (rs.getObject(\"c_DatePicker\") != null) ? rs.getObject(\"c_DatePicker\").toString() : \"\");\r\n                    row.put(\"FileUpload\", (rs.getObject(\"c_FileUpload\") != null) ? rs.getObject(\"c_FileUpload\").toString() : \"\");\r\n                    row.put(\"HiddenField\", (rs.getObject(\"c_HiddenField\") != null) ? rs.getObject(\"c_HiddenField\").toString() : \"\");\r\n                    row.put(\"PasswordField\", (rs.getObject(\"c_PasswordField\") != null) ? rs.getObject(\"c_PasswordField\").toString() : \"\");\r\n                    row.put(\"Radio\", (rs.getObject(\"c_Radio\") != null) ? rs.getObject(\"c_Radio\").toString() : \"\");\r\n                    row.put(\"SelectBox\", (rs.getObject(\"c_SelectBox\") != null) ? rs.getObject(\"c_SelectBox\").toString() : \"\");\r\n                    row.put(\"TextArea\", (rs.getObject(\"c_TextArea\") != null) ? rs.getObject(\"c_TextArea\").toString() : \"\");\r\n                    row.put(\"TextField\", (rs.getObject(\"c_TextField\") != null) ? rs.getObject(\"c_TextField\").toString() : \"\");\r\n \r\n                    rows.add(row);\r\n                    break;\r\n                }\r\n            }\r\n        } catch(Exception e) {\r\n            LogUtil.error(\"Sample app - Form 1\", e, \"Error loading user data in load binder\");\r\n        } finally {\r\n            \/*always close the connection after used*\/\r\n            try {\r\n                if(con != null) {\r\n                    con.close();\r\n                }\r\n            } catch(SQLException e) {\/*ignored*\/}\r\n        }\r\n    }\r\n    return rows;\r\n}\r\n \r\n\/*call load method with injected variable*\/\r\nreturn load(element, primaryKey, formData);"}},"description":"","tableName":"prototype","postProcessorRunOn":"both","name":"Bean Shell Form Binder","postProcessor":{"className":"","properties":{}},"storeBinder":{"className":"","properties":{}},"permission":{"className":"","properties":{}},"noPermissionMessage":""},"elements":[{"elements":[{"elements":[{"className":"org.joget.apps.form.lib.CheckBox","properties":{"id":"Checkbox","workflowVariable":"","readonlyLabel":"","optionsBinder":{"className":"","properties":{}},"validator":{"className":"","properties":{}},"controlField":"","value":"","label":"Checkbox","readonly":"","options":[{"grouping":"","value":"freelance","label":"freelance"},{"grouping":"","value":"permanent","label":"permanent"}]}},{"className":"org.joget.apps.form.lib.DatePicker","properties":{"readonlyLabel":"","validator":{"className":"","properties":{}},"endDateFieldId":"","label":"Date Picker","format":"","startDateFieldId":"","allowManual":"","id":"DatePicker","workflowVariable":"","value":"","dataFormat":"","readonly":"","yearRange":"c-10:c+10","currentDateAs":""}},{"className":"org.joget.apps.form.lib.FileUpload","properties":{"id":"FileUpload","fileType":"","validator":{"className":"","properties":{}},"label":"FileUpload","attachment":"","readonly":"","multiple":"","permissionType":"","fileTypeMsg":"Invalid file type","maxSizeMsg":"File size limit exceeded","maxSize":"","size":""}},{"className":"org.joget.apps.form.lib.HiddenField","properties":{"id":"HiddenField","workflowVariable":"","value":"","useDefaultWhenEmpty":""}},{"className":"org.joget.apps.form.lib.PasswordField","properties":{"id":"TextField","readonlyLabel":"","maxlength":"","validator":{"className":"","properties":{}},"value":"","label":"TextField","readonly":"","size":""}},{"className":"org.joget.apps.form.lib.Radio","properties":{"id":"Radio","workflowVariable":"","readonlyLabel":"","optionsBinder":{"className":"","properties":{}},"validator":{"className":"","properties":{}},"controlField":"","value":"","label":"Radio","readonly":"","options":[{"grouping":"","value":
"female","label":"female"},{"grouping":"","value":"male","label":"male"}]}},{"className":"org.joget.apps.form.lib.SelectBox","properties":{"id":"SelectBox","readonlyLabel":"","workflowVariable":"","optionsBinder":{"className":"","properties":{}},"validator":{"className":"","properties":{}},"value":"","controlField":"","label":"SelectBox","multiple":"","readonly":"","options":[{"grouping":"","value":"programmer","label":"programmer"},{"grouping":"","value":"tester","label":"tester"}],"size":""}},{"className":"org.joget.apps.form.lib.TextArea","properties":{"id":"TextArea","cols":"20","readonlyLabel":"","workflowVariable":"","validator":{"className":"","properties":{}},"value":"","label":"TextArea","readonly":"","rows":"5"}},{"className":"org.joget.apps.form.lib.TextField","properties":{"id":"TextField","readonlyLabel":"","workflowVariable":"","maxlength":"","encryption":"","validator":{"className":"","properties":{}},"value":"","label":"TextField","readonly":"","size":""}}],"className":"org.joget.apps.form.model.Column","properties":{"width":"100%"}}],"className":"org.joget.apps.form.model.Section","properties":{"id":"section1","label":"Section"}}]}

ใส่ Edit Form > Advanced > Configure Bean Shell Form Binder

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 primaryKey, FormData formData) {
FormRowSet rows = new FormRowSet();
if (primaryKey != null && !primaryKey.isEmpty()) {
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 c_Checkbox, c_DatePicker, c_FileUpload, c_HiddenField, c_PasswordField, c_Radio, c_SelectBox, c_TextArea, c_TextField FROM app_fd_prototype WHERE id = ?";
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setObject(1, primaryKey);

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
FormRow row = new FormRow();

System.out.println(rs.getObject("c_Checkbox"));

row.put("Checkbox", (rs.getObject("c_Checkbox") != null) ? rs.getObject("c_Checkbox").toString() : "");
row.put("DatePicker", (rs.getObject("c_DatePicker") != null) ? rs.getObject("c_DatePicker").toString() : "");
row.put("FileUpload", (rs.getObject("c_FileUpload") != null) ? rs.getObject("c_FileUpload").toString() : "");
row.put("HiddenField", (rs.getObject("c_HiddenField") != null) ? rs.getObject("c_HiddenField").toString() : "");
row.put("PasswordField", (rs.getObject("c_PasswordField") != null) ? rs.getObject("c_PasswordField").toString() : "");
row.put("Radio", (rs.getObject("c_Radio") != null) ? rs.getObject("c_Radio").toString() : "");
row.put("SelectBox", (rs.getObject("c_SelectBox") != null) ? rs.getObject("c_SelectBox").toString() : "");
row.put("TextArea", (rs.getObject("c_TextArea") != null) ? rs.getObject("c_TextArea").toString() : "");
row.put("TextField", (rs.getObject("c_TextField") != null) ? rs.getObject("c_TextField").toString() : "");

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(element, primaryKey, formData);

Injected Variables:

element: Element that this binder is tie to. (org.joget.apps.form.model.Element)
primaryKey: The primary key provided by the element to load data. (java.lang.String)
formData: The data holder of the whole form. (org.joget.apps.form.model.FormData)

PlusMagi's Blog By Pitt Phunsanit

Tag Archive Bean Shell

joget:Form Options Binder

joget: Bean Shell Form Binder