Skip to content

Commit 883007d

Browse files
nsundaransundara
authored
Create ShardingDsTest.java
1 parent 95c24aa commit 883007d

File tree

1 file changed

+150
-0
lines changed

1 file changed

+150
-0
lines changed

‎java/jdbc/AdvancedSamples/ShardingDsTest.java

Lines changed: 150 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,150 @@
1+
2+
/**
3+
* The test demostrate the capabilities of JDBC sharding datasource. When using sharding datasource,
4+
* applications are not required to provide the sharding key in order to create a connection from datasource.
5+
* The driver automatically derives sharding keys and executes query on the correct shard.
6+
*/
7+
import java.sql.Connection;
8+
import java.sql.DriverManager;
9+
import java.sql.PreparedStatement;
10+
import java.sql.ResultSet;
11+
import java.sql.SQLException;
12+
import java.sql.Statement;
13+
import java.util.Properties;
14+
15+
import oracle.jdbc.pool.OracleDataSource;
16+
17+
public class ShardingDsTest {
18+
final static String useName = "testuser1";
19+
final static String password = "testuser1";
20+
// GSM connection to be used for insert/select to/from to all shards
21+
final static String gsmURL = "jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (HOST = nshe02cn04.us.oracle.com)(PORT = 3493)(PROTOCOL = tcp))(CONNECT_DATA = (SERVICE_NAME = shsvc.shpool.oradbcloud)))";
22+
// Connection to be used for insert/select to/from to Shard-1
23+
final static String SHARD1_URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nshe02cn04)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb2pdb1.regress.rdbms.dev.us.oracle.com)))";
24+
// Connection to be used for insert/select to/from to Shard-2
25+
final static String SHARD2_URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nshe02cn04)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb3pdb1.regress.rdbms.dev.us.oracle.com)))";;
26+
// Connection to be used for insert/select to/from to Shard-3
27+
final static String SHARD3_URL = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=nshe02cn04)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb4pdb1.regress.rdbms.dev.us.oracle.com)))";;
28+
29+
final static String SELECT_CUST_BY_ID_QUERY = "select id, name from customer where id = ?";
30+
final static String INSERT_CUST = "insert into customer values(?, ?)";
31+
// Cross Shards Query
32+
final static String SELECT_ALL_CUST_QUERY = "select * from customer";
33+
final static int RECORD_COUNT = 10;
34+
35+
36+
public static void main(String args[]) throws Exception {
37+
OracleDataSource shardingDS = createShardingDataSource();
38+
39+
Connection shardingDsConn = null;
40+
Connection shard1Conn = null;
41+
Connection shard2Conn = null;
42+
Connection shard3Conn = null;
43+
try {
44+
// no need to provide the sharding key to get the connection from sharding datasource
45+
shardingDsConn = shardingDS.getConnection();
46+
System.out.println("Get DB connection using the Sharding Datasource ... success!");
47+
System.out.println("Insert customers into all Shards, using the Sharding datasource \n");
48+
49+
String name = "Smith";
50+
for (int i = 1; i < RECORD_COUNT; i++) {
51+
insertCustomer(shardingDsConn, i, name + i);
52+
}
53+
54+
shard1Conn = getDirectShardConnection(SHARD1_URL);
55+
System.out.println("\nCheck customers in shard-1 using direct Shard connection ");
56+
System.out.println("---------------------------- :");
57+
System.out.println("ID\t\t\tNAME" );
58+
displayAllCustomers(shard1Conn);
59+
60+
shard2Conn = getDirectShardConnection(SHARD2_URL);
61+
System.out.println("\nCheck customers in shard-2 using direct Shard connection");
62+
System.out.println("---------------------------- :");
63+
System.out.println("ID\t\t\tNAME" );
64+
displayAllCustomers(shard2Conn);
65+
66+
shard3Conn = getDirectShardConnection(SHARD3_URL);
67+
System.out.println("\nCheck customers in shard-3 using direct Shard connection");
68+
System.out.println("---------------------------- :");
69+
System.out.println("ID\t\t\tNAME" );
70+
displayAllCustomers(shard3Conn);
71+
72+
System.out.println("---------------------------- :");
73+
System.out.println("\nDisplay customers in all Shards using the sharding datasource ");
74+
System.out.println("ID\t\t\tNAME" );
75+
displayAllCustomers(shardingDsConn);
76+
77+
} catch (Exception ex) {
78+
System.out.println("Got exception : "+ex.getMessage());
79+
ex.printStackTrace();
80+
} finally {
81+
if (shardingDsConn != null) {
82+
cleanup(shardingDsConn);
83+
shardingDsConn.close();
84+
}
85+
86+
if (shard1Conn != null)
87+
shard1Conn.close();
88+
if (shard2Conn != null)
89+
shard2Conn.close();
90+
if (shard3Conn != null)
91+
shard3Conn.close();
92+
}
93+
}
94+
95+
96+
private static OracleDataSource createShardingDataSource() throws SQLException {
97+
OracleDataSource ds = new OracleDataSource();
98+
Properties prop = new Properties();
99+
prop.setProperty("user", useName);
100+
prop.setProperty("password", password);
101+
// Set the following system property to enable the Sharding Datasource
102+
System.out.println("\nSet the oracle.jdbc.useShardingDriverConnection property to true \n");
103+
prop.setProperty("oracle.jdbc.useShardingDriverConnection", "true");
104+
ds.setConnectionProperties(prop);
105+
ds.setURL(gsmURL);
106+
return ds;
107+
}
108+
109+
private static void insertCustomer(Connection conn, int id, String name) throws SQLException {
110+
try (PreparedStatement ps = conn.prepareStatement(INSERT_CUST)) {
111+
ps.setInt(1, id);
112+
ps.setString(2, name);
113+
System.out.println("Insert into customer values( "+ id + " " + name + " )" );
114+
ps.executeUpdate();
115+
}
116+
}
117+
118+
private static void displayCustomerDataById(Connection conn, int id) throws SQLException {
119+
try (PreparedStatement ps = conn.prepareStatement(SELECT_CUST_BY_ID_QUERY)) {
120+
ps.setInt(1, id);
121+
try (ResultSet rs = ps.executeQuery()) {
122+
while (rs.next()) {
123+
124+
}
125+
}
126+
127+
}
128+
}
129+
130+
private static void cleanup(Connection conn) throws SQLException {
131+
try (Statement st = conn.createStatement()) {
132+
st.execute("truncate table customer");
133+
}
134+
}
135+
private static Connection getDirectShardConnection(String directShardUrl) throws SQLException {
136+
return DriverManager.getConnection(directShardUrl, useName, password);
137+
138+
}
139+
140+
private static void displayAllCustomers(Connection conn) throws SQLException {
141+
try (PreparedStatement ps = conn.prepareStatement(SELECT_ALL_CUST_QUERY)) {
142+
try (ResultSet rs = ps.executeQuery()) {
143+
while (rs.next()) {
144+
System.out.println(""+rs.getInt(1) + "\t\t\t" + rs.getString(2));
145+
}
146+
}
147+
}
148+
}
149+
150+
}

0 commit comments

Comments
 (0)