Sign up for the free Redhat Developer account
https://developers.redhat.com/
launch free developer sandbox openshift cluster
https://developers.redhat.com/developer-sandbox/get-started
copy login Command
https://github.com/datahawklab/oracle-ucp-spring-boot-oci.git
Directory Structure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SpringBootSample
├── Readme.md
├── pom.xml
└── src
└── main
├── java
│ └── com
│ └── oracle
│ └── springapp
│ ├── OracleJdbcApplication.java
│ ├── dao
│ │ ├── AllTablesDAO.java
│ │ ├── EmployeeDAO.java
│ │ └── impl
│ │ ├── AllTablesDAOImpl.java
│ │ └── EmployeeDAOImpl.java
│ ├── model
│ │ ├── AllTables.java
│ │ └── Employee.java
│ └── service
│ ├── EmployeeService.java
│ └── impl
│ └── EmployeeServiceImpl.java
└── resources
└── application.properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mkdir springboot-ucp &&\
cd springboot-ucp &&\
mkdir -p src/main/java/com/oracle/springapp/dao/impl &&\
mkdir -p src/main/java/com/oracle/springapp/model &&\
mkdir -p src/main/java/com/oracle/springapp/service/impl &&\
mkdir -p src/main/resources &&\
touch pom.xml &&\
touch src/main/resources/application.properties &&\
touch src/main/java/com/oracle/springapp/OracleJdbcApplication.java &&\
touch src/main/java/com/oracle/springapp/AllTablesDAO.java &&\
touch src/main/java/com/oracle/springapp/EmployeeDAO.java &&\
touch src/main/java/com/oracle/springapp/dao/impl/AllTablesDAOImpl.java &&\
touch src/main/java/com/oracle/springapp/dao/impl/EmployeeDAOImpl.java &&\
touch src/main/java/com/oracle/springapp/model/AllTables.java &&\
touch src/main/java/com/oracle/springapp/model/Employee.java &&\
touch src/main/java/com/oracle/springapp/service/EmployeeService.java &&\
touch src/main/java/com/oracle/springapp/service/impl/EmployeeServiceImpl.java
requirements
- spring boot 2.4.5
- oracle 21.1.0.0 jdbc driver
pom.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
<?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.4.5</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>
OracleJdbcApplication
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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;
/**
* SpringBoot application main class. It uses JdbcTemplate class which
* internally uses UCP for connection check-outs and check-ins.
*
*/
@SpringBootApplication
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();
}
}
src/main/resources/application.properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# For connecting to Autonomous Database (ATP) refer https://www.oracle.com/database/technologies/getting-started-using-jdbc.html
# Provide the database URL, database username and database password
spring.datasource.url=jdbc:oracle:thin:@db202109302311_high?TNS_ADMIN=/home/swapanc/wallets/Wallet_DB202109302311
spring.datasource.username=$USER_ID
spring.datasource.password=$PASS
# Properties for using Universal Connection Pool (UCP)
# Note: These properties require JDBC version 21.0.0.0
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
# For using Replay datasource
#spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.replay.OracleDataSourceImpl
# For using Non-Replay datasource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource
spring.datasource.oracleucp.sql-for-validate-connection=select * from dual
spring.datasource.oracleucp.connection-pool-name=connectionPoolName1
spring.datasource.oracleucp.initial-pool-size=15
spring.datasource.oracleucp.min-pool-size=10
spring.datasource.oracleucp.max-pool-size=30
AllTablesDAOImpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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;
/**
* Simple Java class which uses Spring's JdbcDaoSupport class to implement
* business logic.
*
*/
@Repository
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")
));
}
}
EmployeeDAOImpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
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;
/**
* Simple Java class which uses Spring's JdbcDaoSupport class to implement
* business logic.
*
*/
@Repository
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()
});
}
}
AllTablesDAO
1
2
3
4
5
6
7
8
9
10
11
12
13
package com.oracle.springapp.dao;
import java.util.List;
import com.oracle.springapp.model.AllTables;
/**
* Simple DAO interface for EMP table.
*
*/
public interface AllTablesDAO {
public List<AllTables> getTableNames();
}
EmployeeDAO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package com.oracle.springapp.dao;
import java.util.List;
import com.oracle.springapp.model.Employee;
/**
* Simple DAO interface for EMP table.
*
*/
public interface EmployeeDAO {
public List<Employee> getAllEmployees();
void insertEmployee(Employee employee);
}
AllTables
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.oracle.springapp.model;
/**
* Simple model for ALL_TABLES
*/
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);
}
}
Employee
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
package com.oracle.springapp.model;
import java.sql.Date;
import java.sql.Timestamp;
/**
* Simple model for EMP table.
*
*/
public class Employee {
private Integer empno;
private String name;
private String job;
private Integer manager;
private Date hiredate;
private Integer salary;
private Integer commission;
private Integer deptno;
public Employee(int _empno,
String _name,
String _job,
int _mgr,
Date date,
int _sal,
int _commission,
int _deptno) {
empno = _empno;
name = _name;
job = _job;
manager = _mgr;
hiredate = date;
salary = _sal;
commission = _commission;
deptno = _deptno;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getManager() {
return manager;
}
public void setManager(int mgr) {
this.manager = mgr;
}
public Integer getSalary() {
return salary;
}
public void setSalary(int sal) {
this.salary = sal;
}
public Integer getDeptno() {
return deptno;
}
public Integer getCommission() {
return commission;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Timestamp hiredate) {
this.setHiredate(hiredate);
}
public void setCommission(int commission) {
this.commission=commission;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String toString() {
return String.format("%20s %20s %20s %20s %20s %20s %20s %20s", empno, name,
job, manager, hiredate, salary, commission, deptno);
}
}
EmployeeServiceImpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.oracle.springapp.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.oracle.springapp.model.Employee;
import com.oracle.springapp.service.EmployeeService;
import com.oracle.springapp.dao.EmployeeDAO;
import com.oracle.springapp.model.AllTables;
import com.oracle.springapp.dao.AllTablesDAO;
@Service
public class EmployeeServiceImpl implements EmployeeService {
@Autowired
EmployeeDAO employeeDao;
@Autowired
AllTablesDAO allTablesDao;
/**
* Get the top 20 table names from all tables
*/
@Override
public void displayTableNames() {
List<AllTables> allTables_list = allTablesDao.getTableNames();
System.out.println(" Displaying Table Names ");
System.out.println(String.format("%20s %20s %20s %20s \n",
"OWNER", "TABLE_NAME", "STATUS", "NUM_ROWS"));
for(AllTables allTables: allTables_list)
System.out.println(allTables);
}
/**
* Displays the Employees from the EMP table
*/
@Override
public void displayEmployees() {
List<Employee> employees = employeeDao.getAllEmployees();
System.out.println(String.format("%20s %20s %20s %20s %20s %20s %20s %20s \n",
"EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SALARY", "COMM", "DEPT"));
for(Employee employee: employees)
System.out.println(employee);
}
@Override
public void insertEmployee(Employee employee) {
employeeDao.insertEmployee(employee);
}
}
EmployeeService
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.oracle.springapp.service;
import com.oracle.springapp.model.Employee;
public interface EmployeeService {
/**
* Display all employees.
*/
public void displayEmployees();
/**
* Get table name of the top 20 tables
*/
public void displayTableNames();
/**
* Create a new employee record
*/
public void insertEmployee(Employee employee);
}