JFREE CHART Multiple Series BarChart from Database

[anonymousx]

Hello I am new to this site and will try to be as detailed, but concise, as possible. To begin, the question/issue I am having is I don't know how to develop a chart using JFREECHARTS on Java in which multiple series can be placed in a barchart, where the data for this barchart comes from a database. I will now go into further detail about the issue. To begin, my code is here. Bear in mind, I am new to coding. So this may not be the most elegant or concise way to code what I want, but it will do.

Code: Select all

package javaapplication24;
    import java.awt.Color;
    import java.awt.Dimension;
    import java.awt.Font;
    import java.awt.GradientPaint;
    import java.io.*;
    import java.io.IOException;
    import java.net.URI;
    import java.sql.*;
    import java.util.Scanner;
    import org.jfree.chart.ChartFactory;
    import org.jfree.chart.ChartPanel;
    import org.jfree.chart.ChartRenderingInfo;
    import org.jfree.chart.ChartUtilities;
    import org.jfree.chart.JFreeChart;
    import org.jfree.chart.axis.CategoryAxis;
    import org.jfree.chart.axis.CategoryLabelPositions;
    import static org.jfree.chart.axis.CategoryLabelPositions.STANDARD;
    import org.jfree.chart.axis.NumberAxis;
    import org.jfree.chart.entity.StandardEntityCollection;
    import org.jfree.chart.plot.CategoryPlot;
    import org.jfree.chart.plot.PlotOrientation;
    import org.jfree.chart.plot.XYPlot;
    import org.jfree.chart.renderer.category.BarRenderer;
    import org.jfree.chart.renderer.xy.XYLineAndShapeRenderer;
    import org.jfree.data.category.CategoryDataset;
    import org.jfree.data.jdbc.JDBCCategoryDataset;
    import org.jfree.ui.ApplicationFrame;
    import org.jfree.ui.RefineryUtilities;
    import org.odftoolkit.odfdom.doc.OdfTextDocument;

    public final class JavaApplication24 extends ApplicationFrame {
    Scanner sc = new Scanner(System.in);
    String Title = null;
    String CategoryPosition = null;

    public JavaApplication24(String s) throws IOException, Exception {
            super(s);
            CategoryDataset categorydataset = readData(); 
            final JFreeChart chart = createChart(categorydataset);
            CategoryDataset categorydataset1 = readData1(); 
            final JFreeChart chart1 = createChart1(categorydataset1);
            final ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
            final File file = new File("BarChart.jpeg");
            ChartUtilities.saveChartAsJPEG(file, chart, 800, 600, info);      
            final File file1 = new File("BarChart1.jpeg");
            ChartUtilities.saveChartAsJPEG(file1, chart1, 800, 600, info); 



            OdfTextDocument outputDocument;
            outputDocument =
            OdfTextDocument.newTextDocument();
                            outputDocument.newParagraph("");
                            outputDocument.newImage(new URI("BarChart.jpeg"));
                            outputDocument.newParagraph("");
                            outputDocument.newImage(new URI("BarChart1.jpeg"));
                            outputDocument.save("TrendSummaryChart.odt");
    }

    public JFreeChart createChart(final CategoryDataset categorydataset) {
            final JFreeChart chart = ChartFactory.createBarChart(
                    "Some Title",         
                    "",               
                    "",                  
                    categorydataset,                  
                    PlotOrientation.VERTICAL, 
                    false,                     
                    true,                     
                    false                     
                );
                    chart.setBackgroundPaint(Color.white);

    // get a reference to the plot for further customisation...
            final CategoryPlot plot = chart.getCategoryPlot();
                    plot.setBackgroundPaint(Color.white);
                    plot.setDomainGridlinePaint(Color.black);
                    plot.setRangeGridlinePaint(Color.black);

    // set the range axis to display integers only...
            final NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
            rangeAxis.setTickLabelFont(new Font("Arial", Font.BOLD, 16));
                    rangeAxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());

    // disable bar outlines...
            final BarRenderer renderer = (BarRenderer) plot.getRenderer();
                    renderer.setDrawBarOutline(false);

    // set up gradient paints for series...
            final GradientPaint gp0 = new GradientPaint(
                    0.0f, 0.0f, Color.cyan, 
                    0.0f, 0.0f, Color.cyan
                );
            renderer.setSeriesPaint(0, gp0);

            final CategoryAxis domainAxis = plot.getDomainAxis();
            domainAxis.setTickLabelFont(new Font("Arial", Font.BOLD, 16));
                    domainAxis.setCategoryLabelPositions
                (
                    STANDARD
                );

    return chart;
   }
    public JFreeChart createChart1(final CategoryDataset categorydataset1) {
            final JFreeChart chart1 = ChartFactory.createBarChart(
                    "Some Title",         
                    "",               
                    "",                  
                    categorydataset1,                  
                    PlotOrientation.VERTICAL, 
                    false,                     
                    true,                     
                    false                     
                );
                    chart1.setBackgroundPaint(Color.white);

    // get a reference to the plot for further customisation...
            final CategoryPlot plot = chart1.getCategoryPlot();
                    plot.setBackgroundPaint(Color.white);
                    plot.setDomainGridlinePaint(Color.black);
                    plot.setRangeGridlinePaint(Color.black);

    // set the range axis to display integers only...
            final NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
            rangeAxis.setTickLabelFont(new Font("Arial", Font.BOLD, 12));
                    rangeAxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());

    // disable bar outlines...
            final BarRenderer renderer = (BarRenderer) plot.getRenderer();
                    renderer.setDrawBarOutline(false);

    // set up gradient paints for series...
            final GradientPaint gp0 = new GradientPaint(
                    0.0f, 0.0f, Color.cyan, 
                    0.0f, 0.0f, Color.cyan
                );

            renderer.setSeriesPaint(0, gp0);
            final CategoryAxis domainAxis = plot.getDomainAxis();
            domainAxis.setTickLabelFont(new Font("Arial", Font.BOLD, 12));
                    domainAxis.setCategoryLabelPositions
                            (
                            STANDARD
                            );                
    return chart1;
   }

    private CategoryDataset readData()
    {
            JDBCCategoryDataset jdbccategorydataset = null;
            String s = "sql database link";
            try
            {
                    Class.forName("com.mysql.jdbc.Driver");
            }
            catch (ClassNotFoundException classnotfoundexception)
            {
                    System.err.print("ClassNotFoundException: ");
                    System.err.println(classnotfoundexception.getMessage());
            }
            try
            {
                    Connection connection = DriverManager.getConnection(s, "root", "password");
                    jdbccategorydataset  = new JDBCCategoryDataset(connection);
                    String s1 = "select month, sum(count)/31 from table where month like 'January' union\n"
                            +   "select month, sum(count)/28 from table where month like 'February'union\n"
                            +   "select month, sum(count)/31 from table where month like 'March' union\n"
                            +   "select month, sum(count)/30 from table where month like 'April' union\n"
                            +   "select month, sum(count)/31 from table where month like 'May';";
                    System.out.println("Creating Graph..."); 
                    jdbccategorydataset.executeQuery(s1);
                    connection.close();
            }
            catch (SQLException sqlexception)
            {
                    System.err.print("SQLException: ");
                    System.err.println(sqlexception.getMessage());
            }
            catch (Exception exception)
            {
                    System.err.print("Exception: ");
                    System.err.println(exception.getMessage());
            }
            return jdbccategorydataset;
    }
    private CategoryDataset readData1()
    {
            JDBCCategoryDataset jdbccategorydataset1 = null;
            String s = "sql link";
            try
            {
                    Class.forName("com.mysql.jdbc.Driver");
            }
            catch (ClassNotFoundException classnotfoundexception)
            {
                    System.err.print("ClassNotFoundException: ");
                    System.err.println(classnotfoundexception.getMessage());
            }
            try
            {
                    Connection connection = DriverManager.getConnection(s, "root", "password");
                    jdbccategorydataset1  = new JDBCCategoryDataset(connection);
                    String s1 = "select name, sum(count) from table where month='may' group by name order by sum(count) desc;";
                    System.out.println("Creating Graph..."); 
                    jdbccategorydataset1.executeQuery(s1);
                    connection.close();
            }
            catch (SQLException sqlexception)
            {
                    System.err.print("SQLException: ");
                    System.err.println(sqlexception.getMessage());
            }
            catch (Exception exception)
            {
                    System.err.print("Exception: ");
                    System.err.println(exception.getMessage());
            }
            return jdbccategorydataset1;
    }
    public static void main(String args[]) throws IOException, Exception
    {
            JavaApplication24 Final = new JavaApplication24("such");
    }
}

So basically, I have several tables in a mysql database (I'm using netbeans). This code will create charts and draw values I have dictated from the database. There is repetition of the code because I'm making multiple charts and saving them all to one odt document. I'm sure there is a much more efficient way to do this. Tips are welcome but this is not a primary concern. This code creates bar graphs that looks like a normal bar graph. Look at this link (http://www-958.ibm.com/software/data/co ... 1292295884). Normal chart. Now say, for the sake of an argument, my database table has months (Jan-May), the values for these months, and then some sort of averages. So I'm trying to create a bar graph with multiple bars for each month. January would have a bar for its own value + the bar for the average value, and the same for all other months. Kind of looking like (http://custom-analytics.thomsonreutersl ... t_web2.png). These are all dummy images by the way. So again, is there a way to make a barchart with series like these (multiple bars) in which the data is read from a database table. All other topics I've seen use the "dataset.addValue(1.0D, series1, category1);" method but this is just raw values, not read from a database. I changed some basic names in the code for security reasons. I would appreciate any help and I'm sure others wonder exactly what the extent of jfreecharts is.

If no one understands the question, please don't hesitate to ask! I believe I can find a way to be more specific and make help anyone understand my dilemma. I understand everyone is busy, but this isn't a very urgent matter. I appreciate the help whenever it may arrive. Thanks.