Spring jdbcTemplate OneToMany

user_6561309 0
Viewed 964

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

spring-jdbc jdbctemplate spring java
2 Answers

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.

user_1445780

Another approach in your case could be:

@Override
public UserDetails loadUserByUsername(String userName) throws UsernameNotFoundException {
   return ofNullable(userName)
            .map(u -> namedParameterJdbcTemplate.query(
                    "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",
                  Map.of("username", userName),
                  UserMapper.userWithRolesResultExtractor)
            )
            .orElseThrow(() -> new UsernameNotFoundException(
                format("Given username: %s does not exist in database", username)));
}

Creating the following mapper in another class (to reuse it):

@UtilityClass
public class UserMapper {
   public static final ResultSetExtractor<User> userWithRolesResultExtractor = (resultSet) -> {
      User user = null;
      while (resultSet.next()) {
        if (resultSet.isFirst()) {
            user = new User(resultSet.getString("username"),
                      resultSet.getString("password")
                      new HashSet<>());
        }
        user.getRoles().add(new SimpleGrantedAuthority(rs.getString("authority"))));
      }
      return user;
    };
}

As Sebastiaan mentioned there are several ORMs that help you to write less code, however, with a worse performance.

PD. In my example, I have used Set instead of List to avoid duplicates.

user_7311360
Related