Set JFreeChart data from database

by Hamid Reza Fahimi Madjd @ Jan 15, 2008

A couple of days ago I needed to generate some charts using Java so I searched for a library which could do it. Soon I found JFreeChart library and downloaded it from here. But I was surprised when I saw the user guide wasn't free! Therefore I decided to write this post and describe how to obtain JFreeChart data from database (I'll use mysql database).

At first I copied jcommon-1.0.12.jar, jfreechart-1.0.9.jar and mysql-connector-java-5.0.7-bin.jar files to /WEB-INF/lib directory then I created a table in the database and filled it with below data:

type             count
----------------------
invoice_detail    3273
----------------------
object_status     2819
----------------------
service_sales_... 1540
----------------------
call_function     1183
----------------------
contact           904
----------------------
invoice           775
----------------------
payment           596

Now I create DatabaseChart servlet for connecting to database and generating my chart

import org.jfree.data.jdbc.JDBCPieDataset;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;

public class DatabaseChart extends HttpServlet {

  public void doPost(HttpServletRequest request, response) throws ServletException, IOException {}

  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Connection connection = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      try {
        connection = DriverManager.getConnection("jdbc:mysql://localhost/my_db?user=my_un&password=my_pass&useUnicode=true&characterEncoding=utf-8");
      } catch (SQLException e) {
        e.printStackTrace();
      }
    } 
    catch (InstantiationException e) {
      e.printStackTrace();
    } 
    catch (IllegalAccessException e) {
      e.printStackTrace();
    } 
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    }

    JDBCPieDataset dataset = new JDBCPieDataset(connection);
    try {
      dataset.executeQuery("Select `type`, `count` From my_table order by count desc");
      JFreeChart chart = ChartFactory.createPieChart("Pie Chart", dataset, true, true, false);
      if (chart != null) {
        response.setContentType("image/png");
        OutputStream out = response.getOutputStream();
        ChartUtilities.writeChartAsPNG(out, chart, 450, 400);
      }
    } 
    catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      if(connection != null){connection.close();} 
    }
    catch (SQLException e) {e.printStackTrace();}
  }

}

at the end I add the servlet information to web.xml file

<servlet>
<servlet-name>generate_chart</servlet-name>
<servlet-class>DatabaseChart</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>generate_chart</servlet-name>
<url-pattern>/generate_chart</url-pattern>
</servlet-mapping>

now if you run the project you will get a chart like this

java, jfreechart

16 comment(s)

#1. Anonymous @ 2008-04-25 10:53:00

Hi Hamid,
I'm new to JFreeChart and bashing my head against a wall trying to get ANY simple graph to work when accessing a database through MS SQL Server.
I can get graphs to work without accessing a database with hardcoded values.
The error i'm getting is "Exception in thread 'main' java.lang.NoSuchMethodError: main"

Any help would be greatly appreciated.

Regards,
Gavin



Here's my code example below:

import java.*;
import org.jfree.*;
import org.jfree.data.jdbc.JDBCPieDataset;
import java.sql.Connection;
import java.sql.SQLException;
import org.jfree.data.general.PieDataset;
import org.jfree.chart.*;

public class GraphCon {

private PieDataset readData()
{
JDBCPieDataset data = null;
String url = "jdbc:jtds:sqlserver://192.168.0.2:1433/pubs";

Connection con;
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
}
catch (ClassNotFoundException e)
{
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try
{
con = java.sql.DriverManager.getConnection(url, "jfreechart", "password");
data = new JDBCPieDataset(con);
String sql = "SELECT npx_date,npx_value FROM npx_table ORDER BY npx_date";
data.executeQuery(sql);
con.close();
}
catch (SQLException e)
{
System.err.print("SQLException: ");
System.err.println(e.getMessage());
}
catch (Exception e)
{
System.err.print("Exception: ");
System.err.println(e.getMessage());
}
return data;
}

}

#2. Hamid Reza @ 2008-04-26 10:24:00

Gavin,
you can see some more things here:
http://forum.java.sun.com/thread.jspa?threadID=516207&messageID=2460058

please google it.

#3. Anonymous @ 2009-02-12 10:47:00

Nice Blog.
Please Discuss more about JFreeChart and how to create it Dynamically.

#4. Anonymous @ 2009-07-26 17:55:00

THANKS U HELEPED A LOT!!!

#5. Anonymous @ 2010-01-19 14:13:00

Tell me how to run this project without main class and where put this web.xml file when I using NetBeans IDE 6.7

#6. sri-siriworld @ 2010-01-20 07:58:22

Hi Hamid, I am trying jfreecharts now. I am able to get the data from database using jfreechart, bar and pie charts aswell. But I would like to show in a drilldown view, I had seen an example http://homepage.ntlworld.com/richard_c_atkinson/jfreechart/. Where they had given using static data, if you have any example to get on dynamic (database), please post.

Any help would be greatly appreciated.

Thanks & Regards
Srikanth

#7. Binny @ 2010-02-26 11:13:17

i m getting an error saying a connection must be supplied

#8. dip @ 2011-08-31 18:30:41

Many many thanx Hamid.

It is working just fine....thanx a lot!!!!!!!

#9. Brundesh Mishra @ 2011-11-20 06:21:52

I am not able to create the jfree chart dynamically. The chart application is possible only in static data.

Please tell me what to do??

#10. diopy @ 2012-03-28 13:59:41

thanks very much it's good !

#11. Priya @ 2012-07-28 18:46:36

Good post!! This is how you exactly create a pie chart with values from a database. However, refreshing the pie chart is also must in case you have a database that often gets updated.

Take a look at this post, http://programmingfree.blogspot.in/2012/07/jfreechart-create-auto-refreshing-pie.html for updating pie charts with latest values.

#12. Felicia Monika @ 2016-04-29 01:01:48

hi please attach file that u made for this JFreeChart, what's in generate_chart servlet?

#13. Hamid Reza @ 2016-04-29 08:48:50

@Felicia the DatabaseChart class has been extended from HttpServlet and it's your desired servlet

#14. hansol @ 2016-06-08 10:57:55

Thank you for your data. So I could make one bar graph and display in Web.
I wanna make two graph using different Query and display in same time.
But I couldn't display two graph in same time.
How can I display two graph in same time??

#15. manju @ 2016-11-14 10:24:11

How to display line graph using JFreeChart in jsp?

#16. Arunima @ 2017-03-02 04:12:24

public void doPost(HttpServletRequest request, response) throws ServletException, IOException {}

I'm getting an error in this line. It's showing syntac error

add your comment

name (required)
email (required)
url
comment

not readable? sorry, click here

last tweet

Developed withDeveloped with
Clicky Web Analytics