This is a SpringBoot sample that uses Oracle JDBC and UCP to connect to Autonomous Database (ATP/ADW). The sample creates a SpringBoot application and performs different JDBC operations.

* ALL_TABLES: Displays 20 records from the ALL_TABLES table.

* EMP: Displays the records from EMP table. You can create EMP and other tables and populate the data using the script [JDBCSampleData.sql](https://github.com/oracle/oracle-db-examples/blob/master/java/jdbc/BasicSamples/JDBCSampleData.sql)

* Insert into EMP: Insert a new record into the table. The new row will not be deleted. It displays all the records of EMP table and you can see the new row that was created.

* **OracleJdbcAppication.java**: This is the main class where we have the methods to perform some database operations.

* **EmployeeServiceImpl.java**: This is the bean where the business logic is present. It implements the interface EmployeeService.java.

* **Employee.java and AllTables.java**: These are the DAO classes that represent 'Model' of the application.

* **EmployeeDAOImpl.java and AllTablesDAOImpl.java**: These are the Data Access Object classes to access the data from the database.

* **pom.xml**: Maven build script with all the necessary dependencies for the application to run.

* **application.properties**: Contains all the database specific details such as database URL, database username, database password etc., This also contains the properties to UCP as a datasource. Make sure to have Oracle JDBC 21c driver in the class path to use UCP as a datasource.

<?xml version="1.0" encoding="UTF-8"?>

<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/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>

<groupId>com.oracle</groupId>

<artifactId>springapp</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>springapp</name>

<url>http://www.oracle.com</url>

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.2.6.RELEASE</version>

</parent>

<properties>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<maven.compiler.source>1.8</maven.compiler.source>

<maven.compiler.target>1.8</maven.compiler.target>

</properties>

<dependencies>

<dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

<scope>test</scope>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

<dependency>

<groupId>javax.inject</groupId>

<artifactId>javax.inject</artifactId>

<version>1</version>

</dependency>

<dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8-production</artifactId>

<version>21.1.0.0</version>

<type>pom</type>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

<plugin>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

<configuration>

<mainClass>com.oracle.springapp.OracleJdbcApplication</mainClass>

</configuration>

</plugin>

</plugins>

</build>

</project>

package com.oracle.springapp;

import java.sql.Date;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.oracle.springapp.model.Employee;

import com.oracle.springapp.service.EmployeeService;

public class OracleJdbcApplication implements CommandLineRunner {

@Autowired

EmployeeService employeeService;

public static void main(String[] args) {

SpringApplication.run(OracleJdbcApplication.class, args);

}

@Override

public void run(String... args) throws Exception {

// Displays 20 table names from ALL_TABLES

employeeService.displayTableNames();

System.out.println("List of employees");

// Displays the list of employees from EMP table

employeeService.displayEmployees();

// Insert a new employee into EMP table

employeeService.insertEmployee(new Employee(7954,"TAYLOR","MANAGER",7839, Date.valueOf("2020-03-20"),5300,0,10));

System.out.println("List of Employees after the update");

// Displays the list of employees after a new employee record is inserted

employeeService.displayEmployees();

}

}

package com.oracle.springapp.dao;

import java.util.List;

import com.oracle.springapp.model.AllTables;

public interface AllTablesDAO {

public List<AllTables> getTableNames();

}

package com.oracle.springapp.dao;

import java.util.List;

import com.oracle.springapp.model.Employee;

public interface EmployeeDAO {

public List<Employee> getAllEmployees();

void insertEmployee(Employee employee);

}

package com.oracle.springapp.dao.impl;

import java.util.List;

import javax.annotation.PostConstruct;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.stereotype.Repository;

import com.oracle.springapp.dao.AllTablesDAO;

import com.oracle.springapp.model.AllTables;

public class AllTablesDAOImpl extends JdbcDaoSupport implements AllTablesDAO {

@Autowired

private DataSource dataSource;

@PostConstruct

public void initialize() {

setDataSource(dataSource);

System.out.println("Datasource used: " + dataSource);

}

@Override

public List<AllTables> getTableNames() {

final String sql = "SELECT owner, table_name, status, num_rows FROM all_tables where rownum < 20";

return getJdbcTemplate().query(sql,

(rs, rowNum) -> new AllTables(rs.getString("owner"),

rs.getString("table_name"),

rs.getString("status"),

rs.getInt("num_rows")

));

}

}

package com.oracle.springapp.dao.impl;

import java.util.List;

import javax.annotation.PostConstruct;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import org.springframework.stereotype.Repository;

import com.oracle.springapp.dao.EmployeeDAO;

import com.oracle.springapp.model.Employee;

public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO {

@Autowired

private DataSource dataSource;

@PostConstruct

public void initialize() {

setDataSource(dataSource);

System.out.println("Datasource used: " + dataSource);

}

@Override

public List<Employee> getAllEmployees() {

final String sql = "SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp";

return getJdbcTemplate().query(sql,

(rs, rowNum) -> new Employee(rs.getInt("empno"),

rs.getString("ename"),

rs.getString("job"),

rs.getInt("mgr"),

rs.getDate("hiredate"),

rs.getInt("sal"),

rs.getInt("comm"),

rs.getInt("deptno")

));

}

@Override

public void insertEmployee(Employee employee) {

String sql = "Select max(empno) from emp";

int[] emp_no = new int[1];

//getJdbcTemplate().query(sql, (rs, rowNum) -> emp_no[rowNum] = rs.getInt(1));

getJdbcTemplate().query(sql, (resultSet, rowNumber) -> emp_no[rowNumber] = resultSet.getInt(1));

System.out.println("Max emploee number is: " + emp_no[0]);

sql = "INSERT INTO EMP VALUES(?,?,?,?,?,?,?,?)";

int newEmpNo = emp_no[0]+1;

getJdbcTemplate().update(sql, new Object[]{

newEmpNo,

employee.getName()+(newEmpNo),

employee.getJob(),

employee.getManager(),

employee.getHiredate(),

employee.getSalary(),

employee.getCommission(),

employee.getDeptno()

});

}

}

package com.oracle.springapp.model;

public class AllTables {

private String owner;

private String table_name;

private String status;

private int num_rows;

public AllTables(String _owner,

String _table_name,

String _status,

int _num_rows) {

owner = _owner;

table_name = _table_name;

status = _status;

num_rows = _num_rows;

}

public String getOwner() {

return owner;

}

public String getTableName() {

return table_name;

}

public String getStatus() {

return status;

}

public int getNumRows() {

return num_rows;

}

public String toString() {

return String.format("%25s %25s %25s %20d", owner, table_name,

status, num_rows);

}

}