วิธีการพัฒนา JDBC Form Load Binder

- Home
  - joget
    - plugins
    - jw-community
      -5.0.0

cd joget/plugins/
~/joget/jw-community/5.0.0/wflow-plugin-archetype/create-plugin.sh org.joget.tutorial jdbc_load_binder 5.0.0

Define value for property 'version':  1.0-SNAPSHOT: : 5.0.0
[INFO] Using property: package = org.joget.tutorial
Confirm properties configuration:
groupId: org.joget.tutorial
artifactId: jdbc_load_binder
version: 5.0.0
package: org.joget.tutorial
Y: : y

[{
    title : '@@form.jdbcLoadBinder.config@@',
    properties : [{
        name : 'jdbcDatasource',
        label : '@@form.jdbcLoadBinder.datasource@@',
        type : 'selectbox',
        options : [{
            value : 'custom',
            label : '@@form.jdbcLoadBinder.customDatasource@@'
        },{
            value : 'default',
            label : '@@form.jdbcLoadBinder.defaultDatasource@@'
        }],
        value : 'default'
    },{
        name : 'jdbcDriver',
        label : '@@form.jdbcLoadBinder.driver@@',
        description : '@@form.jdbcLoadBinder.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.jdbcLoadBinder.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.jdbcLoadBinder.username@@',
        type : 'textfield',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        value : 'root',
        required : 'true'
    },{
        name : 'jdbcPassword',
        label : '@@form.jdbcLoadBinder.password@@',
        type : 'password',
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false',
        value : ''
    },{
        name : 'sql',
        label : '@@form.jdbcLoadBinder.sql@@',
        description : '@@form.jdbcLoadBinder.sql.desc@@',
        type : 'codeeditor',
        mode : 'sql',
        required : 'true'
    }],
    buttons : [{
        name : 'testConnection',    
        label : '@@form.jdbcLoadBinder.testConnection@@',
        ajax_url : '[CONTEXT_PATH]/web/json/app[APP_PATH]/plugin/org.joget.tutorial.JdbcLoadBinder/service?action=testConnection',
        fields : ['jdbcDriver', 'jdbcUrl', 'jdbcUser', 'jdbcPassword'],
        control_field: 'jdbcDatasource',
        control_value: 'custom',
        control_use_regex: 'false'
    }]
}]

    public FormRowSet load(Element element, String primaryKey, FormData formData) {
        FormRowSet rows = new FormRowSet();
        rows.setMultiRow(true);
        //Check the sql. If require primary key and primary key is null, return empty result.
        String sql = getPropertyString("sql");
        if ((primaryKey == null || primaryKey.isEmpty()) && sql.contains("?")) {
            return rows;
        }
        
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            DataSource ds = createDataSource();
            con = ds.getConnection();
            
            pstmt = con.prepareStatement(sql);
            
            //set query parameters
            if (sql.contains("?") && primaryKey != null && !primaryKey.isEmpty()) {
                pstmt.setObject(1, primaryKey);
            }
            
            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();
                
                //get the name of each column as field id 
                for (int i = 1; i <= columnsNumber; i++) {
                    String name = rsmd.getColumnName(i);
                    String value = rs.getString(name);
                    
                    if (FormUtil.PROPERTY_ID.equals(name)) {
                        row.setId(value);
                    } else {
                        row.setProperty(name, (value != null)?value:"");
                    }
                }
                
                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;
    }

<!-- Change plugin specific dependencies here -->
<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>
<!-- End change plugin specific dependencies here -->

org.joget.tutorial.JdbcLoadBinder.pluginLabel=JDBC Binder
org.joget.tutorial.JdbcLoadBinder.pluginDesc=Used to load form data using JDBC
form.jdbcLoadBinder.config=Configure JDBC Binder
form.jdbcLoadBinder.datasource=Datasource
form.jdbcLoadBinder.customDatasource=Custom Datasource
form.jdbcLoadBinder.defaultDatasource=Default Datasource
form.jdbcLoadBinder.driver=Custom JDBC Driver
form.jdbcLoadBinder.driver.desc=Eg. com.mysql.jdbc.Driver (MySQL), oracle.jdbc.driver.OracleDriver (Oracle), com.microsoft.sqlserver.jdbc.SQLServerDriver (Microsoft SQL Server)
form.jdbcLoadBinder.url=Custom JDBC URL
form.jdbcLoadBinder.username=Custom JDBC Username
form.jdbcLoadBinder.password=Custom JDBC Password
form.jdbcLoadBinder.sql=SQL SELECT Query
form.jdbcLoadBinder.sql.desc=Use question mark (?) in your query to represent primary key or foreign key
form.jdbcLoadBinder.testConnection=Test Connection
form.jdbcLoadBinder.connectionOk=Database connected
form.jdbcLoadBinder.connectionFail=Not able to establish connection.

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

 select * from dir_user where username = ?

select * from dir_group g 
join dir_user_group ug on ug.groupId = g.id
where ug.userId = ?