ตัวอย่างการใช้ bean shell ดึงค่าจาก database มาแสดง
สร้าง table app_fd_prototype โดยใช้
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | -- -- 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 ; |
สร้างฟอร์มโดยใช้
1 2 | { "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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | 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)