How to Pass Parameters to WHERE and ORDER BY Clauses in JasperReports Queries?

When building queries in JasperReports, you may need to pass dynamic parameters from the user. For this purpose, there are two syntax expressions for parameter references: $P{} and $P!{}.

$P{} Grammar

The

$P{} syntax is mainly used to set input parameter values ​​in the WHERE clause. It uses a "smart" replacement algorithm similar to java.sql.PreparedStatement. For example, java.lang.String parameters will be replaced with quoted values, and java.lang.Integer parameters will be replaced with numeric values.

For example, consider the following parameters:

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

Original expression (to be replaced):

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

The result will be:

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

$P!{} Grammar

The

$P!{} syntax is mainly used for simple substitutions. It replaces parameter values ​​verbatim, without any special treatment.

For example, consider the following parameters:

参数名称	参数类型	参数值
tableName	java.lang.String	events
eventName	java.lang.String	Olympic Games

Original expression (to be replaced):

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

The result will be:

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

Usage in your case

According to your question, you need to set WHERE and ORDER BY clause dynamically, you can use the following expression:

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

where $P{key} is a java.lang.String parameter. If the $P!{clause} value requires a quoted value, you can use the following expression instead:

<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 Pass Parameters to WHERE and ORDER BY Clauses in JasperReports Queries?. For more information, please follow other related articles on the PHP Chinese website!