Report example-Implementing MongoDB join with Jasper reports

The diversity data source is a common problem in report development, but it is difficult to deal with report tools such as Jasperreport, such as showing the results of two MongoDB collection connections. Although the Jasperreport has virtual data source or table join, these features are only available in the commercial or high-end versions and are difficult to implement in the free version. And these features only support two data source connections, it is much more troublesome to implement multiple connections. In addition, these features are just graphical interfaces that do not have a structured, SQL-like calculation of connected data.

With a structured and strong computing engine, a diverse data source, and simple integration, the collector can assist reporting tools to easily implement such requirements. The following is an example of how the MONGODB join is implemented.

Sales and EMP are the fields in the two collection,sales in MongoDB Sellerid logically equivalent to the foreign key, the Eid field that points to the EMP, now need to query out the sales order by time period, and the EMP with the left connection, and finally show in the report. Some of the source data are as follows:

Collection Sales

Collectionemp

The Collector script:

A1=mongodb ("Mongo://localhost:27017/test?user=root&password=sa")

The code above is used to create a database connection for MongoDB, and user and password are available to specify the username and password.

The collector also supports using JDBC to connect MongoDB, as with normal databases, but because third-party JDBC is not only charged, but functionally inferior to the official library functions, such as the inability to obtain multiple layers of data, the collector encapsulates the native method directly, and MongoDB's functionality and syntax are preserved. For example, you can use the Find function on this basis,

a2=a1.find ("Sales", "{' $and ': [{' OrderDate ': {' $gte ': '" +string (begin) + '}},{' OrderDate ': {' $lte ': ' "+ String (end) + "'}}]}", "{_id:0}"). Fetch ()

The above code queries a time period record from MongoDB's salescollection. The first parameter of the function find is the collection name, the second parameter is the query condition, follows the MONGODB specification, and the third parameter qualifies the returned field. Note begin and end in the query condition are external parameters from the report, representing the start and end times of the OrderDate, respectively.

The function find returns a cursor and does not read the data directly into memory, so it supports large data volumes. You can continue to manipulate cursors with functions such as skip, sort, conj, and so on until a function fetch, groups, or statement for is encountered. This example reads the data into memory directly with the function fetch (), and if the time period is 2009-01-01 through 2009-12-31, then the A2 evaluates as follows:

a3=a1.find ("emp",, "{_id:0}"). Fetch ()

The above code takes the number from EMP collection, unconditionally, except for _id all fields, the result is as follows:

A4=a1.close ()

The code above is used to close the database connection in A1.

[email protected] (A2:sales,sellerid; A3:emp,eid)

The above code connects A2 and A3 to the left, and the connection field is A3 for A2 Sellerid and Eld, and for the sake of visualization, the two parts of the data are named Sales and EMP respectively. The function join performs a connection calculation, the option @1 represents a left connection, and evaluates to the left:

As you can see, some sellerid in sales cannot find the corresponding record in the EMP because it is a left connection. If you want to make a full connection, you can use the option @f, and the no option indicates an inner connection.

a6=a5.new (sales. Orderid:orderid,sales. Client:client,sales. Amount:amount,sales. Orderdate:orderdate,emp. Name:name,emp. Dept:dept,emp. Gender:gender)

A5 performs a join operation, A6 takes the required fields from the result of the connection and makes up the two-dimensional table with the function new. such as sales. Orderid:orderid indicates that the Sales.orderid field is removed from A5 and renamed to OrderID (field names such as Sales.orderid are not recognized by the report tools). The calculation results are as follows:

So far, the data needed for the report is all calculated. Finally, just use result A6 to return the two-dimensional table in A6 to the report tool. The collector provides a JDBC interface, and the report tool recognizes the collector as a normal database, and the integration scheme is referenced in the relevant documentation.

next Take Jasperreport as an example to design the report, the following table sample:

You need to define two report parameters Pbegin, Pend, respectively, corresponding to the two parameters in the collector. You can see the results of the report after previewing:

The report calls the collector the same way you call a stored procedure, such as saving this script as MONGODBJOIN.DFX, you can use mongodbjoin $P {pbegin} in the Jasperreport SQL designer, $P {pend} To invoke.

Report example-Implementing MongoDB join with Jasper reports