JDBC Datalist Binder Wrapper

Introduction

JDBC Datalist Binder Wrapper plugin contains 2 derivatives based on JDBC Datalist Binder. The JDBC Datalist Binder is known as Database SQL Query in Joget DX 9.

• JdbcDataListBinderNoAlias - JDBC Datalist Database Binder No Wrapper
• JdbcDataListBinderWithoutCount - JDBC Datalist Database Binder Without Count

This plugin extends the JDBC Datalist Binder with the following modifications:-

• SQL SELECT Query with:
  • Run Query(s) without wrapper - Option to not wrap the query. In some database servers, a wrapped query would incur significant execution time. When defining the SELECT query, your query must contain an alias tmp0 for filters to work.
  • Fetch Size - In some database servers + JDBC drivers, allow the designer to define the fetch size property to optimize the network delivery between the Joget app server and the database server.
  • Cache Count Duration (seconds) - Defined in seconds to cache the dataset based on the MD5 of the SQL query sent to the database server.
• Defining columns:
  • Ability to define the available columns by grid or script (BeanShell). In some database servers, fetching the table metadata incurs significant execution time. This option is included to reduce such overhead by defining the available columns upfront.
• SQL SELECT Count Query:
  • Allows custom definition of the COUNT query for performance optimization. Your query must contain an alias tmp0 for filters to work.
  • By default, Joget would wrap the SQL SELECT Query with SELECT count(*) FROM ( original ) as tmp0 to obtain the count. There are 2 counts to be obtained. 1 - Total count without filters, 2 - Total count with filters. In some database servers, this incurs significant execution time due to the wrapping.
  • When defining the query, you may use #FILTERS# to define the placeholder where the additional filters will be placed. By default, additional filters will be appended to the end of the query.
  • The count result will be cached if Cache Count Duration (seconds) is enabled.

Similar to the original plugin but with getDataTotalRowCount() method hardcoded to return an arbitrary number of 100000000. The intent of this plugin is used to compare performance impact when additional queries to obtain count are executed.

This plugin can be used in places where:

• The number of rows is so large that it is no longer meaningful to get the precise count
• Or in listings where the count is not needed for generating pagination links. There could be UI/UX treatment (e.g. lazy load) where next page content is loaded upon scrolling down therefore do not require the use of pagination links, hence the count is no longer useful.

Plugin Info

Plugin Available in the Bundle:

1. JDBC Datalist Database Binder No Wrapper
2. JDBC Datalist Database Binder Without Count

This plugin bundle is compatible with Joget DX 9.

Expected Outcome

Better performance, especially when handling larger lists with the JDBC Datalist Binder Wrapper.

Get started

Where to get the plugin

You can download this plugin from the Joget Marketplace.

How to install

1. Click Download on this plugin screen (It will be a .jar file).
2. Go to your Joget localhost or server. In the Joget Console, go to Admin Bar > System Settings > Manage Plugins and click the Upload Plugins button.
3. Under Upload Plugin, select the plugin .jar file you downloaded and click Upload.
4. Depending on the plugin type, you can now view your new plugin in your form, List, or UI Builder.
5. Remember to uninstall the old plugin before uploading a new version.
6. The Joget Knowledge Base has more information on managing and developing plugins.

How to use it

1. Download the demo app from JDBC Datalist Binder Wrapper Sample App.
2. Go to Joget Home or All Apps and click on Import App to import the downloaded demo app.
3. At the List Builder, there are two lists. The first list (List - Form) is configured with JDBC Datalist Database Binder No Wrapper source of data. Refer to Configure JDBC Datalist Binder No Wrapper to configure.
   
4. The second list (List - Form 2) is configured with JDBC Datalist Database Binder Without Count. Refer to Configure JDBC Datalist Binder Without Count to configure.
5. Run the UI and check the glowroot at http://server-url:4000/, and monitor the performance of the default JDBC Datalist Database Binder with this plugin.

JDBC Datalist Binder Wrapper Properties

Configure JDBC Datalist Binder No Wrapper

• Datasource:
  • Custom Datasource: Set up to connect to an external database; has additional configuration.
  • Default Datasource: Connect to the Joget database. By selecting this, the database your Joget is currently using will be selected; the custom JDBC settings below will be ignored.
