mybatis 查询之神坑

港控/mmm° 2021-12-23 16:07 236阅读 0赞

先看一个示例：

数据表数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 1][]

mybatis类和查询语句：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 2][]

1. 当UserInfoMap中所有字段(包含association)都为NULL的话，getUserInfo的返回结果是个null，**即使查询的记录存在**！运行结果如下：

2019-06-26 10:50:00.800 DEBUG 95032 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : ==>  Preparing: select name,phone,info_id from d_user where id = ? 
    2019-06-26 10:50:00.854 DEBUG 95032 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : ==> Parameters: 1(Integer)
    2019-06-26 10:50:00.954 DEBUG 95032 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : ====>  Preparing: select id, card_id, birth, weight, height from d_info where id = ? 
    2019-06-26 10:50:00.955 DEBUG 95032 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : ====> Parameters: 0(Integer)
    2019-06-26 10:50:00.956 DEBUG 95032 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : <====      Total: 0
    2019-06-26 10:50:00.957 DEBUG 95032 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : <==      Total: 1
    null

2. 当UserInfoMap中有字段不为NULL的时候，getUserInfo返回的结果才不为null(前提查询的记录存在)，如d\_user表id=1中，为phone添加值"x",如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 3][]

这时再运行测试程序，结果如下：

2019-06-26 11:01:20.794 DEBUG 95861 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : ==>  Preparing: select name,phone,info_id from d_user where id = ? 
    2019-06-26 11:01:20.856 DEBUG 95861 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : ==> Parameters: 1(Integer)
    2019-06-26 11:01:20.965 DEBUG 95861 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : ====>  Preparing: select id, card_id, birth, weight, height from d_info where id = ? 
    2019-06-26 11:01:20.966 DEBUG 95861 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : ====> Parameters: 0(Integer)
    2019-06-26 11:01:20.967 DEBUG 95861 --- [           main] c.z.m.d.m.InfoMapper.selectByPrimaryKey  : <====      Total: 0
    2019-06-26 11:01:20.968 DEBUG 95861 --- [           main] com.zcm.mybatis.dao.UserDao.getUserInfo  : <==      Total: 1
    {"phone":"x"}

所以综合本测试案例，得出的最终结果是：

1. 记录存在时，不一定返回的对象一定不为null, 还要看查询的字段**是否全部为NULL**，或者说**是否有不为NULL的字段**！

2. 这个测试结果跟是否有关联查询无关!

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70]: /images/20211223/72631147e8c84a7881cc24f1ecf464a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 1]: /images/20211223/aae44d44650b4bbb8f8db8ee90e6f8e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 2]: /images/20211223/f15b4d56c3eb4bf287fac9f7cb29ba2a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0pvZUJsYWNrenFx_size_16_color_FFFFFF_t_70 3]: /images/20211223/21958dfbb99f4cf1ae1f9ce101e0a321.png