1. Overview
When working with Spring’s JdbcTemplate, we often need to convert the query result to a List of POJOs. However, a common pitfall we might encounter is the IncorrectResultSetColumnCountException.
This usually happens when we misuse the JdbcTemplate‘s queryForList() method, especially when we try to map it directly to a custom POJO class.
In this tutorial, we’ll explore what causes this, how to use queryForList() correctly, and how to map the query result to a custom class.
2. Introduction to the Problem
Let’s say we have a STUDENT_TBL database table and it contains four students’ data:
CREATE TABLE STUDENT_TBL
(
ID int NOT NULL PRIMARY KEY,
NAME varchar(255),
MAJOR varchar(255)
);
INSERT INTO STUDENT_TBL VALUES (1, 'Kai', 'Computer Science');
INSERT INTO STUDENT_TBL VALUES (2, 'Eric', 'Computer Science');
INSERT INTO STUDENT_TBL VALUES (3, 'Kevin', 'Banking');
INSERT INTO STUDENT_TBL VALUES (4, 'Liam', 'Law');
Also, we’ve created a Student POJO class:
public class Student {
private Integer id;
private String name;
private String major;
public Student() {
}
public Student(Integer id, String name, String major) {
this.id = id;
this.name = name;
this.major = major;
}
// getter, setter, equals, and hashCode methods are omitted ...
}
For simplicity, we’ll skip showing related Spring configurations, such as data source configuration.
Now, we want to use JdbcTemplate to query the data from the STUDENT_TBL table and convert each entry to a Student object to obtain a List of Student objects.
After reviewing the JdbcTemplate API, queryForList () seems to handle this task, just as its name suggests. Therefore, we may come up with something like:
List<Student> students = jdbcTemplate.queryForList("SELECT * FROM STUDENT_TBL", Student.class);
However, when we test it, this line throws an exception:
assertThrows(IncorrectResultSetColumnCountException.class, () -> jdbcTemplate.queryForList("SELECT * FROM STUDENT_TBL", Student.class));
This is a common pitfall of using queryForList() with a Class. So next, let’s understand why queryForList() throws IncorrectResultSetColumnCountException in this case and explore the proper way to achieve our goal.
3. The queryForList() Method
To understand why queryForList() throws the exception, we must understand what this method does.
JdbcTemplate offers two queryForList() methods:
- queryForList(String sql, Class<T> elementType) returns List<T>
- queryForList(String sql) returns List<Map<String, Object>>
Next, let’s take a closer look at them.
3.1. Retrieving a Single Column From the Database as a List of Values
Calling the queryForList(String sql, Class<T> elementType) isn’t meant for mapping rows to objects. Instead, it’s intended for queries that return a single column. For example, in our example, we can use if to obtain a list of ID or NAME values:
List<String> names = jdbcTemplate.queryForList("SELECT NAME FROM STUDENT_TBL", String.class);
assertEquals(List.of("Kai", "Eric", "Kevin", "Liam"), names);
List<Integer> ids = jdbcTemplate.queryForList("SELECT ID FROM STUDENT_TBL", Integer.class);
assertEquals(List.of(1, 2, 3, 4), ids);
As the example above shows, the second parameter Class<T> elementType indicates the type of the single column we are querying:
- NAME – String.class
- ID – Integer.class
As queryForList(String sql, Class<T> elementType) is for single-column queries only, it isn’t difficult to understand why it throws IncorrectResultSetColumnCountException when we try to use it for multi-column queries.
3.2. Retrieving Rows of Multiple Columns From the Database as a List of Maps
The other queryForList() only accepts one sql parameter. It executes a SQL query and returns the results as a List<Map<String, Object>>. Each row is represented as a Map, where the column names are the keys.
Next, let’s see a couple of examples:
List<Map<String, Object>> nameMajorRowMaps = jdbcTemplate.queryForList("SELECT NAME, MAJOR FROM STUDENT_TBL");
assertEquals(List.of(
Map.of("NAME", "Kai", "MAJOR", "Computer Science"),
Map.of("NAME", "Eric", "MAJOR", "Computer Science"),
Map.of("NAME", "Kevin", "MAJOR", "Banking"),
Map.of("NAME", "Liam", "MAJOR", "Law")
), nameMajorRowMaps);
In this example, we queried NAME and MAJOR from the STUDENT_TBL table using queryForList(). As a result, each row from the database becomes a Map object.
Similarly, we can select all columns from a table and access any columns by name:
List<Map<String, Object>> rowMaps = jdbcTemplate.queryForList("SELECT * FROM STUDENT_TBL");
assertEquals(List.of(
Map.of("ID", 1, "NAME", "Kai", "MAJOR", "Computer Science"),
Map.of("ID", 2, "NAME", "Eric", "MAJOR", "Computer Science"),
Map.of("ID", 3, "NAME", "Kevin", "MAJOR", "Banking"),
Map.of("ID", 4, "NAME", "Liam", "MAJOR", "Law")
), rowMaps);
As we can see, queryForList(sql) offers a quick way to fetch rows with multiple columns directly from the database without creating custom classes.
4. Mapping Each Row to a Student Object
Now we’ve understood the proper usage of JdbcTemplate‘s queryForList() methods. However, we haven’t achieved our goal since we aim to convert each database row to a Student and get a List of Student objects.
We should use the query() method with a RowMapper to map each row to a Student object. For example, we can employ the convenient built-in BeanPropertyRowMapper class, which works by matching column names from the database to the property names of a Java class:
List<Student> expected = List.of(
new Student(1, "Kai", "Computer Science"),
new Student(2, "Eric", "Computer Science"),
new Student(3, "Kevin", "Banking"),
new Student(4, "Liam", "Law")
);
List<Student> students = jdbcTemplate.query("SELECT * FROM STUDENT_TBL", new BeanPropertyRowMapper<>(Student.class));
assertEquals(expected, students);
As the example shows, BeanPropertyRowMapper saves us from writing custom RowMapper code and is ideal when column and field names match.
5. Conclusion
In this article, we’ve understood why queryForList() isn’t meant for object mapping and explored how to use BeanPropertyRowMapper to map a row in the database to a custom class.
As always, the complete source code for the examples is available over on GitHub.
