有了如何在插件中对数据表进行CRUD 的基础,我们这次来开发下拉选项值绑定插件.

需求

什么时候我们需要开发一个自定义的插件呢?

比如,下拉选项默认的绑定器无法满足你,你想要自己开发一个符合自己的业务模式.

在社区版,没有jdbc绑定器,你想要下拉选项值通过写 SQL 来返回.

如何去做?

1.找到这种类型插件的官方API文档

我们这里需要 https://dev.joget.org/community/pages/viewpage.action?pageId=26117033

https://dev.joget.org/community/display/KBv6/How+to+develop+a+JDBC+Options+Binder

作为参考.

2.实现文中所述的抽象类和接口

package org.joget.tutorial;
  
import org.joget.apps.app.service.AppPluginUtil;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormAjaxOptionsBinder;
import org.joget.apps.form.model.FormBinder;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormLoadOptionsBinder;
import org.joget.apps.form.model.FormRowSet;
  
public class JdbcOptionsBinder extends FormBinder implements FormLoadOptionsBinder, FormAjaxOptionsBinder {
     
    private final static String MESSAGE_PATH = "messages/JdbcOptionsBinder";
     
    public String getName() {
        return "JDBC Option Binder";
    }
  
    public String getVersion() {
        return "5.0.0";
    }
     
    public String getClassName() {
        return getClass().getName();
    }
  
    public String getLabel() {
        //support i18n
        return AppPluginUtil.getMessage("org.joget.tutorial.JdbcOptionsBinder.pluginLabel", getClassName(), MESSAGE_PATH);
    }
     
    public String getDescription() {
        //support i18n
        return AppPluginUtil.getMessage("org.joget.tutorial.JdbcOptionsBinder.pluginDesc", getClassName(), MESSAGE_PATH);
    }
  
    public String getPropertyOptions() {
        return AppUtil.readPluginResource(getClassName(), "/properties/jdbcOptionsBinder.json", null, true, MESSAGE_PATH);
    }
 
    public FormRowSet load(Element element, String primaryKey, FormData formData) {
        return loadAjaxOptions(null); // reuse loadAjaxOptions method
    }
  
