Main Tutorials

Spring Boot JDBC + Oracle database + Commons DBCP2 example

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

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.mkyong</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.mkyong: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.mkyong.dao;

import com.mkyong.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.mkyong.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, 'mkyong','[email protected]', TO_DATE('2017-02-11', 'yyyy-mm-dd'));
INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(2, 'yflow','[email protected]', TO_DATE('2017-02-12', 'yyyy-mm-dd'));
INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(3, 'zilap','[email protected]', 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.mkyong;

import com.mkyong.dao.CustomerRepository;
import com.mkyong.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='mkyong', email='[email protected]', date=2017-02-11}
Customer{id=2, name='yflow', email='[email protected]', date=2017-02-12}
Customer{id=3, name='zilap', email='[email protected]', date=2017-02-13}
Done!

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

Download Source Code

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

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
8 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Carol
5 years ago

Thanks for the post!

fred314
3 years ago

Thanks for the post. Just one question: how would you do if you had to change the default value “JDBC Thin Client” in the Oracle web admin session page (which corresponds to the column MODULE of the view V$SESSION if I’m mot mistaken)?

Sandeep
4 years ago

How can make sure that our application is not creating too many inactive DB sessions from application side while using the pool configurations. Currently i see a problem where connections are not being closed while using pooled config.

jyotsna
4 years ago

Thanks for the post.
If I have created the table in the db and after that if I want to update any value in that. It’s not happening. I am not able to access the table from the SpringBootConsoleApplication (If I remove schema.sql and data.sql, I am not getting the existing values from the table).

O/P after removing Schema.sql and data.sql (but the table has been already created and the table has values)
DATASOURCE= **************************
Display all customers…
Done!

Jyotsna
4 years ago

Thanks for the post.
Just one question if anyone can help me..
Once we have created the table and inserted values, and if I am trying to insert anything or update anything.. it’s not happening. I am not getting any error. Just not getting anything from the DB

shanki
6 years ago

Excluding the tomcat-jdbc module results in datasource and Jdbctemplate beans not being created (and hence run time exceptions) by the spring boot jdbc framework. Remove the exclusion from your gradle/maven build file and it will work like bewdy.

KALLOL SAHA
6 years ago

