ตัวอย่างการใช้ 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)
About the author