Spring jdbcTemplate OneToMany

Question

I am getting the following error, which suggests that I am not calling next() on the result set, but I am as far as I can see (i.e. a debug breakpoint stops on rs.next() and populates as expected).

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT m.username, m.password, t.name as authority FROM members m JOIN administrator a ON a.member_id = m.member_id JOIN admin_type t ON t.admin_type_id = a.admin_type_id WHERE m.username = ?]; SQL state [24000]; error code [0]; ResultSet not positioned properly, perhaps you need to call next.; nested exception is org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.] with root cause

org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.

Code

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
        String sql = "SELECT m.username, m.password, t.name as authority FROM members m " +
                "JOIN administrator a ON a.member_id = m.member_id " +
                "JOIN admin_type t ON t.admin_type_id = a.admin_type_id " +
                "WHERE m.username = :userName";
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("userName", userName);
        List<User> users = namedParameterJdbcTemplate.query(sql, namedParameters, new UserDetailsRowMapper());
        return users.get(0);
    }

    class UserDetailsRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserResultSetExtractor extractor = new UserResultSetExtractor();
            return extractor.extractData(rs);
        }
    }

    class UserResultSetExtractor implements ResultSetExtractor {
        public User extractData(ResultSet rs) throws SQLException, DataAccessException {
            Collection<SimpleGrantedAuthority> roles = new ArrayList<>();
            while (rs.next()) {
                String authority = rs.getString("authority");
                roles.add( new SimpleGrantedAuthority(authority));
            }
            User user = new User(rs.getString("username"), rs.getString("password"), roles);
            return user;
        }
    }

I am not sure why I get the error. It could be do to the fact that my logic is incorrect, in that extractData() is called for each row in the result set.

Question

How should I do the above to populate the User object that has a one-to-many relationship with authorities?

 +-----------+     +---------------+     +---------------+
 | Member    |     | administrator |     |  admin_type   |
 +-----------+     +---------------+     +---------------+
 | member_id |     | admin_id      |     | admin_type_id |
 | username  |     | member_id     |     |   name        |
 | password  |     | admin_type_id |     +---------------+ 
 +-----------+     +---------------+

SQL

SELECT m.username, m.password, t.name as authority FROM members m 
JOIN administrator a ON a.member_id = m.member_id 
JOIN admin_type t ON t.admin_type_id = a.admin_type_id 
WHERE m.username = 'richardmarais';

Result

Answer 1

In your code:

while (rs.next()) {
    String authority = rs.getString("authority");
    roles.add( new SimpleGrantedAuthority(authority));
}
User user = new User(rs.getString("username"), rs.getString("password"), roles);
return user;

You're calling rs.getString outside of the while loop, so potentially even without rs.next() ever being true. But more importantly, by the time you DO break out of the loop, rs.next() has returned false so your get won't work anymore. You didn't mention on which line the exception is thrown but I'd say this is the most likely cause.

You should just populate the user information at any time while you're getting the authorities, for example on the first row. But not wait until you already processed all rows.

For example like this:

public User extractData(ResultSet rs) throws SQLException, DataAccessException {
    Collection<SimpleGrantedAuthority> roles = new ArrayList<>();
    User user = null;
    while (rs.next()) {
        if (user == null) {
            user = new User(rs.getString("username"), rs.getString("password"), roles);
        }
        String authority = rs.getString("authority");
        roles.add( new SimpleGrantedAuthority(authority));
    }
    if (user == null) {
        throw new MyException("User not found");
    }
    return user;
}

Alternatively, just use a framework like Hibernate for this kind of boilerplate code.