Skip to content

Commit 4a5f71f

Browse files
aberinnjaberinnj
authored
Added jdbc-relational-duality-example (oracle-samples#348)
* Added jdbc-relational-duality-example * Update pom.xml * Fixed indentations * Fixed indentation
1 parent 2fb6b8c commit 4a5f71f

File tree

5 files changed

+452
-0
lines changed

5 files changed

+452
-0
lines changed

‎java/jdbc-relational-duality-example/pom.xml

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5+
<modelVersion>4.0.0</modelVersion>
6+
7+
<groupId>org.oracle</groupId>
8+
<artifactId>jdbc-relational-duality-example</artifactId>
9+
<version>1.0-SNAPSHOT</version>
10+
11+
<properties>
12+
<maven.compiler.source>22</maven.compiler.source>
13+
<maven.compiler.target>22</maven.compiler.target>
14+
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
15+
</properties>
16+
17+
<dependencies>
18+
<dependency>
19+
<groupId>com.oracle.database.jdbc</groupId>
20+
<artifactId>ojdbc11-production</artifactId>
21+
<version>23.4.0.24.05</version>
22+
<type>pom</type>
23+
<exclusions>
24+
<exclusion>
25+
<groupId>com.oracle.database.ha</groupId>
26+
<artifactId>simplefan</artifactId>
27+
</exclusion>
28+
<exclusion>
29+
<groupId>com.oracle.database.ha</groupId>
30+
<artifactId>ons</artifactId>
31+
</exclusion>
32+
</exclusions>
33+
</dependency>
34+
</dependencies>
35+
36+
</project>

‎java/jdbc-relational-duality-example/sql/database.sql

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
-- Example Tables
2+
CREATE TABLE TEAMS (
3+
ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
4+
TEAM_NAME VARCHAR2(80),
5+
TEAM_REGION VARCHAR2(80),
6+
TEAM_COLOR VARCHAR2(6)
7+
);
8+
9+
CREATE TABLE PLAYERS (
10+
ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
11+
USERNAME VARCHAR2(80),
12+
TEAM_ID NUMBER references TEAMS (ID) on delete set null,
13+
PLAYER_POSITION VARCHAR2(50)
14+
);
15+
16+
CREATE TABLE AWARDS (
17+
ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
18+
AWARD_NAME VARCHAR2(250),
19+
AWARDED_ON DATE default SYSDATE,
20+
PLAYER_ID NUMBER references PLAYERS (ID) on delete cascade,
21+
AWARD_DETAILS JSON -- Native JSON Datatype
22+
);
23+
24+
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW team_info_dv AS
25+
SELECT JSON {
26+
'_id' : t.ID,
27+
'name' : t.TEAM_NAME,
28+
'region' : t.TEAM_REGION,
29+
'color' : t.TEAM_COLOR,
30+
'players': [
31+
SELECT JSON {
32+
'playerId' : p.ID,
33+
'name' : p.USERNAME,
34+
'position' : p.PLAYER_POSITION
35+
} from PLAYERS p WITH INSERT UPDATE DELETE
36+
where p.TEAM_ID = t.ID
37+
]}
38+
FROM TEAMS t WITH INSERT UPDATE DELETE
39+
40+
41+
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW player_team_info_dv AS
42+
SELECT JSON {
43+
'_id' : p.ID,
44+
'name' : p.USERNAME,
45+
'position' : p.PLAYER_POSITION,
46+
UNNEST (
47+
SELECT JSON {
48+
'teamId' : t.ID,
49+
'teamName' : t.TEAM_NAME,
50+
'teamRegion' : t.TEAM_REGION
51+
} from TEAMS t with NOUPDATE NOINSERT NODELETE
52+
where p.TEAM_ID = t.ID
53+
)}
54+
FROM PLAYERS p WITH INSERT UPDATE DELETE
55+
56+
57+
58+
-- INSERT data
59+
INSERT INTO TEAMS (TEAM_NAME, TEAM_REGION, TEAM_COLOR) VALUES ('Crimson Warriors', 'North America', 'FF0000');
60+
INSERT INTO TEAMS (TEAM_NAME, TEAM_REGION, TEAM_COLOR) VALUES ('Shadow Raiders', 'North America', '000000');
61+
62+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (1, 'PLAYER_ANDY', 1, 'Offense');
63+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (2, 'PLAYER_BARRY', 1, 'Defense');
64+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (3, 'PLAYER_CARL', 1, 'Support');
65+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (4, 'PLAYER_DESMOND', 2, 'Offense');
66+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (5, 'PLAYER_ERIC', 2, 'Defense');
67+
INSERT INTO PLAYERS (ID, USERNAME, TEAM_ID, PLAYER_POSITION) VALUES (6, 'PLAYER_FARIS', 2, 'Tank');
68+
69+
INSERT INTO AWARDS (PLAYER_ID, AWARD_NAME, AWARD_DETAILS) VALUES (1, 'Highest Damage', '{"pts": 20, "damage": 120300}');
70+
INSERT INTO AWARDS (PLAYER_ID, AWARD_NAME, AWARD_DETAILS) VALUES (4, 'Best Defense', '{"pts": 20, "receivedDamage": 322199}');

‎java/jdbc-relational-duality-example/src/main/java/org/oracle/DatabaseConfig.java

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
/* Copyright (c) 2021, 2022, Oracle and/or its affiliates.
2+
This software is dual-licensed to you under the Universal Permissive License
3+
(UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl or Apache License
4+
2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
5+
either license.
6+
Licensed under the Apache License, Version 2.0 (the "License");
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
https://www.apache.org/licenses/LICENSE-2.0
10+
Unless required by applicable law or agreed to in writing, software
11+
distributed under the License is distributed on an "AS IS" BASIS,
12+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
See the License for the specific language governing permissions and
14+
limitations under the License.
15+
16+
DESCRIPTION
17+
DatabaseConfig - Used to retrieve database productInformation from a source (e.g. environment variables).
18+
Set Environment variables or configure this file with your connection details
19+
20+
Used to retrieve and setup connection to an Oracle Database
21+
*/
22+
23+
package org.oracle;
24+
25+
import oracle.jdbc.OracleConnection;
26+
import oracle.ucp.jdbc.PoolDataSource;
27+
import oracle.ucp.jdbc.PoolDataSourceFactory;
28+
29+
import java.sql.SQLException;
30+
31+
public class DatabaseConfig {
32+
33+
private static final String DB_USER = System.getenv("db.user");
34+
private static final String DB_URL = System.getenv("db.url");
35+
private static final String DB_PASSWORD = System.getenv("db.password");
36+
37+
private PoolDataSource pds;
38+
39+
/**
40+
* Creates an instance of pool-enabled data source and configures connection
41+
* properties
42+
*/
43+
public DatabaseConfig() {
44+
try {
45+
this.pds = PoolDataSourceFactory.getPoolDataSource();
46+
this.pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
47+
this.pds.setURL(DB_URL);
48+
this.pds.setUser(DB_USER);
49+
this.pds.setPassword(DB_PASSWORD);
50+
51+
} catch (SQLException e) {
52+
System.err.println(e.getMessage());
53+
System.exit(1);
54+
}
55+
56+
}
57+
58+
/**
59+
* Gets a connection using the data source instance.
60+
*
61+
* @return OracleConnection
62+
*/
63+
public OracleConnection getDatabaseConnection() throws SQLException {
64+
return (OracleConnection) this.pds.getConnection();
65+
66+
}
67+
68+
}

‎java/jdbc-relational-duality-example/src/main/java/org/oracle/Main.java

Lines changed: 127 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,127 @@
1+
/* Copyright (c) 2021, 2022, Oracle and/or its affiliates.
2+
This software is dual-licensed to you under the Universal Permissive License
3+
(UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl or Apache License
4+
2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
5+
either license.
6+
Licensed under the Apache License, Version 2.0 (the "License");
7+
you may not use this file except in compliance with the License.
8+
You may obtain a copy of the License at
9+
https://www.apache.org/licenses/LICENSE-2.0
10+
Unless required by applicable law or agreed to in writing, software
11+
distributed under the License is distributed on an "AS IS" BASIS,
12+
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
See the License for the specific language governing permissions and
14+
limitations under the License.
15+
16+
DESCRIPTION
17+
Main - Runner class calling multiple methods
18+
*/
19+
20+
package org.oracle;
21+
22+
import oracle.sql.json.OracleJsonArray;
23+
import oracle.sql.json.OracleJsonFactory;
24+
import oracle.sql.json.OracleJsonObject;
25+
26+
public class Main {
27+
private static DatabaseConfig pds;
28+
29+
public static void main(String[] args) {
30+
31+
pds = new DatabaseConfig();
32+
OracleJsonFactory f = new OracleJsonFactory();
33+
34+
/*
35+
* Example 1
36+
* Get all teams
37+
*/
38+
System.out.println("Example 1: Retrieve all teams using team_info_dv");
39+
TeamManager.retrieveTeam(pds);
40+
System.out.println("\n");
41+
42+
/*
43+
* Example 2
44+
* Get specific team
45+
*/
46+
System.out.println("Example 2: Retrieve specific teams using team_info_dv");
47+
TeamManager.retrieveTeam(pds, 2);
48+
System.out.println("\n");
49+
/*
50+
* Example 3
51+
* Create new team using INSERT into team_info_dv
52+
*/
53+
System.out.println("Example 3: Create a new team using team_info_dv");
54+
OracleJsonObject withTeamA = f.createObject();
55+
withTeamA.put("_id", 3);
56+
withTeamA.put("name", "Mountain Movers");
57+
withTeamA.put("region", "South America");
58+
withTeamA.put("color", "cc2222");
59+
60+
TeamManager.insertNewTeam(pds, withTeamA);
61+
System.out.println("\n");
62+
/*
63+
* Example 4
64+
* Create new Player in Team using an UPDATE into team_info_dv with
65+
* JSON_TRANSFORM
66+
*/
67+
System.out.println("Example 4: Create a new player using JSON_TRANSFORM and team_info_dv");
68+
OracleJsonObject withPlayerA = f.createObject();
69+
withPlayerA.put("playerId", 7);
70+
withPlayerA.put("name", "PLAYER_GABRIEL");
71+
withPlayerA.put("position", "Support");
72+
73+
TeamManager.updateTeam(pds, withPlayerA, 3);
74+
System.out.println("\n");
75+
76+
/*
77+
* Example 5
78+
* Create new Player in Team using INSERT into player_team_info_dv\
79+
*/
80+
System.out.println("Example 5: Create a new player using player_team_info_dv");
81+
OracleJsonObject withPlayerB = f.createObject();
82+
withPlayerB.put("_id", 8);
83+
withPlayerB.put("name", "PLAYER_HARVEY");
84+
withPlayerB.put("position", "Offense");
85+
withPlayerB.put("teamId", 3);
86+
87+
TeamManager.insertNewPlayer(pds, withPlayerB, 3);
88+
System.out.println("\n");
89+
90+
/*
91+
* Example 5
92+
* Update Team with new players as a whole JSON document using team_info_dv.
93+
*
94+
* This example shows that replacing the players with a new
95+
* set of players remove the old ones from
96+
* the PLAYERS table.
97+
*
98+
* In this example, an OracleJSONObject is created from
99+
* a copy of the oldTeam and an update on the object is done
100+
* to replace the players team
101+
*/
102+
System.out.println("Example 6: ");
103+
OracleJsonObject withPlayerC = f.createObject();
104+
withPlayerC.put("playerId", 10);
105+
withPlayerC.put("name", "PLAYER_MARK");
106+
withPlayerC.put("position", "Offense");
107+
108+
OracleJsonObject withPlayerD = f.createObject();
109+
withPlayerD.put("playerId", 11);
110+
withPlayerD.put("name", "PLAYER_NATHAN");
111+
withPlayerD.put("position", "Offense");
112+
113+
OracleJsonArray newPlayerArr = f.createArray();
114+
newPlayerArr.add(withPlayerC);
115+
newPlayerArr.add(withPlayerD);
116+
117+
int teamId = 1; // Specify which team
118+
OracleJsonObject oldTeam = TeamManager.retrieveAndReferenceTeam(pds, teamId);
119+
if (oldTeam != null) {
120+
OracleJsonObject newTeam = f.createObject(oldTeam);
121+
newTeam.put("players", newPlayerArr);
122+
TeamManager.updateTeamAsAWhole(pds, newTeam, teamId);
123+
System.out.println("\n");
124+
125+
}
126+
}
127+
}

0 commit comments

Comments
 (0)