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