Mysql JDBC 实现Master Slave

[java] view plain copy

1. package com.lss.test;  
2.   
3. import java.beans.PropertyVetoException;  
4. import java.sql.Connection;  
5. import java.sql.ResultSet;  
6. import java.sql.SQLException;  
7.   
8. import com.mchange.v2.c3p0.ComboPooledDataSource;  
9.   
10. /** 
11.  * Mysql JDBC 实现Master Slave 
12.  *  
13.  * @author KEVIN LUAN 
14.  * @datetime Mar 3, 20135:05:54 PM 
15.  *  
16.  */  
17. public class ReplicationDriverTest {  
18.     private static final String URL = "jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8";  
19.     private static final String DRIVER = "com.mysql.jdbc.Driver";  
20.     /* Master Slave */  
21.     private static final String replicationURL = "jdbc:mysql:replication://localhost:3306,10.2.15.123:3306/test?useUnicode=true&characterEncoding=utf8";  
22.     /* 负载平衡 */  
23.     private static final String loadBalanceURL = "jdbc:mysql:loadbalance://localhost:3306,10.2.15.123:3306/test?useUnicode=true&characterEncoding=utf8";  
24.     private static final String REPLICATION_DRIVER = "com.mysql.jdbc.ReplicationDriver";  
25.   
26.     public static void main(String[] args) throws SQLException {  
27.         // oneDB();  
28.         // replicationDB(URL);  
29.         // replicationDB(replicationURL);  
30.         replicationDB(loadBalanceURL);  
31.   
32.     }  
33.   
34.     public static void replicationDB(String url) throws SQLException {  
35.         ComboPooledDataSource dataSource = new ComboPooledDataSource();  
36.         try {  
37.             dataSource.setDriverClass(REPLICATION_DRIVER);  
38.         } catch (PropertyVetoException e1) {  
39.             e1.printStackTrace();  
40.         }  
41.         dataSource.setJdbcUrl(url);  
42.         dataSource.setMaxPoolSize(10);  
43.         dataSource.setMinPoolSize(10);  
44.         dataSource.setUser("kevin");  
45.         dataSource.setPassword("123456");  
46.         dataSource.setCheckoutTimeout(1000);  
47.         dataSource.setDataSourceName("datasource");  
48.         dataSource.setInitialPoolSize(10);  
49.         try {  
50.             Connection connection = dataSource.getConnection();  
51.             connection.setReadOnly(true);//设置为只读，代理类将会获取Slave数据库连接，否则设置Master连接  
52.             java.sql.PreparedStatement pStatement_ = connection.prepareStatement("SELECT user_id, username, PASSWORD, email  FROM account_0 LIMIT 0,3;");  
53.             ResultSet rs = pStatement_.executeQuery();  
54.             while (rs.next()) {  
55.                 System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));  
56.             }  
57.             rs.close();  
58.             pStatement_.close();  
59.             connection.close();  
60.         } catch (SQLException e) {  
61.             e.printStackTrace();  
62.         }  
63.         Connection connection = dataSource.getConnection();  
64.         connection.setReadOnly(false);//设置为只读，代理类将会获取Slave数据库连接，否则设置Master连接  
65.         java.sql.PreparedStatement pStatement_ = connection.prepareStatement("UPDATE account_0 SET  username = 'kevin' ,PASSWORD = 'password' ,email = 'xxxx' WHERE user_id = '1001' ;");  
66.         int row = pStatement_.executeUpdate();  
67.         System.out.println(row);  
68.         pStatement_.close();  
69.         connection.close();  
70.     }  
71.   
72.     public static void oneDB() throws SQLException {  
73.         ComboPooledDataSource dataSource = new ComboPooledDataSource();  
74.         try {  
75.             dataSource.setDriverClass(DRIVER);  
76.         } catch (PropertyVetoException e1) {  
77.             e1.printStackTrace();  
78.         }  
79.         dataSource.setJdbcUrl(URL);  
80.         dataSource.setMaxPoolSize(10);  
81.         dataSource.setMinPoolSize(10);  
82.         dataSource.setUser("root");  
83.         dataSource.setPassword("123456");  
84.         dataSource.setCheckoutTimeout(1000);  
85.         dataSource.setDataSourceName("datasource00");  
86.         dataSource.setInitialPoolSize(10);  
87.         try {  
88.             Connection connection = dataSource.getConnection();  
89.             java.sql.PreparedStatement pStatement_ = connection.prepareStatement("SELECT * FROM user limit 1");  
90.             ResultSet rs = pStatement_.executeQuery();  
91.             while (rs.next()) {  
92.                 System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));  
93.             }  
94.             rs.close();  
95.             pStatement_.close();  
96.             connection.close();  
97.         } catch (SQLException e) {  
98.             e.printStackTrace();  
99.         }  
100.         Connection connection = dataSource.getConnection();  
101.         java.sql.PreparedStatement pStatement_ = connection.prepareStatement("UPDATE user SET   NAME = 'KEVIN-LUAN' , sex = '1' , age = '89' WHERE id = 16 ;");  
102.         int row = pStatement_.executeUpdate();  
103.         System.out.println(row);  
104.         pStatement_.close();  
105.         connection.close();  
106.     }  
107. }