Page History

...

In the properties definition options file, we will need to provide options as below. Please note that we can use "@@message.key@@" syntax to support i18n in our properties options.

Code Block

language	js

[{
    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'
    }]
}]

In the Properties Options, we added a button for testing connection when using a custom datasource. This button is calling a JSON API to do the test. So, our plugin will need to implement org.joget.plugin.base.PluginWebSupport interface to make it as a Web Service Plugin as well. Let implement the webService method as following for test JDBC connection.

Code Block

language	java

    /**
     * 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);
                
                Class.forName(jdbcDriver);
                conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
                
                message = ResourceBundleUtil.getMessage("form.jdbcOptionsBinder.connectionOk");
            } catch (Exception e) {
                LogUtil.error(getClassName(), e, "Test Connection error");
                message = ResourceBundleUtil.getMessage("form.jdbcOptionsBinder.connectionFail") + "\n" + StringEscapeUtils.escapeJavaScript(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);
        }
    }

Once we done the properties option to collect input and the web service to test the connection, we can work on the main method of the plugin which is loadAjaxOptions method.

Code Block

language	java

    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();
            
            // Set retrieved result to Form Row Set
            while (rs.next()) {
                FormRow row = new FormRow();
                
                String value = rs.getString(1);
                String label = rs.getString(2);
                String grouping = rs.getString(3);
                
                row.setProperty(FormUtil.PROPERTY_VALUE, (value != null)?value:"");
                row.setProperty(FormUtil.PROPERTY_LABEL, (label != null)?label:"");
                if (grouping != null) {
                    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;
    }

c. Manage the dependency libraries of your plugin

Our plugin is using javax.servlet.http.HttpServletRequest and javax.servlet.http.HttpServletResponse class, so we will need to add jsp-api library to our POM file.

Code Block

language	xml

<!-- Change plugin specific dependencies here -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jsp-api</artifactId>
    <version>2.0</version>
</dependency>
<!-- End change plugin specific dependencies here -->

d. Make your plugin internationalization (i18n) ready

We are using i18n message key in getLabel and getDescription method. We also used i18n message key in our properties options definition as well. So, we will need to create a message resource bundle properties file for our plugin.

Create directory "resources/messages" under "jdbc_options_binder/src/main" directory. Then, create a "JdbcOptionsBinder.properties" file in the folder. In the properties file, let add all the message keys and its label as below.

Code Block

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.

e. Register your plugin to Felix Framework

We will have to register our plugin class in Activator class (Auto generated in the same class package) to tell Felix Framework that this is a plugin.

Code Block

language	java

    public void start(BundleContext context) {
        registrationList = new ArrayList<ServiceRegistration>();
        //Register plugin here
        registrationList.add(context.registerService(JdbcOptionsBinder.class.getName(), new JdbcOptionsBinder(), null));
    }

f. Build it and testing

8. Take a step further, share it or sell it

You can download the source code from .

To download the ready-to-use plugin jar, please find it in http://marketplace.joget.org/.

Space shortcuts

Page tree

Versions Compared

Old Version 3

New Version 4

Key

c. Manage the dependency libraries of your plugin

d. Make your plugin internationalization (i18n) ready

e. Register your plugin to Felix Framework

f. Build it and testing

8. Take a step further, share it or sell it