Hi i need help to retrieve data from DB. I am trying to retrieve from database the number of cars that we registered each month in 2013.Based on that i will draw a line chart.so on my x-axis will be cars, and y-axis is number.
Note: i have different models of cars in my database like audi,bmw,toyota etc..
The data i want is :
in Jan bmw:12 , toyota:5, audi:0 etc...
in feb bmw:2 ,toyota:18,audi:23 etc...
So my problem is the method that i wrote is not giving me the right amount of cars from database. It gives me the following:
in Jan bmw:13,toyota:2,audi:1 etc...
in Feb bmw:13,toyota:2,audi:1 etc...
in Mar bmw:13,toyota:2,audi:1 etc....
Problems:
1. Firstly, for the month of Jan, it's not giving me the right amount that i have in my database
2. Secondly,for all month it gives me the same amount... which is not good
Drawing the chart is not a problem. The amount to be retrieve from DB is not good.
As a result it draws all the line chart on the same coordinates. I think the problem is in my query.Anyone can help on this?
Platform used: Java, primefaces 4.0, Jsf 2.0 (hibernate,spring)
public CartesianChartModel cars() {
CartesianChartModel lModel = new CartesianChartModel();
List<car> cars = new ArrayList<car>(
carCodeDAO
.findCarCode(Constants.CAR));
Map<String, Integer> map = new HashMap<String, Integer>();
for (carCode c : c1) {
LineChartSeries chart = new LineChartSeries();
chart.setLabel(c.getCarDesc()); //getting the type of car.
for (Month month : Month.values()) {
Calendar dateS = new GregorianCalendar();
Calendar dateE = new GregorianCalendar();
dateS.set(Calendar.getInstance().get(Calendar.YEAR), month.getCode(), 1);
dateE.set(Calendar.getInstance().get(Calendar.YEAR), month.getCode(), dateS.getActualMaximum(Calendar.DAY_OF_MONTH));
List<Cars> carList = new ArrayList<Cars>(carListDAO.findCarListByMonth(dateS, dateE, c.getCarCode()));
chart.set(month.getLabel(), carList.size());
}
lModel.addSeries(chart);
}
return lModel;
}
This is my namedQuery:
@NamedQuery(name = "findCarListByMonth", query = "select mycarList from carList mycarList where mycarList.Date between :startDate and :endDate")