Of Stack

How to use Oracle11g database in SpringBoot project

  • 2021-09-12 00:58:51
  • OfStack

Using Oracle11g database in SpringBoot project

The specific steps are as follows:

1: Download ojdbc6.jar, just put it in an English directory

2: Under the command line, enter the following line. Note that the last-Dfile is your download directory

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=H:\eclpise-workspace\ojdbc6.jar

3: Introduce the following dependencies in the project's pom file 


<dependency>
     <groupId>com.oracle</groupId>
     <artifactId>ojdbc6</artifactId>
     <version>11.2.0.1.0</version>
 </dependency>

4: Write the connection information of database in the configuration file of the project, in which, assume that the connection of Oracle to ip is 127.0. 0.1, the port number is 1521, the database is database, the user name is username, and the password is password 


spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:database
spring.datasource.username=username
spring.datasource.password=password

Done!

Problems with SpringBoot 2.0 Connecting Oracle 11g

Basic configuration information:

驱动 oracle.jdbc.driver.OracleDriver
连接Url jdbc:oracle:thin:@192.168.112.129:1521:orcl
用户 System
密码 Aa123456

Add a driver package:

The ojdbc6 package is added here, because I made an error using ojdbc14 on the Davinci system before, and I used the version of Jdk 1.8.

ojdbc6. jar is much higher than the version of ojdbc14. jar because 6 in it means support for jdk 1.6 and 14 for support for jdk 1.4. 


        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
        </dependency>

An error was reported while creating IDatabasePlatform 


<!--org.jumpmind.db.platform.JdbcDatabasePlatformFactory-->
Constructor<? extends IDatabasePlatform> construtor = clazz.getConstructor(DataSource.class, SqlTemplateSettings.class);
            IDatabasePlatform platform = construtor.newInstance(dataSource, settings);

 
2020-10-13 16:57:47.929  INFO 13740 --- [manager-startup] o.j.metl.core.runtime.AgentRuntime       : Deploying the  Local database  resource to the <default user on DESKTOP-INGH9R2> agent
resourceId:abc9cfbe-44cf-4e83-9eb1-325422db9dd8
sql:select AIRDATA,AIRTIME from MAI_DATA
projectVersionId:f55241fd-9564-4f6a-bd0d-95a50a729000
2020-10-13 16:57:49.150  INFO 13740 --- [manager-startup] o.j.metl.core.runtime.AgentRuntime       : Deploying the Oracle Data source  resource to the <default user on DESKTOP-INGH9R2> agent
2020-10-13 16:57:49.308  INFO 13740 --- [nio-9999-exec-1] o.j.d.p.JdbcDatabasePlatformFactory      : Detected database 'Oracle', version '11', protocol 'oracle'
2020-10-13 16:57:49,342 ERROR [GlobalExceptionHandler.java:47] : requestUrl=http://localhost:9999/open/api/ml/comp/handler?name=rdbmsreader&param=%7B%22sql%22:%22select+AIRDATA,AIRTIME+from+MAI_DATA%22,%22resourceId%22:%22abc9cfbe-44cf-4e83-9eb1-325422db9dd8%22,%22projectVersionId%22:%22f55241fd-9564-4f6a-bd0d-95a50a729000%22%7D  
2020-10-13 16:57:49,343 ERROR [GlobalExceptionHandler.java:27] : System_error:requestId=1516430392  
org.jumpmind.db.platform.DdlException: Could not create a platform of type Oracle
	at org.jumpmind.db.platform.JdbcDatabasePlatformFactory.createNewPlatformInstance(JdbcDatabasePlatformFactory.java:166)
	at org.jumpmind.metl.ui.views.design.RdbmsHandler.sqlExecute(RdbmsHandler.java:68)
	at org.jumpmind.metl.ui.views.design.RdbmsHandler.execute(RdbmsHandler.java:42)
	at org.jumpmind.metl.core.handler.HandlerService.execute(HandlerService.java:14)
	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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:338)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy138.execute(Unknown Source)
	at com.yak.api.mlengine.CompController.handler(CompController.java:53)
	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:209)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:870)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:776)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:870)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:158)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:126)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:111)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.yak.platform.api.config.CORSFilter.doFilter(CORSFilter.java:71)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:84)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.reflect.InvocationTargetException
	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 org.jumpmind.db.platform.JdbcDatabasePlatformFactory.createNewPlatformInstance(JdbcDatabasePlatformFactory.java:160)
	... 71 more
Caused by: java.lang.NoClassDefFoundError: org/springframework/jdbc/support/lob/OracleLobHandler
	at org.jumpmind.db.platform.oracle.OracleLobHandler.<init>(OracleLobHandler.java:16)
	at org.jumpmind.db.platform.oracle.OracleDatabasePlatform.createSqlTemplate(OracleDatabasePlatform.java:70)
	at org.jumpmind.db.platform.oracle.OracleDatabasePlatform.createSqlTemplate(OracleDatabasePlatform.java:34)
	at org.jumpmind.db.platform.AbstractJdbcDatabasePlatform.<init>(AbstractJdbcDatabasePlatform.java:47)
	at org.jumpmind.db.platform.oracle.OracleDatabasePlatform.<init>(OracleDatabasePlatform.java:55)
	... 76 more
Caused by: java.lang.ClassNotFoundException: org.springframework.jdbc.support.lob.OracleLobHandler
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:338)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	... 81 more

Solution:

After inquiry, when initializing OracleJdbcSqlTemplate, because Spring-jdbc-5. 0.4 is used in SpringBoot2.0, the above error message appears.

This code previously uses spring-jdbc. jar of spring 4 without reporting an error, because the class OracleLobHandler does not exist after spring 5.

Because you don't want to modify the code, continue to use the old version: 


<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.2.6.RELEASE</version>
  </dependency>
Related articles: