bloggers bloggers

Marco Napolitano
Messaggi: 79
Stelle: 0
Data: 17/02/22
Jader Jed Francia
Messaggi: 63
Stelle: 0
Data: 18/02/21
Paolo Gambetti
Messaggi: 2
Stelle: 0
Data: 11/11/19
Katia Pazzi
Messaggi: 1
Stelle: 0
Data: 27/06/19
Ezio Lombardi
Messaggi: 11
Stelle: 0
Data: 10/04/18
Chiara Mambretti
Messaggi: 25
Stelle: 0
Data: 27/02/17
Serena Traversi
Messaggi: 3
Stelle: 0
Data: 21/07/16
Francesco Falanga
Messaggi: 8
Stelle: 0
Data: 14/06/16
Antonio Musarra
Messaggi: 2
Stelle: 0
Data: 18/11/13
Simone Celli Marchi
Messaggi: 6
Stelle: 0
Data: 09/07/13
Indietro

Clausola IN e NOT IN in jreport

Se volete passare a un report una lista di valori sui quali eseguire una query con le clausole IN o NOT IN, jasper report mette a disposizione questo operatore:

$X{functionName,param1,param2,...}

In pratica si usa cosi':

Nella query del report mettete questo:

SELECT * from tabella
WHERE 1 = 1
AND $X{IN, id, ticketList}


Dove:

IN e' la clausola che vogliamo mappare; e' un valore costante, puo' assumere il valore 'IN' o 'NOTIN'

id e' la colonna del database sulla quale eseguire la condizione

ticketList e' un parametro (quelli che di solito si usano con l'operatore $P{ticketList}) passato esternamente usando la hashmap che si passa al report di tipo java.util.Collection
Nel mio caso, era un ArrayList<String>

Ponendo ticketList = a "abc", "cde", "fgh", la query di sopra verra' interpretata dal report cosi':

SELECT * FROM TABELLA WHERE 1 = 1 AND ID IN ("abc", "cde", "fgh")

Di seguito l'estratto preso dal libro di jasper report:

$X{functionName,param1,param2,...} Syntax
There are also cases when apart of the query needs to be dynamically built starting from a
report parameter value, with the query part containing both query text and bind parameters.
This is the case, for instance, with IN and NOT IN query clauses that need to use a collection
report parameter as a list of values.
Such complex query clauses are introduced into the query using the $X{} syntax. The
general form of a $X{} clause is $X{functionName, param1, param2,...}.

JasperReports has built-in support for two clause functions: IN and NOTIN.

Both functions
expect two parameters:

  • The SQL column or expression to be used as the left side in the IN/NOT IN clause.
  • The name of the report parameter that will supply the values list. The value of this

parameter can either be a java.util.Collectioninstance or an object or primitive
Java array.
For instance, if a report receives as a parameter a list of countries and needs to filter
orders based on this list, you would write a query of the following form:

<parameter name="CountryList" class="java.util.List"/>
<queryString><![CDATA[
SELECT * FROM Orders WHERE $X{IN, ShipCountry, CountryList}
]]></queryString>


Before the query is executed, $X{IN, <column>, <param>} and $X{NOTIN, <column>,
<param>}
expand to the following:

  • <column> IN/NOT IN (?, ?, ..) when the parameter value is neither null nor empty. Each component in the collection/array generates a bind parameter; the type of the bind parameters is decided based on the runtime value of the collection/array component.
  • A true clause (0 = 0) whenthe parameter value is null or empty.

New clause functions (in addition to the built-in IN and NOTIN) can be added by imple-

menting net.sf.jasperreports.engine.query.JRClauseFunction and by extending the query executer to register the new functions.

Some of the provided samples, such as jasper, subreport, scriptlet, and query, use inter-

nal SQL queries to retrieve data. The most interesting sample illustrating this is in the query

sample found in the /demo/samples/querydirectory of the project’s distribution.

Precedente
Commenti
Nessun commento. Vuoi essere il primo.