Help me in JDBC BarChart problem??

[bryan523]

Hi all,
I really need yours help to solve the below problem.

Code: Select all

I got the database as below: 

 CATEGORY | SERIES  | COUNT(*)| 
 ---------+---------+---------|
 London   | SERIES1 |    10   |
 London   | SERIES2 |    25   |
 London   | SERIES3 |    31   |
 New York | SERIES1 |    54   |
 New York | SERIES3 |    54	|
 Paris    | SERIES2 |    34	|

this is my coding:

Code: Select all

try {
            con = DriverManager.getConnection(url, "root", "");
            data = new JDBCCategoryDataset(con);
            String sql = "SELECT DISTINCT CATEGORY, SERIES, count(*)   FROM STANDARD GROUP BY CATEGORY, SERIES;";            

            data.executeQuery(sql);
            con.close();
        }

THE PROBLEM IS: The result dint show all the 3 series for each categories?? The result only show the total count(*) for each category! the legend only show count(*) but not series1,series2,series3 which i want! ist my sql got problem? How to make series1,series2,series3 with different color in one vertical bar for each category??

How to make it Like below:

Code: Select all

count
     |        ___                                        
     |       |S3 |        ___          
     |       |___|       |   |          __           S=Series   
     |     S2|___|       |S3 |         |  |            
     |       |   |       |___|         |  | 
     |       |S1 |       |S1 |         |S2|  
     |----------------------------------------- 
           category1 category2 category3  
                                                                     

please help me. thanks!

Rgds,
Bryan

[david.gilbert]

I'd recommend writing some Java code to read your data and populate a regular dataset (e.g. DefaultCategoryDataset) directly, that way you can access the data in whatever format is convenient.

[bryan523]

Thanks for your quick reply but i have no idea how to set the regular dataset!
I dint found any this kind of example in JFreeChart Developer Guide, so can you show me the coding example?? sorry to give you trouble but i am very new in this field and i felt sad when i din get what i want in JFreeChart Developer Guide!

Rgds,
Bryan

[angel]

A small hint: don't use the JDBCCategoryDataset. Use a CategoryDataset instead. The jdbc-dataset are ment to be a convenience, but they are difficult to debug when you run into trouble.

[Karl Klammer]

Well, I am sorry for that,
but I think this should be a forum for advanced java developers,
which are using the JFreeChart API for generating charts.
And of course, they known what they do.

- Why are there always the same questions in this forum ???

- Why does nobody of this beginners read the example sources
which belongs to the JFreeChart release ???

- Why are they not able themselves to use the "search functionality"
on this site ???

[david.gilbert]

I think this should be a forum for advanced java developers

No, the forum is for all developers that use JFreeChart, regardless of "level". There's always going to be people asking questions that could be answered by the search button, but to keep it friendly we should just give gentle hints and reminders.

[bryan523]

Hi all,
when i just used CategoryDataset instead of JDBCCategoryDataset, there was an error! I am trying to read the data from database where the X axis will read the Date1 from database, Y axis is the integer counting the number of (SERIES = FRUIT from database) as below:

Code: Select all

   DATE1	   FRUIT

2005-08-01	 Apple
2005-08-01	 Apple
2005-08-01	 Apple
2005-08-01	 Orange
2005-08-01	 Notice
2005-08-01	 Notice

2005-08-02	 Apple
2005-08-02	 Orange
2005-08-02	 Orange
2005-08-02	 Grape
2005-08-02	 Grape

I set my sql queries like this:

SELECT DATE1, FRUITE,count(*) FROM STANDARD GROUP BY DATE1, FRUITE;

AND show the table below which i need to create the stacked bar:

Code: Select all

DATE1	     FRUIT   COUNT(*)
2005-08-01	Apple	    3
2005-08-01	Orange	   1
2005-08-01	Grape	    2
		
2005-08-02	Apple	    1
2005-08-02	Orange	   2
2005-08-02	Grape	    2

What I want in stacked bar is:

X-axis = DATE1 (2005-08-01 and 2005-08-02)
Y-axis = COUNT(*) value
series = FRUITE (Apple,Orange and Grape)

I have no idea how to set the dataset especially the count(*) and series??
I very appreciate and hope someone can show me hw to do it!

[angel]

when i just used CategoryDataset instead of JDBCCategoryDataset, there was an error!

Look at the BarChartDemo1.java which is included in the sources. Because you are using the JDBCCategoryDataset nobody can reproduce your problem.

[bryan523]

Look at the BarChartDemo1.java which is included in the sources. Because you are using the JDBCCategoryDataset nobody can reproduce your problem.

Thanks but i still dun get what u mean? BarChartDemo1.java show to set the data manually but what i need is set the data automatically through the database!! So any idea?

I populate the regular dataset like this (like BarChartDemo1.java) as below and there was no error for compile but there was an error for run time which show :{JDBCCategoryDataset.executeQuery() : insufficient colums returned from the database.}

Code: Select all

String series = "SELECT Fruit FROM STANDARD GROUP BY DATE1;
Fruit;";
String category = "SELECT DATE1 FROM STANDARD GROUP BY DATE1;";
dataset.executeQuery(series);
dataset.executeQuery(category);
dataset.addValue(5.0, series, category);

con.close();

i just simply set the data 5.0 for each series because if i set as below there is an error for integer while compile:

Code: Select all

Integer count = "Select count(*) from standard group by date1,fruit;";
dataset.addValue(count, series, category);

Rgds,
Bryan

[bryan523]

Thanks for everyone who help me, i managed to get the answer already!

It is no problem using JDBC, and dun need to populate regular dataset(because i dun know how to do it), i just need to change the SQL Query without using count(*), then i managed to get count of all data for FRUIT automatically from database!

I felt sad because nobody teach me how to populate regular dataset (David suggest) where all data need to get from database which different than the demo from developer guide where the data is set manually to plot the stacked bar chart! Really hope somebody can show it to me!

Rgds,
Bryan

[angel]

I'm glad you found your answer!

I felt sad because nobody teach me how to populate regular dataset

Come on, to query a database and access the resultset via a loop has nothing to do with JFreeChart.