FavTuts

Favourite tutorials for developers

Spring Boot

Spring Boot JDBC + Oracle database + Commons DBCP2 example

ByFavTuts Editorial

Jun 14, 2022 #connection pool, #dbcp2, #JDBC, #Oracle, #Spring Boot, #spring jdbc

In this article, we will show you how to create a Spring Boot JDBC application + Oracle database + Commons DBCP2 connection pool.

Tools used in this article :

  1. Spring Boot 1.5.1.RELEASE
  2. Oracle database 11g express
  3. Oracle JDBC driver ojdbc7.jar
  4. Commons DBCP2 2.1.1
  5. Maven
  6. Java 8

Note

Related – Spring Boot JDBC + MySQL + HikariCP example

1. Project Structure

A standard Maven project structure.

2. Project Dependency

Download and Install Oracle JDBC driver.
Oracle license restriction, you can’t get the Oracle JDBC driver from the public Maven repository. Instead, you need to go the Oracle website to download the driver and install into the Local Maven repository manually.

Declares Spring Boot JDBC spring-boot-starter-jdbc, Oracle JDBC Driver (install manually) ojdbc7, and Common DBCP2 connection pool.

pom.xml

<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/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.favtuts</groupId>
    <artifactId>spring-boot-jdbc</artifactId>
    <packaging>jar</packaging>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.1.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>

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

		<!-- Exclude teh default Tomcat connection pool -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

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

        <!-- Common DBCP2 connection pool -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <!-- Package as an executable jar/war -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

        </plugins>
    </build>

</project>

Terminal

$ mvn dependency:tree
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building spring-boot-jdbc 1.0
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ spring-boot-jdbc ---
[INFO] com.favtuts:spring-boot-jdbc:jar:1.0
[INFO] +- org.springframework.boot:spring-boot-starter:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot:jar:1.5.1.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-context:jar:4.3.6.RELEASE:compile
[INFO] |  |     +- org.springframework:spring-aop:jar:4.3.6.RELEASE:compile
[INFO] |  |     \- org.springframework:spring-expression:jar:4.3.6.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-autoconfigure:jar:1.5.1.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-logging:jar:1.5.1.RELEASE:compile
[INFO] |  |  +- ch.qos.logback:logback-classic:jar:1.1.9:compile
[INFO] |  |  |  +- ch.qos.logback:logback-core:jar:1.1.9:compile
[INFO] |  |  |  \- org.slf4j:slf4j-api:jar:1.7.22:compile
[INFO] |  |  +- org.slf4j:jcl-over-slf4j:jar:1.7.22:compile
[INFO] |  |  +- org.slf4j:jul-to-slf4j:jar:1.7.22:compile
[INFO] |  |  \- org.slf4j:log4j-over-slf4j:jar:1.7.22:compile
[INFO] |  +- org.springframework:spring-core:jar:4.3.6.RELEASE:compile
[INFO] |  \- org.yaml:snakeyaml:jar:1.17:runtime
[INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:1.5.1.RELEASE:compile
[INFO] |  \- org.springframework:spring-jdbc:jar:4.3.6.RELEASE:compile
[INFO] |     +- org.springframework:spring-beans:jar:4.3.6.RELEASE:compile
[INFO] |     \- org.springframework:spring-tx:jar:4.3.6.RELEASE:compile
[INFO] +- com.oracle:ojdbc7:jar:12.1.0:compile
[INFO] \- org.apache.commons:commons-dbcp2:jar:2.1.1:compile
[INFO]    +- org.apache.commons:commons-pool2:jar:2.4.2:compile
[INFO]    \- commons-logging:commons-logging:jar:1.2:compile
[INFO] ------------------------------------------------------------------------

Database Connection Pooling
Spring Boot uses Tomcat pooling tomcat-jdbc by default, and follow this sequence to find the connection pool : Tomcat pool -->> - HikariCP -->> Commons DBCP -->> Commons DBCP2

Read this official Spring Boot doc – Connection to a production database

3. JdbcTemplate

3.1 Spring Boot will register a JdbcTemplate bean automatically, just inject it into your bean.

CustomerRepository.java

package com.favtuts.dao;

import com.favtuts.model.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CustomerRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

	// thanks Java 8, look the custom RowMapper
    public List<Customer> findAll() {

        List<Customer> result = jdbcTemplate.query(
                "SELECT id, name, email, created_date FROM customer",
                (rs, rowNum) -> new Customer(rs.getInt("id"),
                        rs.getString("name"), rs.getString("email"), rs.getDate("created_date"))
        );

        return result;

    }

}

3.2 Customer Model.

Customer.java

package com.favtuts.model;

