Put 0 in arraylist if no record in table (SQLite) using JDBC by checking resultSet

Question

I know there are similar threads for same title, but my question addresses different point. I am checking resultSet to see

whether query returns any value from rating column if yes take the value and add it to arraylist if no add 0.0 into arraylist(ratings).

In my code , else never gets executed resulting into no 0.0 in arraylist(for missing dates).Is my query going wrong somewhere? Please help.

String query = "SELECT * FROM DATA WHERE (dateofday BETWEEN ? AND ?) AND id = ?";
    String mediapath = null;
    try {
        preparedStatement = connection.prepareStatement(query);
        preparedStatement.setInt(3, Controller.id_logged_in);
        preparedStatement.setString(1, datepickerfrom.getValue().toString());
        preparedStatement.setString(2, datepickerto.getValue().toString());
        resultSet = preparedStatement.executeQuery();
        boolean flag = true;
        if (resultSet.next()) {
            do {
                ratings.add(resultSet.getFloat("rating"));
                countofday++;
            }
            while (resultSet.next());
        } else {
            ratings.add(Float.valueOf(0));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        connection.close();
    }

davidxxx · Answer 1 · 2017-04-16T10:08:05.687

Here :

if (resultSet.next()) {
    do {
        ratings.add(resultSet.getFloat("rating"));
        countofday++;
    }
    while (resultSet.next());
} else {
    ratings.add(Float.valueOf(0));
}

If you have at least a result in the resultSet, you iterate over all results but you never enter in ratings.add(Float.valueOf(0)); because you enter in only if you have not result.

The first conditional statement should be removed : if (resultSet.next()) {.
You should test the value returned by resultSet.getFloat("rating").
If it is not null, do ratings.add(resultSet.getFloat("rating")); otherwise do ratings.add(Float.valueOf(0));.

For example :

while (resultSet.next());
    float ratingObj = resultSet.getFloat("rating");
    if (!resultSet.wasNull()){
       ratings.add(ratingObj);
       countofday++;
    }
    else{
       ratings.add(Float.valueOf(0));
    }
}

it is still printing `[9.505962, 7.734242, 9.67128, 5.054585]` even though daterange is of 26 days — minigeek, Apr 16 '17 at 10:05
i guess query is wrong .not sure though , this is what i want but can't understand/convert in my case http://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present — minigeek, Apr 16 '17 at 10:08

score 0 · Accepted Answer · answered Apr 16 '17 at 11:01

Temporarily, I am using this inefficient way by querying for every date . No choice.

    SimpleDateFormat myFormat = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String date1 = datepickerfrom.getValue().toString();
    String date2 = datepickerto.getValue().toString();
    try {
        Date d1 = myFormat.parse(date1);
        Date d2 = myFormat.parse(date2);

        while( d1.before(d2) ){
            d1 = addDays(d1, 1);
            allDates.add(d1);
            allDatesString.add(formatter.format(d1));
        }
        //System.out.println(allDatesString);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    for(int i = 0; i < allDatesString.size(); i++) {
        String query = "SELECT * FROM DATA WHERE dateofday  = ? AND id = ?";
        try {
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(2, Controller.id_logged_in);
            preparedStatement.setString(1, allDatesString.get(i));
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
                ratings.add(resultSet.getFloat("rating"));
            else
                ratings.add(Float.valueOf(0));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

it gives :

[0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 9.505962, 0.0, 0.0, 0.0, 0.0, 7.734242, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 9.67128, 0.0, 5.054585, 0.0, 0.0, 0.0]

re: *"No choice."* - That's silly. You are already taking the trouble to create an `allDates` list; you could just as easily create a `Map` with the dates as the keys and the values initialized to zero, loop through the original ResultSet to fill in the values, and then use `Arrays.asList(dateMap.values().toArray(new Float[0]))` to convert the map's values into an ArrayList. — Gord Thompson, Apr 18 '17 at 22:35
@GordThompson yeah i could do that too..but my main q was how to do it without looping for every date and doing with one query using between. — minigeek, Apr 19 '17 at 03:04
*"how to do it ... with one query using between"* - That is what I meant when I said to loop through "the original ResultSet". — Gord Thompson, Apr 19 '17 at 11:40

Put 0 in arraylist if no record in table (SQLite) using JDBC by checking resultSet

2 Answers2