網頁

2020/12/16

Spring JdbcTemplate query BeanPropertyRowMapper return List elements are null

今天同事和我分享關於BeanPropertyRowMapper回傳List<String>長度大於零但內容為空的現象。

同事發現用NamedParameterJdbcTemplate.query()搭配BeanPropertyRowMapper<String.class>回傳的List不是空的且長度正確但裡面的元素卻是null。


測試環境:

  • Spring Boot 2.3.2.RELEASE
  • Spring Core 5.2.8.RELEASE

例如資料表EMPLOYEE有資料如下。

+----+------+-----+
| ID | NAME | AGE |
+----+------+-----+
|  1 | John |  30 |
|  2 | Iris |  31 |
|  3 | Dave |  25 |
|  4 | Marl |  19 |
+----+------+-----+

下面EmployeeDao使用NamedParameterJdbcTemplate查詢資料。

EmployeeDao

package com.abc.demo.entity.dao;

import com.abc.demo.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<Employee> getEmployeeListByAboveAge(int age) {

        String sql = "select * from employee e where age > :age order by id";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("age", age);

        return namedParameterJdbcTemplate.query(
                sql,
                params,
                new BeanPropertyRowMapper<>(Employee.class));
    }

    public List<String> getNameListByAboveAge(int age) {

        String sql = "select e.name from employee e where age > :age order by id";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("age", age);

        return namedParameterJdbcTemplate.query(
                sql,
                params,
                new BeanPropertyRowMapper<>(String.class));
    }
    
}

依條件getNameListByAboveAge()回傳的List長度為3,但裡面的elements卻都是空字串。原因出在BeanPropertyRowMapper是用泛型類別的屬性名稱去對映資料表的欄位名稱塞值,但BeanPropertyRowMapper<String.class>String並沒有屬性來對應欄位名稱NAME,又BeanPropertyRowMapper可能利用反射產生裝載查詢結果的物件,也就是產生了String又無法正確對映塞值所以造成上述結果。


測試如下。

EmployeeRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
import com.abc.demo.entity.dao.EmployeeDao;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class EmployeeRepositoryTests {

    @Autowired
    private EmployeeDao employeeDao;

    @Test
    void getEmployeeListByAboveAge_returnListLengthIs3WithCorrectName() {

        List<Employee> resultList = employeeDao.getEmployeeListByAboveAge(20);

        System.out.println(resultList); // [Employee(id=1, name=John, age=30), Employee(id=2, name=Iris, age=31), Employee(id=3, name=Dave, age=25)]

        for (Employee employee : resultList) {
            System.out.println(employee.getName());
        }

        Assertions.assertAll(
                () -> Assertions.assertEquals(3, resultList.size()),
                () -> Assertions.assertEquals("John", resultList.get(0).getName()),
                () -> Assertions.assertEquals("Iris", resultList.get(1).getName()),
                () -> Assertions.assertEquals("Dave", resultList.get(2).getName())
        );

    }

    @Test
    void getNameListByAboveAge_returnListLengthIs3ButElementsAreNull() {

        List<String> resultList = employeeDao.getNameListByAboveAge(20);

        System.out.println(resultList); // [, , ]

        for (String name : resultList) {
            System.out.println(name);
        }

        Assertions.assertAll(
                () -> Assertions.assertEquals(3, resultList.size()),
                () -> Assertions.assertEquals(0, resultList.get(0).length()),
                () -> Assertions.assertEquals(0, resultList.get(1).length()),
                () -> Assertions.assertEquals(0, resultList.get(2).length())
        );

    }

}

測試getEmployeeListByAboveAge_returnListLengthIs3WithCorrectName印出以下。

[Employee(id=1, name=John, age=30), Employee(id=2, name=Iris, age=31), Employee(id=3, name=Dave, age=25)]
John
Iris
Dave

測試getNameListByAboveAge_returnListLengthIs3ButElementsAreNull印出以下。

[, , ]




沒有留言:

張貼留言