“C:Program FilesJavajdk1.8.0_131binjava” -XX:TieredStopAtLevel=1 -noverify -Dspring.output.ansi.enabled=always -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=59631 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Djava.rmi.server.hostname=localhost -Dspring.liveBeansView.mbeanDomain -Dspring.application.admin.enabled=true “-javaagent:C:Program FilesJetBrainsIntelliJ IDEA 2017.3.1libidea_rt.jar=59632:C:Program FilesJetBrainsIntelliJ IDEA 2017.3.1bin” -Dfile.encoding=UTF-8 -classpath “C:Program FilesJavajdk1.8.0_131jrelibcharsets.jar;C:Program FilesJavajdk1.8.0_131jrelibdeploy.jar;C:Program FilesJavajdk1.8.0_131jrelibextaccess-bridge-64.jar;C:Program FilesJavajdk1.8.0_131jrelibextcldrdata.jar;C:Program FilesJavajdk1.8.0_131jrelibextdnsns.jar;C:Program FilesJavajdk1.8.0_131jrelibextjaccess.jar;C:Program FilesJavajdk1.8.0_131jrelibextjfxrt.jar;C:Program FilesJavajdk1.8.0_131jrelibextlocaledata.jar;C:Program FilesJavajdk1.8.0_131jrelibextnashorn.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunec.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunjce_provider.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunmscapi.jar;C:Program FilesJavajdk1.8.0_131jrelibextsunpkcs11.jar;C:Program FilesJavajdk1.8.0_131jrelibextzipfs.jar;C:Program FilesJavajdk1.8.0_131jrelibjavaws.jar;C:Program FilesJavajdk1.8.0_131jrelibjce.jar;C:Program FilesJavajdk1.8.0_131jrelibjfr.jar;C:Program FilesJavajdk1.8.0_131jrelibjfxswt.jar;C:Program FilesJavajdk1.8.0_131jrelibjsse.jar;C:Program FilesJavajdk1.8.0_131jrelibmanagement-agent.jar;C:Program FilesJavajdk1.8.0_131jrelibplugin.jar;C:Program FilesJavajdk1.8.0_131jrelibresources.jar;C:Program FilesJavajdk1.8.0_131jrelibrt.jar;D:Git-hub Repositoryspring-boot-jdbc-oracle-exampletargetclasses;C:UsersSONY.m2repositoryorgspringframeworkbootspring-boot-starter1.5.9.RELEASEspring-boot-starter-1.5.9.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkbootspring-boot1.5.9.RELEASEspring-boot-1.5.9.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-context4.3.13.RELEASEspring-context-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-aop4.3.13.RELEASEspring-aop-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-expression4.3.13.RELEASEspring-expression-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkbootspring-boot-autoconfigure1.5.9.RELEASEspring-boot-autoconfigure-1.5.9.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkbootspring-boot-starter-logging1.5.9.RELEASEspring-boot-starter-logging-1.5.9.RELEASE.jar;C:UsersSONY.m2repositorychqoslogbacklogback-classic1.1.11logback-classic-1.1.11.jar;C:UsersSONY.m2repositorychqoslogbacklogback-core1.1.11logback-core-1.1.11.jar;C:UsersSONY.m2repositoryorgslf4jslf4j-api1.7.25slf4j-api-1.7.25.jar;C:UsersSONY.m2repositoryorgslf4jjcl-over-slf4j1.7.25jcl-over-slf4j-1.7.25.jar;C:UsersSONY.m2repositoryorgslf4jjul-to-slf4j1.7.25jul-to-slf4j-1.7.25.jar;C:UsersSONY.m2repositoryorgslf4jlog4j-over-slf4j1.7.25log4j-over-slf4j-1.7.25.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-core4.3.13.RELEASEspring-core-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgyamlsnakeyaml1.17snakeyaml-1.17.jar;C:UsersSONY.m2repositoryorgspringframeworkbootspring-boot-starter-jdbc1.5.9.RELEASEspring-boot-starter-jdbc-1.5.9.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-jdbc4.3.13.RELEASEspring-jdbc-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-beans4.3.13.RELEASEspring-beans-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgspringframeworkspring-tx4.3.13.RELEASEspring-tx-4.3.13.RELEASE.jar;C:UsersSONY.m2repositoryorgapachecommonscommons-dbcp22.1.1commons-dbcp2-2.1.1.jar;C:UsersSONY.m2repositoryorgapachecommonscommons-pool22.4.3commons-pool2-2.4.3.jar;C:UsersSONY.m2repositorycommons-loggingcommons-logging1.2commons-logging-1.2.jar” com.mkyong.SpringBootConsoleApplication
DATASOURCE = org.apache.commons.dbcp2.BasicDataSource@676cf48
Display all customers…
2018-01-01 17:36:57 ERROR o.s.boot.SpringApplication – Application startup failed
java.lang.IllegalStateException: Failed to execute CommandLineRunner
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:735)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:716)
at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:703)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:304)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1118)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1107)
at com.mkyong.SpringBootConsoleApplication.main(SpringBootConsoleApplication.java:25)
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot load JDBC driver class ‘oracle.jdbc.OracleDriver’
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:394)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:488)
at com.mkyong.dao.CustomerRepository.findAll(CustomerRepository.java:18)
at com.mkyong.dao.CustomerRepository$$FastClassBySpringCGLIB$$37709738.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
at com.mkyong.dao.CustomerRepository$$EnhancerBySpringCGLIB$$befc0419.findAll()
at com.mkyong.SpringBootConsoleApplication.run(SpringBootConsoleApplication.java:38)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:732)
… 6 common frames omitted
Caused by: java.sql.SQLException: Cannot load JDBC driver class ‘oracle.jdbc.OracleDriver’
at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:2139)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2033)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
… 20 common frames omitted
Caused by: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:2131)
… 24 common frames omitted

Process finished with exit code 1

Importing this project i am getting above error message. unable to load JDBC driver. Please find and help me to fix this issue

Adnan
6 years ago

***************************
APPLICATION FAILED TO START
***************************

Description:

Field userDao in com.bi.webservices.asaanaccount.main.AsaanAccountWsApplication required a bean of type ‘com.bi.dao.UserDao’ that could not be found.

Action:

Consider defining a bean of type ‘com.bi.dao.UserDao’ in your configuration.

Please help me with this error