如何在Jaspersoft中使用动态参数查询Domain

Hey there! Let's tackle your questions one by one—you're definitely on the right track with using Domain for cached data access, so let's clear up the confusion around SQL execution and how to leverage the cached Domain views properly.

Is using Domain the right approach? #

Absolutely! Domain is Jaspersoft's go-to tool for building a semantic data layer that abstracts raw database tables into business-friendly views, and it's designed specifically to reduce database load via caching. Your goal of cutting down DB access by caching data aligns perfectly with what Domain was built for—so your direction is spot-on.

The catch here is that Domain doesn't let you run raw SQL directly against its objects because it's meant to encapsulate the underlying SQL logic into a reusable, visual model. Instead, you'll work with the pre-built views and parameters within the Domain layer, which still lets you pass dynamic values and leverage cached data.

How to query cached Domain views (instead of hitting the database directly) #

Here's a step-by-step breakdown to make this work for your template-based reports with dynamic parameters:

1. Configure Domain caching properly #

First, make sure your Domain is set up to cache data:

• Open your Domain in the Jaspersoft Domain Designer.
• Navigate to the Data Source section, then find the Cache Settings (location might vary slightly by Jaspersoft version).
• Enable caching, then set your preferred rules:
  • Choose a cache expiration time (e.g., 1 hour for semi-static data, 5 minutes for frequently updated data).
  • Set the cache scope (global for all users, or user-specific if data is personalized).
• Save the Domain—now it will store query results in cache after the first run, and subsequent requests will pull from cache instead of the database.

2. Build your report using Domain as the data source #

Forget writing raw SQL for the report—use the Domain's semantic model instead:

• When creating a new report, select Domain as your data source (not JDBC).
• In the report designer, drag and drop fields from your Domain's pre-built views onto your report template. This uses the Domain's encapsulated logic to fetch data.

3. Add dynamic parameters to your Domain and report #

To pass dynamic values without writing SQL:

• In the Domain Designer, go to the Parameters tab.
• Create a new parameter (e.g., startDate, regionId) and map it to a filter condition on your Domain's view (e.g., filter orders where order_date >= $P{startDate}).
• Save the Domain, then in your report, reference these Domain parameters. When you run the report, you'll be able to input dynamic values, and the Domain will handle parameterized queries—while still using cached data if the parameter combination has been requested before.

4. For custom SQL needs (if you really need it) #

If you must use custom SQL logic but still want to leverage caching, try this workaround:

• Create a database view or stored procedure that implements your custom SQL.
• Add this view/procedure to your Domain as a new entity.
• Build your report using this Domain entity, and configure caching as described earlier. This way, you get your custom logic while still benefiting from Domain's caching layer.

Final Notes #

The key shift here is moving from writing raw SQL to working with Domain's semantic model. Once you get comfortable with that, you'll be able to build dynamic, template-based reports that pull from cached data instead of hitting the database every time.

内容的提问来源于stack exchange，提问作者Chetan Shirke