Results 1 to 4 of 4
  1. #1
    caraldur is offline Member
    Join Date
    Feb 2015
    Posts
    27
    Rep Power
    0

    Default ObservableList Problem

    Hello everyone,

    I've working on a javafx app for a while now and I've run into a problem that I can't figure out how to correct. I have written a mySQL query that will pull production data from a database that totals the parts produced, total of rejected parts and group them by month. Below is the query I am using.
    Java FX Code:
    BEGIN
    SELECT SUM(PressCounter) AS TotalMolded, SUM(Rejects) AS TotalRejects, MONTH(ProductionDate) AS dMonth, ProductID FROM productionlogs WHERE  ProductID = _productID AND YEAR(ProductionDate) = _year GROUP BY MONTH(ProductionDate), ProductID;
    END
    In my java application I want to display a line chart with each months production display and a tableview with the actual data displayed. I have successfully done this and below is a screenshot.
    ObservableList Problem-productproductionbymonth.jpg
    The problem I am having is not every month has production data so the observablelist I am filling with database data is missing those months. I have tried to add in the missing months while filling the observablelist but I'm not getting the desired results. I know there is probably a simple solution to this problem but I can figure it out.

    Here is the code I am using to add the missing months and hopefully this post makes sense.
    Java FX Code:
    public ObservableList<YearsProductionData> getYearsProductionData_SP(String productID, String year){
    		ObservableList<YearsProductionData> yearData = FXCollections.observableArrayList();
    		calculations calc = new calculations();
    		String query= "{call Get_Product_Production_Data_ForYear(?,?)}";
    		
    		ResultSet rs;
    		PreparedStatement pS;
    		try{
    			pS = con.prepareStatement(query);
    			pS.setString(1, productID);
    			pS.setString(2, year);
    			rs = pS.executeQuery();
    			
    			String month;
    			int count = 1;
    			boolean skip = false;
    			while(rs.next())
    			{
    				log.info("Month: " + rs.getInt("dMonth"));
    				log.info("Count: " + count);
    				if(rs.getInt("dMonth") != count)
    				{
    					yearData.addAll(new YearsProductionData(rs.getString("ProductID"),rs.getInt("TotalMolded"),0,0,calc.formatMonth(count),0.0));
    					skip = true;
    					log.info("Skip");
    					count++;
    				}
    				month = calc.formatMonth(rs.getInt("dMonth"));
    			
    				Double percentage = calc.doPercentages(String.valueOf(rs.getInt("TotalRejects")), String.valueOf(rs.getInt("TotalMolded")));
    				DecimalFormat df2 = new DecimalFormat("###.##");
    				percentage = Double.valueOf(df2.format(percentage));
    			
    				int AddedStock = rs.getInt("TotalMolded")-rs.getInt("TotalRejects");
    			
    				yearData.addAll(new YearsProductionData(rs.getString("ProductID"),rs.getInt("TotalMolded"),AddedStock,rs.getInt("TotalRejects"),month,percentage));
    				
    				if(skip == false){
    					log.info("Increase count by 1");
    					count++;
    				}
    			}
    			
    		}catch(SQLException e){
    			MessageClass message = new MessageClass();
    			
    			Alert a = new Alert(AlertType.ERROR);
    			String title = "Exception Dialog";
    			String header = "SQL Exception!";
    			String content = "There was an error while getting data from the years production data.";
    		
    			a = message.getSQLExpectionMessage(a, title, header, content, e);
    			a.showAndWait();
    		}
    		return yearData;
    	}
    Thanks in advance for any help!

    -Fred

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: ObservableList Problem

    OK, this is off the top of my head so may not work, but I tend to come at these things from the query.

    For this you'll need a pretend table representing the months. You'll probably have to ferret around for a suitable one.

    SQL Code:
    BEGIN
    SELECT SUM(PressCounter) AS TotalMolded, 
           SUM(Rejects) AS TotalRejects, 
           MONTH(ProductionDate) AS dMonth, 
           ProductID 
    FROM productionlogs 
    WHERE  ProductID = _productID AND YEAR(ProductionDate) = _year 
    GROUP BY MONTH(ProductionDate), ProductID;
    END
    So the above would be outer joined against something that listed all the months, 1-12.

    Maybe as simple as:
    SQL Code:
    ( SELECT 1 AS month
    UNION
    SELECT 2 AS month
    UNION
    SELECT 3 AS month
    UNION
    SELECT 4 AS month
    UNION
    SELECT 5 AS month
    UNION
    SELECT 6 AS month
    UNION
    SELECT 7 AS month
    UNION
    SELECT 8 AS month
    UNION
    SELECT 9 AS month
    UNION
    SELECT 10 AS month
    UNION
    SELECT 11 AS month
    UNION
    SELECT 12 AS month )
    as months
    ?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    benji2505 is offline Senior Member
    Join Date
    Sep 2014
    Location
    MA, USA
    Posts
    398
    Rep Power
    5

    Default Re: ObservableList Problem

    You could also filter the SQL resultset for results that do not make sense. Loop over the resultset that the database returns and only add those values to the ObservableList that make sense, e.g. that are larger than zero and below moon values. You would add each single result to yearData rather than adding the whole collection at once (line 36 above). If you want to go fancy you can do that with a stream.

  4. #4
    caraldur is offline Member
    Join Date
    Feb 2015
    Posts
    27
    Rep Power
    0

    Default Re: ObservableList Problem

    Quote Originally Posted by benji2505 View Post
    You could also filter the SQL resultset for results that do not make sense. Loop over the resultset that the database returns and only add those values to the ObservableList that make sense, e.g. that are larger than zero and below moon values. You would add each single result to yearData rather than adding the whole collection at once (line 36 above). If you want to go fancy you can do that with a stream.
    Thanks for the help. I decided to go the route of making an observablelist with each month and then loop through each month while comparing the month field to the resultset from the database. If a match was found then the data from the resultset would overwrite the blank entries in my observablelist of months. Below is the code I used.

    Thanks for the help.

    Java FX Code:
    public ObservableList<YearsProductionData> getYearsProductionData_SP(String productID, String year){
    		ObservableList<YearsProductionData> yearData = FXCollections.observableArrayList();
    		ObservableList<YearsProductionData> c = FXCollections.observableArrayList();
    		
    		calculations calc = new calculations();
    		String query= "{call Get_Product_Production_Data_ForYear(?,?)}";
    		
    		ResultSet rs;
    		PreparedStatement pS;
    		try{
    			pS = con.prepareStatement(query);
    			pS.setString(1, productID);
    			pS.setString(2, year);
    			rs = pS.executeQuery();
    			
    			String month;
    
    			for(int i =1; i <= 12; i++)
    			{
    				c.addAll(new YearsProductionData(productID,0,0,0,calc.formatMonth(i),0.0));
    			}
    
    			while(rs.next())
    			{
    				month = calc.formatMonth(rs.getInt("dMonth"));
    				Double percentage=0.0;
    				
    				log.info("Month: " +month +" Total Molded: " + rs.getInt("TotalMolded")+ " TotalRejects: " + rs.getInt("TotalRejects"));
    				if(rs.getInt("TotalMolded") != 0)
    				{
    					percentage = calc.doPercentages(String.valueOf(rs.getInt("TotalRejects")), String.valueOf(rs.getInt("TotalMolded")));
    					DecimalFormat df2 = new DecimalFormat("###.##");
    					percentage = Double.valueOf(df2.format(percentage));
    				}
    				int AddedStock = rs.getInt("TotalMolded")-rs.getInt("TotalRejects");
    			
    				yearData.addAll(new YearsProductionData(rs.getString("ProductID"),rs.getInt("TotalMolded"),AddedStock,rs.getInt("TotalRejects"),month,percentage));
    			}	
    				
    				for(int i=0;i <=11; i++)
    				{
    					for(int z=0;z<=(yearData.size()-1);z++)
    					{
    						if(c.get(i).getMonth().equals(yearData.get(z).getMonth()))
    						{
    							c.set(i,new YearsProductionData(yearData.get(z).getProductID(),yearData.get(z).getTotalMolded(),yearData.get(z).getPartsAddedToStock(),yearData.get(z).getTotalRejected(),yearData.get(z).getMonth(),yearData.get(z).getRejectPercentage()));
    						}
    					}
    				}
    				
    		}catch(SQLException e){
    			MessageClass message = new MessageClass();
    			
    			Alert a = new Alert(AlertType.ERROR);
    			String title = "Exception Dialog";
    			String header = "SQL Exception!";
    			String content = "There was an error while getting data from the years production data.";
    		
    			a = message.getSQLExpectionMessage(a, title, header, content, e);
    			a.showAndWait();
    		}
    		return c;
    	}

Similar Threads

  1. Replies: 5
    Last Post: 11-11-2015, 12:16 PM
  2. Replies: 1
    Last Post: 07-28-2015, 02:33 PM
  3. Replies: 3
    Last Post: 04-17-2015, 07:48 PM
  4. Replies: 0
    Last Post: 11-07-2012, 12:44 PM
  5. Replies: 9
    Last Post: 09-21-2010, 04:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •