honjo 发表于 2018-6-5 15:12:17

jeecg3.7.1切换用postgres数据库后报错：org.hibernate.exception.SQLGr...

3.7.1非maven版源码，通过修改dbconfig, jeecg_database properties 配置及spring-minidao.xml 配置，切换使用postgres数据库（postgres数据库通过工具从mysql初始库转过来的），但程序启动时老是报如下错:org.hibernate.exception.SQLGrammarException: 错误: 字段 this_.id 不存在 。 请问是jeecg框架的bug，还是切换postgres数据库时还需要更新其它地方的配置信息？但切换用mysql时，启动时正常不报错。

-----相关出错log如下：----------------------
错误: 字段 this_.id 不存在
Exception encountered during context initialization - cancelling refresh attempt
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dynamicTask' defined in file : Invocation of init method failed; nested exception is org.hibernate.exception.SQLGrammarException: 错误: 字段 this_.id 不存在
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1554)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:229)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:706)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:762)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:403)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4992)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5490)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:649)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1247)
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1898)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: 错误: 字段 this_.id 不存在
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy286.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
at org.hibernate.loader.Loader.doList(Loader.java:2447)
at org.hibernate.loader.Loader.doList(Loader.java:2433)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263)
at org.hibernate.loader.Loader.list(Loader.java:2258)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:122)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1535)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
at org.jeecgframework.core.common.dao.impl.GenericBaseCommonDao.findByExample(GenericBaseCommonDao.java:576)
at org.jeecgframework.core.common.service.impl.CommonServiceImpl.findByExample(CommonServiceImpl.java:284)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy277.findByExample(Unknown Source)
at org.jeecgframework.core.timer.DynamicTask.loadTask(DynamicTask.java:171)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1682)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1621)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1550)
... 25 more
Caused by: org.postgresql.util.PSQLException: 错误: 字段 this_.id 不存在
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2708)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2705)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:99)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:211)
at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 60 more
Shutting down Quartz Scheduler
Scheduler schedulerFactory_$_NON_CLUSTERED shutting down.
Scheduler schedulerFactory_$_NON_CLUSTERED paused.
Scheduler schedulerFactory_$_NON_CLUSTERED shutdown complete.
{dataSource-1} closed
Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dynamicTask' defined in file : Invocation of init method failed; nested exception is org.hibernate.exception.SQLGrammarException: 错误: 字段 this_.id 不存在
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1554)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:229)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:298)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:706)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:762)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:403)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:306)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:106)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4992)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5490)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:649)
at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1247)
at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1898)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: 错误: 字段 this_.id 不存在
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at com.sun.proxy.$Proxy286.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
at org.hibernate.loader.Loader.doList(Loader.java:2447)
at org.hibernate.loader.Loader.doList(Loader.java:2433)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263)
at org.hibernate.loader.Loader.list(Loader.java:2258)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:122)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1535)
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
at org.jeecgframework.core.common.dao.impl.GenericBaseCommonDao.findByExample(GenericBaseCommonDao.java:576)
at org.jeecgframework.core.common.service.impl.CommonServiceImpl.findByExample(CommonServiceImpl.java:284)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy277.findByExample(Unknown Source)
at org.jeecgframework.core.timer.DynamicTask.loadTask(DynamicTask.java:171)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1682)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1621)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1550)
... 25 more
Caused by: org.postgresql.util.PSQLException: 错误: 字段 this_.id 不存在
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2708)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2705)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:99)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:211)
at sun.reflect.GeneratedMethodAccessor103.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 60 more

admin 发表于 2018-6-5 16:24:41

数据库区分大小写了吧？

honjo 发表于 2018-6-5 17:45:23

admin 你好，数据库名都是小写的，dbconfig 配置如下：
#postgresSQL
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
validationQuery.sqlserver=SELECT 1
jdbc.url.jeecg=jdbc:postgresql://localhost:5432/jeecg371
jdbc.username.jeecg=postgres
jdbc.password.jeecg=123456
jdbc.dbType=postgres

spring minidao.xml 相关配置如下：
<!-- MiniDao动态代理类 -->
        <bean id="miniDaoHandler" class="org.jeecgframework.minidao.factory.MiniDaoBeanScannerConfigurer">
                <!-- 是使用什么字母做关键字Map的关键字 默认值origin 即和sql保持一致,lower小写(推荐),upper 大写 -->
                <property name="keyType" value="lower"></property>
                <!-- 格式化sql -->
                <property name="formatSql" value="false"></property>
                <!-- 输出sql -->
                <property name="showSql" value="false"></property>
                <!-- 数据库类型 mysql-->
                <property name="dbType" value="postgres"></property>
                <!-- dao地址,配置符合spring方式 -->
                <property name="basePackage" value="org.jeecgframework.web,com.jeecg"></property>
                <!-- 使用的注解,默认是Minidao,推荐 Repository-->
                <property name="annotation" value="org.springframework.stereotype.Repository"></property>
        </bean>

sysConfig 相关配置如下：
#database type for spring jdbc
jdbc.url.jeecg=postgres

admin 发表于 2018-6-5 18:29:37

postgres区分大小写，百度

honjo 发表于 2018-6-5 23:21:51

admin 你好，问题应该是 jeecg中hinbernate 生成的 sql 对于postges中大写列名没有加双引号 导致，jeecg中hinbernate生成的sql 如下：
select this_.ID as ID45_0_, this_.CLASS_NAME as CLASS2_45_0_, this_.CREATE_BY as CREATE3_45_0_, this_.CREATE_DATE as CREATE4_45_0_, this_.CREATE_NAME as CREATE5_45_0_, this_.CRON_EXPRESSION as CRON6_45_0_, this_.IS_EFFECT as IS7_45_0_, this_.IS_START as IS8_45_0_, this_.RUN_SERVER as RUN9_45_0_, this_.RUN_SERVER_IP as RUN10_45_0_, this_.TASK_DESCRIBE as TASK11_45_0_, this_.TASK_ID as TASK12_45_0_, this_.UPDATE_BY as UPDATE13_45_0_, this_.UPDATE_DATE as UPDATE14_45_0_, this_.UPDATE_NAME as UPDATE15_45_0_ from t_s_timetask this_

postgers 根据mysql 转换过来的字段名与mysql一样，列名也是大写的，如postgres从mysql 转换过来的 t_s_timetask表的列名（都为大写）

这个问题的解决办法 能通过 初始化postgres数据库 字段名表名为小写， 或通过配置jeecg的中相应配置让hibernate生成的sql 正常匹配到postgres中的大写的列名吗（因为mysql库的初始脚本中好些表的字段是大写的）？

admin 发表于 2018-6-6 09:46:45

设置下数据库不区分大小写，这种问题多百度
页: [1]
查看完整版本: jeecg3.7.1切换用postgres数据库后报错：org.hibernate.exception.SQLGr...