Mysql JDBC 实现Master Slave

分手后的思念是犯贱 2022-03-27 02:46 143阅读 0赞

package com.lss.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 KEVIN LUAN
     * @datetime Mar 3, 20135:05:54 PM
     * 
     */
    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();
    	}
    }