• Custom JDBC Driver: Custom JDBC Driver. This field is required when Custom Datasource is selected in Datasource above. Example: com.mysql.jdbc.Driver
• Custom JDBC URL: Custom JDBC URL. This field is required when Custom Datasource is selected in Datasource above. Example: jdbc:mysql://localhost/jwdb?characterEncoding=UTF8&useSSL=false
• Custom JDBC Username: Custom JDBC Username. This field is required when Custom Datasource is selected in Datasource above.
• Custom JDBC Password: Custom JDBC Password. This field is required when Custom Datasource is selected in Datasource above.
  
  Test the connection parameters 
  Click on the "Test Connection" button at the bottom of the page to quickly test out your configurations.
  
• SQL SELECT Query: The SQL query specific to your database type (MySQL, MSSQL, Oracle, etc.). Use a question mark (?) in your query to represent the primary key or foreign key. Alias the source as tmp0 and use #FILTERS# to choose where filter conditions go.

  Example

  SELECT
     *
  FROM
     app_fd_table
  WHERE
     id = ?

  If a column name contains reserved keywords, do ensure it is encapsulated properly.

  For example, for MySQL, if the column identifier itself contains a dot symbol ( . ), it should be encapsulated like this:

  SELECT `myAppName.myColumn` FROM app_fd_myTable;

  Table & Column Naming
  For database tables created by Joget Forms, Joget adds a "c_" in front of table column names (or "t_" if your column name starts with a number) and "app_fd_" in front of database table names.
  If you use environment hash variables to store SQL query strings, use "?noescape" to escape SQL query strings in JDBC binders to prevent the "<>" "not equal" operator from being converted, i.e., disables XSS prevention checking. Read here for more information.
• Column Setup: Define the list columns so Joget doesn't have to read the table's column metadata. Options include:
  • Script
  • Grid
• Columns (BeanShell Script): If Column Setup is Script, this field can be configured by referring to the BeanShell List Data Store. Script that returns a DataListColumn[]. If it returns nothing, the columns come from the query.
• Columns: If Column Setup is Grid, this grid can be configured by:
  • Column ID: Database column.
  • Column Name: Column label.
• Primary Key: Column that uniquely identifies each row.
• Run Query(s) without wrapper: Run your queries as written instead of wrapping them in SELECT * FROM (…) tmp0. Faster on some databases.
• Fetch Size: Rows fetched from the database per round trip.
• SQL SELECT Count Query: If Run Query(s) without wrapper is checked, this field can be configured as a custom row-count query. Leave empty to skip counting; the list then reports 100,000,000 rows.
• Cache Count Duration (seconds): Duration to cache the row count. Leave empty or set to 0 to disable caching.
• Debug: When checked, SQL queries will be logged in the Joget log to monitor the exact cost of each query.
• Optimize query for paging: Add syntax to the query to optimize performance for large dataset paging. Only works for MySQL and Microsoft SQL Server 2012 onwards.
  

Configure JDBC Datalist Binder Without Count

Configure Database SQL Query

• Datasource: 
  • Custom Datasource: Setup to connect to an external database, has additional configuration.
  • Default Datasource: connect to the Joget database. By selecting this, the database your Joget is currently using will be selected; the custom JDBC settings below will be ignored.
• Custom JDBC Driver: Custom JDBC Driver. This field is required when Custom Datasource is selected in Datasource above. Example: com.mysql.jdbc.Driver
• Custom JDBC URL: Custom JDBC URL. This field is required when Custom Datasource is selected in Datasource above. Example: jdbc:mysql://localhost/jwdb?characterEncoding=UTF8&useSSL=false
• Custom JDBC Username: Custom JDBC Username. This field is required when Custom Datasource is selected in Datasource above.
• Custom JDBC Password: Custom JDBC Password. This field is required when Custom Datasource is selected in Datasource above.
  
  Test the connection parameters 
  Click on the "Test Connection" button at the bottom of the page to quickly test out your configurations.
• SQL SELECT Query: Query that returns the list of rows. Joget wraps it as SELECT * FROM (…) tmp0 and adds filters as a WHERE on the outer query.
• Primary Key: Column that uniquely identifies each row.
• Optimize query for paging: Add paging syntax to the query for faster paging on large datasets. MySQL and SQL Server 2012+ only.

Caching

• Cache Row Count: Use stale-while-revalidate caching if another request is made within the TTL.