How to pass parameters to query?

by

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

More Related Contents:

  1. Pass multiple values in single parameter
  2. how to pass parameters to query in SQL (Excel)
  3. Can we pass parameters to a view in SQL?
  4. passing SQL “IN” parameter list in jasperreport
  5. How to Write SQL funtion
  6. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  7. Is it possible to insert multiple rows at a time in an SQLite database?
  8. SQL join: selecting the last records in a one-to-many relationship
  9. Create unique constraint with null columns
  10. Function vs. Stored Procedure in SQL Server
  11. Check if value exists in Postgres array
  12. SQL Query – Using Order By in UNION
  13. Does the order of where clauses matter in SQL?
  14. Access-SQL: Inner Join with multiple tables
  15. Insert Into… Merge… Select (SQL Server)
  16. update one table with data from another
  17. The parameterized query expects the parameter which was not supplied
  18. Generate Delete Statement From Foreign Key Relationships in SQL 2008?
  19. SQL Server converting varbinary to string
  20. SQL primary key: integer vs varchar
  21. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  22. Use ‘=’ or LIKE to compare strings in SQL?
  23. Join multiple tables with active records
  24. UNION the results of multiple stored procedures
  25. Why is 30 the default length for VARCHAR when using CAST?
  26. Oracle: SQL query to find all the triggers belonging to the tables?
  27. Can a COMMIT statement (in SQL) ever fail? How?
  28. “SELECT *” vs “SELECT ” for performance
  29. Count number of NULL values in each column in SQL
  30. Postgres INTERVAL using value from table

Leave a Comment