Report example -- use Jasper report to implement MongoDB join and jaspermongodb

Report example -- use Jasper report to implement MongoDB join and jaspermongodb

Diverse data sources are common issues in report development. However, it is difficult to use report tools such as JasperReport to handle them. For example, the results of two MongoDB collection connections are displayed. Although JasperReport has virtual data source or table join, these functions are only available in commercial or high-end versions, making it difficult to implement them in the free version. These functions only support the connection of two data sources, which is much more troublesome to achieve multiple connections. In addition, these functions are only graphical interfaces and cannot perform SQL-like structured computing on connected data.

The collection and computing appliance has a structured and strong computing engine that supports diverse data sources and is easy to integrate. It can help report tools easily meet such requirements. The following example illustrates the implementation of MongoDB join.

Sales and emp are two collections in MongoDB. The SellerId field in sales is logically equivalent to a foreign key and points to the EId field of emp. Now you need to query orders in sales by time period, connect to emp and display it in the report. Some source data is as follows:

Collection sales

Collectionemp

Set the compute script:

A1 = MongoDB ("mongo: // localhost: 27017/test? User = root & password = sa ")

The above code is used to create a MongoDB database connection. user and password can be used to specify the user name and password.

The assembler also supports JDBC connection to MongoDB, which is used in the same way as a common database. However, since third-party JDBC is not charged, it is not as functional as the official database function, for example, it cannot obtain multi-layer data, therefore, the cube directly encapsulates the native method, and the functions and syntaxes of MongoDB are retained. 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 records of a certain period of time from the salescollection of MongoDB. The first parameter of the function "find" is the collection name, and the second parameter is the query condition. The MongoDB specification is followed. The third parameter limits the returned fields. Note that the begin and end parameters in the query conditions are external parameters from the report, indicating the start time and end time of OrderDate respectively.

The function find returns a cursor that does not directly read data into the memory. Therefore, large data volumes are supported. You can use functions such as skip, sort, and conj to continue the operation of the cursor until the fetch, groups, or for functions are encountered. In this example, the function fetch () is used to read data into the memory. If the time range is to, the calculation result of A2 is as follows:

A3 = A1.find ("emp", "{_ id: 0}"). fetch ()

The above code retrieves the number from the emp collection, unconditional, all fields except _ id, the results are as follows:

A4 = A1.close ()

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

A5 = join @ 1 (A2: sales, SellerId; A3: emp, EId)

The above code connects A2 and A3 to the left, and the connection field is the SellerId of A2 and the Eld of A3. For the sake of intuition, the two pieces of data after the connection are named sales and emp respectively. Function join performs join calculation. Option @ 1 indicates the left join. The calculation result is shown in the left join:

As you can see, because it is a left join, some sellerids in sales cannot find the corresponding records in emp. If you want to perform a full connection, you can use option @ f. If there is no option, it indicates the 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 the connection operation. A6 extracts the required fields from the connection results and uses the new function to form the orders table. For example, sales. OrderID: OrderID indicates to retrieve the sales. OrderID field from A5 and rename it as OrderID (the report tool cannot recognize field names such as sales. OrderID ). The calculation result is as follows:

So far, all the data required by the report is calculated. Finally, you only need to use result A6 to return the "statement" table in A6 to the report tool. The assembler provides a JDBC interface. The report tool recognizes the assembler as a common database. For the integration solution, see related documents.

 

The following uses JasperReport as an example to design a report. The table is as follows:

You need to define two report parameters Pbegin and Pend, which correspond to the two parameters in the Set calculator respectively. After previewing, you can see the report results:

The method of the report call set calculator is the same as that of the call stored procedure. For example, you can save this script as mongodbJoin. dfx, you can use mongodbJoin $ P {pbegin}, $ P {pend} to call it in the SQL designer of JasperReport.