CODEWITHSUNDEEP
javamysqljdbcjfreechart
Krellex
Krellex

Reputation: 753

Retrieving from database to display as line graph

I am trying to display my data from my database. I am currently doing one query execution at a time to get one particular result for each year. I need to do this for all the columns in my database however this will make the code incredibly messy. Is there an easier way to read the data in from the database to display to a line graph?

Current code snippet:

DefaultCategoryDataset dataset = new DefaultCategoryDataset();

String sql = "SELECT SUM(all_motor_vehicles), Year FROM Vehicle WHERE Year = 2005";
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
amount = Integer.parseInt(rs.getString(1)); //get the result
yearFromDB = rs.getString(2);
System.out.println(amount + " " + yearFromDB); //print result


int amount2006;
String yearFromDB2006;
String sql2 = "SELECT SUM(all_motor_vehicles), Year FROM Vehicle WHERE Year = 2006";
pst = con.prepareStatement(sql2);
rs = pst.executeQuery();
amount2006 = Integer.parseInt(rs.getString(1)); //get the result
yearFromDB2006 = rs.getString(2);
System.out.println(amount2006 + " " + yearFromDB2006); //print result

int amount2007;
String yearFromDB2007;
String sql3 = "SELECT SUM(all_motor_vehicles), Year FROM Vehicle WHERE Year = 2007";
pst = con.prepareStatement(sql3);
rs = pst.executeQuery();
amount2007 = Integer.parseInt(rs.getString(1)); //get the result
yearFromDB2007 = rs.getString(2);
System.out.println(amount2007 + " " + yearFromDB2007); //print result

dataset.addValue(amount, allMotorVehicles, yearFromDB);
dataset.addValue(amount2006, allMotorVehicles, yearFromDB2006);
dataset.addValue(amount2007, allMotorVehicles, yearFromDB2007);

JFreeChart chart = ChartFactory.createLineChart("Traffic by Vehicle type", "Vehicle", `"Amount",dataset);`

Snippet of information in database:

enter image description here

Implementing suggested Query giving SQL Error:

Executing query in DB returns:

enter image description here

I can retrieve the year 2005 using rs.getString(1) and it's corresponding number using rs.getString(2) but when I try to retrieve year 2006 and it's number through rs.getString(3) and rs.getString(4) I get an SQLException Error

String sql = "SELECT Year, SUM(all_motor_vehicles) AS allmotor\n" +
          

"FROM Vehicle\n"
            + "WHERE Year IN (2005, 2006, 2007)\n"
            + "GROUP BY YEAR;";
    pst = con.prepareStatement(sql);
    rs = pst.executeQuery();

    amount = Integer.parseInt(rs.getString(2)); //retrieves amount
    yearFromDB = rs.getString(1);
    System.out.println(amount + " " + yearFromDB); //retrieves year (2005)
    
    System.out.println(rs.getString(3) + " " + rs.getString(4)); //gives me an SQL ERROR

Upvotes: 0

Views: 263

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521379

You should instead do a single GROUP BY query in which you aggregate sales by year:

SELECT Year, SUM(all_motor_vehicles) AS all_sales
FROM Vehicle
WHERE Year IN (2005, 2006, 2007)  -- or whatever years you want in your report
GROUP BY Year;

I won't bother giving you boilerplate JDBC code for the above query, as you seem to already have the basics of that mastered. Note that I give an alias to the SUM(), in case you would want to access the result set in Java using it.

Upvotes: 1

Related Questions