How to execute more than one query to draw line chart in jfreechart in connection with the database

Question

I have to draw a multi line chart which indicates the three types of values in a single chart. It has to get data from the database and plot the line graph. My code is

package barr4;


import java.sql.*;
import java.io.*;
import org.jfree.ui.*;
import org.jfree.chart.ChartPanel;
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.*;
import org.jfree.data.jdbc.JDBCCategoryDataset;

public class Barr4 {
public static void main(String[] args) throws Exception 
{
String query = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Five by Eight' group by tmp.s_id, u.u_keyboard ";
JDBCCategoryDataset dataset = new JDBCCategoryDataset( "jdbc:mysql://localhost:3306/kumararaja", 
    "com.mysql.jdbc.Driver","root", "test123");
dataset.executeQuery(query);
JFreeChart chart = ChartFactory.createLineChart("Tamil Five by Eight", "s_id", "AVG(cpm)", dataset, PlotOrientation.VERTICAL, true, true, false);
ChartPanel chartPanel = new ChartPanel(chart);
chartPanel.setPreferredSize(new java.awt.Dimension(500, 270));
ApplicationFrame f = new ApplicationFrame("Chart");
f.setContentPane(chartPanel);
f.pack();
f.setVisible(true);
}
}

When i execute the above get i get a line chart for only one query. What if i should get the linechart for three values which should be indicated in different colours. I should put on two more extra queries such as

String query = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Logical' group by tmp.s_id, u.u_keyboard ";

and another

String query = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Inscript' group by tmp.s_id, u.u_keyboard ";

Please suggest me a solution.

Edit: Here's a my attempt at an XY chart.

package xychart;

import java.sql.*;
import java.io.*;
import org.jfree.ui.*;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.data.xy.XYSeries;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.xy.XYSeriesCollection;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.ChartUtilities;
import org.jfree.data.*;
import org.jfree.data.jdbc.JDBCCategoryDataset;


public class XYchart
{
public static void main( String[ ] args )throws Exception
{

   final XYSeries TamilFivebyEight = new XYSeries( "TamilFivebyEight" );

   String query = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Five by Eight' group by tmp.s_id, u.u_keyboard ";
JDBCCategoryDataset dataset = new JDBCCategoryDataset( "jdbc:mysql://localhost:3306/kumararaja", 
    "com.mysql.jdbc.Driver","root", "test123");
   dataset.executeQuery(query);


  final XYSeries TamilLogical = new XYSeries( "TamilLogical" );
  String query1 = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Logical' group by tmp.s_id, u.u_keyboard ";
  dataset.executeQuery(query1);


  final XYSeries TamilInscript = new XYSeries( "TamilInscript" );
  String query2 = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Inscript' group by tmp.s_id, u.u_keyboard ";
  dataset.executeQuery(query2);


 final XYSeriesCollection dataset1 = new XYSeriesCollection( );
 dataset1.addSeries( TamilFivebyEight );
 dataset1.addSeries( TamilLogical );
 dataset1.addSeries( TamilInscript );

  JFreeChart xylineChart = ChartFactory.createXYLineChart(
     "Keyboard performance", 
          "s_id", 
          "AVG(cpm)", 
          dataset1, PlotOrientation.VERTICAL,
          true, true, false);


  ChartPanel chartPanel = new ChartPanel(xylineChart);
  chartPanel.setPreferredSize(new java.awt.Dimension(500, 270));
  ApplicationFrame f = new ApplicationFrame("Chart");
  f.setContentPane(chartPanel);
  f.pack();
  f.setVisible(true);

   }
}

This is the edited one for xy chart.

package xychart;

import java.sql.*;
import java.io.*;
import org.jfree.ui.*;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.data.xy.XYSeries;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.data.xy.XYSeriesCollection;
import org.jfree.chart.ChartPanel;
import org.jfree.chart.ChartUtilities;
import org.jfree.data.*;
import org.jfree.data.jdbc.JDBCCategoryDataset;


public class XYchart
{
public static void main( String[ ] args )throws Exception
{

final XYSeries TamilFivebyEight = new XYSeries( "TamilFivebyEight" );

String query = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Five by Eight' group by tmp.s_id, u.u_keyboard ";
 JDBCCategoryDataset dataset = new JDBCCategoryDataset( "jdbc:mysql://localhost:3306/kumararaja", 
"com.mysql.jdbc.Driver","root", "test123");
dataset.executeQuery(query);


 final XYSeries TamilLogical = new XYSeries( "TamilLogical" );
 String query1 = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Logical' group by tmp.s_id, u.u_keyboard ";
 dataset.executeQuery(query1);


 final XYSeries TamilInscript = new XYSeries( "TamilInscript" );
 String query2 = "select tmp.s_id, avg(tmp.cpm), u.u_keyboard from (SELECT u_id, s_id, avg((char_length(phrase_typed)-1)/((l_timestamp - f_timestamp)/60000)) as cpm FROM `session_details_table` WHERE s_id<32 group by U_ID, S_ID having cpm is not null) tmp, userdetails u where tmp.u_id = u._id and u.u_keyboard = 'Tamil Inscript' group by tmp.s_id, u.u_keyboard ";
 dataset.executeQuery(query2);


 final XYSeriesCollection dataset1 = new XYSeriesCollection( );
 dataset1.addSeries( TamilFivebyEight );
 dataset1.addSeries( TamilLogical );
 dataset1.addSeries( TamilInscript );

 JFreeChart xylineChart = ChartFactory.createXYLineChart(
 "Keyboard performance", 
      "s_id", 
      "AVG(cpm)", 
      dataset1, PlotOrientation.VERTICAL,
      true, true, false);


   ChartPanel chartPanel = new ChartPanel(xylineChart);
   chartPanel.setPreferredSize(new java.awt.Dimension(500, 270));
   ApplicationFrame f = new ApplicationFrame("Chart");
   f.setContentPane(chartPanel);
   f.pack();
   f.setVisible(true);

    }
    }

Possible [duplicate](http://stackoverflow.com/q/23930650/230513) using `JDBCCategoryDataset`; some [examples](http://stackoverflow.com/search?tab=votes&q=user%3a230513%20JDBCXYDataset) using `JDBCXYDataset`. — trashgod, Apr 01 '15 at 00:57
Cross-posted [here](http://www.jfree.org/forum/viewtopic.php?f=3&t=117251). — trashgod, Apr 08 '15 at 21:51

score 0 · Answer 1 · answered Mar 31 '15 at 08:21

0

Have you tried to use other chart types as the solution? It seems like you may be able to use createxylinechart. Check out for an example http://www.codejava.net/java-se/graphics/using-jfreechart-to-draw-xy-line-chart-with-xydataset

answered Mar 31 '15 at 08:21

Eissa

700
5
17

I tried with xyline chart, but i don't know how should i query it, that is integration with database. I will post my code for xy chart. Only when i give points to it, it is getting plotted. I don't know how to plot it using query. Please see to it. – ssh Mar 31 '15 at 09:21

How to execute more than one query to draw line chart in jfreechart in connection with the database

1 Answers1