How to Properly Pass Parameters to JasperReports Queries Using $P{} and $P!{} Syntax?

JasperReports query parameter passing

In database queries, it is a common requirement to dynamically transfer parameters based on user input. Some users may encounter issues when passing parameters using the $P{} syntax.

JasperReports provides two syntaxes for referencing parameters: $P{} and $P!{}.

$P{paramName} Grammar

$P{paramName} is mainly used to set the input parameter value of the WHERE clause. The replacement algorithm for this syntax is similar to java.sql.PreparedStatement. It handles different parameter types intelligently:

• For java.lang.String type parameters, the engine replaces $P{parameterName} with a quoted value.
• For java.lang.Integer type parameters, the engine will replace $P{parameterName} with a numeric value.

Example:

参数名称	参数类型	参数值
eventName	java.lang.String	Olympic Games
eventType	java.lang.Integer	2

Original expression:

<code class="language-sql">SELECT startDate, endDate, rating FROM events WHERE name=$P{eventName} AND type=$P{eventType}</code>

Result:

<code class="language-sql">SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND type=2</code>

$P!{paramName} Grammar

The

$P!{paramName} syntax performs a simple substitution, typically used to replace the exact value of an argument.

Example:

参数名称	参数类型	参数值
tableName	java.lang.String	events
eventName	java.lang.String	Olympic Games
channel	java.lang.String	'BBC'
type	java.lang.String	sport

Original expression:

<code class="language-sql">SELECT startDate, endDate, rating FROM $P!{tableName} WHERE name='$P!{eventName}' AND channel=$P!{channel} AND type=$P!{type}</code>

Result:

<code class="language-sql">SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND channel='BBC' AND type=sport</code>

Apply to question

For the given query, the following expressions may apply:

• If $P{key} is java.lang.String type parameter:

<code class="language-sql">SELECT name, phone, email FROM company WHERE $P!{clause} = $P{key} ORDER BY $P!{order}</code>

• If $P{key} is a non-string type parameter:

<code class="language-sql">SELECT name, phone, email FROM company WHERE $P!{clause} = $P!{key} ORDER BY $P!{order}</code>

The above is the detailed content of How to Properly Pass Parameters to JasperReports Queries Using $P{} and $P!{} Syntax?. For more information, please follow other related articles on the PHP Chinese website!