Skip to content

Commit 525d7fc

Browse files
aberinnjaberinnj
authored
Added JSON Relational duality view examples (oracle-samples#369)
* Added JDBC Centralized config code sample * Added extended examples for JSON relational duality view * Fixing indentation * Patched code-issues * Fixed indentation
1 parent 36e60b0 commit 525d7fc

File tree

4 files changed

+605
-88
lines changed

4 files changed

+605
-88
lines changed

‎java/jdbc-relational-duality-example/sql/extended-sample.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
CREATE TABLE COPY_STATUS_LU
2+
(
3+
STATUS_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
4+
STATUS_NAME VARCHAR2(50)
5+
);
6+
7+
CREATE TABLE BOOKS
8+
(
9+
BOOK_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
10+
BOOK_NAME VARCHAR2(250)
11+
);
12+
13+
CREATE TABLE BOOK_COPY
14+
(
15+
BOOK_COPY_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
16+
BOOK_ID NUMBER REFERENCES BOOKS (BOOK_ID),
17+
YEAR_PUBLISHED NUMBER(4),
18+
STATUS_ID NUMBER DEFAULT 1 references COPY_STATUS_LU (STATUS_ID)
19+
);
20+
21+
INSERT INTO COPY_STATUS_LU (STATUS_ID, STATUS_NAME) VALUES (1, 'Available');
22+
INSERT INTO COPY_STATUS_LU (STATUS_ID, STATUS_NAME) VALUES (2, 'In Circulation');
23+
INSERT INTO COPY_STATUS_LU (STATUS_ID, STATUS_NAME) VALUES (3, 'Reserved');
24+
25+
-- JSON document of books and a list of its copies
26+
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW BOOK_COPY_DV AS
27+
SELECT JSON {
28+
'_id': b.BOOK_ID,
29+
'name': b.BOOK_NAME,
30+
'copies': [ SELECT JSON {
31+
'id' : bc.BOOK_COPY_ID,
32+
'year' : bc.YEAR_PUBLISHED,
33+
'status' : bc.STATUS_ID
34+
} FROM BOOK_COPY bc WITH INSERT UPDATE DELETE
35+
WHERE bc.BOOK_ID = b.BOOK_ID
36+
]}
37+
FROM BOOKS b WITH INSERT UPDATE DELETE
38+
/
39+

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

Lines changed: 330 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,330 @@
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+
BooksManager - Class that has requests methods for Books and Book Copy tables and their duality view
18+
*/
19+
20+
package org.oracle;
21+
22+
import oracle.jdbc.OracleType;
23+
import oracle.sql.json.OracleJsonObject;
24+
25+
import java.sql.Connection;
26+
import java.sql.PreparedStatement;
27+
import java.sql.ResultSet;
28+
import java.sql.SQLException;
29+
import java.util.List;
30+
31+
public class BooksManager {
32+
33+
public static void retrieveBooks(DatabaseConfig pds) {
34+
retrieveAllBooksDualityView(pds, -1);
35+
}
36+
37+
public static void retrieveBooks(DatabaseConfig pds, int withBookId) {
38+
retrieveAllBooksDualityView(pds, withBookId);
39+
}
40+
41+
public static void insertNewIntoBookDualityView(DatabaseConfig pds, OracleJsonObject data) {
42+
try (
43+
Connection connection = pds.getDatabaseConnection();
44+
PreparedStatement stmt = connection.prepareStatement(
45+
"insert into book_copy_dv values (?)")) {
46+
stmt.setObject(1, data, OracleType.JSON);
47+
48+
int created = stmt.executeUpdate();
49+
if (created > 0)
50+
System.out.println("New Book and book copies created");
51+
52+
} catch (SQLException e) {
53+
e.printStackTrace();
54+
}
55+
}
56+
57+
private static void retrieveAllBooksDualityView(DatabaseConfig pds, int withBookId) {
58+
String query = withBookId > 0 ? "SELECT data FROM book_copy_dv WHERE json_value(data, '$._id') = ?" : "SELECT data FROM book_copy_dv";
59+
try (
60+
Connection connection = pds.getDatabaseConnection();
61+
PreparedStatement stmt = connection.prepareStatement(query)) {
62+
63+
if (withBookId > 0) {
64+
stmt.setInt(1, withBookId);
65+
}
66+
67+
try (ResultSet rs = stmt.executeQuery()) {
68+
while (rs.next()) {
69+
OracleJsonObject book = rs.getObject(1, OracleJsonObject.class);
70+
System.out.println(book.toString());
71+
}
72+
}
73+
74+
} catch (SQLException e) {
75+
e.printStackTrace();
76+
}
77+
}
78+
79+
public static OracleJsonObject retrieveAndReferenceBook(DatabaseConfig pds, int withBookId) {
80+
try (
81+
Connection connection = pds.getDatabaseConnection();
82+
PreparedStatement stmt = connection.prepareStatement("SELECT data FROM book_copy_dv WHERE json_value(data, '$._id') = ?")) {
83+
84+
stmt.setInt(1, withBookId);
85+
try (ResultSet rs = stmt.executeQuery()) {
86+
if (rs.next()) {
87+
return rs.getObject(1, OracleJsonObject.class);
88+
}
89+
}
90+
91+
} catch (SQLException e) {
92+
e.printStackTrace();
93+
}
94+
return null;
95+
}
96+
97+
public static void updateBookDualityViewAsAWhole(DatabaseConfig pds, OracleJsonObject data, int withBookId) {
98+
try (
99+
Connection connection = pds.getDatabaseConnection();
100+
PreparedStatement stmt = connection
101+
.prepareStatement("UPDATE book_copy_dv dv SET dv.data = ? where JSON_VALUE(dv.data, '$._id') = ?")) {
102+
stmt.setObject(1, data, OracleType.JSON);
103+
stmt.setInt(2, withBookId);
104+
105+
int i = stmt.executeUpdate();
106+
if (i > 0)
107+
System.out.println("Updated book information");
108+
109+
} catch (SQLException e) {
110+
e.printStackTrace();
111+
}
112+
}
113+
114+
public static void insertNewBook(DatabaseConfig pds, String name) {
115+
try (
116+
Connection connection = pds.getDatabaseConnection();
117+
PreparedStatement stmt = connection.prepareStatement(
118+
"insert into BOOKS(BOOK_NAME) values (?)")) {
119+
stmt.setString(1, name);
120+
121+
int created = stmt.executeUpdate();
122+
if (created > 0)
123+
System.out.println("New book added.");
124+
125+
} catch (SQLException e) {
126+
e.printStackTrace();
127+
}
128+
}
129+
130+
public static void insertNewBook(DatabaseConfig pds, int id, String name) {
131+
try (
132+
Connection connection = pds.getDatabaseConnection();
133+
PreparedStatement stmt = connection.prepareStatement(
134+
"insert into BOOKS(BOOK_ID, BOOK_NAME) values (?, ?)")) {
135+
stmt.setInt(1, id);
136+
stmt.setString(2, name);
137+
138+
int created = stmt.executeUpdate();
139+
if (created > 0)
140+
System.out.println("New book added.");
141+
142+
} catch (SQLException e) {
143+
e.printStackTrace();
144+
}
145+
}
146+
147+
public static void insertNewBookStatus(DatabaseConfig pds, int id, String name) {
148+
try (
149+
Connection connection = pds.getDatabaseConnection();
150+
PreparedStatement stmt = connection.prepareStatement(
151+
"insert into COPY_STATUS_LU(STATUS_ID, STATUS_NAME) values (?, ?)")) {
152+
stmt.setInt(1, id);
153+
stmt.setString(2, name);
154+
155+
int created = stmt.executeUpdate();
156+
if (created > 0)
157+
System.out.println("New status added.");
158+
159+
} catch (SQLException e) {
160+
e.printStackTrace();
161+
}
162+
}
163+
164+
public static void insertNewBookStatusAsList(DatabaseConfig pds, List<BookCopyStatus> statusList) {
165+
try (
166+
Connection connection = pds.getDatabaseConnection();
167+
PreparedStatement stmt = connection.prepareStatement(
168+
"insert into COPY_STATUS_LU(STATUS_ID, STATUS_NAME) values (?, ?)")) {
169+
170+
for (BookCopyStatus status : statusList) {
171+
stmt.setInt(1, status.getStatusId());
172+
stmt.setString(2, status.getStatusName());
173+
stmt.addBatch();
174+
175+
stmt.clearParameters();
176+
177+
}
178+
179+
int[] created = stmt.executeBatch();
180+
for (int i = 0; i < created.length; i++) {
181+
if (created[i] > 0)
182+
System.out.println("New status added.");
183+
else System.out.println("Failed to add new status.");
184+
185+
}
186+
187+
} catch (SQLException e) {
188+
e.printStackTrace();
189+
}
190+
}
191+
192+
public static void insertNewBookCopy(DatabaseConfig pds, int bookId, int yearPublished, int statusId) {
193+
try (
194+
Connection connection = pds.getDatabaseConnection();
195+
PreparedStatement stmt = connection.prepareStatement(
196+
"insert into BOOK_COPY(BOOK_ID, YEAR_PUBLISHED, STATUS_ID) values (?, ?, ?)")) {
197+
stmt.setInt(1, bookId);
198+
stmt.setInt(2, yearPublished);
199+
stmt.setInt(3, statusId);
200+
201+
int created = stmt.executeUpdate();
202+
if (created > 0)
203+
System.out.println("New book copy added.");
204+
205+
} catch (SQLException e) {
206+
e.printStackTrace();
207+
}
208+
}
209+
210+
public static void insertNewBookCopies(DatabaseConfig pds, List<BookCopy> copies) {
211+
212+
try (
213+
Connection connection = pds.getDatabaseConnection();
214+
PreparedStatement stmt = connection.prepareStatement(
215+
"insert into BOOK_COPY(BOOK_ID, YEAR_PUBLISHED, STATUS_ID) values (?, ?, ?)")) {
216+
217+
for (BookCopy bookCopy : copies) {
218+
stmt.setInt(1, bookCopy.getBookId());
219+
stmt.setInt(2, bookCopy.getYearPublished());
220+
stmt.setInt(3, bookCopy.getStatusId());
221+
stmt.addBatch();
222+
223+
stmt.clearParameters();
224+
}
225+
226+
227+
int[] created = stmt.executeBatch();
228+
for (int i = 0; i < created.length; i++) {
229+
if (created[i] > 0)
230+
System.out.println("New book copy added.");
231+
else System.out.println("Failed to add a new book copy.");
232+
}
233+
234+
235+
236+
} catch (SQLException e) {
237+
e.printStackTrace();
238+
}
239+
}
240+
241+
public static void getBooks(DatabaseConfig pds) {
242+
String query = "SELECT * FROM books";
243+
try (
244+
Connection connection = pds.getDatabaseConnection();
245+
PreparedStatement stmt = connection.prepareStatement(query)) {
246+
try (ResultSet rs = stmt.executeQuery()) {
247+
while (rs.next()) {
248+
int bookId = rs.getInt(1);
249+
String bookName = rs.getString(2);
250+
System.out.println(bookId + " - " + bookName);
251+
}
252+
}
253+
254+
} catch (SQLException e) {
255+
e.printStackTrace();
256+
}
257+
}
258+
259+
public static void getBookCopies(DatabaseConfig pds) {
260+
String query = """
261+
SELECT bc.BOOK_COPY_ID, bc.BOOK_ID, b.BOOK_NAME, bc.YEAR_PUBLISHED, cs.STATUS_NAME
262+
FROM book_copy bc, copy_status_lu cs, books b
263+
WHERE bc.STATUS_ID=cs.STATUS_ID and bc.BOOK_ID=b.BOOK_ID
264+
""";
265+
266+
try (
267+
Connection connection = pds.getDatabaseConnection();
268+
PreparedStatement stmt = connection.prepareStatement(query)) {
269+
270+
try (ResultSet rs = stmt.executeQuery()) {
271+
while (rs.next()) {
272+
273+
int bookCopyId = rs.getInt(1);
274+
int bookId = rs.getInt(2);
275+
String bookName = rs.getString(3);
276+
int publishedYear = rs.getInt(4);
277+
String status = rs.getString(5);
278+
System.out.println("ID:" + bookId + " " + bookCopyId + " - " + bookName + ", " + publishedYear + " [" +status+ "]");
279+
}
280+
}
281+
282+
} catch (SQLException e) {
283+
e.printStackTrace();
284+
}
285+
}
286+
}
287+
288+
class BookCopyStatus {
289+
final int statusId;
290+
final String statusName;
291+
292+
BookCopyStatus(int statusId, String statusName) {
293+
this.statusId = statusId;
294+
this.statusName = statusName;
295+
}
296+
297+
public int getStatusId() {
298+
return statusId;
299+
}
300+
301+
public String getStatusName() {
302+
return statusName;
303+
}
304+
}
305+
306+
class BookCopy {
307+
final int bookId;
308+
final int yearPublished;
309+
final int statusId;
310+
311+
BookCopy(int bookId, int yearPublished, int statusId) {
312+
this.bookId = bookId;
313+
this.yearPublished = yearPublished;
314+
this.statusId = statusId;
315+
}
316+
317+
public int getBookId() {
318+
return bookId;
319+
}
320+
321+
public int getStatusId() {
322+
return statusId;
323+
}
324+
325+
public int getYearPublished() {
326+
return yearPublished;
327+
}
328+
}
329+
330+

0 commit comments

Comments
 (0)