Friday, May 23, 2008

Using Jasper reports with Postgres database

As Jasper reports has been written in Java we need JDK/JRE installed on machine.
Download jasper report from here (jasperreports-3.0.0-project.tar.gz)
http://sourceforge.net/project/showfiles.php?group_id=36382&package_id=28579

Open it at some convenient location lets say:
/usr/local
A folder will be created jasperreports-3.0.0

Here we are going to create reports from the Postgres sql so we need Java Postgres driver.
Download driver from here:
http://jdbc.postgresql.org/download.html
Select appropriate jar file, means if JDK is >1.4 <1.6 and Postgres >8.1 then
download the JDBC3 (postgresql-8.2-508.jdbc3.jar)
Place this file at /usr/local/jasperreports-3.0.0/lib folder.

There are many sample reports type availabe in the jasperreports-3.0.0/demo/samples
Let say we are interested in the Charts type of report (Bar, Pie and Line charts)

To connet to the postgres database open the ChartsApp.java file.
By defualt Jasper uses inbuilt HSQL db and below function does this:
private static Connection getConnection() throws ClassNotFoundException, SQLException
{
//Change these settings according to your local configuration
String driver = "org.hsqldb.jdbcDriver";
String connectString = "jdbc:hsqldb:hsql://localhost";
String user = "sa";
String password = "";


Class.forName(driver);
Connection conn = DriverManager.getConnection(connectString, user, password);
return conn;
}


Change it to something like this:
// for postgre sql db
private static Connection getConnection() throws ClassNotFoundException, SQLException
//Change these settings according to your local configuration

String driver = "org.postgresql.Driver";
String connectString = "jdbc:postgresql://localhost:5432/YOUR_PG_DB_NAME";
String user = "PG_USER_NAME";
String password = "PG_USER_PASSWORD";

Class.forName(driver);
Connection conn = DriverManager.getConnection(connectString, user, password);
return conn;
}


Now in any one of the jrxml file you can write your sql query depending upon the requirement.

1 comment:

Ajeet Singh Raina said...

Any idea how can we integrate Jasper Report with Nagios?

Whats new in Ajuby 0.5