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.

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 :

  1. Spring Boot 1.5.1.RELEASE
  2. Spring Data 1.13.0.RELEASE
  3. Hibernate 5
  4. Oracle database 11g express
  5. Oracle JDBC driver ojdbc7.jar
  6. HikariCP 2.6
  7. Maven
  8. 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.

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-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.

Terminal
$ 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] ------------------------------------------------------------------------
Note
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.

Customer.java
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.

application.properties
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.

import.sql
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'));
Note
Read this – Spring Database initialization

5. @Repository

5.1 Create an interface and extends Spring Data CrudRepository

CustomerRepository.java
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);
Note
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

Application.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 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.

Terminal
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

  1. Maven Install Oracle JDBC driver
  2. Initialize a database using JPA
  3. Spring boot ddl auto generator
  4. Spring Data Commons – Reference Documentation
  5. Accessing Data with JPA
  6. TopLink JPA: How to Configure Primary Key Generation
  7. Oracle / PLSQL: Sequences (Autonumber)
  8. Spring Boot JDBC + Oracle database + Commons DBCP2 example

发布日期: 2019-08-11 标签： hibernate hikaricp jpa oracle sequence spring boot spring-data
文章来源:https://www.mkyong.com/spring-boot/spring-boot-spring-data-jpa-oracle-example/
上一篇: Java 8 Optional In Depth
下一篇: WordPress Disable comments on attachments
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明：

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章或者资源来源于互联网, 有时候很难判断是否侵权, 若有侵权, 请联系邮箱：summer@yihaomen.com, 同时欢迎大家注册用户，主动发布无版权争议的 文章/资源.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号