paramX形式传递参数
在UserMapper.xml中添加一段查询代码
<select id="selectByidAndName" resultMap="userMap">
select * from sys_user where id =#{id} and user_name=#{userName}
</select>
在UserMapper.java接口中添加一个接口
public SysUser selectByidAndName(long ID, String userName);
在UserMapperTest.java测试类中添加
@Test
public void testselectByidAndName(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser = userMapper.selectByidAndName(Long.valueOf(1),"admin");
System.out.println(sysUser);
} finally {
sqlSession.commit();
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]
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:83)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy6.selectByidAndName(Unknown Source)
at simple.UserMapperTest.testselectByidAndName(UserMapperTest.java:160)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2]
at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:204)
at org.apache.ibatis.reflection.wrapper.MapWrapper.get(MapWrapper.java:45)
at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:122)
at org.apache.ibatis.executor.BaseExecutor.createCacheKey(BaseExecutor.java:219)
at org.apache.ibatis.executor.CachingExecutor.createCacheKey(CachingExecutor.java:146)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 29 more
由于是多个参数, mybatis是按照参数的位置进行赋值, 再看错误提示: Caused by: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [arg1, arg0, param1, param2], 这个错误说xml的参数可以是arg1, arg0, param1, param2,
所以上面的这种情况压根就找不到,所以我们可以将xml里面的查询语句改成下列形式,然后运行,,,成功
<select id="selectByidAndName" resultMap="userMap">
select * from sys_user where id =#{ param1 } and user_name=#{param2}
</select>
所以说mybatis是按照参数传递的顺序进行一个赋值,从param1 开始, 记住是从1开始, 现实中很不建议这么干, 这么干如果参数多的话会是一种悲剧
＠Param注解传参
还是UserMapper.java
public SysUser selectByidAndName( @Param("ID") long ID, @Param("userName") String userName);
UserMapper.xml
<select id="selectByidAndName" resultMap="userMap">
select * from sys_user where id =#{ ID } and user_name=#{userName}
</select>
或者是
<select id="selectByidAndName" resultMap="userMap">
select * from sys_user where id =#{ param1 } and user_name=#{param2}
</select>
运行之后消息返回
[DEBUG] 2018-11-05 15:28:26,572 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: select * from sys_user where id =? and user_name=?
[DEBUG] 2018-11-05 15:28:26,616 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 1(Long), admin(String)
[TRACE] 2018-11-05 15:28:26,628 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
[TRACE] 2018-11-05 15:28:26,628 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.trace(BaseJdbcLogger.java:165)
<== Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2016-06-07 01:11:12.0
[DEBUG] 2018-11-05 15:28:26,631 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Total: 1
SysUser [id=1 , userName=admin , userPassword=null , userEmail=null , userInfo=null , headImg=null , createTime=null ]
给参数配直＠Param注解后， MyBatis就会自动将参数封装成Map类型，＠Param注解值会作为Map中的 key ，因此在 SQL 部分就可以通过配置的注解值来使用参数。
多个javabean方式
为了演示, 就直接点啦
UserMapper.java添加下列代码
public List<SysUser> selectRolesByUser1AndUser2(@Param("user1") SysUser user1,@Param("user2") SysUser user2);
UserMapper.xml添加对应的代码
<select id="selectRolesByUser1AndUser2" resultMap="userMap">
select * from sys_user where id =#{ user1.id } and user_name=#{user2.userName}
</select>
UserMapperTest.java中添加相应的代码进行测试:
@Test
public void testselectRolesByUserAndRole() {
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser1 = new SysUser();
sysUser1.setId(Long.valueOf(1));
SysUser sysUser2 = new SysUser();
sysUser2.setUserName("admin");
List<SysUser> sysUser = userMapper.selectRolesByUser1AndUser2(sysUser1, sysUser2);
System.out.println(sysUser);
} finally {
sqlSession.commit();
sqlSession.close();
}
}
批注: 在xml中获取对象一定是 xxx.属性, 而且属性名区分大小写
如果单个参数可以直接用基本类型, 或者是直接用单个javabean, 至于用哪个就应该自己衡量啦
单个javabean的方式, 单个参数的其实没必要多说, 前面几个章节都写很多了, 下面代码为一个简单的javabean参数方式进行传入
UserMapper.java
public SysUser selectSysUser(SysUser sysUser);
UserMapper.xml
<select id="selectSysUser" resultMap="userMap">
select * from sys_user where id =#{ id } and user_name=#{userName}
</select>
UserMapperTest.java
@Test
public void testselectSysUser() {
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser sysUser1 = new SysUser();
sysUser1.setId(Long.valueOf(1));
sysUser1.setUserName("admin");
SysUser sysUser = userMapper.selectSysUser(sysUser1);
System.out.println(sysUser);
} finally {
sqlSession.commit();
sqlSession.close();
}
}
注意xml中参数的获取方式, 也是却分大小写的 ,
