This discussion is archived
6 Replies Latest reply: Jun 19, 2011 11:06 PM by 805536 RSS

Enabling datasource jdbc with Charts

861530 Newbie
Currently Being Moderated
Is there any demo or can anyone describe how to enable charts(bar or pie) connect with db or to view charts according to the db data's(even any online data's please)
  • 1. Re: Enabling datasource jdbc with Charts
    805536 Journeyer
    Currently Being Moderated
    That's a rather broad question to answer in a forum post I'm afraid. It depends on all sorts of things, like the way the data was saved in the database and how you wish to represent it. As such, I am not sure where an example could be found. The best I can do with the given information is point you to the appropriate tutorials so you can gain the skills to figure it out yourself:

    SQL tutorial
    http://www.w3schools.com/sql/default.asp

    Java database access
    http://download.oracle.com/javase/tutorial/jdbc/index.html

    Chart Base class documentation
    http://download.oracle.com/docs/cd/E17802_01/javafx/javafx/1.3/docs/api/javafx.scene.chart/javafx.scene.chart.Chart.html

    If you have the skills needed, such a thing isn't too difficult. Could you post the table format? I might be able to write an example right here if you do so.
  • 2. Re: Enabling datasource jdbc with Charts
    861530 Newbie
    Currently Being Moderated
    Its not needed for a table format to post for example if i want to graph the no of customer per week or month in a pie chart or bar chart using local myslq stuffs that would be very help ful
  • 3. Re: Enabling datasource jdbc with Charts
    Narayan Pro
    Currently Being Moderated
    Hello User,

    For Piechart data from database, this blog might be helpful : http://blog.ngopal.com.np/?p=821

    Thanks.
    Narayan
  • 4. Re: Enabling datasource jdbc with Charts
    805536 Journeyer
    Currently Being Moderated
    Ok. Java has an interface API for connecting to SQL databases. There are different implementations for connecting to different SQL databases. It has one implementation built in for a variation of Apache Derby. You can retrieve the implementation for MySQL and learn more about it right here: http://dev.mysql.com/usingmysql/java/

    I am going to assume a table built like this:
    -- Table recording the number of customers per week.
    CREATE TABLE weekly_customers (
        -- Uniquely identifying number of the row.
        id INTEGER AUTO_INCREMENT,
    
        -- Number of weeks since some time.
        week INTEGER,
    
        -- How many customers we have had in this week.
        customers INTEGER,
    
        -- There should only be one row for each week.
        UNIQUE(week),
    
        -- ID uniquely identifies each row in the table.
        PRIMARY KEY(id)
    );
    I cannot actually test out this code due to being a Linux user (JavaFX only supports Windows at the moment) and I have never used the driver for MySQL before, but if I'm reading the documentation correctly, it should go something like this:
    public class Main extends Application {
    
        public static void main(String[] args) {
            launch(args);
        }
    
        public void start(Stage stage) {
            //Create the axis labels.
            NumberAxis xAxis = new NumberAxis("Week", 0.0, 10.0, 1.0);
            NumberAxis yAxis = new NumberAxis("Customers", 0.0, 10000.0, 1.0);
    
            //Create the chart.
            BarChart<Number, Number> chart = new BarChart<Number, Number>(xAxis, yAxis);
    
            //Create a series to contain the data.
            XYChart.Series<Number, Number> series = new XYChart.Series<Number, Number>();
    
            //Get the list that contains the actual data.
            ObservableList<XYChart.Data<Number, Number>> data = series.getData();
    
            //Populate the chart with customers/week by connecting the MySQL database.
            try {
                //Register the MySQL driver so that DriverManager knows a MySQL implementation exists.
                Class.forName("com.mysql.jdbc.Driver");
    
                /**
                 * Get a connection to a mysql database hosted on this computer and named "mysql".
                 * http://download.oracle.com/javase/7/docs/api/java/sql/DriverManager.html#
                 * getConnection%28java.lang.String,%20java.lang.String,%20java.lang.String%29
                 */
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "my_username", "my_password");
    
                /**
                 * Ask for the first ten weeks of customers.
                 * If you want to make multiple similar queries
                 * (such as using different ranges of weeks),
                 * you may want to use a prepared statement
                 * in your own code.
                 */
                Statement statement = con.createStatement();
                ResultSet results = statement.executeQuery(
                        "SELECT week, customers FROM weekly_customers " +
                        "WHERE week <= 10 " +
                        "ORDER BY week ASC"
                        );
    
                //Add results to the list.
                while (results.next()) {
                    data.add(new XYChart.Data<Integer, Integer>(results.getInteger("week"), results.getInteger("customers"));
                }
                
                //Close the connection.
                statement.close();
                connection.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
    
            //Add the data to the chart.
            chart.getData().add(series);
    
            /**
             * Use the chart as the root node of the scene. Since it extends Region,
             * making it root should make everything scale with the window size.
             */
            Scene scene = new Scene(chart, 500, 300);
    
            //Attach the scene to the stage and show the scene.
            stage.setScene(scene);
            stage.setVisible(true);
        }
    
    }
    I'm sure there are errors in the code above, but I hope it gives you an idea of what to do.

    Edited by: aidreamer on Jun 19, 2011 9:34 AM
  • 5. Re: Enabling datasource jdbc with Charts
    861530 Newbie
    Currently Being Moderated
    I want this part of it this is what am figuring out to integrate with the pie chart let me try it and let you know soon
     while (results.next()) {
                    data.add(new XYChart.Data<Integer, Integer>(results.getInteger("week"), results.getInteger("customers"));
                }
                
                //Close the connection.
                statement.close();
                connection.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
     
            //Add the data to the chart.
            chart.getData().add(series);
     
            /**
             * Use the chart as the root node of the scene. Since it extends Region,
             * making it root should make everything scale with the window size.
             */
            Scene scene = new Scene(chart, 500, 300);
     
            //Attach the scene to the stage and show the scene.
            stage.setScene(scene);
            stage.setVisible(true);
        }
     
    }
    Edited by: userJBS on Jun 20, 2011 1:30 AM
  • 6. Re: Enabling datasource jdbc with Charts
    805536 Journeyer
    Currently Being Moderated
    For future reference, code should be placed between \
    \
    tags to make it easier to read.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points