    public boolean useAjax() {
        return "true".equalsIgnoreCase(getPropertyString("useAjax")); // let user to decide whether or not to use ajax for dependency field
    }
  
/**
 * JSON API for test connection button
 * @param request
 * @param response
 * @throws ServletException
 * @throws IOException
 */
public void webService(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    //Limit the API for admin usage only
    boolean isAdmin = WorkflowUtil.isCurrentUserInRole(WorkflowUserManager.ROLE_ADMIN);
    if (!isAdmin) {
        response.sendError(HttpServletResponse.SC_UNAUTHORIZED);
        return;
    }
     
    String action = request.getParameter("action");
    if ("testConnection".equals(action)) {
        String message = "";
        Connection conn = null;
        try {
            AppDefinition appDef = AppUtil.getCurrentAppDefinition();
             
            String jdbcDriver = AppUtil.processHashVariable(request.getParameter("jdbcDriver"), null, null, null, appDef);
            String jdbcUrl = AppUtil.processHashVariable(request.getParameter("jdbcUrl"), null, null, null, appDef);
            String jdbcUser = AppUtil.processHashVariable(request.getParameter("jdbcUser"), null, null, null, appDef);
            String jdbcPassword = AppUtil.processHashVariable(SecurityUtil.decrypt(request.getParameter("jdbcPassword")), null, null, null, appDef);
             
            Properties dsProps = new Properties();
            dsProps.put("driverClassName", jdbcDriver);
            dsProps.put("url", jdbcUrl);
            dsProps.put("username", jdbcUser);
            dsProps.put("password", jdbcPassword);
            DataSource ds = BasicDataSourceFactory.createDataSource(dsProps);
             
            conn = ds.getConnection();
             
            message = AppPluginUtil.getMessage("form.jdbcOptionsBinder.connectionOk", getClassName(), MESSAGE_PATH);
        } catch (Exception e) {
            LogUtil.error(getClassName(), e, "Test Connection error");
            message = AppPluginUtil.getMessage("form.jdbcOptionsBinder.connectionFail", getClassName(), MESSAGE_PATH) + "\n"  + e.getMessage();
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (Exception e) {
                LogUtil.error(DynamicDataSourceManager.class.getName(), e, "");
            }
        }
        try {
            JSONObject jsonObject = new JSONObject();
            jsonObject.accumulate("message", message);
            jsonObject.write(response.getWriter());
        } catch (Exception e) {
            //ignore
        }
    } else {
        response.setStatus(HttpServletResponse.SC_NO_CONTENT);
    }
}
public FormRowSet loadAjaxOptions(String[] dependencyValues) {
    FormRowSet rows = new FormRowSet();
    rows.setMultiRow(true);
     
    //add empty option based on setting
    if ("true".equals(getPropertyString("addEmpty"))) {
        FormRow empty = new FormRow();
        empty.setProperty(FormUtil.PROPERTY_LABEL, getPropertyString("emptyLabel"));
        empty.setProperty(FormUtil.PROPERTY_VALUE, "");
        rows.add(empty);
    }
     
    //Check the sql. If require dependency value and dependency value is not exist, return empty result.
    String sql = getPropertyString("sql");
    if ((dependencyValues == null || dependencyValues.length == 0) && sql.contains("?")) {
        return rows;
    }
     
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
     
    try {
        DataSource ds = createDataSource();
        con = ds.getConnection();
         
        //support for multiple dependency values
        if (sql.contains("?") && dependencyValues != null && dependencyValues.length > 1) {
            String mark = "?";
            for (int i = 1; i < dependencyValues.length; i++) {
                mark += ", ?";
            }
            sql = sql.replace("?", mark);
        }
         
        pstmt = con.prepareStatement(sql);
         
        //set query parameters
        if (sql.contains("?") && dependencyValues != null && dependencyValues.length > 0) {
            for (int i = 0; i < dependencyValues.length; i++) {
                pstmt.setObject(i + 1, dependencyValues[i]);
            }
        }
         
        rs = pstmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnsNumber = rsmd.getColumnCount();
         
        // Set retrieved result to Form Row Set
        while (rs.next()) {
            FormRow row = new FormRow();
             
            String value = rs.getString(1);
            String label = rs.getString(2);
             
            row.setProperty(FormUtil.PROPERTY_VALUE, (value != null)?value:"");
            row.setProperty(FormUtil.PROPERTY_LABEL, (label != null)?label:"");
             
            if (columnsNumber > 2) {
                String grouping = rs.getString(3);
                row.setProperty(FormUtil.PROPERTY_GROUPING, grouping);
            }
             
            rows.add(row);
        }
    } catch (Exception e) {
        LogUtil.error(getClassName(), e, "");
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
            LogUtil.error(getClassName(), e, "");
        }
    }
     
    return rows;
}
 
/**
 * To creates data source based on setting
 * @return
 * @throws Exception
 */
protected DataSource createDataSource() throws Exception {
    DataSource ds = null;
    String datasource = getPropertyString("jdbcDatasource");
    if ("default".equals(datasource)) {
        // use current datasource
         ds = (DataSource)AppUtil.getApplicationContext().getBean("setupDataSource");
    } else {
        // use custom datasource
        Properties dsProps = new Properties();
        dsProps.put("driverClassName", getPropertyString("jdbcDriver"));
        dsProps.put("url", getPropertyString("jdbcUrl"));
        dsProps.put("username", getPropertyString("jdbcUser"));
        dsProps.put("password", getPropertyString("jdbcPassword"));
        ds = BasicDataSourceFactory.createDataSource(dsProps);
    }
    return ds;
}

}


3.抽象配置项

为什么我们需要配置项?

  1. 插件要灵活,需求sql不可能是固定的,肯定是变化的
  2. 要不要使用表单上的字段作为参数传到SQL里面?
  3. 要不要使用默认数据源,能不能访问其他的数据库获取数据?
