Jfreechart and jdbc.CategoryDataset

Question

My goal is to write a small program that reaches into our Public Safety database and retrieves ambulance response times. Then I want to take that data and generate a histogram. I am having a very difficult time getting JFreechart to print a histogram of ambulance response times. When I run my code Netbeans generates an error message stating:

Exception in thread "main" java.lang.ClassCastException: org.jfree.data.jdbc.JDBCCategoryDataset cannot be cast to org.jfree.data.xy.IntervalXYDataset at EMSResearch.histogram1.main(histogram1.java:39) C:\Users\kulpandm\AppData\Local\NetBeans\Cache\8.1\executor-snippets\run.xml:53: Java returned: 1 BUILD FAILED (total time: 7 seconds)

I have absolutely no idea how to solve this issue. If I can get a histogram to print out I can then move on and hack away at making it look good for my reports. Shown below is my code:

package EMSResearch;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.jdbc.JDBCCategoryDataset;
import org.jfree.data.xy.IntervalXYDataset;

public class histogram1
{

public static void main(String[] args) throws ClassNotFoundException
{

    try
    {
        Connection conn = null;
        conn = DriverManager.getConnection("jdbc:sqlserver://MyURL;database=MyDatabase;integratedsecurity=false;user=MyUser;password=MyPassword");
        //dataset = new JDBCCategoryDataset(conn);
        String query = "SELECT PUN_UnitID\n"
                + ", DATEDIFF(SECOND, IIU_tenroute, IIU_tArrive) AS [ResponseTime]\n"
                + "FROM IIncidentUnitSummary\n"
                + "WHERE IIU_tDispatch > 'may 15, 2016'\n"
                + "AND PUN_UnitID LIKE 'M[0-4]_'\n"
                + "AND IIU_tEnroute IS NOT NULL\n"
                + "AND IIU_tArrive IS NOT NULL\n"
                + "AND DATEDIFF(SECOND, IIU_tEnroute, IIU_tArrive) BETWEEN 0 AND 1800";
        JDBCCategoryDataset dataset = new JDBCCategoryDataset(conn, query);
        dataset.executeQuery(query);
        conn.close();
        //the following blows up.
        JFreeChart chart;
        chart = ChartFactory.createHistogram("data", "ws range", "freq", (IntervalXYDataset) dataset, PlotOrientation.VERTICAL, true, false, false);
        int width = 560;
        int height = 370;
        File histChart = new File("c:\\histChart2.jpeg");

        try
        {
            ChartUtilities.saveChartAsJPEG(histChart, chart, width, height);
        } catch (IOException ex)
        {
            Logger.getLogger(chart2.class.getName()).log(Level.SEVERE, null, ex);
        }
    } catch (SQLException ex)
    {
        ex.printStackTrace();
    }

}
}

Can you please show me what I am doing incorrectly? What can I do to fix this? (please be specific since I am not an expert programmer).

score 2 · Answer 1 · edited May 23 '17 at 11:52

A CategoryDataset is not an IntervalXYDataset; they provide completely different things to clients.

A CategoryDataset can be used to create a bar chart. Invoking ChartFactory.createBarChart() using your JDBCCategoryDataset should at least give you something to see. Refer to this related example.

The API suggests "writing your own code to read data from a ResultSet." I'd use the data to populate a HistogramDataset, seen here. Because HistogramDataset implements IntervalXYDataset, it is suitable for use with ChartFactory.createHistogram(). Where the example has one series for each primary color, it looks like you might want one series for each PUN_UnitID.

Conceptually, it may be easier to SELECT DISTINCT PUN_UnitID that meet your LIKE clause in an initial query. Then query the response time values for each PUN_UnitID and invoke dataset.addSeries(PUN_UnitID, values, bins).

Alternatively, loop through the ResultSet, adding entries to a Map<String, List<Integer>>, where the key is the PUN_UnitID the value is aList of response times. Use the Map entries to construct the HistogramDataset as before.

If latency becomes a problem, update the dataset in using a SwingWorker, as shown here.

Jfreechart and jdbc.CategoryDataset

1 Answers1