Mybatis学习常见bug及其解决办法-sql映射篇2

bug1:

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'ssid' in 'class edu.xlh.vo.UserVo'
### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'ssid' in 'class edu.xlh.vo.UserVo'

cause1:

sourse code1:

public class User {
    private  String sid;
    private  String sname;
    private  String password;
    private  Integer superuser;
    private  Integer state;

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getSuperuser() {
        return superuser;
    }

    public void setSuperuser(Integer superuser) {
        this.superuser = superuser;
    }

    public Integer getState() {
        return state;
    }

    public void setState(Integer state) {
        this.state = state;
    }

    @Override
    public String toString() {
        return "User{" +
                "sid='" + sid + '\'' +
                ", sname='" + sname + '\'' +
                ", password='" + password + '\'' +
                ", superuser=" + superuser +
                ", state=" + state +
                '}';
    }
}

work1:

<select id="findUserListByUserVo"
            resultType="edu.xlh.vo.UserVo">
<!--方法参数类型为类类型，占位符里面的属性名为实体类的属性名 -->
    select * from student where sid like concat("%",#{sid},"%") and sname=#{sname}
    </select>

bug2:

### Error querying database.  Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='supperuser', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.String (java.lang.Integer and java.lang.String are in module java.base of loader 'bootstrap')
### The error may exist in mapper/User-Mapper.xml
### The error may involve edu.xlh.dao.UserMapper.findUserListByUserCodeAndSname-Inline
### The error occurred while setting parameters
### SQL: select * from student where sid like concat("%",?,"%") and supperuser = ?
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='supperuser', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. 
    Cause: org.apache.ibatis.type.TypeException:Error setting non null for parameter #2 with JdbcType null . 
    Try setting a different JdbcType for this parameter or a different configuration property. 
    Cause: java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.String (java.lang.Integer and java.lang.String are in module java.base of loader 'bootstrap')

cause2:

<select id="findUserListByUserCodeAndSname"
            parameterType="string" //这里只传了string，supperuser是int型
            resultType="edu.xlh.entity.User">
select * from student where sid like concat("%",#{userCode},"%") and superuser = #{superuser}
</select>

sourse code2:

public interface UserMapper {
    List<User> findUserListByUserCodeAndSname(@Param("userCode") String userCode , @Param("superuser") int superuser);
}

work2:

不设参数，会自己找

<select id="findUserListByUserCodeAndSname"

            resultType="edu.xlh.entity.User">
select * from student where sid like concat("%",#{userCode},"%") and superuser = #{superuser}

bug3:

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//方法参数类型为类类型，占位符里面的属性名为实体类的属' at line 1
### The error may exist in mapper/User-Mapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: //方法参数类型为类类型，占位符里面的属性名为实体类的属性名 select * from student where sid like concat("%",?,"%") and sname=?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near '//方法参数类型为类类型，占位符里面的属性名为实体类的属' at line 1

cause3:

</select>

    <select id="findUserListByUserVo"

            resultType="edu.xlh.entity.User">
//方法参数类型为类类型，占位符里面的属性名为实体类的属性名
select * from student where sid like concat("%",#{sid},"%") and sname=#{sname}
</select>

work3:

</select>

    <select id="findUserListByUserVo"

            resultType="edu.xlh.entity.User">
<!--方法参数类型为类类型，占位符里面的属性名为实体类的属性名 -->
    select * from student where sid like concat("%",#{sid},"%") and sname=#{sname}
    </select>

bug4:

### Error querying database.  Cause: org.apache.ibatis.binding.BindingException: Parameter 'sid' not found. Available parameters are [user, param1]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'sid' not found. Available parameters are [user, param1]

public interface UserMapper {
    List<User> findUserListByUserVo(@Param("user") UserVo userVo);
}

cause4:

<select id="findUserListByUserVo"
            resultType="edu.xlh.vo.UserVo">
<!--方法参数类型为类类型，占位符里面的属性名为实体类的属性名 -->
    select * from student where sid like concat("%",#{sid},"%") and sname=#{sname}
    </select>

sourse code4:

public interface UserMapper {
    List<User> findUserListByUserVo(@Param("user") UserVo userVo);
}

work4:

</select>

    <select id="findUserListByUserVo"
            resultType="edu.xlh.vo.UserVo">
<!--方法参数类型为类类型，占位符里面的属性名为实体类的属性名 -->
    select * from student where sid like concat("%",#{user.sid},"%") and sname=#{user.sname}
    </select>

总结1：

原始：映射文件占位符属性名任意填写；
强化：接口函数参数使用别名，映射文件占位符属性名与接口函数参数别名一致；
再强化：接口函数参数为类类型，映射文件占位符属性名与为实体类的属性名一致；
再再强化：接口函数参数为类类型且使用别名，映射文件占位符属性名为接口函数参数为类类型的别名.实体类的属性名。

总结2：

接口函数参数有两个及两个以上时，映射文件不写参数类型；
映射文件的函数返回值类型可以是实体类类型，也可以是实体类的继承类类型。