Connecting Oracle Database in JPA

This example shows how to connect to Oracle database from a JPA application.

We are going to use Oracle Database Express Edition. If you do not have the Oracle database server installed, follow this guide for downloading/installing Oracle Express Edition and getting started with Oracle SQL Developer.

Installing JDBC driver to local Maven repository

Follow this guide to install Oracle JDBC driver to maven local repository.

Creating and populating example table

Let's create a table in Oracle Database and populate with some data. Paste following in Oracle SQL Developer and execute it:

DROP TABLE PERSON;
DROP SEQUENCE SQ_PERSON;
CREATE TABLE PERSON(
 ID NUMBER(19),
 FIRST_NAME VARCHAR(255),
 LAST_NAME VARCHAR(255),
 ADDRESS VARCHAR(255),
 PRIMARY KEY (ID)
);

CREATE SEQUENCE SQ_PERSON MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

INSERT INTO PERSON VALUES (SQ_PERSON.nextval,'Rose','Kantata', '2736 Kooter Lane');
INSERT INTO PERSON VALUES (SQ_PERSON.nextval,'Mike','Togglie', '111 Cool Dr');
INSERT INTO PERSON VALUES (SQ_PERSON.nextval,'Dana', 'Whitley', '464 Gorsuch Drive');
INSERT INTO PERSON VALUES (SQ_PERSON.nextval,'Robin', 'Cash', '64 Zella Park');
INSERT INTO PERSON VALUES (SQ_PERSON.nextval,'Chary', 'Mess', '112 Yellow Hill');

Also don't forget to commit the changes.

JPA Example Application

pom.xml

<project .....>
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.logicbig.example</groupId>
    <artifactId>jpa-connecting-oracle</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.3.6.Final</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ora-jdbc</artifactId>
            <version>7</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

src/main/resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
             xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="example-unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
            <property name="javax.persistence.jdbc.user" value="system"/>
            <property name="javax.persistence.jdbc.password" value="1111"/>
        </properties>
    </persistence-unit>
</persistence>

Example Entity

@Entity
public class Person {
  @Id
  @GeneratedValue
  private long id;
  @Column(name = "FIRST_NAME")
  private String firstName;
  @Column(name = "LAST_NAME")
  private String lastName;
  private String address;
    .............
}

Querying database

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          findAllEmployeeEntities();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findAllEmployeeEntities() {
      System.out.println("-- finding Person entities -  --");
      EntityManager em = entityManagerFactory.createEntityManager();
      TypedQuery<Person> query = em.createQuery("SELECT p from Person p", Person.class);
      List<Person> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}

-- finding Person entities -  --
Person{id=1, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}
Person{id=2, firstName='Mike', lastName='Togglie', address='111 Cool Dr'}
Person{id=3, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person{id=4, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Person{id=5, firstName='Chary', lastName='Mess', address='112 Yellow Hill'}

Example Project

Dependencies and Technologies Used:

• hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• ora-jdbc 7: POM was created from install:install-file.
• JDK 1.8
• Maven 3.5.4