How to pass parameters to query?

Question

I'm trying to write a query for my application, but I'm having some troubles. I need to pass as a parameter one field of my database, like:

SELECT name, phone, email 
FROM company 
WHERE $P{clause} = $P{key}
ORDER BY $P{order}

Because WHERE clause and ORDER BY clause are dynamic for the user to select.

Using $P{} it didn't work.

Answer 1

There are two syntax expressions for parameter referencing in JasperReports: $P{} and $P!{}.

• $P{paramName} syntax is using mostly for setting WHERE input parameters values. The replacement algorithm is "smart", its implementation uses java.sql.PreparedStatement: for java.lang.String parameter the engine will replace $P{parameterName} with quoted value, for java.lang.Integer - with numeric value and so on.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|-------------------|:---------------:|
|   eventName    | java.lang.String  |  Olympic Games  |
|   eventType    | java.lang.Integer |       2         |

Original expression (to replace):

SELECT startDate, endDate, rating FROM events WHERE name=$P{eventName} AND type=$P{eventType} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND type=2 

• $P!{paramName} syntax is using mostly for doing the "simple" replace.

The sample:

| Parameter name |  Parameter type   | Parameter value |
|:---------------|------------------:|:---------------:|
|   tableName    | java.lang.String  |     events      |
|   eventName    | java.lang.String  |  Olympic Games  |
|   channel      | java.lang.String  |     'BBC'       |
|   type         | java.lang.String  |     sport       |

Original expression (to replace):

SELECT startDate, endDate, rating FROM $P!{tableName} WHERE name='$P!{eventName}' AND channel=$P!{channel} AND type=$P!{type} 

The result will be:

SELECT startDate, endDate, rating FROM events WHERE name='Olympic Games' AND channel='BBC' AND type=sport

---

For more information you can read this Using report parameters post and look at this Query sample.

---

In your case the right expression may be like this:

SELECT name, phone, email FROM company WHERE $P!{clause} = $P{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

or like this (it depends on $P!{clause} value)

SELECT name, phone, email FROM company WHERE $P!{clause} = $P!{key} ORDER BY $P!{order}

where $P{key} is a java.lang.String parameter

Answer 2

if you need to order by parameter try this:

SELECT name, phone, email ,(case when $P{order} = 'name' then name when $P{order} = 'phone' then phone else email end) as orderlist
FROM company 
WHERE $P{clause} = $P{key}
ORDER BY orderlist