SQLState: S0022

最新推荐文章于 2021-01-19 03:37:16 发布
最新推荐文章于 2021-01-19 03:37:16 发布
阅读量1.4k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 问题及解决方法 文章标签： 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/flashdream8/article/details/83966809
本文讨论了在使用Hibernate时遇到的问题，即从打印的SQL语句能够独立执行，但在程序中执行时出现错误。通过分析发现，问题在于自动映射实体时的不当操作。建议在不需要查询特定实体时，避免使用`.addEntity()`方法，并调整SQL语句以匹配实际需求。总结了如何正确处理此类情况，确保程序与SQL语句的无缝集成。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

SQLState: S0022

 

	StringBuffer sb = new StringBuffer();
		sb.append(" select  {m.*}   from d_provider_to_factory as d , m_enterprice  as m where  1=1 ");
		if ("0".equals(enterpriceType)) {
			sb.append(" and  d.provider_id = m.enterprice_id ");
		}else if("1".equals(enterpriceType)){
			sb.append(" and d.factory_id = m.enterprice_id ");
		}
		sb.append(" and  m.flag_del = '0' ");
		sb.append(" and  d.flag_del = '0' ");
		
		if ("0".equals(enterpriceType)) {
			sb.append(" group by   d.provider_id  ");
		}else if("1".equals(enterpriceType)){
			sb.append(" group by  d.factory_id ");
		}
		
		if (page <=0) {
			page = 1;
		}
		
		String sql = sb.toString();
		sb = null;
		Session session = getSession();
	    List  pList = session.createSQLQuery(sql)
			.addEntity("d", DProviderToFactory.class)
			.addEntity("m",MEnterprice.class)
			.setFirstResult((page-1)*pageSize)
			.setMaxResults(pageSize)
			.list();

 

执行的时候 报错：

Hibernate: 
    select
        m.enterprice_id as enterprice1_84_1_,
        m.enterprice_name as enterprice2_84_1_,
        m.enterprice_short_name as enterprice3_84_1_,
        m.enterprice_original_name as enterprice4_84_1_,
        m.parent_enterprice_id as parent5_84_1_,
        m.trade_type as trade6_84_1_,
        m.province_id as province7_84_1_,
        m.administrative_division as administ8_84_1_,
        m.address as address84_1_,
        m.post_code as post10_84_1_,
        m.enterprise_type as enterprise11_84_1_,
        m.corporate_representative as corporate12_84_1_,
        m.fax as fax84_1_,
        m.phone_number as phone14_84_1_,
        m.cell_phone_number as cell15_84_1_,
        m.email as email84_1_,
        m.begin_date as begin17_84_1_,
        m.register_capital as register18_84_1_,
        m.operate_begin_date as operate19_84_1_,
        m.operate_end_date as operate20_84_1_,
        m.register_department as register21_84_1_,
        m.tax_number as tax22_84_1_,
        m.register_code as register23_84_1_,
        m.operate_area as operate24_84_1_,
        m.remark as remark84_1_,
        m.flag_del as flag26_84_1_,
        m.create_datetime as create27_84_1_,
        m.create_date_user as create28_84_1_,
        m.update_datetime as update29_84_1_,
        m.update_date_user as update30_84_1_   
    from
        d_provider_to_factory as d ,
        m_enterprice  as m 
    where
        1=1  
        and d.factory_id = m.enterprice_id  
        and  m.flag_del = '0'  
        and  d.flag_del = '0'  
    group by
        d.factory_id 
2011-07-06 15:44:29,595 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2011-07-06 15:44:29,595 ERROR [org.hibernate.util.JDBCExceptionReporter] - Column 'provider1_83_0_' not found.
org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:2223)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
	at org.hibernate.loader.Loader.list(Loader.java:2099)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
	at cn.com.jdlh.enterprice.dao.EnterPriceDAO.getEnterPriceList(EnterPriceDAO.java:62)
	at cn.com.jdlh.enterprice.dao.EnterPriceDAO$$FastClassByCGLIB$$4c9aae10.invoke(<generated>)
	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
	at cn.com.jdlh.enterprice.dao.EnterPriceDAO$$EnhancerByCGLIB$$dffb0954.getEnterPriceList(<generated>)
	at cn.com.jdlh.enterprice.action.EnterPriceAction.enterPriceList(EnterPriceAction.java:30)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
	at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
	at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
	at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:148)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:128)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
	at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
	at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.struts2.dispatcher.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:102)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at filters.SecurityFilter.doFilter(SecurityFilter.java:105)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:242)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:201)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:563)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:163)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:556)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:401)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:267)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:245)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:260)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Column 'provider1_83_0_' not found.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
	at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:987)
	at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:2749)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt(NewProxyResultSet.java:2573)
	at org.hibernate.type.IntegerType.get(IntegerType.java:28)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
	at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
	at org.hibernate.loader.Loader.doQuery(Loader.java:701)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
	at org.hibernate.loader.Loader.doList(Loader.java:2220)
	... 95 more

 

 

 

