forked from oracle-samples/oracle-db-examples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathUCPSample.java
More file actions
143 lines (126 loc) · 5.48 KB
/
UCPSample.java
File metadata and controls
143 lines (126 loc) · 5.48 KB
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
/* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/
/*
DESCRIPTION
The code sample demonstrates Universal Connection Pool (UCP) as a client
side connection pool and does the following.
(a)Set the connection factory class name to
oracle.jdbc.pool.OracleDataSource before getting a connection.
(b)Set the driver connection properties(e.g.,defaultNChar,includeSynonyms).
(c)Set the connection pool properties(e.g.,minPoolSize, maxPoolSize).
(d)Get the connection and perform some database operations.
Step 1: Enter the Database details in DBConfig.properties file.
USER, PASSWORD, UCP_CONNFACTORY and URL are required.
Step 2: Run the sample with "ant UCPSample"
NOTES
Use JDK 1.7 and above
MODIFIED (MM/DD/YY)
nbsundar 02/13/15 - Creation (Contributor - tzhou)
*/
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
public class UCPSample {
final static String DB_URL="jdbc:oracle:thin:@myhost:1521/orclservicename";
// Use the TNS Alias name along with the TNS_ADMIN - For ATP and ADW
// final static String DB_URL="jdbc:oracle:thin:@myhost:1521@wallet_dbname?TNS_ADMIN=/Users/test/wallet_dbname";
final static String DB_USER = "hr";
final static String DB_PASSWORD = "hr";
final static String CONN_FACTORY_CLASS_NAME="oracle.jdbc.pool.OracleDataSource";
/*
* The sample demonstrates UCP as client side connection pool.
*/
public static void main(String args[]) throws Exception {
// Get the PoolDataSource for UCP
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
// Set the connection factory first before all other properties
pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME);
pds.setURL(DB_URL);
pds.setUser(DB_USER);
pds.setPassword(DB_PASSWORD);
pds.setConnectionPoolName("JDBC_UCP_POOL");
// Default is 0. Set the initial number of connections to be created
// when UCP is started.
pds.setInitialPoolSize(5);
// Default is 0. Set the minimum number of connections
// that is maintained by UCP at runtime.
pds.setMinPoolSize(5);
// Default is Integer.MAX_VALUE (2147483647). Set the maximum number of
// connections allowed on the connection pool.
pds.setMaxPoolSize(20);
// Default is 30secs. Set the frequency ineconds to enforce the timeout
// properties. Applies to inactiveConnectionTimeout(int secs),
// AbandonedConnectionTimeout(secs)& TimeToLiveConnectionTimeout(int secs).
// Range of valid values is 0 to Integer.MAX_VALUE. .
pds.setTimeoutCheckInterval(5);
// Default is 0. Set the maximum time, in seconds, that a
// connection remains available in the connection pool.
pds.setInactiveConnectionTimeout(10);
// Get the database connection from UCP.
try (Connection conn = pds.getConnection()) {
System.out.println("Available connections after checkout: "
+ pds.getAvailableConnectionsCount());
System.out.println("Borrowed connections after checkout: "
+ pds.getBorrowedConnectionsCount());
// Perform a database operation
doSQLWork(conn);
}
catch (SQLException e) {
System.out.println("UCPSample - " + "SQLException occurred : "
+ e.getMessage());
}
System.out.println("Available connections after checkin: "
+ pds.getAvailableConnectionsCount());
System.out.println("Borrowed connections after checkin: "
+ pds.getBorrowedConnectionsCount());
}
/*
* Creates an EMP table and does an insert, update and select operations on
* the new table created.
*/
public static void doSQLWork(Connection conn) {
try {
conn.setAutoCommit(false);
// Prepare a statement to execute the SQL Queries.
Statement statement = conn.createStatement();
// Create table EMP
statement.executeUpdate("create table EMP(EMPLOYEEID NUMBER,"
+ "EMPLOYEENAME VARCHAR2 (20))");
System.out.println("New table EMP is created");
// Insert some records into the table EMP
statement.executeUpdate("insert into EMP values(1, 'Jennifer Jones')");
statement.executeUpdate("insert into EMP values(2, 'Alex Debouir')");
System.out.println("Two records are inserted.");
// Update a record on EMP table.
statement.executeUpdate("update EMP set EMPLOYEENAME='Alex Deborie'"
+ " where EMPLOYEEID=2");
System.out.println("One record is updated.");
// Verify the table EMP
ResultSet resultSet = statement.executeQuery("select * from EMP");
System.out.println("\nNew table EMP contains:");
System.out.println("EMPLOYEEID" + " " + "EMPLOYEENAME");
System.out.println("--------------------------");
while (resultSet.next()) {
System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2));
}
System.out.println("\nSuccessfully tested a connection from UCP");
}
catch (SQLException e) {
System.out.println("UCPSample - "
+ "doSQLWork()- SQLException occurred : " + e.getMessage());
}
finally {
// Clean-up after everything
try (Statement statement = conn.createStatement()) {
statement.execute("drop table EMP");
}
catch (SQLException e) {
System.out.println("UCPSample - "
+ "doSQLWork()- SQLException occurred : " + e.getMessage());
}
}
}
}