Java项目,从Oracle迁移到达梦数据库笔记
达梦数据库是个国产数据库,据说对Oracle有很好的兼容,本人亲自从Oracle11g迁移到达梦6.0的数据库上来,遇到一些问题,以下是问
达梦数据库是个国产数据库,据说对Oracle有很好的兼容,本人亲自从Oracle11g迁移到达梦6.0的数据库上来,遇到一些问题,以下是问题记录:
1。字段名错误
虽然达梦SQL可以兼容字段大小写,但是JDVC实际上执行与Oracle会有差别,
达梦的SQL可以执行,但是返回的字段的列名仍然按SQL的大小写返回,而Oracle会变成全部的大写返回,
select typeid, typename from T_PUB_PRODUCT_TYPE
在用oracle执行后返回的列名叫TYPEID,TYPENAME
达梦返回值则为typeid,typename
比较好的做法是在数据库执行语句之前,强制所有语句转大写:
rs = stmt.executeQuery(strSql.toUpperCase()); //达梦数据库对列名区分大小写,Oracle会自动全转大写
==============================================
cuont(*)数据类型错误
语句:
select count(*) counts from t_cus_product left join t_pub_product_type on p_type=typeid where p_createrid=200
oracle的count(*)可以兼容BigDecimal,达梦的count(*)是Long型
//count = ((BigDecimal)mapCount.get("counts")).intValue();//Orcale 为 BigDecimal
count = ((Long)mapCount.get("counts")).intValue(); //达梦数据库为Long
=====================================================
2。达梦数据库语法关键字错误
以下语句oracle 可以执行,达梦却报错误
select c.contractid,CONTRACTNO, c.contractname, c.type, sum,begin,end
state, userid, serviceid,P_NAME
from t_contract c
left join T_CUS_PRODUCT
on c.PRODUCTID=P_ID
where userid=200 order by contractid
原因:begin,end为达梦的保留关键字,语句修改为双引号的形式,可以通过:
select c.contractid,CONTRACTNO, c.contractname, c.type, sum,"begin","end"
state, userid, serviceid,P_NAME
from t_contract c
left join T_CUS_PRODUCT
on c.PRODUCTID=P_ID
where userid=200 order by contractid
=============================================
3。查询dual表报错,
语句:select seq_c006_message_content.nextval from dual
java报错:
java.sql.SQLException: 无效的表或视图名 'dual'
at dm.jdbc.dbaccess.DBError.throwSQLException(Unknown Source)
at dm.jdbc.driver.DmdbCSI.prepareSQL(Unknown Source)
at dm.jdbc.driver.DmdbStatement.directExec(Unknown Source)
at dm.jdbc.driver.DmdbStatement.executeQuery(Unknown Source)
at cn.org.hz.common.dao.DBAccess.queryOneRow(DBAccess.java:110)
at cn.org.hz.blh.i007.I007Blh.I007MessageAdd(I007Blh.java:136)
at cn.org.hz.blh.i007.I007Blh$$FastClassByCGLIB$$f90d5dcf.invoke(
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 cn.org.hz.common.aop.AroundInterceptor.invoke(AroundInterceptor.java:13)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
at cn.org.hz.blh.i007.I007Blh$$EnhancerByCGLIB$$67fbf5f5.I007MessageAdd(
at cn.org.hz.ctrl.i007.I007Ctrl.I007MessageAdd(I007Ctrl.java:113)
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 org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:473)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:410)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at cn.org.hz.filter.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:23)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:619)
解决在本用户下建立一个dual同义词
如果需要直接访问,可以用SYSDBA创建一个PUBLIC同义词:
CREATE PUBLIC SYNONYM dual for SYSTEM.SYSDBA.SYSDUAL;
达梦虽然在sysdba下造了一个dual表来兼容oracle但是却没有在每个新用户下建立同义词,,所有一般对dual表的查询都会报错。
=================================================