Example of using Jasper report to make MongoDB join MySQL

Diversity and multi-data source problems using reporting tools such as jasperreport are not easy to handle, such as displaying MongoDB and MySQL hybrid operations. Although Jasperreport/birt has features such as virtual data source or table join, but only in the commercial or high-end version, it is very difficult to implement in the free version, and the function has a large limitation, it is impossible to make a structured calculation of SQL-like structure for the 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 MongoDB join MySQL is implemented.

EMP1 is the collection,cities of the TABLE,EMP1 in MySQL is the field Cityid logically equivalent to the foreign key, the cities field that points to Cityid, Cities has both Cityid and CityName fields. You now need to query out the employees in EMP1 by time period and display Cityid as CityName. Some of the source data are as follows:

Collectionemp1

Tablecities

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 functions are not as good as the official library functions, such as the inability to get 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 ("Emp1", "{' $and ': [{' Birthday ': {' $gte ': '" +string (begin) + '}},{' Birthday ': {' $lte ': ' "+string (end) +" '}}] } "," {_id:0} "). Fetch ()

The above code queries a time period record from MongoDB's emp1collection. 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 birthday, 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 1976-01-01 through 1988-12-31, then the A2 evaluates as follows:

A3=a1.close ()

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

A4=mydb1.query ("Select * From cities")

The code above executes SQL, taking the number from the MySQL data source. Where myDB1 is the data source name, the configuration interface is as follows:

As you can see, the data source here uses the JDBC connection to support any database. The JDBC data source can be automatically connected/closed or manually connected/closed like MongoDB, where the former is used.

The function query uses the SQL statement to retrieve the query with the following results:

A5=a2.switch (CITYID,A4)

The code above replaces the Cityid field in A2 with the corresponding record in A4, which works like a left connection. The replacement A2 is as follows (A2 and A5 point to the same two-dimensional table):

Click the Blue hyperlink in Cityid to see the corresponding record:

Sometimes you need to make an internal connection, you should use the option @i in the function Swtich, the code is:[email protected] (CITYID,A4), The result will be as follows:

a6=a5.new (Eid,dept,cityid.cityname:cityname,name,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. Where CityID.CityName:CityName represents the CityName field in the record of the Cityid field that is taken from A5. Rename to CityName (the report tool does not recognize field names such as Cityid.cityname).

as can be seen from the above code, after replacing the field with switch, the relationship between the table can be accessed in the way of the object, which is straightforward and more obvious when multi-table multi-level association.

The A6 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 MONGODBJOIN2.DFX, you can use mongodbJoin2 $P {pbegin} in the Jasperreport SQL designer, $P {pend} to invoke.

Example of using Jasper report to make MongoDB join MySQL