Database Wizard List Data Store

The Database Wizard List Data Store is a feature introduced in Joget DX 8, allowing users to integrate external databases as a data source in the Datalist Builder. This tool eliminates the need to write SQL code, enabling users to configure database connections, queries, and updates visually.

Configure database Wizard

To set up an external directory, go to Data, then select Data Store. 

Fields to Configure:

• Select Source of Data (Data Store): Choose Database Wizard.
  Ensure your Joget environment is configured to use LDAP/AD in the Directory Manager Settings.
  
• Datasource
  • Custom Datasource: Setup to connect to an external database. Additional configuration is required.
  • Default Datasource: Connects to the Joget database. By selecting this, the database your Joget is currently using will be selected.
• Custom JDBC Driver: JDBC driver name (e.g., com.mysql.jdbc.Driver for MySQL).
  
  This is only applicable to the Custom Datasource option.
• Custom JDBC URL: Database connection URL (e.g., jdbc:mysql://localhost:3306/jwdb).
  This is only applicable to the Custom Datasource option.
• Custom JDBC Username: Database username (e.g., root).
  This is only applicable to the Custom Datasource option.
• Custom JDBC Password: Specified database user's password.
  This is only applicable to the Custom Datasource option.
  Click the Test Connection button at the bottom of the page to quickly test your configurations.
• Table: Choose a table from the database as a source to retrieve data.
• Joins Form Data Table
  • Join Type: Condition type to fulfill.
  • Table: Target table to join with.
  • Column: Field ID from the source form to set as the option label.
  • Join Column: Field ID from the source form to set as the option grouping value.
• Primary Key
  • Define the primary key column. By default, it should be id.

Advanced

The configuration includes three main sections: Filter, Aggregate Query, and Expression Columns.

Filter

Filters in the Database Wizard List Data Store allow users to define specific conditions for data retrieval, ensuring only relevant data is fetched. These filters can be configured using various operators and conditions to match specific data requirements.

Fields to Configure:

• Filter Conditions: Define filter conditions for the data set.
  • Join Type:
    • And
    • Or
  • Column: Table Column ID.
  • Operator:
    • Equal
    • Not Equal
    • Greater Than
    • Greater Than Or Equal
    • Less Than
    • Less Than Or Equal
    • Like
    • Not Like
    • In
    • Not In
    • Is True
    • Is False
    • Is Null
    • Is Not Null
  • Value: Filter value.
• Extra Conditions: Additional conditions for filtering the data set. HQL is expected here.

Aggregate Query

Aggregate queries allow users to perform calculations on a set of values to return a single value. This feature helps in summarizing and analyzing data efficiently by grouping and aggregating data based on specific criteria.

Fields to Configure:

• Group By: Add a grouping clause/function to the eventual data set.
• Aggregate Fields: Select fields to aggregate.
  • Count
  • Count Distinct
  • Sum
  • Min
  • Max
  • Avg
• Having Conditions: Specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. For more information, see http://www.dofactory.com/sql/having.

Expression Columns

Expression columns allow users to add columns to the data set using Hibernate Query Language (HQL). This is useful for performing computations on multiple columns and creating custom fields based on existing data.

Field to Configure:

• Expression Columns: An additional column can be added using Hibernate Query Language (HQL). This is especially useful when performing additional computation on multiple columns.

By leveraging the Database Wizard List Data Store, users can efficiently configure external databases as data sources for their Joget applications, making data integration and management seamless and intuitive.