一、下载amoeba代码
首先先到网站(http://sourceforge.net/projects/amoeba)上下载amoeba for Mysql 代码。然后解压到
C:/amoeba 目录。增加系统环境变量:amoeba.home = C:/amoeba
二、准备mysql数据库
Server1: localhost schema: test table: test_table2
Server2: 10.2.224.241 schema: test table: test_table2
表名称test_table1结构为:
`ID` INTEGER(11) NOT NULL,
`NAME` VARCHAR(250) COLLATE gbk_chinese_ci DEFAULT NULL,
先插入一些模拟数据。
三、修改配置文件
找到amoeba.xml配置,修改mysql代理服务器配置信息:
<server>
<!-- proxy server绑定的端口 -->
<property name="port">8066</property>
<!-- proxy server绑定的IP -->
<property name="ipAddress">127.0.0.1</property>
<!-- proxy server net IO Read thread size -->
<property name="readThreadPoolSize">20</property>
<!-- proxy server client process thread size -->
<property name="clientSideThreadPoolSize">30</property>
<!-- mysql server data packet process thread size -->
<property name="serverSideThreadPoolSize">30</property>
<!-- socket Send and receive BufferSize(unit:K) -->
<property name="netBufferSize">100</property>
<!-- Enable/disable TCP_NODELAY (disable/enable Nagle's algorithm). -->
<property name="tcpNoDelay">true</property>
<!-- 对外验证的用户名 -->
<property name="user">root</property>
<!-- 对外验证的密码 -->
<property name="password">admin</property>
</server>
我设置的监控端口为:8066。
配置两个数据库服务器地址,分别对应Server1和Server2。
修改查询规则文件:
<tableRule name="test_table2" schema="test" defaultPools="server2,server1">
<rule name="rule1">
<parameters>ID</parameters>
<expression><![CDATA[ ID <= 20 ]]></expression>
<defaultPools>server1</defaultPools>
<readPools>server1</readPools>
<writePools>server1</writePools>
</rule>
<rule name="rule2">
<parameters>ID</parameters>
<expression><![CDATA[ ID > 20 ]]></expression>
<defaultPools>server2</defaultPools>
<writePools>server2</writePools>
<readPools>server2</readPools>
</rule>
</tableRule>
规则:以ID=20为界。
OK,配置文件配置好了以后,我们可以执行:${amoeba.home}/bin/amoeba.bat ,启动代理服务器。
四、编写查询代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DataAccess {
private String CONNECTION_STRING = "jdbc:mysql://localhost:8066/test";
// jdbc:mysql://localhost:8066
private String connErrInfo;
private Connection conn;
public DataAccess(){
if(conn == null){
conn = GetConnObj();
}
}
public Connection GetConnObj() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(CONNECTION_STRING,"root","admin");
return conn;
} catch (ClassNotFoundException ex) {
this.connErrInfo += ";dbConn ex:" + ex.toString();
ex.printStackTrace();
} catch (SQLException es) {
this.connErrInfo += ";dbConn es:" + es.getMessage();
es.printStackTrace();
} catch (Exception e) {
this.connErrInfo += ";dbConn e:" + e.getMessage();
e.printStackTrace();
}
return null;
}
public String commonUpdate(String rSqlString) {
if (conn != null) {
try {
Statement stmt = conn.createStatement();
stmt.execute(rSqlString);
//conn.close();
} catch (SQLException e) {
return e.getMessage();
}
}
return "";
}
public ResultSet commonSelect(String rSqlString) {
if (conn != null) {
try {
Statement stmt = conn.createStatement();
stmt.execute(rSqlString);
ResultSet result = stmt.executeQuery(rSqlString);
//conn.close();
return result;
} catch (SQLException es) {
this.connErrInfo = "dbProcess es:" + es.getMessage();
} catch (Exception e) {
this.connErrInfo = "dbProcess e:" + e.getMessage();
}
}
return null;
}
public void close(){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public String getConnErrInfo() {
return connErrInfo;
}
public void setConnErrInfo(String connErrInfo) {
this.connErrInfo = connErrInfo;
}
public static void main(String[] args) throws SQLException{
DataAccess dataAccess = new DataAccess();
java.util.Date startDate = new java.util.Date();
ResultSet rs = dataAccess.commonSelect("select * from test_table2 where ID in(14,15,16,50)");
while(rs.next()){
String siteName = (String)rs.getString("name");
System.out.println("siteName:" + siteName );
}
java.util.Date endDate = new java.util.Date();
long period = endDate.getTime() - startDate.getTime();
System.out.println("耗费时间:" + period);
dataAccess.close();
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
}
五、查询结果
siteName:10.2.224.241_test_table1_14
siteName:10.2.224.241_test_table1_15
siteName:10.2.224.241_test_table1_16
siteName:test_table2_14
siteName:test_table2_15
siteName:test_table2_16
耗费时间:156
我现在只是模拟简单的规则查询,后面我们将逐步深入了解。