Shulou(Shulou.com)06/03 Report--

When using Jasper report tools to make reports, we often encounter the situation that the data comes from multiple data sources. The usual practice is to use the main subreport or javabean as the data source. Using the master subreport usually increases the complexity of the report design, while using javabean as the data source requires a javabean class to support it, and in order to see the data when designing the report, it also provides a static method for ireport to return a result set that defines javabean above.

Obviously, neither of the above methods is very convenient, and this article will provide an easier way to solve the problem of multiple data sources in ireport through aggregators, and further improve the performance of ireport. We will take the JasperReport5.6.0 development environment as an example.

How to use multiple data sources in ireport

In report projects, it often occurs that the report source data comes from different databases, for example, the database load of the same application system is so heavy that it has to be divided into multiple databases, just like the most common sales system data is divided into current database and history database, part of the data is stored in the database, part of the data is stored in files, and so on.

In terms of the database type of multiple data sources, the report tool may connect to the same type of database, such as mysql or oracle;, or different types, such as txt, csv, or Excel.

In our example, the report data comes partly from the mysql database and partly from the text file.

The employee table of the mysql database stores data with an EID of 1-100000, as follows:

Data with an EID of 100001-101000 is stored in the data.txt file, which is as follows:

Our task is to make a report in ireport and view all the data after the merge of the employee table and the emp.txt file. This requirement can be easily achieved with the help of an aggregator with ireport. The aggregator uses what we call structured processing language (Structured Process Language, referred to as SPL). The specific SPL code is as follows:

A1=connect ("mysql") 2=A1.query@x ("select * from employee") 3=file ("F:\\ files\\ emp.txt") .import@t () 4 = [A2Magazine A3] .conj () 5return A4

A1: create a data source connection to connect to mysql data sources.

A2: query the data in the employee table in the mysql data source and return the query results.

A3: read the contents of the file emp.txt.

A4: merge A2 and A3 data.

A4: returns the merged results of A4 to the report.

In order to present the calculation results in the report, we need to save the above SPL code as a file employee.dfx, and then we can call the script using the JDBC interface provided by the aggregator.

The way to introduce an aggregator script in a report tool by establishing a JDBC data source is the same as calling a stored procedure, which can be called with call employee () in Jasper's SQL designer. The specific steps are described in detail in the article "JasperReport calls SPL script".

Then, we can design the simplest report employee.jrxml in ireport, with the following template:

After the preview, you can see the report results directly:

Obviously, this process is much simpler than the traditional master subreport or javabean method, and more importantly, the computing logic is very clear and the integration method has almost no learning cost.

Performance optimization

After addressing the basic functional requirements, we can further focus on performance. In report projects, it is often necessary to join multiple tables to queries, and these joined tables may contain large amounts of data. For example, connect the employee table and the order table through a common field employee number to view the information of the salesperson for some orders. Obviously, the order table will grow over time, eventually putting a heavy burden on the system.

The report data in the following example comes partly from the employee table of the mysql database and partly from the sales table of the mysql database.

Where the employee table stores employee data with an EID of 1-3000000, as follows:

On the other hand, the order data sales table stores 760000 pieces of data and continues to increase. Examples of data content are as follows:

In order to implement the join query, we design the simplest report mysql_join.jrxml in ireport, with the following template:

First of all, let's take a look at the performance of traditional practices. We need to query the sales data generated by employees whose EID is less than 1000001 before 2015-04-01. The SQL statement is as follows:

Select sales.OID,sales.Date,sales.EID,sales.Amount,employee.Name from sales join employee on sales.EID=employee.EID where sales.Date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.