mysql 集群 jdbc配置

8516830

　　项目中使用mysql 主从复制，但是用程序实现的读写分离，代码片段如下：
1 public DataSource getDataSource(MethodType methodType) {
2 if (methodType == MethodType.WRITE) {
3 return getDataSource(MasterDataSources);
4} else {
5return getDataSource(SlaveDataSources);
6 }
7 }
　　
获取数据源，首先你要确定MethodType 类型，一个是读，一个是写
1public enum MethodType {
2READ, WRITE
3}
　　读是获取从库数据源，写是获取主库数据源。

这样，就需要在jdbc配置配置两个数据源（一主一从）

还有另一种实现方式，不用程序来控制。mysql 驱动包提供相应的实现 com.mysql.jdbc.ReplicationDriver.
我写了一个简单的例子：
　　1 package com.howard.loadbalance;
2
3import java.sql.Connection;
4import java.sql.ResultSet;
5import java.sql.SQLException;
6import java.sql.Statement;
7import java.util.Properties;
8import java.util.concurrent.ExecutorService;
9import java.util.concurrent.Executors;
10
11import org.slf4j.Logger;
12import org.slf4j.LoggerFactory;
13
14import com.mysql.jdbc.ReplicationDriver;
15
16public class MysqlTest {
17
18public static class QueryRunnable implements Runnable {
19
20protected final static Logger logger = LoggerFactory
21.getLogger(QueryRunnable.class);
22
23@Override
24public void run() {
25logger.info("user size: " + this.query());
26}
27
28public int query() {
29int count = 0;
30try {
31ReplicationDriver driver = new ReplicationDriver();
32Properties props = new Properties();
33props.put("roundRobinLoadBalance", "true");
34props.put("autoReconnect", "true");
35props.put("user", "core");
36props.put("password", "core");
37Connection conn = null;
38Statement stat = null;
39ResultSet res = null;
40try {
41conn = driver
42.connect(
43//注意url串中间不要有空格，因为mysql源码对多个地址split时没有trim.
44 "jdbc:mysql:replication://127.0.0.1:3309,127.0.0.1:3306/core",
45props);
46//读写分离标记
47//当设置true时，只会在从库查询数据
48//当设置false时，会在主库做更新删除操作
49// conn.setReadOnly(true);
50 stat = conn.createStatement();
51res = stat.executeQuery("select count(1) from c_user");
52while (res.next())
53count = res.getInt(1);
54} finally {
55if (res != null)
56res.close();
57if (stat != null)
58stat.close();
59if (conn != null)
60conn.close();
61}
62} catch (SQLException e) {
63e.printStackTrace();
64}
65return count;
66}
67}
68
69public static void main(String[] args) {
70// 创建线程池测试负载军衡
71 ExecutorService service = Executors.newCachedThreadPool();
72for (int i = 0; i < 10; i++) {
73service.execute(new QueryRunnable());
74}
75service.shutdown();
76}
77
78}