[FIXED] Spring JDBC template throws an Incorrect result size exception

Issue

I am getting an exception when I run the jdbctemplate to get the id from my table. The exception is:

org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0

I have the script as below:

CREATE TABLE LOGIN(
PERSON_ID SERIAL PRIMARY KEY, 
USERNAME VARCHAR(20) CHECK (USERNAME IS NOT NULL),
PASSWORD VARCHAR(20) CHECK (PASSWORD IS NOT NULL)
);

and the jdbctemplate code is:

@Override
public int getPersonId(UsernamePassword usernamePassword) {
    return jdbcTemplate.queryForObject("SELECT PERSON_ID FROM LOGIN WHERE USERNAME = ? AND PASSWORD = ?", Integer.class,
            usernamePassword.getUser_name(), usernamePassword.getPassword());

}

I also tried other methods that jdbctemplate provides, but I had no luck. I will appreciate any help. Thanks.

Solution

JdbcTemplate's queryForObject expects that executed query will return only one row. If you get 0 rows or more than 1 row that will result in IncorrectResultSizeDataAccessException.

I guess in your case, queryForObject is returning o rows or more than 1 row,

So if you don't want to catch this IncorrectResultSizeDataAccessException, go for query method instead.

Answered By - shankarsh15
Answer Checked By - David Goodson (JavaFixing Volunteer)