Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Sunday, March 6, 2011

Setting up a MySQL Datasource in Jasper iReport

Jasper's iReport supports many different Datasource types to provide data for its reports.  One of these Datasources is "Database JDBC connection" which is used to write reports against MySQL.

Jasper iReport's Datasources include

  • JDBC, 
  • XML,
  • JavaBeans,
  • CSV File
  • Hibernate,
  • Mondrian OLAP ,
  • EJB, and
  • Microsoft Excel.
There are also variations of JDBC like NetBeans, Jasper Datasources (JRDataSourceProvider), and an empty Datasource.

To add a Datasource to iReport, making it available during report creation, Press the cylinder database icon in the top toolbar.
Manage Datasource Icon on iReport Toolbar
This will display a dialog containing a list of Datasources.

If you want to create a MySQL Datasource, press the New button.  When the Datasource dialog is displayed, select "Datasource JDBC Connection" and next.  Fill out the dialog as follows.
MySQL Datasource in iReport
Select one of the MySQL drivers.  This example uses com.mysql.jdbc.Driver.  Enter the JDBC URL which contains the host, port, and database identifier.  Finally, add the username and password.

If you're not familiar with JDBC URLs, there is a Wizard that will help form the correct syntax.  Enter the host and database identifier and press "Wizard".  If you're running MySQL on a different port than the default 3306, add the port number after the hostname, separated by a colon.

Test the Datasource and Save, optionally saving the password.  Since the password is stored in the clear, it would be a good idea to create a special database account for reporting.

Once the Datasource is created, it can be used in iReport for creating reports.  In iReport, you write queries (SQL in this case) against the Datasource and format the result set using the Designer.

9 comments:

  1. You Have to mention how can we add driver jar in ireports.
    thanks

    ReplyDelete
    Replies
    1. Hi,

      Go to Tools > Options > Classpath. This post has a screenshot of the window: http://bekwam.blogspot.com/2012/03/dynamic-crosstab-sorting-in-jasper.html.

      Delete
  2. How to use this IReport and how to configure it and how to use this report with SugarCRM

    ReplyDelete
    Replies
    1. I wouldn't start out using iReport with SugarCRM. There's a reporting feature that may meet your requirements. It may be easier to work with the columns and filters in SugarCRM rather than the underlying database.

      If you need to access the database on which SugarCRM is stored, then follow these procedures for general MySQL access.

      http://youtu.be/103MUpnyrvk

      Delete
  3. Hi
    Thank you very much.
    Can you please tell me how to set up the Database connection from java and use that in the report to generate?

    ReplyDelete
  4. Hi,

    Need urgent reply so thanks in advance.

    How do we populate a database with data and tables? I mean just after we connect it with a database, the database is empty when how do we create tables in it using ireport?

    ReplyDelete
    Replies
    1. Hi,

      iReport is a reporting tool that turns a query (SELECT) into a formatted report (PDF, text, HTML). You don't create data with iReport. Any prompts that retrieve input from the user are for parameterizing the query rather than inserting or updating.

      Delete
  5. Hi,

    how to design different charts or pie diagrams using the ireport.

    Thanks in advance

    ReplyDelete