关于mybatis传入参数一直为null的问题

2022-11-13 11:11:49 mybatis 参数传入

mybatis传入参数一直为null

1.配置方面都对的情况下，考虑连接数据库的时候是否设置了编码为utf-8，如果没设置，数据库传过来的时候有可能就是乱码，就会一直是null。

2.有可能是字段名与实体不匹配。

3.可能是有空格问题

mybatis字段为null的解决

今天在写项目的mapper.xml文件的时候，出现了个别字段查询结果为null的情况，但sql语句没有错误，仔细查看了一遍，才发现错误，现在记录下来

先讲一讲sql语句和ResultMap的顺序

首先会执行sql语句，sql语句返回字段信息，然后才是ResultMap映射字段信息。

实体类UserInfo

package com.school.oauth.endpoint.domain;
import com.fasterxml.jackson.annotation.JSONFORMat;
import com.fasterxml.jackson.annotation.jsonIgnore;
import com.school.parent.domain.BaseDomain;
import io.swagger.annotations.apiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Update;
import org.hibernate.validator.constraints.Length;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.*;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.time.ZonedDateTime;
import java.util.Date;
import java.util.List;
@Table(name = "oauth_user")
@ApiModel(value = "用户表")
@Data
public class UserInfo extends BaseDomain {
    @Id
    @GeneratedValue(generator = "JDBC")
    @ApiModelProperty(value = "用户主键，提供给其他表做外键")
    private Long userId;
    @Column(unique = true)
    @NotBlank
    @Length(max = 32)
    @ApiModelProperty(value = "用户名，必须唯一")
    private String username;
    @NotBlank
    @Length(max = 32)
    @ApiModelProperty(value = "昵称，可以重复")
    private String nickname;
    @Length(max = 128)
    @ApiModelProperty(value = "加密密码")
    private String encryptedPassWord;
    @Length(max = 32)
    @Pattern(regexp = "^[1][3,4,5,7,8][0-9]{9}$"
    ,message = "手机号码格式错误")
    @ApiModelProperty(value = "用户手机号码")
    private String phone;
    @Email
    @Length(max = 128)
    @ApiModelProperty(value = "用户邮箱")
    private String email;
    @Length(max = 8)
    @ApiModelProperty(value = "国际冠码，默认 +86")
    private String idd;
    @Length(max = 16)
    @ApiModelProperty(value = "性别：男/女")
    private String gender;
    @Length(max = 1024)
    @ApiModelProperty(value = "头像地址")
    private String avatar;
    @NotBlank
    @Length(max = 128)
    @ApiModelProperty(value = "所属学校")
    private String school;
    @DateTimeFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @ApiModelProperty(value = "账户过期时间")
    private Date accountExpiredTime;
    @DateTimeFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @ApiModelProperty(value = "账户锁定时间")
    private Date accountLockedTime;
    @DateTimeFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd hh:mm:ss")
    @ApiModelProperty(value = "凭证过期时间")
    private Date credentialsExpiredTime;
    @Column(name = "is_admin")
    @ApiModelProperty(value = "是否是管理员")
    private Boolean admin;
    @Column(name = "is_able")
    @ApiModelProperty(value = "账户是否启用")
    private Boolean able;
    @Length(max = 32)
    @NotBlank(groups = Insert.class)
    @ApiModelProperty(value = "用户注册平台：WEB(默认)/AliPay/WeChat")
    private String userType;
    @Length(max = 64)
    @ApiModelProperty(value = "微信用户识别ID")
    private String weChatUserId;
    @Length(max = 64)
    @ApiModelProperty(value = "支付宝用户识别ID")
    private String aliPayUserId;
    @Transient
    @NotBlank(groups = Insert.class)
    @ApiModelProperty(value = "密码")
    private String password;
    
    @Transient
    @ApiModelProperty(value = "用户角色集合：guest/user/admin")
    private List<Role> roleList;
    public UserInfo() {
    }
}

UserMapper.xml错误代码

