Page MenuHomec4science
Files F73210323

EmployeesJdbc.java
No OneTemporary
Actions

File Metadata

Created
Fri, Jul 19, 15:31

EmployeesJdbc.java
View Options

/*
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();
}
}

Event Timeline

c4science · Help