Close

Connecting Oracle Database in Spring JDBC

[Updated: Sep 27, 2017, Created: Sep 26, 2017]

This example shows how to connect to the Oracle database and use Oracle specific features in Spring JDBC application. We are going to use Oracle Database Express Edition. If you do not have the Oracle database server installed, follow this guide to download Oracle Express Edition, install it and to get started with Oracle SQL Developer.

Installing JDBC driver to local Maven repository

Since Oracle does not provide Oracle JDBC driver in a public Maven repository, we have to download it and install to the local Maven repository (.m2 directory).

You can get the driver from here. Download ojdbc7.jar file. Let's say we have saved ojdbc7.jar at following location:

D:\ora-jdbc-driver>dir /b
ojdbc7.jar

Now run the following maven command to install the jar to local repository:

 mvn install:install-file -Dfile=ojdbc7.jar
                          -DgroupId=com.oracle
                          -DartifactId=ora-jdbc
                          -Dversion=7
                          -Dpackaging=jar
D:\ora-jdbc-driver>mvn install:install-file -Dfile=ojdbc7.jar -DgroupId=com.oracle -DartifactId=ora-jdbc -Dversion=7 -Dpackaging=jar
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-install-plugin:2.4:install-file (default-cli) @ standalone-pom ---
[INFO] Installing D:\ora-jdbc-driver\ojdbc7.jar to C:\Users\Joe\.m2\repository\com\oracle\ora-jdbc\7\ora-jdbc-7.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 0.356 s
[INFO] Finished at: 2017-09-27T01:37:47-05:00
[INFO] Final Memory: 13M/43M
[INFO] ------------------------------------------------------------------------

Inserting example data in the database

We are going to create following table and its corresponding sequence (Oracle does not provide feature like AUTO_INCREMENT embedded in the create table statement, we have to create a Sequence object separately and then use its reference in the insert statement):

src/main/resources/createPersonTable.sql

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');

To execute above database statements, we need to use Oracle SQL developer as shown:

Don't forget to commit by clicking the green check icon (where our red arrow is pointing to)

Creating Spring JDBC application

Additional Maven dependency

Following dependency is the one which we installed ourselves above.

pom.xml

<dependency>
   <groupId>com.oracle</groupId>
   <artifactId>ora-jdbc</artifactId>
   <version>7</version>
</dependency>

Creating the DAO

@Repository
public class PersonDao implements Dao<Person> {
  @Autowired
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;

  @PostConstruct
  private void postConstruct() {
      jdbcTemplate = new JdbcTemplate(dataSource);
  }

  @Override
  public void save(Person person) {
      String sql = "insert into Person values (sq_person.nextval, ?, ?, ?)";
      jdbcTemplate.update(sql, person.getFirstName(),
              person.getLastName(), person.getAddress());
  }
    .............
  @Override
  public List<Person> loadAll() {
      return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
          return toPerson(resultSet);
      });
  }
    .............
}

Note that we used 'sq_person.nextval' in above insert statement which is an Oracle specific way to generate Primary ids.

public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
    .............
}

A Test Client

@Component
public class PersonClient {

  @Autowired
  Dao<Person> personDao;

  public void process() {
      List<Person> list = personDao.loadAll();
      System.out.println("Loaded all: " + list);

      Person person = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
      System.out.println("saving: "+person);
      personDao.save(person);

      person = Person.create("Robin", "Cash", "64 Zella Park");
      System.out.println("saving: "+person);
      personDao.save(person);

      List<Person> list2 = personDao.loadAll();
      System.out.println("Loaded all: " + list2);

  }
}

Java Config and main class

@Configuration
@ComponentScan
public class AppConfig {
  @Bean
  public DataSource dataSource() {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName(oracle.jdbc.driver.OracleDriver.class.getName());
      ds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
      ds.setUsername("system");
      ds.setPassword("1234");
      return ds;
  }

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      context.getBean(PersonClient.class).process();
  }
}

Output

Sep 27, 2017 1:14:11 AM org.springframework.context.annotation.AnnotationConfigApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@20ad9418: startup date [Wed Sep 27 01:14:11 CDT 2017]; root of context hierarchy
Sep 27, 2017 1:14:12 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: oracle.jdbc.driver.OracleDriver
Loaded all: [Person{id=1, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}]
saving: Person{id=0, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
saving: Person{id=0, firstName='Robin', lastName='Cash', address='64 Zella Park'}
Loaded all: [Person{id=1, firstName='Rose', lastName='Kantata', address='2736 Kooter Lane'}, Person{id=2, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}, Person{id=3, firstName='Robin', lastName='Cash', address='64 Zella Park'}]

Example Project

Dependencies and Technologies Used:

  • spring-context 4.2.3.RELEASE: Spring Context.
  • spring-jdbc 4.2.3.RELEASE: Spring JDBC.
  • ora-jdbc 7: POM was created from install:install-file.
  • JDK 1.8
  • Maven 3.3.9

Connect Oracle Select All Download
  • spring-jdbc-with-oracle
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also