JDBC访问Mysql进行读写分离测试



JDBC访问Mysql进行读写分离测试。

在程序中添加两个数据源、分别对应读跟写：

 

  1. #读#
  2. DBDriver=com.mysql.jdbc.Driver
  3. url=jdbc\:mysql\:loadbalance\://10.11.0.75,172.16.0.202\:3306/DB_TEST7?roundRobinLoadBalance\=true&characterEncoding\=UTF-8
  4. name=TESTUSER
  5. pass=TESTPWD
  6. characterEncoding=utf8

 

  1. #写#
  2. DBDriver=com.mysql.jdbc.Driver
  3. url=jdbc\:mysql\:loadbalance\://10.11.2.126\:3306/DB_TEST7?roundRobinLoadBalance\=true&characterEncoding\=UTF-8
  4. name=TESTUSER
  5. pass=TESTPWD
  6. characterEncoding=utf8

测试类：

 


 

  1. /**
  2.  * 数据连接类
  3.  * @author 胡汉三
  4.  *
  5.  */
  6. public class UtilDao {
  7.     static Properties properties = null;
  8.     public UtilDao(String rw){
  9.         //读取属性文件
  10.         properties = new Properties();
  11.         java.io.InputStream in = null;
  12.         if(rw.equals(“R”)){
  13.             in = (java.io.InputStream) this.getClass()
  14.             .getResourceAsStream(“/mysqlDBR.properties”);
  15.         }else if (rw.equals(“W”)){
  16.             in = (java.io.InputStream) this.getClass()
  17.             .getResourceAsStream(“/mysqlDBW.properties”);
  18.         }
  19.         try {
  20.             properties.load(in);
  21.         } catch (IOException ex) {
  22.             System.out.println(ex.getMessage());
  23.             ex.printStackTrace();
  24.         }
  25.     }
  26.     public Connection getConn(){
  27.         Connection connection = null;
  28.         try{
  29.             Class.forName(properties.getProperty(“DBDriver”));
  30.             connection = DriverManager.getConnection(properties.getProperty(“url”),properties.getProperty(“name”),properties.getProperty(“pass”));
  31.         }catch (Exception err) {
  32.             System.out.println(“连接ConDB–>getCon()____JDBC错误!”);
  33.             err.printStackTrace();
  34.             return null;
  35.         }
  36.         return connection;
  37.     }
  39.     public static void main(String[] args) throws SQLException {
  40.         UtilDao uW = new UtilDao(“W”);
  41.         UtilDao uR = new UtilDao(“R”);
  42.         Connection connR = uR.getConn();  //connectionsToHostsMap()
  43.         Connection connW = uW.getConn();  //connectionsToHostsMap()
  44.         connW.setAutoCommit(false);  //自动提交为False
  45.         connR.setAutoCommit(false);  //自动提交为False
  46.         String inSql = ”insert into city(sname) values(‘复制’)”;
  47.         String sql = ”select * from city where sname = ’复制’”;
  48.         Statement sW = connW.createStatement();
  49.         Statement sR = connR.createStatement();
  50.             try {
  51.                 sW.execute(inSql);
  52.                 connW.commit();
  53.             } catch (Exception e) {
  54.                 connW.rollback();
  55.                 e.printStackTrace();
  56.             }
  57.         ResultSet r = sR.executeQuery(sql);
  58.         int l = 0 ;
  59.         while (r.next()){
  60.             System.out.println(r.getString(“sname”)+” ” +r.getString(“Id”)+”    第：”+l+”条”);
  61.             l++;
  62.         }
  63.         r.close();
  64.         sW.close();
  65.         sR.close();
  66.         connW.close();
  67.         connR.close();
  68.     }

 

 

输出：

 

  1. 复制 737    第：0条
  2. 复制 738    第：1条