使用MyBatis实现会员登录时selectOne查询返回null问题求助

问题描述 #

配置了简单的会员登录功能，DAO层中sqlSession.selectOne方法返回null，但注册功能的insert语句可正常执行，需要找出登录查询无结果的原因及解决方案。

相关代码与信息 #

loginPage.html #

<form action="/memberLogin" method="post" class="login100-form validate-form">
    <div class="wrap-input100 validate-input m-b-26" data-validate="Username is required">
        <span class="label-input100">Username</span>
        <input class="input100" type="text" name="member_name" placeholder="Enter username">
        <span class="focus-input100"></span>
    </div>

    <div class="wrap-input100 validate-input m-b-18" data-validate = "Password is required">
        <span class="label-input100">Password</span>
        <input class="input100" type="password" name="member_pwd" placeholder="Enter password">
        <span class="focus-input100"></span>
    </div>

    <div class="flex-sb-m w-full p-b-30">
        <div class="contact100-form-checkbox">
            <input class="input-checkbox100" id="ckb1" type="checkbox" name="remember-me">
            <label class="label-checkbox100" for="ckb1">
                Remember me
            </label>
        </div>

        <div>
            <a href="#" class="txt1">
                Forgot Password?
            </a>
        </div>
    </div>

    <div class="container-login100-form-btn">
        <button type="submit" class="login100-form-btn">
            login
        </button>
        <button type="button" id = "loginBtn" class="login100-form-btn"><a href="/signUp">
            signUp</a>
        </button>
    </div>
</form>

MemberController #

@PostMapping(value = "/memberLogin")
public String login(MemberDTO memberDTO, HttpServletRequest request) throws Exception {
    HttpSession session = request.getSession();
    session.setMaxInactiveInterval(3600);
    System.out.println("세션 아이디 : " + session.getId());
    System.out.println("세션 유효시간 : " + session.getMaxInactiveInterval());

    System.out.println("memberDTO : " + memberDTO);
    MemberDTO member = memberService.login(memberDTO);

    System.out.println("member : " + member);

    if (member != null){
        session.setAttribute("member", member);
        System.out.println("로그인 성공");

        return "redirect:/";
    } else {
        System.out.println("로그인 실패");
        return "redirect:login";
    }
}

MemberService #

@Override
public MemberDTO login(MemberDTO memberDTO) throws Exception {
    System.out.println("login Service : " + memberDTO);
    return memberDAO.login(memberDTO);
}

MemberDAO #

@Override
public MemberDTO login(MemberDTO memberDTO) throws Exception {
    MemberDTO member = null;
    System.out.println("DAO : " + memberDTO);
    member =  sqlSession.selectOne(NAMESPACE + "loginCheck", memberDTO);
    System.out.println("dao : " + member);
    return sqlSession.selectOne(NAMESPACE + "loginCheck", memberDTO);
}

MemberDTO #

public class MemberDTO {
    private String member_name;
    private String member_pwd;
    private String member_email;
    private String member_role;
    private Date member_register_datetime;

    public String getMember_name() {
        return member_name;
    }

    public void setMember_name(String member_name) {
        this.member_name = member_name;
    }

    public String getMember_pwd() {
        return member_pwd;
    }

    public void setMember_pwd(String member_pwd) {
        this.member_pwd = member_pwd;
    }

    public String getMember_email() {
        return member_email;
    }

    public void setMember_email(String member_email) {
        this.member_email = member_email;
    }

    public String getMember_role() {
        return member_role;
    }

    public void setMember_role(String member_role) {
        this.member_role = member_role;
    }

    public Date getMember_register_datetime() {
        return member_register_datetime;
    }

    public void setMember_register_datetime(Date member_register_datetime) {
        this.member_register_datetime = member_register_datetime;
    }

    @Override
    public String toString() {
        return "MemberDTO{" +
                "member_name='" + member_name + '\'' +
                ", member_pwd='" + member_pwd + '\'' +
                ", member_email='" + member_email + '\'' +
                ", member_role='" + member_role + '\'' +
                ", member_register_datetime=" + member_register_datetime +
                '}';
    }
}

MemberMapper.xml #

<mapper namespace="member">

    <insert id = "signUp" parameterType="com.withstudy.domain.MemberDTO">
        insert into MEMBER
        values (#{member_email, jdbcType = VARCHAR}, #{member_pwd, jdbcType = VARCHAR},  #{member_name, jdbcType = VARCHAR}, 'General', sysdate)
    </insert>

    <select id = "loginCheck" parameterType="com.withstudy.domain.MemberDTO" resultType="com.withstudy.domain.MemberDTO">
        SELECT * FROM MEMBER
        WHERE MEMBER_NAME = #{member_name, jdbcType = VARCHAR}
        AND MEMBER_PWD = #{member_pwd, jdbcType = VARCHAR}
    </select>
</mapper>

数据库与控制台信息 #

• Oracle数据库中MEMBER表包含字段：MEMBER_EMAIL、MEMBER_PWD、MEMBER_NAME、MEMBER_ROLE、MEMBER_REGISTER_DATETIME
• 控制台输出显示memberDTO已正确接收前端传入的用户名和密码，但selectOne返回null

解决方案 #

1. 修正INSERT语句的字段映射 #

当前INSERT语句未指定字段名，依赖表字段顺序插入，若表字段顺序与插入值顺序不匹配，会导致数据存入错误字段（比如用户名和密码位置颠倒），最终查询不到匹配记录。

修改INSERT语句，明确指定字段名：

<insert id="signUp" parameterType="com.withstudy.domain.MemberDTO">
    INSERT INTO MEMBER (MEMBER_EMAIL, MEMBER_PWD, MEMBER_NAME, MEMBER_ROLE, MEMBER_REGISTER_DATETIME)
    VALUES (#{member_email, jdbcType=VARCHAR}, #{member_pwd, jdbcType=VARCHAR}, #{member_name, jdbcType=VARCHAR}, 'General', sysdate)
</insert>

修改后重新注册测试账号，确保数据存入正确字段。

2. 验证数据库中的数据 #

直接在Oracle中执行查询语句，确认是否存在匹配的记录：

SELECT * FROM MEMBER WHERE MEMBER_NAME = '测试用户名' AND MEMBER_PWD = '测试密码';

若查询结果为空，说明数据未正确存储，需检查注册逻辑或字段映射。

3. 开启MyBatis驼峰命名映射（可选） #

确保数据库下划线命名的字段（如MEMBER_NAME）能自动映射到DTO的驼峰属性（member_name），在MyBatis配置文件中添加：

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

内容的提问来源于stack exchange，提问作者Suho Lee