Column not found in JDBC join method

Question

I tried several options, but every time get the same error:

PreparedStatementCallback; uncategorized SQLException for SQL [SELECT

• FROM GardeningPost limit ? offset ?]; SQL state [S0022]; error code [0]; Column 'user_image_url' not found.

@Override
public List<GardeningPost> getAllGardeningPosts(int pageSize, int offset) {
    return jdbcTemplate.query("SELECT GardeningPost.*, Users.image_url AS user_image_url " +
            "FROM GardeningPost " +
            "INNER JOIN Users ON GardeningPost.users_gardening_post_id = Users.id " +
            "LIMIT ? OFFSET ?", gardeningPostRowMapper, pageSize, offset);
}

Since I'm using only JDBC, I didn't modify my schema.sql which is:

CREATE TABLE Users
(
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email      VARCHAR(100) NOT NULL,
    password   VARCHAR(255) DEFAULT NULL,
    enabled    BOOLEAN DEFAULT FALSE,
    non_locked BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    image_url  VARCHAR(255) DEFAULT 'https://cdn-icons-png.flaticon.com/512/149/149071.png',
    CONSTRAINT UQ_Users_Email UNIQUE (email)
); 
CREATE TABLE GardeningPost (
                      id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                      date DATETIME DEFAULT CURRENT_TIMESTAMP,
                      title VARCHAR(60) NOT NULL,
                      description TEXT,
                      tag VARCHAR(50),
                      likes INT,
                      view_count INT,
                      img_url  VARCHAR(255),
                      users_gardening_post_id BIGINT UNSIGNED NOT NULL,
                      FOREIGN KEY (users_gardening_post_id) REFERENCES Users(id)
);

I added a new column user_image_url in the class directly:

@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
@JsonInclude(NON_DEFAULT)
public class GardeningPost {
    private Long id;
    @JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss")
    private LocalDateTime date;
    private String title;
    private String description;
    private String tag;
    private Long likes;
    private Long view_count;
    private String img_url;
    private String user_image_url;
}

The reason I created the method is because I want to use the relation and provide in controller response which would include in GardeningPost also user_image_url as user profile picture.

Answer 1

I tried your code and didn't reproduce the same error.Here is my code:

@Repository
public class GardeningPostRepository {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public GardeningPostRepository(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void test() {
        RowMapper<GardeningPost> gardeningPostRowMapper = (rs, i) -> {
            GardeningPost post = new GardeningPost();
            post.setUser_image_url(rs.getString("user_image_url"));
            return post;
        };
        List<GardeningPost> postList = jdbcTemplate.query("SELECT GardeningPost.*, Users.image_url AS user_image_url " +
                "FROM GardeningPost " +
                "INNER JOIN Users ON GardeningPost.users_gardening_post_id = Users.id ", gardeningPostRowMapper);
        System.out.println(postList);
    }
}

Since your error sql script is SELECT * FROM GardeningPost limit ? offset ? and it's not the same one in getAllGardeningPosts method, I guess you called another method, and used the wrong rowMapper that tries to map user_image_url from an non-existent column in GardeningPost table.