...
| Code Block | ||
|---|---|---|
| ||
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);
String grouping = rs.getString(3);
row.setProperty(FormUtil.PROPERTY_VALUE, (value != null)?value:"");
row.setProperty(FormUtil.PROPERTY_LABEL, (label != null)?label:"");
if
(grouping != null) {
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;
} |
...
| Code Block | ||
|---|---|---|
| ||
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
Let build our plugin. Once the building process is done, we will found a "jdbc_options_binder-5.0.0.jar" file is created under "jdbc_options_binder/target" directory.
Then, let upload the plugin jar to Manage Plugins. After upload the jar file, double check the plugin is uploaded and activated correctly.
Then, let us create an AJAX Cascading Drop-Down List in a form to test it. Let create our test form as following.
Then, configure our select box and JDBC binder.
In the query, we will use the following query to get user list based on group id.
| Code Block | ||
|---|---|---|
| ||
select distinct username, firstName, groupId from dir_user u
join dir_user_group g on u.username=g.userId
where groupId in (?) group by username; |
Configure the dependency to "group". Then, test the result.
The user select box options changed based on the selected values of group select box.
Now, let change the query to following to test Cascading Drop-Down List without using AJAX.
| Code Block | ||
|---|---|---|
| ||
select distinct username, firstName, groupId from dir_user u
join dir_user_group g on u.username=g.userId
group by username; |
Remember to un-tick the "Use AJAX for cascade options?" option to make it not using AJAX.
Yes, it work as well.
8. Take a step further, share it or sell it
You can download the source code from . jdbc_options_binder.zip.
To download the ready-to-use plugin jar, please find it in http://marketplace.joget.org/.
...