Developers use jasperreport to query databases with Parameters

 

Preface

In the previous article, we introduced how to use jasperreport to generate a database query report. Today, we will make a further step on the basis of this report, that is, how to query the database with parameters.

Question

As in the previous article, the following steps are required to generate a report:

1. Introduce the jar package. See static text report.

2. Create a report template:

Because we need to query the database with parameters (the t_user data table is queried) this time, the report template needs to be changed. In fact, only the query statement is changed, others are the same as in the previous article (we use $ P {} to identify the parameter and $ f {} to identify the field ):

<? Xmlversion = "1.0" encoding = "UTF-8"?>

<Jasperreportxmlns = "http://jasperreports.sourceforge.net/jasperreports"

Xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"

Xsi: schemalocation = "http://jasperreports.sourceforge.net/jasperreportshttp://jasperreports.sourceforge.net/xsd/jasperreport.xsd"

Name = "parameterreport">

<Parametername = "Nm" class = "Java. Lang. String"/>

<Querystring>

<! [CDATA [select * From t_user t where T. Name = $ P {nm}]>

</Querystring>

<Fieldname = "ID" class = "Java. Lang. Integer"/>

<Fieldname = "name" class = "Java. Lang. String"/>

<Fieldname = "sex" class = "Java. Lang. String"/>

<Pageheader>

<Bandheight = "30">

<Statictext>

<Reportelementx = "0" Y = "0" width = "69" Height = "24"/>

<Textelementverticalalignment = "bottom"/>

<Text> id </text>

</Statictext>

<Statictext>

<Reportelementx = "140" Y = "0" width = "79" Height = "24"/>

<Text> name </text>

</Statictext>

<Statictext>

<Reportelementx = "280" Y = "0" width = "69" Height = "24"/>

<Text> sex </text>

</Statictext>

</Band>

</Pageheader>

<Detail>

<Bandheight = "30">

<Textfield>

<Reportelementx = "0" Y = "0" width = "69" Height = "24"/>

<Textfieldexpressionclass = "Java. Lang. Integer">

$ F {ID}

</Textfieldexpression>

</Textfield>

<Textfield>

<Reportelementx = "140" Y = "0" width = "69" Height = "24"/>

<Textfieldexpressionclass = "Java. Lang. String">

$ F {name}

</Textfieldexpression>

</Textfield>

<Textfield>

<Reportelementx = "280" Y = "0" width = "69" Height = "24"/>

<Textfieldexpressionclass = "Java. Lang. String">

$ F {sex}

</Textfieldexpression>

</Textfield>

</Band>

</Detail>

</Jasperreport>

3. compile the report template. See static text report.

4. Compile servlet:

Because we need to transmit parameters, we need to establish a database connection in the servlet and transmit parameters to the Report Template:

Packagecom. Dan. servlet;

 

Importjava. Io. ioexception;

Importjava. Io. inputstream;

Importjava. Io. printwriter;

Importjava. Io. stringwriter;

Importjava. SQL. connection;

Importjava. SQL. drivermanager;

Importjava. util. hashmap;

 

Importjavax. servlet. servletexception;

Importjavax. servlet. servletoutputstream;

Importjavax. servlet. http. httpservlet;

Importjavax. servlet. http. httpservletrequest;

Importjavax. servlet. http. httpservletresponse;

 

Importnet. SF. jasperreports. Engine. jasperrunmanager;

 

/**

* Query databases based on parameters

* @ Author zdd

*

*/

Publicclass dbreportparamservlet extends httpservlet {

 

@ Override

Protectedvoid doget (httpservletrequest req, httpservletresponse resp)

Throwsservletexception, ioexception {

Connectionconnection;

Resp. setcontenttype ("application/pdf ");

 

Servletoutputstreamservletoutputstream = resp. getoutputstream ();

 

Inputstreamreportstream = getservletconfig (). getservletcontext (). getresourceasstream ("/WEB-INF/classes/reports/parameterreport. Jasper ");

 

Hashmap parametermap = new hashmap ();

Parametermap. Put ("Nm", new string ("name1 "));

Try {

Class. forname ("oracle. JDBC. Driver. oracledriver ");

Connection = drivermanager. getconnection ("JDBC: oracle: thin: @ 192.168.24.36: 1521: testreport", "test", "test ");

Jasperrunmanager. runreporttopdfstream (reportstream, servletoutputstream, parametermap, connection );

Connection. Close ();

Servletoutputstream. Flush ();

Servletoutputstream. Close ();

} Catch (effectione ){

Stringwriterstringwriter = new stringwriter ();

Printwriterprintwriter = new printwriter (stringwriter );

E. printstacktrace (printwriter );

Resp. setcontenttype ("text/plain ");

Resp. getoutputstream (). Print (stringwriter. tostring ());

}

}

 

}

5. configuring web. xml means configuring servlet.

6. Run the project

Let's take a look at my running results:

 

 

Summary:

In my "using jasperreport for developers" series, I will show you how to simply use jasperreport, from the initial report generation to subsequent interaction with the database, then, we will introduce different forms of data source generation reports.