Mybatis上手教程–传递参数的问题
一传递多个参数报错的问题现象
如果SQL语句中传递的是单个参数的话，则通常没有什么问题。比如前面的基本入门的那个查询。现在，如果传递的是2个参数的话，我们想通过id和name来查询员工信息：
接口文件中的定义：
package com.knockatdatabase.dao;
import com.knockatdatabase.Employee;
import org.apache.ibatis.annotations.Param;
/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
Employee getEmployeeById(Integer id);
void insertEmployee(Employee employee);
Boolean updateEmployee(Employee employee);
Boolean deleteEmployeeById(Integer id);
Employee getEmployeeByIdAndName(Integer id, String name);
}
SQL mapper文件：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.knockatdatabase.dao.EmployeeMapper">
<insert id="insertEmployee" parameterType="com.knockatdatabase.Employee" useGeneratedKeys="true" keyProperty="id">
insert into tbl_employee(last_name,gender,email)
values (#{lastName},#{gender},#{mail})
</insert>
<update id="updateEmployee">
update tbl_employee set last_name=#{lastName},gender=#{gender},email=#{mail}
where id=#{id}
</update>
<delete id="deleteEmployeeById">
delete from tbl_employee where id=#{id}
</delete>
<select id="getEmployeeById" resultType="com.knockatdatabase.Employee">
select id,last_name ,gender,email as mail from tbl_employee where id = #{id}
</select>
<select id="getEmployeeByIdAndName" resultType="com.knockatdatabase.Employee">
select id,last_name,gender,email as mail from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
</mapper>
测试类代码：
@Test
public void test2Parameters() {
String resource = "mybatis-config.xml";
SqlSession sqlSession = null;
Map hashmap = new HashMap();
try {
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
sqlSession = sessionFactory.openSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmployeeByIdAndName(1, "黄伟");
System.out.println(employee);
} catch (IOException e) {
e.printStackTrace();
}finally {
sqlSession.close();
}
}
//执行结果报错：
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]
二解决方案
1 方案1，更改参数为arg0,arg1
根据错误提示，将SQL mapper文件中的参数改为对应的arg0,arg1就可以了，其它地方不需改动。
<select id="getEmployeeByIdAndName" resultType="com.knockatdatabase.Employee">
select id,last_name,gender,email as mail from tbl_employee where id=#{arg0} and last_name=#{arg1}
</select>
2 方案2，接口方法的参数上加注解@Param
在接口的方法上，使用@Param注解给参数加注解，相当于是给参数取对应的别名。然后，SQL mapper文件中，使用的参数名，就是接口方法上参数的注解别名。
package com.knockatdatabase.dao;
import com.knockatdatabase.Employee;
import org.apache.ibatis.annotations.Param;
/**
* @Author:asher
* @Date:2021/10/2 10:24
* @Description:com.knockatdatabase.dao
* @Version:1.0
*/
public interface EmployeeMapper {
Employee getEmployeeById(Integer id);
void insertEmployee(Employee employee);
Boolean updateEmployee(Employee employee);
Boolean deleteEmployeeById(Integer id);
Employee getEmployeeByIdAndName(@Param("id") Integer id, @Param("lastName") String name);
}
然后，在SQL mapper文件中，直接写对应的参数注解的别名即可：
<select id="getEmployeeByIdAndName" resultType="com.knockatdatabase.Employee">
select id,last_name,gender,email as mail from tbl_employee where id=#{id} and last_name=#{lastName}
</select>
我们当然推荐使用第2个方案，这样比较见名知意，而第一个方案的话，如果参数多了的话，则完全不知道arg0/1/2/3究竟表示的是什么意思。
三 Mybatis源码解读部分
跟着教程学习，补习。