mybatis常见错误记录

最新推荐文章于 2023-02-17 14:42:09 发布

weixin_34202952

最新推荐文章于 2023-02-17 14:42:09 发布

阅读量510

点赞数

文章标签： java 数据库测试

原文链接：https://segmentfault.com/a/1190000017904392

版权

Mybatis使用过程中，常见的一些问题汇总记录。

1. Mybatis执行SQL，查出来的比使用PL/SQL等工具查出来的记录多。

问题描述：由于查询要使用到not in，因此 not in ()括号里面的东西自己使用了前端拼接传值，传值格式为'',''
问题处理：在Mapper.xml中使用了#{}，这块涉及到#{}和${}的区别。替换成${} 就好了。

2. MyBatis mapper文件中的变量引用方式#{}与${}的差别

默认情况下,使用#{}语法,MyBatis会产生PreparedStatement语句中，并且安全的设置PreparedStatement参数，这个过程中MyBatis会进行必要的安全检查和转义。
示例1：
执行SQL：

Select * from emp where name = #{employeeName}

参数：employeeName传入值为：Smith
解析后执行的SQL：

Select * from emp where name = ?

执行SQL：

Select * from emp where name = ${employeeName}

参数：employeeName传入值为：Smith
解析后执行的SQL：

Select * from emp where name = Smith

综上所述、${}方式会引发SQL注入的问题、同时也会影响SQL语句的预编译，所以从安全性和性能的角度出发，能使用#{}的情况下就不要使用${}
${}的使用场景：
有时候可能需要直接插入一个不做任何修改的字符串到SQL语句中。这时候应该使用${}语法。
比如，动态SQL中的字段名，如：ORDER BY ${columnName}，not in ${items}
注意：当使用${}参数作为字段名或表名时、需指定statementType为“STATEMENT”，如：

<select id="queryMetaList" resultType="Map" statementType="STATEMENT">
    Select * from emp where name = ${employeeName} ORDER BY ${columnName}
</select>

3. ssm-web项目启动报错（一）

Could not resolve type alias 'map '. Cause: java.lang.ClassNotFoundException: Cannot find class: map

<select id="selectByCode" resultType="com.lucifer.pojo.BaseParams" parameterType="java.util.map">
    SELECT BP.ID,BP.NAME FROM BASE_PARAMS BP WHERE BP.DOMAIN=#{domain} AND BP.IS_CANCEL='N'
</select>

处理方式：把resultType改为resultMap，把parameterType改为parameterMap，重新发布并运行。

4. ssm-web项目启动报错（二）

org.mybatis.spring.transaction.SpringManagedTransaction - JDBC Connection [ ] will not be managed by Spring

处理方式：

5.

需求：因为要实现使用一个入参从两个表中查询结果：
例如从个人信息表中查询姓名，床位表中查询床位，但是这是一个参数，这个怎么整呢？我用了同一个字段，分别查询两个表，然后将查询结果union起来。当然，报错了，哈哈哈。。。
废话不多说，直接贴错误：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='searchStr', mode=IN, javaType=class java.lang.Object, 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: 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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:79)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
    at com.sun.proxy.$Proxy21.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:231)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy25.getRelatedPersonList(Unknown Source)
    at com.jframe.service.LoginUserServiceImpl.getRelatedPersonList(LoginUserServiceImpl.java:177)
    at com.jframe.action.LoginControl.getRelatedPersonList(LoginControl.java:517)
    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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at com.jframe.filter.SystemFilter.doFilter(SystemFilter.java:72)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:474)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:798)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1434)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='searchStr', mode=IN, javaType=class java.lang.Object, 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: 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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
    at org.apache.ibatis.executor.ReuseExecutor.prepareStatement(ReuseExecutor.java:92)
    at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:59)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    ... 57 more
Caused by: 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: 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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55)
    at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87)
    ... 70 more
Caused by: 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: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55)
    at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:45)
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53)
    ... 71 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:940)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:954)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1532)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:2823)
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1340)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:2820)
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_setString(FilterAdapter.java:1340)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_setString(FilterChainImpl.java:2820)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.setString(PreparedStatementProxyImpl.java:560)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.setString(DruidPooledPreparedStatement.java:365)
    at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:67)
    at com.sun.proxy.$Proxy32.setString(Unknown Source)
    at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:31)
    at org.apache.ibatis.type.StringTypeHandler.setNonNullParameter(StringTypeHandler.java:26)
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53)
    ... 73 more

错误原因：
尝试为该参数设置不同的Jdbc类型，或者设置不同的配置属性；
似乎是因为两个字段类型不一样，而我视图传入一个参数，从而导致了错误。

欢迎关注微信公众号weyoung，记录学习笔记和一些有趣的东西，欢迎交流~~

图片描述

更多联系方式

平台	链接
个人博客	https://www.wanglixia.top/
预览项目：	https://nelucifer.gitee.io/
个人微信公众号：	weyoung
segmentfault:	https://segmentfault.com/u/nelucifer
CSDN:	https://me.csdn.net/wlx001
简书：	https://www.jianshu.com/u/99211cc23788
掘金：	https://juejin.im/user/59b08c575188250f4850e80e

weixin_34202952

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis常见错误记录

Mybatis使用过程中，常见的一些问题汇总记录。1. Mybatis执行SQL，查出来的比使用PL/SQL等工具查出来的记录多。问题描述：由于查询要使用到not in，因此 not in ()括号里面的东西自己使用了前端拼接传值，传值格式为'',''问题处理：在Mapper.xml中使用了#{}，这块涉及到#{}和${}的区...
复制链接

扫一扫