EmployeesJdbc.java
5.78 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
/*
Copyright (c) 2013, 2014, Oracle and/or its affiliates. All rights reserved.
The MySQL Connector/J is licensed under the terms of the GPLv2
<http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>, like most MySQL Connectors.
There are special exceptions to the terms and conditions of the GPLv2 as it is applied to
this software, see the FOSS License Exception
<http://www.mysql.com/about/legal/licensing/foss-exception.html>.
This program is free software; you can redistribute it and/or modify it under the terms
of the GNU General Public License as published by the Free Software Foundation; version 2
of the License.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this
program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth
Floor, Boston, MA 02110-1301 USA
*/
package demo.fabric;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.fabric.jdbc.FabricMySQLConnection;
/**
* Demonstrate working with employee data in MySQL Fabric with Connector/J and the JDBC APIs.
*/
public class EmployeesJdbc {
public static void main(String args[]) throws Exception {
String hostname = System.getProperty("com.mysql.fabric.testsuite.hostname");
String port = System.getProperty("com.mysql.fabric.testsuite.port");
String database = System.getProperty("com.mysql.fabric.testsuite.database");
String user = System.getProperty("com.mysql.fabric.testsuite.username");
String password = System.getProperty("com.mysql.fabric.testsuite.password");
String baseUrl = "jdbc:mysql:fabric://" + hostname + ":" + Integer.valueOf(port) + "/";
// Load the driver if running under Java 5
if (!com.mysql.jdbc.Util.isJdbc4()) {
Class.forName("com.mysql.fabric.jdbc.FabricMySQLDriver");
}
// 1. Create database and table for our demo
Connection rawConnection = DriverManager.getConnection(baseUrl + "mysql?fabricServerGroup=fabric_test1_global", user, password);
Statement statement = rawConnection.createStatement();
statement.executeUpdate("create database if not exists employees");
statement.close();
rawConnection.close();
// We should connect to the global group to run DDL statements, they will be replicated to the server groups for all shards.
// The 1-st way is to set it's name explicitly via the "fabricServerGroup" connection property
rawConnection = DriverManager.getConnection(baseUrl + database + "?fabricServerGroup=fabric_test1_global", user, password);
statement = rawConnection.createStatement();
statement.executeUpdate("create database if not exists employees");
statement.close();
rawConnection.close();
// The 2-nd way is to get implicitly connected to global group when the shard key isn't provided, ie. set "fabricShardTable" connection property but
// don't set "fabricShardKey"
rawConnection = DriverManager.getConnection(baseUrl + "employees?fabricShardTable=employees.employees", user, password);
// At this point, we have a connection to the global group for the `employees.employees' shard mapping.
statement = rawConnection.createStatement();
statement.executeUpdate("drop table if exists employees");
statement.executeUpdate("create table employees (emp_no int not null, first_name varchar(50), last_name varchar(50), primary key (emp_no))");
// 2. Insert data
// Cast to a Fabric connection to have access to specific methods
FabricMySQLConnection connection = (FabricMySQLConnection) rawConnection;
// example data used to create employee records
Integer ids[] = new Integer[] { 1, 2, 10001, 10002 };
String firstNames[] = new String[] { "John", "Jane", "Andy", "Alice" };
String lastNames[] = new String[] { "Doe", "Doe", "Wiley", "Wein" };
// insert employee data
PreparedStatement ps = connection.prepareStatement("INSERT INTO employees.employees VALUES (?,?,?)");
for (int i = 0; i < 4; ++i) {
// choose the shard that handles the data we interested in
connection.setShardKey(ids[i].toString());
// perform insert in standard fashion
ps.setInt(1, ids[i]);
ps.setString(2, firstNames[i]);
ps.setString(3, lastNames[i]);
ps.executeUpdate();
}
// 3. Query the data from employees
System.out.println("Querying employees");
System.out.format("%7s | %-30s | %-30s%n", "emp_no", "first_name", "last_name");
System.out.println("--------+--------------------------------+-------------------------------");
ps = connection.prepareStatement("select emp_no, first_name, last_name from employees where emp_no = ?");
for (int i = 0; i < 4; ++i) {
// we need to specify the shard key before accessing the data
connection.setShardKey(ids[i].toString());
ps.setInt(1, ids[i]);
ResultSet rs = ps.executeQuery();
rs.next();
System.out.format("%7d | %-30s | %-30s%n", rs.getInt(1), rs.getString(2), rs.getString(3));
rs.close();
}
ps.close();
// 4. Connect to the global group and clean up
connection.setServerGroupName("fabric_test1_global");
statement.executeUpdate("drop table if exists employees");
statement.close();
connection.close();
}
}