<resultMap id="BaseMap" type="com.school.oauth.endpoint.domain.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="nickname" property="nickname"/>
        <result column="encrypted_password" property="encryptedPassword"/>
        <result column="phone" property="phone"/>
        <result column="email" property="email"/>
        <result column="idd" property="idd"/>
        <result column="gender" property="gender"/>
        <result column="avatar" property="avatar"/>
        <result column="school" property="school"/>
        <result column="account_expired_time" property="accountExpiredTime"/>
        <result column="account_locked_time" property="accountLockedTime"/>
        <result column="credentials_expired_time" property="credentialsExpiredTime"/>
        <result column="is_admin" property="admin" jdbcType="TINYINT"/>
        <result column="is_able" property="able" jdbcType="TINYINT"/>
        <result column="user_type" property="userType"/>
        <result column="we_char_user_id" property="weChatUserId"/>
        <result column="ali_pay_user_id" property="aliPayUserId"/>
        <collection property="roleList" ofType="com.school.oauth.endpoint.domain.Role"
                    column="user_id" select="com.school.oauth.endpoint.mapper.RoleMapper.selectRoleByUserId">
        </collection>
    </resultMap>
    <select id="selectByUsername" resultMap="BaseMap">
        SELECT
            u.user_id,
            u.username,
            u.nickname,
            u.encrypted_password,
            u.phone,
            u.email,
            u.idd,
            u.gender,
            u.avatar,
            u.school,
            u.account_expired_time,
            u.account_locked_time,
            u.credentials_expired_time,
            u.is_admin AS admin,
            u.is_able AS able,
            u.user_type,
            u.we_chat_user_id,
            u.ali_pay_user_id
        FROM oauth_user u
        WHERE u.username = #{username}
    </select>

这样查询出来的User对象的admin和able字段为空。这是因为我们的selectByUsername 引用了我们定义的ResultMap。

注意看sql语句，其中有两个字段我们取了别名

    u.is_admin AS admin,
        u.is_able AS able,

也就是说，当sql语句查询出来的is_admin字段和is_able 字段已经变为了admin字段和able字段，而ResultMap里面这两个字段的映射：

   <result column="is_admin" property="admin" jdbcType="TINYINT"/>
        <result column="is_able" property="able" jdbcType="TINYINT"/>

当ResultMap去映射字段信息的时候，发现找不到is_admin字段和is_able字段，因为在sql语句我们已经为这两个字段去了别名，现在这两个字段叫admin和able。所有ResultMap映射到实体类的时候，就会出现这两个字段为空的情况。

正确写法

去掉sql语句里面的别名，就可以咯。这样resultMap就可以找到相应的字段了。

<resultMap id="BaseMap" type="com.school.oauth.endpoint.domain.UserInfo">
        <id column="user_id" property="userId"/>
        <result column="username" property="username"/>
        <result column="nickname" property="nickname"/>
        <result column="encrypted_password" property="encryptedPassword"/>
        <result column="phone" property="phone"/>
        <result column="email" property="email"/>
        <result column="idd" property="idd"/>
        <result column="gender" property="gender"/>
        <result column="avatar" property="avatar"/>
        <result column="school" property="school"/>
        <result column="account_expired_time" property="accountExpiredTime"/>
        <result column="account_locked_time" property="accountLockedTime"/>
        <result column="credentials_expired_time" property="credentialsExpiredTime"/>
        <result column="is_admin" property="admin" jdbcType="TINYINT"/>
        <result column="is_able" property="able" jdbcType="TINYINT"/>
        <result column="user_type" property="userType"/>
        <result column="we_char_user_id" property="weChatUserId"/>
        <result column="ali_pay_user_id" property="aliPayUserId"/>
        <collection property="roleList" ofType="com.school.oauth.endpoint.domain.Role"
                    column="user_id" select="com.school.oauth.endpoint.mapper.RoleMapper.selectRoleByUserId">
        </collection>
    </resultMap>
    <select id="selectByUsername" resultMap="BaseMap">
        SELECT
            u.user_id,
            u.username,
            u.nickname,
            u.encrypted_password,
            u.phone,
            u.email,
            u.idd,
            u.gender,
            u.avatar,
            u.school,
            u.account_expired_time,
            u.account_locked_time,
            u.credentials_expired_time,
            u.is_admin,
            u.is_able,
            u.user_type,
            u.we_chat_user_id,
            u.ali_pay_user_id
        FROM oauth_user u
        WHERE u.username = #{username}
    </select>

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。

相关文章