MySQL之——JDBC 實現Master Slave

package com.lyz.test;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * Mysql JDBC 实现Master Slave
 * 
 * @author liuyazhuang
 * @datetime 2016-11-17
 * 
 */
public class ReplicationDriverTest {
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8";
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	/* Master Slave */
	private static final String replicationURL = "jdbc:mysql:replication://localhost:3306,10.2.15.123:3306/test?useUnicode=true&characterEncoding=utf8";
	/* 负载平衡 */
	private static final String loadBalanceURL = "jdbc:mysql:loadbalance://localhost:3306,10.2.15.123:3306/test?useUnicode=true&characterEncoding=utf8";
	private static final String REPLICATION_DRIVER = "com.mysql.jdbc.ReplicationDriver";

	public static void main(String[] args) throws SQLException {
		// oneDB();
		// replicationDB(URL);
		// replicationDB(replicationURL);
		replicationDB(loadBalanceURL);

	}

	public static void replicationDB(String url) throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		try {
			dataSource.setDriverClass(REPLICATION_DRIVER);
		} catch (PropertyVetoException e1) {
			e1.printStackTrace();
		}
		dataSource.setJdbcUrl(url);
		dataSource.setMaxPoolSize(10);
		dataSource.setMinPoolSize(10);
		dataSource.setUser("kevin");
		dataSource.setPassword("123456");
		dataSource.setCheckoutTimeout(1000);
		dataSource.setDataSourceName("datasource");
		dataSource.setInitialPoolSize(10);
		try {
			Connection connection = dataSource.getConnection();
			connection.setReadOnly(true);//设置为只读，代理类将会获取Slave数据库连接，否则设置Master连接
			java.sql.PreparedStatement pStatement_ = connection.prepareStatement("SELECT user_id, username, PASSWORD, email  FROM account_0 LIMIT 0,3;");
			ResultSet rs = pStatement_.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));
			}
			rs.close();
			pStatement_.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Connection connection = dataSource.getConnection();
		connection.setReadOnly(false);//设置为只读，代理类将会获取Slave数据库连接，否则设置Master连接
		java.sql.PreparedStatement pStatement_ = connection.prepareStatement("UPDATE account_0 SET  username = 'kevin' ,
		PASSWORD = 'password' ,email = 'xxxx' WHERE user_id = '1001' ;");
		int row = pStatement_.executeUpdate();
		System.out.println(row);
		pStatement_.close();
		connection.close();
	}

	public static void oneDB() throws SQLException {
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		try {
			dataSource.setDriverClass(DRIVER);
		} catch (PropertyVetoException e1) {
			e1.printStackTrace();
		}
		dataSource.setJdbcUrl(URL);
		dataSource.setMaxPoolSize(10);
		dataSource.setMinPoolSize(10);
		dataSource.setUser("root");
		dataSource.setPassword("123456");
		dataSource.setCheckoutTimeout(1000);
		dataSource.setDataSourceName("datasource00");
		dataSource.setInitialPoolSize(10);
		try {
			Connection connection = dataSource.getConnection();
			java.sql.PreparedStatement pStatement_ = connection.prepareStatement("SELECT * FROM user limit 1");
			ResultSet rs = pStatement_.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));
			}
			rs.close();
			pStatement_.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Connection connection = dataSource.getConnection();
		java.sql.PreparedStatement pStatement_ = connection.prepareStatement("UPDATE user SET	NAME = 'KEVIN-LUAN' , sex = '1' ,
		 age = '89' WHERE id = 16 ;");
		int row = pStatement_.executeUpdate();
		System.out.println(row);
		pStatement_.close();
		connection.close();
	}
}