我将 Hibernate 打印出来的SQL 拿出来可以单独执行，但是 放到程序里就出错了。

 

 

最后找到问题根源

 

 

   List pList = session.createSQLQuery(sql)
			//.addEntity("d", DProviderToFactory.class)
			.addEntity("m",MEnterprice.class)
			.setFirstResult((page-1)*pageSize)
			.setMaxResults(pageSize)
			.list();

 

因为 我不需要 查询出 d 实体的内容 所以 就不用吧 d也实例化 将 

//.addEntity("d", DProviderToFactory.class)

 

注释掉 就可以了。

 

如果不注释掉 也行 就修改sql 语句

 

StringBuffer sb = new StringBuffer();
		sb.append(" select  {m.*} ,{d.*}  from d_provider_to_factory as d , m_enterprice  as m where  1=1 ");
		if ("0".equals(enterpriceType)) {
			sb.append(" and  d.provider_id = m.enterprice_id ");
		}else if("1".equals(enterpriceType)){
			sb.append(" and d.factory_id = m.enterprice_id ");
		}
		sb.append(" and  m.flag_del = '0' ");
		sb.append(" and  d.flag_del = '0' ");
		
		if ("0".equals(enterpriceType)) {
			sb.append(" group by   d.provider_id  ");
		}else if("1".equals(enterpriceType)){
			sb.append(" group by  d.factory_id ");
		}
		
		if (page <=0) {
			page = 1;
		}
		
		String sql = sb.toString();
		sb = null;
		Session session = getSession();
	    List  pList = session.createSQLQuery(sql)
		.addEntity("d", DProviderToFactory.class)
		.addEntity("m",MEnterprice.class)
		.setFirstResult((page-1)*pageSize)
		.setMaxResults(pageSize)
		.list();

 

 

总结：如果非要用hibernate查询sql 语句并且进行自动映射的话，最好用到那个实体就添加那个实体。

