本文将为您提供关于mybatis的报错总结_____2Errorupdatingdatabase.Cause:java.sql.SQLSyntaxErrorException:Youhavean...
本文将为您提供关于mybatis的报错总结_____2Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an ...的详细介绍,同时,我们还将为您提供关于### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...、### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02291...、Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ..... this is incompatible with...、Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146的实用信息。
本文目录一览:- mybatis的报错总结_____2Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an ...
- ### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...
- ### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02291...
- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ..... this is incompatible with...
- Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
mybatis的报错总结_____2Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an ...
在编写动态sql时 trim的属性写错了,如下图:
错误示范:
(接口的xml配置文件中:)
1 <update id="modifyBill" parameterType="Bill">
2 update smbms_bill
3 <trim suffix="set" prefixOverrides="," prefix="where id=#{id}">
4 <if test="billCode!=null">billCode=#{billCode},</if>
5 <if test="productName!=null">productName=#{productName},</if>
6 <if test="productDesc!=null">productDesc=#{productDesc}</if>
7 </trim>
8 </update>
报错日志:
Error updating 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 ''where id=3 billCode=''33333'',
//可以看到日志中打印出来的sql语句也是有错的,如下:
### SQL: update smbms_bill where id=? billCode=?, productName=?, productDesc=? set
正确的示范:
接口的xml配置文件中:
<!--最后一个没有逗号~~~~~-->
<update id="modifyBill" parameterType="Bill">
update smbms_bill
<trim prefix="set" prefixOverrides="," suffix="where id=#{id}">
<if test="billCode!=null">billCode=#{billCode},</if>
<if test="productName!=null">productName=#{productName},</if>
<if test="productDesc!=null">productDesc=#{productDesc}</if>
</trim>
</update>
打印出来的sql语句:
[com.xbf.dao.bill.BillMapper.modifyBill]-==> Preparing: update smbms_bill set billCode=?, productName=?, productDesc=? where id=?
总结:(网上找的图,感觉说的很清晰!)
prefix:字首 eg:set
prefixOverrides:字首的替换 eg: ,
suffix:后缀 eg:where id=#{ id }
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,【??】,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
### The error may involve com.foen.cloud.mapping.CustMemoMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into ERP_BASIC_CUS_Memorandum(djno,custno,custname,sales_company_projectno,Projectname,Contact_phone,contractno,Contact,Contact_account,ywy,qyjl,kfzy,qht,shop_number,project_area,qyzzwj,if_idcard,sales_area,hb,province,prefecture_level_city,county_level_city,Delivery_address,aluminum_lb,aluminum_fs,weight_jsfs,aluminum_lb_shop,pj_price_type,if_deposit,contract_dt,if_js_days,receivment_no,ztz,bhm,ssm,fwbz,gsc,zzm,bz_number,qtyq,bzfs,all_number,out_day,if_stop,begin_date,end_date,validity,ex_ysdm,kaifa_company,yfzf,wd_xl,lrj,lrj_price,cust_class,modifier,modify_dt,oper,date1)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,??,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
; uncategorized SQLException for SQL []; SQL state [S0002]; error code [137]; 必须声明标量变量 "@P23@P24"。; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 "@P23@P24"。
### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02291...
在向Oracle数据库里面插入数据时发生了以下错误
; ]; ORA-02291: 违反完整约束条件 (SSM.SYS_C0011830) - 未找到父项关键字
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-02291: 违反完整约束条件 (SSM.SYS_C0011830) - 未找到父项关键字
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:986)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:881)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
A表和B表通过主键与中间表C表关联,在向C表中插入数据时,如果插入C表时中数据没有与A表或者B表中主键对应,就会出现该错误。
简而言之就是在插入数据时,中间表的数据必须在它所关联的表中存在。
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ..... this is incompatible with...
一、异常信息
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ''daotable.he_store_file.file_url'' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in com/zbq/springbootbase/mysql/HeStoreFileDAO.java (best guess)
### The error may involve com.zbq.springbootbase.mysql.HeStoreFileDAO.findFileInfoByFileTypeAndStoreIdList-Inline
### The error occurred while setting parameters
### SQL: SELECT store_id, file_url FROM he_store_file WHERE (1=1) AND (store_id in (8310596)) AND (file_type = ? ) GROUP BY store_id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ''daotable.he_store_file.file_url'' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ''daotable.he_store_file.file_url'' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.3.2.jar:1.3.2]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-1.3.2.jar:1.3.2]
at com.sun.proxy.$Proxy85.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.2.jar:1.3.2]
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) ~[mybatis-3.4.6.jar:3.4.6]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) ~[mybatis-3.4.6.jar:3.4.6]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.6.jar:3.4.6]
at com.sun.proxy.$Proxy94.findFileInfoByFileTypeAndStoreIdList(Unknown Source) ~[na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_112]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_112]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_112]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_112]
二、分析原因
ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
1.临时解决方案
set @@GLOBAL.sql_mode='''';
set sql_mode =''STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'';
2.永久解决方案
修改my.ini 配置(如果你们mysql 没有这个文件,就把my-default.ini 改成my.ini)
在 [mysqld]和[mysql]下添加
sql_mode =''STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'';
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
问题介绍:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
MySql 语法错误,但是错误是未知的,执行所有的增删改查操作都会报这个错误!
原因分析:
原来是在 mybatis 配置数据库的 db.proerties 中,忘记修改数据库名字了
解决办法:
把jdbc.url=jdbc:mysql://localhost:3306/xiaomi?characterEncoding=utf-8&useSSL=false
修改为你需要的数据库
jdbc.url=jdbc:mysql://localhost:3306/ehome?characterEncoding=utf-8&useSSL=false
问题解决!
附上错误的详细内容!
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
### The error may exist in com/bj186/crm/mapper/UserMapper.xml
### The error may involve com.bj186.crm.mapper.UserMapper.selectUserById-Inline
### The error occurred while setting parameters
### SQL: select user_id uid,user_name username,user_password password,user_gender gender, user_birthday birthday, user_status status from `users` where user_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
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 com.bj186.crm.test.TestDemo.test1(TestDemo.java:48)
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.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
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.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
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: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy20.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
... 33 more
关于mybatis的报错总结_____2Error updating database. Cause: java.sql.SQLSyntaxErrorException: You have an ...的问题就给大家分享到这里,感谢你花时间阅读本站内容,更多关于### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 必须声明标量变量 &qu...、### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: ORA-02291...、Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ..... this is incompatible with...、Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146等相关知识的信息别忘了在本站进行查找喔。
本文标签: