4 Replies Latest reply: May 21, 2013 12:58 PM by rp0428 RSS

    how to return DefaultCategoryDataset

    a.stoyanov
      Hi all ,
      I'm trying to return a DefaultCategoryDatased filled from database but i'm not sure the method is correct.. Here is my code:
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.util.logging.Level;
      import java.util.logging.Logger;
      import org.jfree.data.category.DefaultCategoryDataset;
      
      /**
       *
       * @author sasho_b
       */
      public class Charts {
      
          String host = "jdbc:derby:release\\snejana_bah";
          String uName = "fester";
          String uPass = "fester";
          DefaultCategoryDataset dataset;
          Double limit;
          Double total;
          String category;
      
          public DefaultCategoryDataset getChartDataLimit() {
      
      
              try {
      
      
                  Connection con = DriverManager.getConnection(host, uName, uPass);
                  Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      
      
                  PreparedStatement ps = con.prepareStatement("select sum(t.AMOUNT) as total,t.PRODUCT_NAME as category, l.\"LIMIT\" as lim from fester.transactions t, fester.LIMITS l \n"
                          + "where l.CAT_ID = t.CATEGORY\n"
                          + "group by t.PRODUCT_NAME, l.\"LIMIT\"");
      
      
      
                  ResultSet rs = ps.executeQuery();
                  
                  
      
      
                  while (rs.next()) {
                      total = rs.getDouble("total");
                      category = rs.getString("category");
                      limit = rs.getDouble("lim");
                      
                      
                      dataset.setValue(total, category, limit);
                  }
      
      
      
              } catch (SQLException | ClassNotFoundException ex) {
                  Logger.getLogger(Reports.class
                          .getName()).log(Level.SEVERE, null, ex);
              }
      
              return dataset;
      
          }
      }
      I refer to this method in a button action listener. I'm just trying to set DefaultCategoryDataset and pass it to a JFreeChart. The error is at the line:
      dataset.setValue(total, category, limit);
      with message:
      java.lang.NullPointerException
      I'm sure the query works fine...
      Any ideas?


      Thanks in advance,
      Alexander.
        • 1. Re: how to return DefaultCategoryDataset
          PhHein
          You've never initialized your dataset
          while (rs.next()) {
              dataset = new DefaultCategoryDataset(); // <-------         
              total = rs.getDouble("total");
              category = rs.getString("category");
              limit = rs.getDouble("lim");                
              dataset.setValue(total, category, limit);
          } 
          • 2. Re: how to return DefaultCategoryDataset
            a.stoyanov
            Hi ,
            Thanks for reply - it works. But... this returns the last row from DB. Can i fetch all the rows to the dataset ?


            Thanks in advance,
            Alexander.
            • 3. Re: how to return DefaultCategoryDataset
              PhHein
              Probably, have a look for the API and examples. There's quite a few results on Google.
              If you're stuck, go to a JFreeChart forum and ask there.
              • 4. Re: how to return DefaultCategoryDataset
                rp0428
                >
                Thanks for reply - it works. But... this returns the last row from DB. Can i fetch all the rows to the dataset ?
                >
                One reason it returns the last row is because the code provided is creating a new instance each time through the loop
                while (rs.next()) {
                    dataset = new DefaultCategoryDataset(); // <-------         
                    total = rs.getDouble("total");
                    category = rs.getString("category");
                    limit = rs.getDouble("lim");                
                    dataset.setValue(total, category, limit);
                }
                Create the dataset BEFORE the loop
                -- create the dataset BEFORE the loop
                dataset = new DefaultCategoryDataset(); // <-------         
                while (rs.next()) {
                    total = rs.getDouble("total");
                    category = rs.getString("category");
                    limit = rs.getDouble("lim");                
                    dataset.setValue(total, category, limit);
                }
                Also the 'setValue' method will NOT add a new row to the dataset unless the 'rowKey' value you provide is different. If you provide the same value for 'rowKey' in the 'setValue' method you will replace the prior 'rowKey'/'columnKey' value with the new one.

                See the Dataset API
                http://www.jfree.org/jfreechart/api/javadoc/org/jfree/data/category/DefaultCategoryDataset.html
                >
                void setValue(double value, java.lang.Comparable rowKey, java.lang.Comparable columnKey)
                Adds or updates a value in the table and sends a DatasetChangeEvent to all registered listeners.

                void setValue(java.lang.Number value, java.lang.Comparable rowKey, java.lang.Comparable columnKey)
                Adds or updates a value in the table and sends a DatasetChangeEvent to all registered listeners.