Site icon PlusMagi's Blog By Pitt Phunsanit

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)
Exit mobile version