CODEWITHSUNDEEP
javamysqlarraysjdbc
Olly
Olly

Reputation: 371

Iterating over JDBC result set repeating result on last iteration

As the title says, here is my code:

String query = "SELECT * FROM " + TABLE_UNIVERSITY_INFO + " WHERE university_code = ?";
    Connection conn = DriverManager.getConnection(DATABASE_SCHEMA, USERNAME, PASSWORD);

    PreparedStatement stmt = conn.prepareStatement(query);
    stmt.setString(1, universityCode);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
            // Get DTO from database
            summonerUniversityDTO.setUniversityName(rs.getString("university_code"));
            summonerUniversityDTO.setID(rs.getLong("summonerId"));
            summonerUniversityDTO.setSummonerName(rs.getString("summoner_name"));

            //Add all to array
            summonerUniversityDTOArray.add(summonerUniversityDTO);
            logger.info("ARRAY" + summonerUniversityDTOArray);
            count++;
        }
        logger.info(String.format("[%s] summoners were returned from the database matching university code [%s]", count, universityCode));
        universitySummonerDTO.setSingleSummonerPlayerDTOs(summonerUniversityDTOArray);
        logger.info(String.format("UniversityArrayDTO is [%s]", universitySummonerDTO));

In my database I have 3 rows that will be returned, each one with a different name and UID - ripolly, kaibai and lol.

It's getting all of the results, but when I hit my endpoint and look at what's being returned:

2016-01-24 18:25:31 INFO  DatabaseAccessor:201 - Returning University DTO from g
iven university name: [SOME]

2016-01-24 18:25:31 INFO  DatabaseAccessor:219 - ARRAY[SummonerUniversityDTO{sum
monerID=155767, summonerName='lol', universityName='SOME'}]

2016-01-24 18:25:31 INFO  DatabaseAccessor:219 - ARRAY[SummonerUniversityDTO{sum
monerID=37656713, summonerName='kaibai', universityName='SOME'}, SummonerUnivers
ityDTO{summonerID=37656713, summonerName='kaibai', universityName='SOME'}]

2016-01-24 18:25:31 INFO  DatabaseAccessor:219 - ARRAY[SummonerUniversityDTO{sum
monerID=38584682, summonerName='ripolly', universityName='SOME'}, SummonerUniver
sityDTO{summonerID=38584682, summonerName='ripolly', universityName='SOME'}, Sum
monerUniversityDTO{summonerID=38584682, summonerName='ripolly', universityName='
SOME'}]

2016-01-24 18:25:31 INFO  DatabaseAccessor:222 - [3] summoners were returned fro
m the database matching university code [SOME]

2016-01-24 18:25:31 INFO  DatabaseAccessor:224 - UniversityArrayDTO is [Universi
tySummonerDTO{singleSummonerPlayerDTOs=[SummonerUniversityDTO{summonerID=3858468
2, summonerName='ripolly', universityName='SOME'}, SummonerUniversityDTO{summone
rID=38584682, summonerName='ripolly', universityName='SOME'}, SummonerUniversity
DTO{summonerID=38584682, summonerName='ripolly', universityName='SOME'}]}]

The names are entered fine into the array, but they are being repeated in the arraylist. Is this a problem with my code, or the way i'm iterating over it/result set cursors etc. Stuck on this for a while!

Apologies my naming conventions aren't exactly simple...

Cheers

Upvotes: 0

Views: 439

Answers (2)

Olly
Olly

Reputation: 371

I was instantiating my summonerUniversityDTO outside of my while loop - rather then inside it:

while (rs.next()) {
        // Get DTO from database
        SummonerUniversityDTO summonerUniversityDTO = new SummonerUniversityDTO();
        summonerUniversityDTO.setUniversityName(rs.getString("university_code"));
        summonerUniversityDTO.setID(rs.getLong("summonerId"));
        summonerUniversityDTO.setSummonerName(rs.getString("summoner_name"));

instead of what it was:

        SummonerUniversityDTO summonerUniversityDTO = new SummonerUniversityDTO();
        while (rs.next()) {
        // Get DTO from database
        summonerUniversityDTO.setUniversityName(rs.getString("university_code"));
        summonerUniversityDTO.setID(rs.getLong("summonerId"));
        summonerUniversityDTO.setSummonerName(rs.getString("summoner_name"));

Upvotes: 0

EternalCode
EternalCode

Reputation: 26

You aren't creating a new object in each iteration of the while loop, therefore it's just editing the same object that was already in the array and adding it a 2nd and 3rd time.

The while loop should look something like:

while (rs.next()) {
        summonerUniversityDTO = new SummonerUniversityDTO();
        // Get DTO from database
        summonerUniversityDTO.setUniversityName(rs.getString("university_code"));
        summonerUniversityDTO.setID(rs.getLong("summonerId"));
        summonerUniversityDTO.setSummonerName(rs.getString("summoner_name"));

        //Add all to array
        summonerUniversityDTOArray.add(summonerUniversityDTO);
        logger.info("ARRAY" + summonerUniversityDTOArray);
        count++;
    }

Upvotes: 1

Related Questions