Mysql SQL Error: 1146, SQLState: 42S02
马句
10-10 5282
java项目访问提示：Mysql SQL Error: 1146, SQLState: 42S02 找不到数据库中的表 Table'opo.User' doesn't exist 然后根据错误提示查了查，需要把MySQL的配置文件大小写敏感去掉 项目在centos服务器上 执行命令 vi /etc/my.cnf #在[mysqld]下 添加 lower_case_table_na...
关于hibernate5最新版本无法序列化，SQL Error: 2289, SQLState: 42000的问题
m0_37891883的博客
12-09 2423
关于hibernate5最新版本无法序列化 xxx.hbm.xml文件配置如下： &lt;id name="uuid" &gt; &lt;generator class="org.hibernate.id.enhanced.SequenceStyleGenerator"&gt; &lt;param name="sequence"&gt;emp_seq&lt;/param&gt;
SQL错误代码大全
02-04
收集汇总了设计者在SQL 编程中所有出现的错误代码提示大全 希望对大家有所帮助
SQL Error: 0, SQLState: S0022
qq_35441940的博客
07-23 3250
搞了半天一直以为是自己数据库查询语句问题。 SELECT b.fee AS 报销金额 ," + "a.mo AS 月份 " + "FROM (SELECT AAa102 AS mo " + "FROM aa10 " + "WHERE aaa100...
数据库异常：SQL Error: 0, SQLState: S0022
weixin_30520015的博客
12-10 1887
问题描述 在本地搭建 mysql + MyEclipse + tomcat，系统跑起来之后，调用自己写的查询接口，报错“SQL Error: 0, SQLState: S0022” 具体报错： -couldnotreadcolumnvaluefromresultset:S_CAPTION;Column'S_CAPTION'notfound. -SQLErr...
SQL Error: 0,SQLState: S0022错误
GOUGOU3601的博客
08-25 1628
    在使用Hibernate createSQLQuery 时候执行一天简单的sql语句“select count(*) from ams_user where 1=1” 遇到如下错误   [http-80-4] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0,SQLState: S0022 [http-80-4] ...
关于hibernate报错SQL Error :0 SQLState :s0022 Column "name" not found 的问题
没有猫的猫奴
01-17 8238
今日在写bug的时候，突然发现了一条不可思议的bug，如下图所示： 表示很难理解，因为我的sql 里面并没有查询如"create_time"的字段，为啥会“Column "name" not found”呢？ 修改前sql： SELECT t.id,t.pic_end_time,t.estimate,t.total_money,t.merchant_id,count( bo.o...
sql 运行错误信息，错误代码汇总，ODBC 错误信息：SQL Error: 1048, SQLState: 23000
秋̶᭄ꦿ攻城狮࿆ྂ
12-27 2631
sql 运行错误信息，ODBC 错误信息：SQL Error: 1048, SQLState: 23000 00000 成功 01000 常规警告 01002 断开连接错误 01004 数据被截断 01006 未废除特权 01S00 无效连接字符串特性 01S01 行中的错误 01S02 选项值已更改 01S03 未更新或删除任何行 01S04 更新或删除了多行 01S05 取消操作被当成 FreeStmt/Close 01S06 设法在结果返回第一个行集之前进行提取 07001 参数个数错误 07006
SQLSTATE: 08S01
最新发布
05-30
SQLSTATE: 08S01是一个常见的数据库错误码，通常表示网络连接中断或数据库服务器无法访问。这个错误码经常出现在客户端应用程序试图连接到数据库服务器时，可能由于网络故障、数据库服务器宕机或数据库服务器防火墙...
sql error: 0, sqlstate: s1009
03-16
这是一个 SQL 错误代码，错误代码为 0，错误状态为 S1009。这个错误代码通常表示 SQL 语句中的参数数量不正确。可能是参数数量过多或过少，或者参数类型不匹配。需要检查 SQL 语句和参数，确保它们的数量和类型正确...
奇怪的SQL Error: 0,SQLState: S0022错误
.NET开源应用程序框架 HYAppFrame
04-19 7267
<br />我使用hibernate和javabean开发web application遇到以下错误<br />22203 [http-80-4] INFO org.hibernate.type.StringType - could not read column value from result set: name; Column ' name ' not found.<br /> 22203 [http-80-4] WARN org.hibernate.util.JDBCExceptionRepor
Hibernate native sql s0022错误 column "name" not found
liuhui_306的专栏
10-07 4557
hibernate在我们的项目中确实提供了很大的方便，提高了项目的开发进度，它的缓存机制也提高了我们的查询效率；但它在一定程度上也降低了程序的运行性能，在进行复杂的查询时，为了提高查询效率，我们还会不知觉的去使用sql进行查询，使用hibernate的native sql时，当我们的查询过于复杂时，将会出现各种各样的问题。      最近做一个项目时就遇到了一个很头疼的问题，报的是s0022错误
Extracted SQL state class 'S0' from value 'S0022' 错误解决方式
DaySurprise的博客
03-25 1926
发生这种错误的两种可能性： 1. mapper中调用实体类的get方法出错，mapper中get的列名和数据库列名不一致 比如： People p = new People(); p.setName(rs.getString("naem"))；而数据库中的列名为name 2. 数据库该字段不能为空，但是程序给插入了一个空值 所以报错 ...
mysql查询报错2014_mysql Hibernate 查询时用别名报错
weixin_32951341的博客
01-19 193
mysqlHibernate查询时用别名报错在mysql查询工具中,能正常执行SQL:SELECTROLE_IDASVALUE,SNAMEASTESTFROMSYS_ROLEORDERBYROLE_ID;但在代码中就报错.具体错误信息如下:Hibernate:SELEC...mysql Hibernate 查询时用别名报错在mysql查询工具中,能正常执行SQL:SELECT ROLE_ID A...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值