Skip to content

Commit c9f383b

Browse files
nsundaransundara
authored
Create ADBQuickStart.java
1 parent f670cad commit c9f383b

File tree

1 file changed

+145
-0
lines changed

1 file changed

+145
-0
lines changed

‎java/jdbc/ConnectionSamples/ADBQuickStart.java

Lines changed: 145 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,145 @@
1+
/* Copyright (c) 2020, Oracle and/or its affiliates. All rights reserved.*/
2+
3+
/*
4+
DESCRIPTION
5+
The code sample demonstrates establishing a connection to Autonomous Database (ATP/ADW) using
6+
Oracle JDBC driver and Universal Connection Pool (UCP). It does the following.
7+
8+
(a)Set the connection factory class name to
9+
oracle.jdbc.pool.OracleDataSource before getting a connection.
10+
(b)Set the connection pool properties(e.g.,minPoolSize, maxPoolSize).
11+
(c)Get the connection and perform some database operations.
12+
For a quick test, the sample retrieves 20 records from the Sales History (SH) schema
13+
that is accessible to any DB users on autonomous Database.
14+
15+
Step 1: Enter the Database details DB_URL and DB_USER.
16+
You will need to enter the DB_PASSWORD of your Autonomous Database through console
17+
while running the sample.
18+
Step 2: Download the latest Oracle JDBC driver(ojdbc8.jar) and UCP (ucp.jar)
19+
along with oraclepki.jar, osdt_core.jar and osdt_cert.jar to your classpath.
20+
Refer to https://www.oracle.com/database/technologies/maven-central-guide.html
21+
Step 3: Compile and Run the sample.
22+
23+
SH Schema:
24+
This sample uses the Sales History (SH) sample schema. SH is a data set suited for
25+
online transaction processing operations. The Star Schema Benchmark (SSB) sample schema
26+
is available for data warehousing operations. Both schemas are available
27+
with your shared ADB instance and do not count towards your storage.
28+
ou can use any ADB user account to access these schemas.
29+
30+
NOTES
31+
Use JDK 1.8 and above
32+
33+
MODIFIED (MM/DD/YY)
34+
nbsundar 11/09/2020 - Creation
35+
*/
36+
import java.sql.Connection;
37+
import java.sql.ResultSet;
38+
import java.sql.SQLException;
39+
import java.sql.Statement;
40+
import java.util.Scanner;
41+
42+
import oracle.ucp.jdbc.PoolDataSourceFactory;
43+
import oracle.ucp.jdbc.PoolDataSource;
44+
45+
public class ADBQuickStart {
46+
47+
// Make sure to have Oracle JDBC driver 18c or above
48+
// to pass TNS_ADMIN as part of a connection URL.
49+
// TNS_ADMIN - Should be the path where the client credentials zip file is downloaded.
50+
final static String DB_URL="jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=/Users/test/wallet_dbname/";
51+
// Update the Database Username and Password to point to your Autonomous Database
52+
final static String DB_USER = "admin";
53+
static String DB_PASSWORD ;
54+
final static String CONN_FACTORY_CLASS_NAME="oracle.jdbc.pool.OracleDataSource";
55+
56+
/*
57+
* The sample demonstrates UCP as client side connection pool.
58+
*/
59+
public static void main(String args[]) throws Exception {
60+
61+
62+
// For security purposes, you must enter the password through the console
63+
try {
64+
Scanner scanner = new Scanner(System.in);
65+
System.out.print("Enter the password for Autonomous Database: ");
66+
DB_PASSWORD = scanner.nextLine();
67+
}
68+
catch (Exception e) {
69+
System.out.println("ADBQuickStart - Exception occurred : " + e.getMessage());
70+
}
71+
72+
// Get the PoolDataSource for UCP
73+
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
74+
75+
// Set the connection factory first before all other properties
76+
pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME);
77+
pds.setURL(DB_URL);
78+
pds.setUser(DB_USER);
79+
pds.setPassword(DB_PASSWORD);
80+
pds.setConnectionPoolName("JDBC_UCP_POOL");
81+
82+
// Default is 0. Set the initial number of connections to be created
83+
// when UCP is started.
84+
pds.setInitialPoolSize(5);
85+
86+
// Default is 0. Set the minimum number of connections
87+
// that is maintained by UCP at runtime.
88+
pds.setMinPoolSize(5);
89+
90+
// Default is Integer.MAX_VALUE (2147483647). Set the maximum number of
91+
// connections allowed on the connection pool.
92+
pds.setMaxPoolSize(20);
93+
94+
95+
// Get the database connection from UCP.
96+
try (Connection conn = pds.getConnection()) {
97+
System.out.println("Available connections after checkout: "
98+
+ pds.getAvailableConnectionsCount());
99+
System.out.println("Borrowed connections after checkout: "
100+
+ pds.getBorrowedConnectionsCount());
101+
// Perform a database operation
102+
doSQLWork(conn);
103+
}
104+
105+
System.out.println("Available connections after checkin: "
106+
+ pds.getAvailableConnectionsCount());
107+
System.out.println("Borrowed connections after checkin: "
108+
+ pds.getBorrowedConnectionsCount());
109+
}
110+
111+
112+
/*
113+
* Selects 20 rows from the SH (Sales History) Schema that is the accessible to all
114+
* the database users of autonomous database.
115+
*/
116+
public static void doSQLWork(Connection conn) {
117+
118+
try {
119+
conn.setAutoCommit(false);
120+
// Prepare a statement to execute the SQL Queries.
121+
try (Statement statement = conn.createStatement() ) {
122+
// Select 20 rows from the CUSTOMERS table from SH schema.
123+
try (ResultSet resultSet = statement.executeQuery ("select CUST_ID,"
124+
+ "CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY, CUST_CREDIT_LIMIT "
125+
+ "FROM SH.CUSTOMERS WHERE ROWNUM < 20 order by CUST_ID ") ) {
126+
System.out.println("\nCUST_ID" + " " + "CUST_FIRST_NAME" + " " + "CUST_LAST_NAME"
127+
+ " " + "CUST_CITY" + " " + "CUST_CREDIT_LIMIT");
128+
System.out.println("-----------------------------------------------------------");
129+
while (resultSet.next()) {
130+
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " +
131+
resultSet.getString(3)+ " " + resultSet.getString(4) + " " +
132+
resultSet.getInt(5));
133+
}
134+
System.out.println("\nSuccessfully established a connection to Autonomous Database\n");
135+
}
136+
}
137+
}
138+
catch (SQLException e) {
139+
System.out.println("ADBQuickStart - "
140+
+ "doSQLWork()- SQLException occurred : " + e.getMessage());
141+
}
142+
}
143+
}
144+
145+

0 commit comments

Comments
 (0)