/*
  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 FLOSS 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.Statement;
import java.sql.ResultSet;

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