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 :
- Spring Boot 1.5.1.RELEASE
- Oracle database 11g express
- Oracle JDBC driver ojdbc7.jar
- Commons DBCP2 2.1.1
- Maven
- Java 8
Related – Spring Boot JDBC + MySQL + HikariCP example
1. Project Structure
A standard Maven project structure.
2. Project Dependency
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.
<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>
$ 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] ------------------------------------------------------------------------
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.
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.
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.
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.
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'));
Read this – Spring Database initialization
5. Configuration
Configure Oracle and dbcp2 settings.
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
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.
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
- Maven Install Oracle JDBC driver
- Connect to Oracle DB via JDBC driver
- Spring Boot – Working with SQL databases
- Spring Boot – Database initialization
- Spring Boot common application properties
- OracleDriver Doc
- Oracle Database 12.1.0.2 JDBC Driver & UCP Downloads
- Using Java with Oracle Database
- Commons DBCP2 Configuration Parameters
Thanks for the post!
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)?
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.
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!
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
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.
“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
***************************
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