[{
    title : '@@form.jdbcOptionsBinder.config@@',
    properties : [{
        name : 'jdbcDatasource',
        label : '@@form.jdbcOptionsBinder.datasource@@',
        type : 'selectbox',
        options : [{
            value : 'custom',
            label : '@@form.jdbcOptionsBinder.customDatasource@@'
        },{
            value : 'default',
            label : '@@form.jdbcOptionsBinder.defaultDatasource@@'
        }],
        value : 'default'
    },{
        name : 'jdbcDriver',
        label : '@@form.jdbcOptionsBinder.driver@@',
        description : '@@form.jdbcOptionsBinder.driver.desc@@',
        type : 'textfield',
        value : 'com.mysql.jdbc.Driver',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        required : 'true'
    },{
        name : 'jdbcUrl',
        label : '@@form.jdbcOptionsBinder.url@@',
        type : 'textfield',
        value : 'jdbc:mysql://localhost/jwdb?characterEncoding=UTF8',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        required : 'true'
    },{
        name : 'jdbcUser',
        label : '@@form.jdbcOptionsBinder.username@@',
        type : 'textfield',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        value : 'root',
        required : 'true'
    },{
        name : 'jdbcPassword',
        label : '@@form.jdbcOptionsBinder.password@@',
        type : 'password',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        value : ''
    },{
        name : 'useAjax',
        label : '@@form.jdbcOptionsBinder.useAjax@@',
        type : 'checkbox',
        options : [{
            value : 'true',
            label : ''
        }]
    },{
        name : 'addEmpty',
        label : '@@form.jdbcOptionsBinder.addEmpty@@',
        type : 'checkbox',
        options : [{
            value : 'true',
            label : ''
        }]
    },{
        name : 'emptyLabel',
        label : '@@form.jdbcOptionsBinder.emptyLabel@@',
        type : 'textfield',
        control_field: 'addEmpty',
        control_value: 'true',
        control_use_regex: 'false',
        value : ''
    },{
        name : 'sql',
        label : '@@form.jdbcOptionsBinder.sql@@',
        description : '@@form.jdbcOptionsBinder.sql.desc@@',
        type : 'codeeditor',
        mode : 'sql',
        required : 'true'
    }],
    buttons : [{
        name : 'testConnection',   
        label : '@@form.jdbcOptionsBinder.testConnection@@',
        ajax_url : '[CONTEXT_PATH]/web/json/app[APP_PATH]/plugin/org.joget.tutorial.JdbcOptionsBinder/service?action=testConnection',
        fields : ['jdbcDriver', 'jdbcUrl', 'jdbcUser', 'jdbcPassword'],
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false'
    }]
}]

4.打包测试

  1. 打包前需要添加依赖
  2. 确保插件国际化
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jsp-api</artifactId>
    <version>2.0</version>
</dependency>
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.3</version>
</dependency>
org.joget.tutorial.JdbcOptionsBinder.pluginLabel=JDBC Binder
org.joget.tutorial.JdbcOptionsBinder.pluginDesc=Used to load field's options using JDBC
form.jdbcOptionsBinder.config=Configure JDBC Binder
form.jdbcOptionsBinder.datasource=Datasource
form.jdbcOptionsBinder.customDatasource=Custom Datasource
form.jdbcOptionsBinder.defaultDatasource=Default Datasource
form.jdbcOptionsBinder.driver=Custom JDBC Driver
form.jdbcOptionsBinder.driver.desc=Eg. com.mysql.jdbc.Driver (MySQL), oracle.jdbc.driver.OracleDriver (Oracle), com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft SQL Server)
form.jdbcOptionsBinder.url=Custom JDBC URL
form.jdbcOptionsBinder.username=Custom JDBC Username
form.jdbcOptionsBinder.password=Custom JDBC Password
form.jdbcOptionsBinder.useAjax=Use AJAX for cascade options?
form.jdbcOptionsBinder.addEmpty=Add Empty Option?
form.jdbcOptionsBinder.emptyLabel=Empty Option Label
form.jdbcOptionsBinder.sql=SQL SELECT Query
form.jdbcOptionsBinder.sql.desc=Use question mark (?) in your query to represent dependency values when using AJAX
form.jdbcOptionsBinder.testConnection=Test Connection
form.jdbcOptionsBinder.connectionOk=Database connected
form.jdbcOptionsBinder.connectionFail=Not able to establish connection.

5.源码分享

jdbc_options_binder_src.zip

  • No labels