Get an oracle connection using JDBC, tomcat and jpa

Question

I'm porting a webservice from .NET WCF, which has a few non-generated classes that help with the control, to a standalone java web service.

I'm having a hard time understanding all the options to connection to an Oracle Database 10g. I've been scanning the internet to try and find a simple way to get a connection or use the default setup provided by Spring Boot.

More or less what I want: Connection conn = DriverManager.getConnection("connectionDB")

I am using Spring Boot, spring, jpa and tomcat (8.5) connection pooling with Maven. Here is the POM file:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.mydomain</groupId>
<artifactId>ArtifactName</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>

<name>SolicitacaoProcedimento3_03_02</name>
<description></description>

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.8.RELEASE</version>
    <relativePath/> 
    <!-- lookup parent from repository -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web-services</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-tomcat</artifactId>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <dependency>
        <groupId>com.sun.xml.ws</groupId>
        <artifactId>jaxws-rt</artifactId>
        <version>2.3.0</version>
    </dependency>

    <dependency>
        <groupId>org.glassfish.metro</groupId>
        <artifactId>webservices-rt</artifactId>
        <version>2.3</version>
    </dependency>

    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <scope>test</scope>
        <version>2.44.0</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <scope>test</scope>
        <version>4.11</version>
    </dependency>

    <!-- Spring data JPA, default tomcat pool -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- Oracle JDBC driver -->
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc7</artifactId>
        <version>12.1.0</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <plugin>
            <groupId>org.jvnet.jax-ws-commons</groupId>
            <artifactId>jaxws-maven-plugin</artifactId>
            <version>2.3</version>
            <executions>
                <execution>
                    <goals>
                        <goal>wsimport</goal>
                    </goals>
                    <configuration>
                        <wsdlFiles>
                            <wsdlFile>tissSolicitacaoProcedimentoV3_03_02.wsdl</wsdlFile>
                        </wsdlFiles>
                        <vmArgs>
                            <vmArg>-Djavax.xml.accessExternalSchema=all</vmArg>
                        </vmArgs>
                        <staleFile>${project.build.directory}/jaxws/stale/tissSolicitacaoProcedimentoV3_03_02.stale</staleFile>
                    </configuration>
                    <id>wsimport-generate-tissSolicitacaoProcedimentoV3_03_02</id>
                    <phase>generate-sources</phase>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>javax.xml</groupId>
                    <artifactId>webservices-api</artifactId>
                    <version>2.0</version>
                </dependency>
            </dependencies>
            <configuration>
                <sourceDestDir>${project.build.directory}/generated-sources/jaxws-wsimport</sourceDestDir>
                <xnocompile>true</xnocompile>
                <verbose>true</verbose>
                <extension>true</extension>
                <catalog>${basedir}/src/jax-ws-catalog.xml</catalog>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-war-plugin</artifactId>
            <version>2.0.2</version>
            <configuration>
                <webResources>
                    <resource>
                        <directory>src</directory>
                        <targetPath>WEB-INF</targetPath>
                        <includes>
                            <include>jax-ws-catalog.xml</include>
                            <include>wsdl/**</include>
                        </includes>
                    </resource>
                </webResources>
            </configuration>
        </plugin>
    </plugins>
</build>

Here is my application.properties file:

spring.datasource.url=jdbc:oracle:oci:@tnsDBName
spring.datasource.username=usr
spring.datasource.password=pass
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

#hibernate config
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

Any help would be much appreciated.

How can I change the existing non-generated classes to use jpa? In the .net project a Connection Factory was used to access the db. I'm not at all sure how to go about doing the same in the java project. — Chrispy, Dec 31 '17 at 01:37
who knows what is a "non-generated class". A class you cannot add annotations to? but then you can define XML metadata for them. Something else? — , Dec 31 '17 at 07:33
I have a bunch of classes which were generated by jaxws-wsimport, but I have a few which non-generated classes are like the control part of the project and were borrowed from the .NET WCF project. — Chrispy, Dec 31 '17 at 17:16
I posted a solution to this question here: [https://stackoverflow.com/questions/48352498/use-autowired-datasource-in-spring-boot-application-getconnection/48729992#48729992](https://stackoverflow.com/questions/48352498/use-autowired-datasource-in-spring-boot-application-getconnection/48729992#48729992) — Chrispy, Feb 11 '18 at 09:17

score 0 · Answer 1 · edited Dec 31 '17 at 02:02

0

Can you explain what are you want to achieve? If you need only to setup connection to database (datasource) there is a two ways:

by using application.properties (spring.datasource.*), example spring.datasource.url= db_url
by writing a Java configuration class with @Configuration annotation you can read more about it from here

edited Dec 31 '17 at 02:02

Haroldo Gondim

7,725
9
43
62

answered Dec 31 '17 at 01:34

Danilo Tošić

1

I'm already using application.properties, I posted the configurations in my question. I edited it to add the configuration. – Chrispy Dec 31 '17 at 01:44
What I need to know is: how do I access a connection to the DB using spring.datasource.*. What do I need to change in my existing .net classes so that they can use the implicit DB Connection. – Chrispy Dec 31 '17 at 01:48
What are you need is to create Java configuration class to setup your datasource. You can access to your datasource by @Autowired DataSource dataSource. – Danilo Tošić Dec 31 '17 at 09:54
Hi, thanks for the reply. I thought I didn't need a Java Configuration class if I'm using Spring Boot and have the application.properties file configured. I just want to know how to access the DataSource in existing classes. – Chrispy Jan 02 '18 at 13:45
I think I'm getting it, but still can't find the import I need for the @Autowired to work. Thanks – Chrispy Jan 02 '18 at 15:52
Does: import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; look right? – Chrispy Jan 02 '18 at 17:26
hi, @user9151096 thanks for the response. Basically I created the `application.properties` file like in the question, except without the `hibernate config` and I'm using `@Autowired private DataSource dataSource;` I need to know if there is some instanciation of `dataSource` in the code to return a connection through `dataSource.getConnection();` At the moment it's returning `java.lang.NullPointerException`. Thanks in advance... – Chrispy Jan 17 '18 at 21:43
hi @user9151096, the `by using application.properties (spring.datasource.*), example spring.datasource.url= db_url` is just what I'm trying to use. I added the ´application.properties´ to the question. I just want to know how to use the autoconfigured dataSource in a class. – Chrispy Jan 23 '18 at 16:03
Hi again. Just an update. The problem I am having is that I cannot inject the datasource `@Autowired private DataSource dataSource;` created by Spring Boot into a `@WebService` endpoint that is generated by `JAX-WS` along with all the classes from the wsdl (in netbeans 8.2). It doesn't seem to work even if the class calling the dataSource has itself got spring-boot annotations. If I call from the endpoint it seems that spring-boot cannot see it. How do I inject spring boot beans into jaxws endpoint? And I have tried to find this on google with no full answers or they are too old. – Chrispy Feb 08 '18 at 20:42
I posted a solution to this question here: [https://stackoverflow.com/questions/48352498/use-autowired-datasource-in-spring-boot-application-getconnection/48729992#48729992](https://stackoverflow.com/questions/48352498/use-autowired-datasource-in-spring-boot-application-getconnection/48729992#48729992) – Chrispy Feb 11 '18 at 09:18

Get an oracle connection using JDBC, tomcat and jpa

1 Answers1