5.2.?接口使用说明

5.2.1. mpp相关测试场景环境搭建
5.2.2. mpp场景-多CN轮询接口用例
5.2.3. mpp场景-直连worker接口用例
5.2.4. 非mpp场景-当作普通jdbc连接池使用用例

5.2.1.?mpp相关测试场景环境搭建

  1. 节点分布

    master:192.168.120.100:5432

    cn1:192.168.120.100:5433

    worker1:192.168.120.100:1234

    worker2:192.168.120.100:1235

    在master节点设置分片规则,分为两片,每个worker占用一片,并搭建mpp集群环境,详细过程请参考《优炫数据库uxmpp使用手册 V2.1》(加入网络链接)。

  2. 测试表创建

    在master节点上,创建一个测试表。

    create table test(id  int,name text);  //创建普通表
    #insert into test (id,name) select generate_series(1,10000),'test'; //添加10000条数据
    select create_distributed_table('test','id');//以id字段分片

5.2.2.?mpp场景-多CN轮询接口用例

  1. 初始化集群连接

    //创建代理conn
    //是否懒加载
    private static boolean isLazyInitialize=false;
    //是否轮询
    private static boolean pollingMode=true;
    //当前是否是mpp环境
    private static boolean mppInstall=true;
    ShardingConnection conn = new ShardingConnection(isLazyInitialize, pollingMode,mppInstall);
    
    //添加master节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.setMasterCNDataSource
     ("com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:5432/mydb", "uxdb", "123456");
    
    //添加cn节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.putCNDataSources
     ("com.uxsino.uxdb.Driver","jdbc:uxdb://192.168.120.100:5433/mydb","uxdb","123456");
    
    //worker节点初始化名称;取值必须包含 worker
    String worker1Key="worker1";
    String worker2Key="worker2";
    
    //添加worker节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.putWorkerDataSources
      (worker1Key, "com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:1234/mydb", "uxdb", "123456");
    conn.putWorkerDataSources
      (worker2Key, "com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:1235/mydb", "uxdb", "123456");
    //连接池及mpp元数据初始化
     conn.initConnection();
  2. 关闭直连worker开关

    //关闭直连worker开关
    conn.setDirectWorker(false);
  3. 创建statement,执行SQL

    ResultSet rs = null;
    PreparedStatement statement = null;
    PreparedStatement statement2 = null;
    PreparedStatement statement3 = null;
    PreparedStatement statement4 = null;
    
    //注意insert语句格式,必须包含表列名,分片字段
    statement2 = conn.prepareStatement("insert into test (ID,NAME) values (?,?)");
    statement2.setInt(1, 1);
    statement2.setString(2, "bar");
    int count = statement2.executeUpdate();
    System.out.println("添加数据" + count);
    
    //注意update语句格式,必须包含分片字段
    statement4 = conn.prepareStatement("update test set name = ? where id=?");
    statement4.setInt(2, 1);
    statement4.setString(1, "xxxx");
    int count2 = statement4.executeUpdate();
    System.out.println("修改数据" + count2);
    
    //注意select 语句格式,必须包含分片字段
    statement = conn.prepareStatement("select * from test where id = ?");
    statement.setInt(1, 1);
    rs = statement.executeQuery();
    while (rs.next()) {
        System.out.print("查询数据---");
        System.out.print("id=" + rs.getString("id") + " ,");
        System.out.print("name=" + rs.getString("name") + "");
        System.out.println();
    }
    
    //注意delete语句格式,必须包含分片字段
    statement3 = conn.prepareStatement("delete from test where id = ?");
    statement3.setInt(1, 1);
    int count1 = statement3.executeUpdate();
    System.out.println("删除数据" + count1);
    conn.commit();

5.2.3.?mpp场景-直连worker接口用例

  1. 初始化集群连接

    //创建代理conn
    //是否懒加载
    private static boolean isLazyInitialize=false;
    
    //是否轮询—直连worker,设置false
    private static boolean pollingMode=false;
    
    //当前是否是mpp环境
    private static boolean mppInstall=true;
    ShardingConnection conn = new ShardingConnection(isLazyInitialize, pollingMode,mppInstall);
    
    //添加master节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.setMasterCNDataSource
     ("com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:5432/mydb", "uxdb", "123456");
    
    //添加cn节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.putCNDataSources
    ("com.uxsino.uxdb.Driver","jdbc:uxdb://192.168.120.100:5433/mydb","uxdb","123456");
    
    //worker节点初始化名称;取值必须包含 worker
    String worker1Key="worker1";
    String worker2Key="worker2";
    
    //添加worker节点信息,参数分别为驱动,数据库url,用户名,密码
    conn.putWorkerDataSources
      (worker1Key, "com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:1234/mydb", "uxdb", "123456");
    conn.putWorkerDataSources
      (worker2Key, "com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:1235/mydb", "uxdb", "123456");
    
    //连接池及mpp元数据初始化
     conn.initConnection();
  2. 开启直连worker开关,设置worker列表

    //开启直连worker开关
    conn.setDirectWorker(true);
    List < String  > directWorkerList = new ArrayList<>();
    //规定那些woker可以路由,取值范围在worker节点初始化时已经说明
    directWorkerList.add("worker1");
    directWorkerList.add("worker2");
    conn.setDirectWorkerList(directWorkerList);
  3. 创建statement,执行SQL

    ResultSet rs = null;
    PreparedStatement statement = null;
    PreparedStatement statement2 = null;
    PreparedStatement statement3 = null;
    PreparedStatement statement4 = null;
    
    
    //注意insert语句格式,必须包含表列名,分片字段
    statement2 = conn.prepareStatement("insert into test (ID,NAME) values (?,?)");
    statement2.setInt(1, 1);
    statement2.setString(2, "bar");
    int count = statement2.executeUpdate();
    System.out.println("添加数据" + count);
    
    //注意update语句格式,必须包含分片字段
    statement4 = conn.prepareStatement("update test set name = ? where id=?");
    statement4.setInt(2, 1);
    statement4.setString(1, "xxxx");
    int count2 = statement4.executeUpdate();
    System.out.println("修改数据" + count2);
    
    //注意select 语句格式,必须包含分片字段
    statement = conn.prepareStatement("select * from test where id = ?");
    statement.setInt(1, 1);
    rs = statement.executeQuery();
    while (rs.next()) {
        System.out.print("查询数据---");
        System.out.print("id=" + rs.getString("id") + " ,");
        System.out.print("name=" + rs.getString("name") + "");
        System.out.println();
    }
    
    //注意delete语句格式,必须包含分片字段
    statement3 = conn.prepareStatement("delete from test where id = ?");
    statement3.setInt(1, 1);
    int count1 = statement3.executeUpdate();
    System.out.println("删除数据" + count1);
    conn.commit();

5.2.4.?非mpp场景-当作普通jdbc连接池使用用例

  1. 初始化集群连接

    //建立数据库对象
    //初始化连接数
    private static  int initialSize=2;
    
    //最大连接数
    private static int maxActive=5;
    
    //是否是公平锁
    private static boolean lockFair=false;
      
    //是否懒加载
    private static boolean isLazyInitialize=true;
    
    //是否轮询—直连worker,设置false
     private static boolean pollingMode=false;
    
    //当前是否是mpp环境
    private static boolean mppInstall=false;
    ShardingDataSource shardingDataSource = new
    	ShardingDataSource(initialSize, maxActive, lockFair, true, false,false);
    
    shardingDataSource.setMasterCNDataSource
        ("com.uxsino.uxdb.Driver", "jdbc:uxdb://192.168.120.100:5432/mydb", "uxdb", "123456");
    //建立操作对象
    Connection conn = shardingDataSource.getConnection();
  2. 创建statement,执行SQL

    ResultSet rs = null;
    PreparedStatement statement = null;
    PreparedStatement statement2 = null;
    PreparedStatement statement3 = null;
    PreparedStatement statement4 = null;
    
    
    //注意insert语句格式,必须包含表列名,分片字段
    statement2 = conn.prepareStatement("insert into test (ID,NAME) values (?,?)");
    statement2.setInt(1, 1);
    statement2.setString(2, "bar");
    int count = statement2.executeUpdate();
    System.out.println("添加数据" + count);
    
    //注意update语句格式,必须包含分片字段
    statement4 = conn.prepareStatement("update test set name = ? where id=?");
    statement4.setInt(2, 1);
    statement4.setString(1, "xxxx");
    int count2 = statement4.executeUpdate();
    System.out.println("修改数据" + count2);
    
    //注意select 语句格式,必须包含分片字段
    statement = conn.prepareStatement("select * from test where id = ?");
    statement.setInt(1, 1);
    rs = statement.executeQuery();
    while (rs.next()) {
        System.out.print("查询数据---");
        System.out.print("id=" + rs.getString("id") + " ,");
        System.out.print("name=" + rs.getString("name") + "");
        System.out.println();
    }
    
    //注意delete语句格式,必须包含分片字段
    statement3 = conn.prepareStatement("delete from test where id = ?");
    statement3.setInt(1, 1);
    int count1 = statement3.executeUpdate();
    System.out.println("删除数据" + count1);
    conn.commit();
XML 地图 | Sitemap 地图