Osiris93 Osiris93 - 7 months ago 39
SQL Question

Create a 3D bar chart in NetBeans to display top 10 items in a database

I have developed a small application that must show the top ten selling items for a company using a 3D bar graph. The items will be retrieved from a mysql database.

I do understand how to retrieve data from a mysql database, but how do I get it to work with the bar graph on Neteans?

How can I go about achieving this or where is the best resource to help me achieve this?

Answer

The way of going about this is firstly establishing your database connection with the following coding as shown below:

  //connects to the database
    Class.forName("com.mysql.jdbc.Driver");
        Connection con = (Connection)
        DriverManager.getConnection("jdbc:mysql://localhost:3306/DBName","root","password");
        //select statement calling data from the sales database
        PreparedStatement stmt = con.prepareStatement("SELECT * FROM dbsales ORDER BY usold DESC LIMIT 10");
        ResultSet rs = stmt.executeQuery();

An then you will needs to use the following coding for the creation and display of the chart in netbeans

//creates the graph object
        DefaultCategoryDataset ddataset = new DefaultCategoryDataset();
        while (rs.next()) 
        {
            //retrieves data from the database for the graph
            ddataset.setValue(new Double(rs.getDouble("usold")), rs.getString("pbrand")  + " " + rs.getString("pname"),  rs.getString("pid"));
        }
         //generates the graph
         JFreeChart chart = ChartFactory.createBarChart3D("Top 10 Selling Products", "Products", "Number of Units Sold", ddataset);
         //creates the graph title
        chart.getTitle().setPaint(Color.RED);
        //plots the graph
        CategoryPlot p = chart.getCategoryPlot();
        p.setRangeGridlinePaint(Color.BLUE);
        //creates the frame 
        ChartFrame frame2 = new ChartFrame("Top 10 Selling Products", chart);
        //sets the frame visible
        frame2.setVisible(true);
        //sets the frame size
        frame2.setSize(900,700);

You will need to put such in a try catch block.

Remember that you will need a Jar file which allow you to import the necessary data from those JAR files for the charts.

Here is the complete set of coding shown below:

//method for top ten graph
private void topten()
{
    try
    {
        //connects to the database
    Class.forName("com.mysql.jdbc.Driver");
        Connection con = (Connection)
        DriverManager.getConnection("jdbc:mysql://localhost:3306/DBName","root","password");
        //select statement calling data from the sales database
        PreparedStatement stmt = con.prepareStatement("SELECT * FROM dbsales ORDER BY usold DESC LIMIT 10");
        ResultSet rs = stmt.executeQuery();
        //creates the graph object
        DefaultCategoryDataset ddataset = new DefaultCategoryDataset();
        while (rs.next()) 
        {
            //retrieves data from the database for the graph
            ddataset.setValue(new Double(rs.getDouble("usold")), rs.getString("pbrand")  + " " + rs.getString("pname"),  rs.getString("pid"));
        }
         //generates the graph
         JFreeChart chart = ChartFactory.createBarChart3D("Top 10 Selling Products", "Products", "Number of Units Sold", ddataset);
         //creates the graph title
        chart.getTitle().setPaint(Color.RED);
        //plots the graph
        CategoryPlot p = chart.getCategoryPlot();
        p.setRangeGridlinePaint(Color.BLUE);
        //creates the frame 
        ChartFrame frame2 = new ChartFrame("Top 10 Selling Products", chart);
        //sets the frame visible
        frame2.setVisible(true);
        //sets the frame size
        frame2.setSize(900,700);
        }
     catch(Exception e)
    {
        //error message for when the graph cannot be generated
        JOptionPane.showMessageDialog(null, "Error 111: Unable to identify and load best ten sellers for graph", "Database Error", JOptionPane.ERROR_MESSAGE);
    }
}
Comments