Exporting a small MySQL database, Importing into MongoDB, and Viewing the MongoDB data in IReport / Jasper reports

MySQL Database

I started by creating a sample MySQL database with the following schema:

PersonID    FirstName    LastName    Address    City 1    Miles    Davenport    10 Hight Street    London 2 John    Smith    1    Westminster Abbey    York 3 Sam    Davis    2    Highmount    Avenue    Southampton

Not the largest database, but OK for what this example :)

Export the database as a CSV file. The file will look like this:

PersonID,”FirstName”,”LastName”,”Address”,”City” 1,”Miles”,”Davenport”,”10 Hight Street”,”London” 2,”John”,”Smith”,”1 Westminster Abbey”,”York” 3,”Sam”,”Davis”,”2 Highmount Avenue”,”Southampton”

The first line details the column headings.

Ensure that MongoDB is running. The default URL is http://localhost:28017

If you go to http://localhost:27017, you will get the following message “Y_ou are trying to access MongoDB on the native driver port. For http diagnostic access, add 1000 to the port number_”.

Make sure you go to port 28017.

Import into MongoDB

Import your CSV into MongoDB. Use the mongoimport tool (http://docs.mongohq.com/importing-exporting/from-csv.html).

I used the following command parameters (which specify the name of the MongoDB database “-d database”, the collection “-c collection”, and –headerline, which uses the first line of the CSV (with the MySQL column names) to be used by MongoDB:

$ mongoimport -h localhost –port 27017 -d people -c address –type csv –file ./address.csv –headerline connected to: localhost:27017 Tue Dec 16 20:36:13.266 imported 3 objects

A MongoDB “people” database, with an “address collection” has been created.

Query MongoDB

You can query the MongoDB, using the “mongo” shell / query tool (http://docs.mongodb.org/manual/tutorial/getting-started-with-the-mongo-shell/).

$ mongo people MongoDB shell version: 2.4.8 connecting to: people

Show collections (for that database).

show collections address system.indexes

Find everything in the “address” collection, by using the “db.collection.find()” command.

db.address.find() { “_id” : ObjectId(“52b07d7dece23287ac458765”), “PersonID” : 1, “FirstName” : “Miles”, “LastName” : “Davenport”, “Address” : “10 Hight Street”, “City” : “London” } { “_id” : ObjectId(“52b07d7dece23287ac458766”), “PersonID” : 2, “FirstName” : “John”, “LastName” : “Smith”, “Address” : “1 Westminster Abbey”, “City” : “York” } { “_id” : ObjectId(“52b07d7dece23287ac458767”), “PersonID” : 3, “FirstName” : “Sam”, “LastName” : “Davis”, “Address” : “2 Highmount Avenue”, “City” : “Southampton” }

IReport / Jasper Reports

The next step is to access your data in IReport.

Create a new report, which is not blank, by using the “Report Wizard”.

Give the report a name, and click Next.

Create a new “connection” to you MongoDB.  Use

mongodb://127.0.0.1:27017/people

I’m not using a username or password. Click Next.

For query, use:

{‘collectionName’ : ‘address’}

The will point “your query” to the “address” collection. Click Next.

You will “see” Fields” (with the same names as the original database columns) which you can select by moving them to the right window.

Select all the the fields. Click Next.

Click Finish.

The report will be displayed (with the named $F{} parameters).

Click “Preview”.

All address information from the “address” collection will be displayed (on the IReport).

Author | Miles Davenport

Career programmer, who designs, assembles, fixes, and supports customers, software and systems.