Spring Boot + Spring Data JPA + Oracle example
In this article, we will show you how to create a Spring Boot + Spring Data JPA + Oracle + HikariCP connection pool example.
Tools used in this article :
- Spring Boot 1.5.1.RELEASE
- Spring Data 1.13.0.RELEASE
- Hibernate 5
- Oracle database 11g express
- Oracle JDBC driver ojdbc7.jar
- HikariCP 2.6
- Maven
- Java 8
1. Project Structure
A standard Maven project structure.

2. Project Dependency
Declares spring-boot-starter-data-jpa, it grabs Spring Data, Hibernate and JPA related stuff.
<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-jpa-oracle-example</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> <!-- Spring data JPA, default tomcat pool, exclude it --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</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> <!-- HikariCP connection pool --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>2.6.0</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>
Review the project dependencies in detail.
$ mvn dependency:tree [INFO] ------------------------------------------------------------------------ [INFO] Building spring-boot-jpa-oracle-example 1.0 [INFO] ------------------------------------------------------------------------ [INFO] [INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ spring-boot-jpa-oracle-example --- [INFO] com.mkyong:spring-boot-jpa-oracle-example: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-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: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-data-jpa:jar:1.5.1.RELEASE:compile [INFO] | +- org.springframework.boot:spring-boot-starter-aop:jar:1.5.1.RELEASE:compile [INFO] | | +- org.springframework:spring-aop:jar:4.3.6.RELEASE:compile [INFO] | | \- org.aspectj:aspectjweaver:jar:1.8.9:compile [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.hibernate:hibernate-core:jar:5.0.11.Final:compile [INFO] | | +- org.jboss.logging:jboss-logging:jar:3.3.0.Final:compile [INFO] | | +- org.hibernate.javax.persistence:hibernate-jpa-2.1-api:jar:1.0.0.Final:compile [INFO] | | +- org.javassist:javassist:jar:3.21.0-GA:compile [INFO] | | +- antlr:antlr:jar:2.7.7:compile [INFO] | | +- org.jboss:jandex:jar:2.0.0.Final:compile [INFO] | | +- dom4j:dom4j:jar:1.6.1:compile [INFO] | | \- org.hibernate.common:hibernate-commons-annotations:jar:5.0.1.Final:compile [INFO] | +- org.hibernate:hibernate-entitymanager:jar:5.0.11.Final:compile [INFO] | +- javax.transaction:javax.transaction-api:jar:1.2:compile [INFO] | +- org.springframework.data:spring-data-jpa:jar:1.11.0.RELEASE:compile [INFO] | | +- org.springframework.data:spring-data-commons:jar:1.13.0.RELEASE:compile [INFO] | | +- org.springframework:spring-orm:jar:4.3.6.RELEASE:compile [INFO] | | +- org.springframework:spring-tx:jar:4.3.6.RELEASE:compile [INFO] | | \- org.springframework:spring-beans:jar:4.3.6.RELEASE:compile [INFO] | \- org.springframework:spring-aspects:jar:4.3.6.RELEASE:compile [INFO] +- com.oracle:ojdbc7:jar:12.1.0:compile [INFO] \- com.zaxxer:HikariCP:jar:2.6.0:compile [INFO] \- org.slf4j:slf4j-api:jar:1.7.22:compile [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------
Read this – Maven Install Oracle JDBC driver
3. Java Persistence API – JPA
3.1 Customer Model. Add JPA annotations, and use “sequence” to generate the auto increase primary ID.
package com.mkyong.model; import javax.persistence.*; import java.util.Date; @Entity public class Customer { // "customer_seq" is Oracle sequence name. @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CUST_SEQ") @SequenceGenerator(sequenceName = "customer_seq", allocationSize = 1, name = "CUST_SEQ") Long id; String name; String email; @Column(name = "CREATED_DATE") Date date; //getters and setters, contructors
4. Configuration + Database Initialization
4.1 Configure Oracle data source, HikariCP settings and show Hibernate query.
spring.main.banner-mode=off # create and drop tables and sequences, loads import.sql spring.jpa.hibernate.ddl-auto=create-drop # Oracle settings 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 # HikariCP settings # spring.datasource.hikari.* spring.datasource.hikari.connection-timeout=60000 spring.datasource.hikari.maximum-pool-size=5 # logging logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n logging.level.org.hibernate.SQL=debug #logging.level.org.hibernate.type.descriptor.sql=trace logging.level.=error
4.2 If import.sql is found in the classpath, Hibernate will load it automatically.
INSERT INTO "CUSTOMER" (ID, NAME, EMAIL, CREATED_DATE) VALUES(1, 'mkyong','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'));
Read this – Spring Database initialization
5. @Repository
5.1 Create an interface and extends Spring Data CrudRepository
package com.mkyong.dao; import com.mkyong.model.Customer; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import org.springframework.data.repository.query.Param; import java.util.Date; import java.util.List; import java.util.stream.Stream; public interface CustomerRepository extends CrudRepository<Customer, Long> { List<Customer> findByEmail(String email); List<Customer> findByDate(Date date); // custom query example and return a stream @Query("select c from Customer c where c.email = :email") Stream<Customer> findByEmailReturnStream(@Param("email") String email);
No need implementation, Spring data will create the common implementation by field name, like findByfieldName (). Read this working with Spring Data Repositories
6. Spring Boot Starter
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 org.springframework.transaction.annotation.Transactional; import javax.sql.DataSource; import java.text.SimpleDateFormat; import java.util.stream.Stream; import static java.lang.System.exit; @SpringBootApplication public class Application implements CommandLineRunner { private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); @Autowired DataSource dataSource; @Autowired CustomerRepository customerRepository; public static void main(String[] args) throws Exception { SpringApplication.run(Application.class, args); @Transactional(readOnly = true) @Override public void run(String... args) throws Exception { System.out.println("DATASOURCE = " + dataSource); System.out.println("\n1.findAll()..."); for (Customer customer : customerRepository.findAll()) { System.out.println(customer); System.out.println("\n2.findByEmail(String email)..."); for (Customer customer : customerRepository.findByEmail("222@yahoo.com")) { System.out.println(customer); System.out.println("\n3.findByDate(Date date)..."); for (Customer customer : customerRepository.findByDate(sdf.parse("2017-02-12"))) { System.out.println(customer); // For Stream, need @Transactional System.out.println("\n4.findByEmailReturnStream(@Param(\"email\") String email)..."); try (Stream<Customer> stream = customerRepository.findByEmailReturnStream("333@yahoo.com")) { stream.forEach(x -> System.out.println(x)); System.out.println("Done!"); exit(0);
8. DEMO
Run it, read console for self-explanatory.
2017-02-22 12:36:49 DEBUG org.hibernate.SQL - drop table customer cascade constraints 2017-02-22 12:36:49 ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: drop table customer cascade constraints 2017-02-22 12:36:49 ERROR o.h.tool.hbm2ddl.SchemaExport - ORA-00942: table or view does not exist 2017-02-22 12:36:49 DEBUG org.hibernate.SQL - drop sequence customer_seq 2017-02-22 12:36:49 ERROR o.h.tool.hbm2ddl.SchemaExport - HHH000389: Unsuccessful: drop sequence customer_seq 2017-02-22 12:36:49 ERROR o.h.tool.hbm2ddl.SchemaExport - ORA-02289: sequence does not exist 2017-02-22 12:36:49 DEBUG org.hibernate.SQL - create sequence customer_seq start with 1 increment by 1 2017-02-22 12:36:49 DEBUG org.hibernate.SQL - create table customer (id number(19,0) not null, created_date timestamp, email varchar2(255 char), name varchar2(255 char), primary key (id)) DATASOURCE = HikariDataSource (HikariPool-1) 1.findAll()... 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - select customer0_.id as id1_0_, customer0_.created_date as created_date2_0_, customer0_.email as email3_0_, customer0_.name as name4_0_ from customer customer0_ Customer{id=1, name='mkyong', email='111@yahoo.com', date=2017-02-11 00:00:00.0} Customer{id=2, name='yflow', email='222@yahoo.com', date=2017-02-12 00:00:00.0} Customer{id=3, name='zilap', email='333@yahoo.com', date=2017-02-13 00:00:00.0} 2.findByEmail(String email)... 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - select customer0_.id as id1_0_, customer0_.created_date as created_date2_0_, customer0_.email as email3_0_, customer0_.name as name4_0_ from customer customer0_ where customer0_.email=? Customer{id=2, name='yflow', email='222@yahoo.com', date=2017-02-12 00:00:00.0} 3.findByDate(Date date)... 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - select customer0_.id as id1_0_, customer0_.created_date as created_date2_0_, customer0_.email as email3_0_, customer0_.name as name4_0_ from customer customer0_ where customer0_.created_date=? Customer{id=2, name='yflow', email='222@yahoo.com', date=2017-02-12 00:00:00.0} 4.findByEmailReturnStream(@Param("email") String email)... 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - select customer0_.id as id1_0_, customer0_.created_date as created_date2_0_, customer0_.email as email3_0_, customer0_.name as name4_0_ from customer customer0_ where customer0_.email=? Customer{id=3, name='zilap', email='333@yahoo.com', date=2017-02-13 00:00:00.0} Done! 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - drop table customer cascade constraints 2017-02-22 12:36:50 DEBUG org.hibernate.SQL - drop sequence customer_seq Process finished with exit code 0
References
- Maven Install Oracle JDBC driver
- Initialize a database using JPA
- Spring boot ddl auto generator
- Spring Data Commons – Reference Documentation
- Accessing Data with JPA
- TopLink JPA: How to Configure Primary Key Generation
- Oracle / PLSQL: Sequences (Autonumber)
- Spring Boot JDBC + Oracle database + Commons DBCP2 example