import java.util.Date;

public class Customer {

    int id;
    String name;
    String email;
    Date date;

    public Customer(int id, String name, String email, Date date) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.date = date;
    }

    //getters and setters and toString...
}

4. Database Initialization

Spring boot enables the dataSource initializer by default and loads SQL scripts – schema.sql and data.sql from the root of the classpath.

4.1 SQL script to create a customer table.

schema.sql

CREATE TABLE CUSTOMER(
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(100) NOT NULL,
CREATED_DATE DATE NOT NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY (ID)
);

4.2 SQL script to insert 3 rows into the customer table.

data.sql

INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(1, 'favtuts','111@yahoo.com', TO_DATE('2017-02-11', 'yyyy-mm-dd'));
INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(2, 'yflow','222@yahoo.com', TO_DATE('2017-02-12', 'yyyy-mm-dd'));
INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(3, 'zilap','333@yahoo.com', TO_DATE('2017-02-13', 'yyyy-mm-dd'));

Note

Read this – Spring Database initialization

5. Configuration

Configure Oracle and dbcp2 settings.

application.properties

spring.main.banner-mode=off

# Set true for first time db initialization.
spring.datasource.initialize=true

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=system
spring.datasource.password=password
spring.datasource.driver-class-oracle.jdbc.driver.OracleDriver

# dbcp2 settings
# spring.datasource.dbcp2.*

spring.datasource.dbcp2.initial-size=7
spring.datasource.dbcp2.max-total=20
spring.datasource.dbcp2.pool-prepared-statements=true

6. @SpringBootApplication

Spring Boot command line application

SpringBootConsoleApplication.java

package com.favtuts;

import com.favtuts.dao.CustomerRepository;
import com.favtuts.model.Customer;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import javax.sql.DataSource;
import java.util.List;

import static java.lang.System.exit;

@SpringBootApplication
public class SpringBootConsoleApplication implements CommandLineRunner {

    @Autowired
    DataSource dataSource;

    @Autowired
    CustomerRepository customerRepository;

    public static void main(String[] args) throws Exception {
        SpringApplication.run(SpringBootConsoleApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {

        System.out.println("DATASOURCE = " + dataSource);

        /// Get dbcp2 datasource settings
        // BasicDataSource newds = (BasicDataSource) dataSource;
        // System.out.println("BasicDataSource = " + newds.getInitialSize());

        System.out.println("Display all customers...");
        List<Customer> list = customerRepository.findAll();
        list.forEach(x -> System.out.println(x));

        System.out.println("Done!");

        exit(0);
    }
}

7. DEMO

Run it, Spring Boot loads schema.sql and data.sql scripts automatically, and display the result.

Terminal

DATASOURCE = org.apache.commons.dbcp2.BasicDataSource@4eb386df
Display all customers...
Customer{id=1, name='favtuts', email='111@yahoo.com', date=2017-02-11}
Customer{id=2, name='yflow', email='222@yahoo.com', date=2017-02-12}
Customer{id=3, name='zilap', email='333@yahoo.com', date=2017-02-13}
Done!

If you set a breakpoint in the debug session, review the Oracle web admin session page.

Download Source Code

$ git clone https://github.com/favtuts/java-spring-tutorials.git
$ cd spring-boot-jdbc-oracle
$ mvn spring-boot:run

References

  1. Maven Install Oracle JDBC driver
  2. Connect to Oracle DB via JDBC driver
  3. Spring Boot – Working with SQL databases
  4. Spring Boot – Database initialization
  5. Spring Boot common application properties
  6. OracleDriver Doc
  7. Oracle Database 12.1.0.2 JDBC Driver & UCP Downloads
  8. Using Java with Oracle Database
  9. Commons DBCP2 Configuration Parameters

By FavTuts Editorial

Related Post

Spring Boot

Validation with Spring Boot – the Complete Guide

Dec 10, 2024 FavTuts Editorial
Spring Boot

How to Record Request and Response Bodies in Sping Boot Applications

Dec 8, 2024 FavTuts Editorial
Spring Boot

How to create a Spring Boot console application

Dec 4, 2024 FavTuts Editorial

Leave a Reply

Your email address will not be published. Required fields are marked *

You missed

Spring Boot

Validation with Spring Boot – the Complete Guide

December 10, 2024 FavTuts Editorial
Spring Boot

How to Record Request and Response Bodies in Sping Boot Applications

December 8, 2024 FavTuts Editorial
Spring Boot

How to create a Spring Boot console application

December 4, 2024 FavTuts Editorial
PHP

How To Change Your PHP Settings on Ubuntu

November 30, 2024 FavTuts Editorial