Java GroupDataSourceRouteHelper.executeByGroupDataSourceIndex方法代码示例

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void test_设单个in() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi5, ds1:ri6, ds2:ri7, ds3:ri8"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(6);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds1", "select 1 from dual"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(8);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds3", "select 1 from dual"));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
/**
 * CAS更新sequence值
 * 
 * @param dataSource
 * @param keyName
 * @param oldValue
 * @param newValue
 * @return
 * @throws SQLException
 */
protected int updateNewValue(DataSource dataSource, String keyName, long oldValue, long newValue)
                                                                                                 throws SQLException {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(getUpdateSql());
        stmt.setLong(1, newValue);
        stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
        stmt.setString(3, keyName);
        stmt.setLong(4, oldValue);
        GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
        return stmt.executeUpdate();
    } finally {
        closeDbResource(rs, stmt, conn);
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void testGroupDataSourceRouteHelper() throws Exception {
    try {
        TGroupDataSource ds = new TGroupDataSource(GROUP0, APPNAME);
        MockServer.setConfigInfo(ds.getFullDbGroupKey(), DSKEY0 + ":rw" + "," + DSKEY1 + ":r");
        ds.init();

        GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        assertEquals(stmt.executeUpdate("insert into tddl_test_0000(id,name) values(100,'str')"), 1);
        ResultSet rs = stmt.executeQuery("select id,name from tddl_test_0000 where id=100");
        assertTrue(rs.next());
        // 如果指定了index，忽略rw限制
        ThreadLocalMap.put(ThreadLocalString.DATASOURCE_INDEX, 1);
        assertEquals(stmt.executeUpdate("insert into tddl_test_0000(id,name) values(100,'str')"), 1);
        rs = stmt.executeQuery("select count(*) from tddl_test_0000 where id=100");
        assertTrue(rs.next());
        assertEquals(rs.getInt(1), 1);

        stmt.close();
        conn.close();
    } finally {
        ThreadLocalMap.put(ThreadLocalString.DATASOURCE_INDEX, null);
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void test_设多个i分流() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi0, ds1:ri0, ds2:ri1, ds3:ri1"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds0", "select") || MockDataSource.hasTrace("", "ds1", "select"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds2", "select") || MockDataSource.hasTrace("", "ds3", "select"));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void replaceByPreStTest() {
    prepareData(tddlJT, "insert into normaltbl_0001 (pk,gmt_create) values (?,?)", new Object[] { RANDOM_ID, time });

    String sql = "replace into normaltbl_0001 (pk,gmt_create) values (?,?)";
    tddlJT.update(sql, new Object[] { RANDOM_ID, nextDay });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    Map re = tddlJT.queryForMap("select * from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
    Assert.assertEquals(nextDay, String.valueOf(re.get("gmt_create")));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void queryByPreStTest() {
    prepareData(tddlJT, "insert into normaltbl_0001 (pk,gmt_create) values (?,?)", new Object[] { RANDOM_ID, time });

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    String sql = "select * from normaltbl_0001 where pk=?";
    Object[] arguments = new Object[] { RANDOM_ID };
    Map re = tddlJT.queryForMap(sql, arguments);
    Assert.assertEquals(time, String.valueOf(re.get("gmt_create")));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void test_设单个i不加数字等同于没设() {
    JdbcTemplate jt = new JdbcTemplate(createGroupDataSource("ds0:rwi, ds1:ri, ds2:ri, ds3:ri"));

    MockDataSource.clearTrace();
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    jt.query("select 1 from dual", new Object[] {}, new ColumnMapRowMapper());
    MockDataSource.showTrace();
    Assert.assertTrue(MockDataSource.hasTrace("", "ds1", "select 1 from dual"));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void deleteByStTest() {
    prepareData(tddlJT, "insert into normaltbl_0001 (pk,gmt_create) values (?,?)", new Object[] { RANDOM_ID, time });

    String sql = "delete from normaltbl_0001 where pk=" + RANDOM_ID;
    tddlJT.update(sql);

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    List re = tddlJT.queryForList("select * from normaltbl_0001 where pk=" + RANDOM_ID);
    Assert.assertEquals(0, re.size());
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void oneConnRollbackTest() throws SQLException {
    Connection conn = tds.getConnection();
    conn.setAutoCommit(false);
    Statement stat = conn.createStatement();

    String sql = "insert into normaltbl_0001 (pk,gmt_create) values (" + RANDOM_ID + ",'" + time + "')";
    stat.executeUpdate(sql);

    // 没提交之前使用同一个连接肯定查得到
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    String sqlx = "select * from normaltbl_0001 where pk=" + RANDOM_ID;
    ResultSet rs = stat.executeQuery(sqlx);
    while (rs.next()) {
        Assert.assertEquals(time, DateUtil.formatDate(rs.getDate("gmt_create"), DateUtil.DATE_FULLHYPHEN));
    }

    // 回滚
    conn.rollback();

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    rs = stat.executeQuery(sqlx);
    while (rs.next()) {
        // 肯定不相等
        Assert.assertNotSame(time, DateUtil.formatDate(rs.getDate("gmt_create"), DateUtil.DATE_FULLHYPHEN));
    }

    // 多次提交或者回滚
    try {
        conn.commit();
        conn.commit();
        conn.rollback();
        conn.rollback();
    } catch (Exception e) {
        Assert.fail(e.toString());
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@After
public void destroy() {
    // 清数据防止干扰
    String sql = "delete from normaltbl_0001 where pk=?";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void writeDataToReadOnlyDSTest() throws Exception {
    // 主备切换之前，正常执行一条sql
    String sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    tddlJT.update(sql, new Object[] { RANDOM_ID, time });

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    Map re = tddlJT.queryForMap("select * from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
    Assert.assertEquals(time, String.valueOf(re.get("gmt_create")));
    // 清除数据
    clearData(tddlJT, "delete from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });

    // 修改为只读(确保推送成功)
    for (int i = 0; i < 3; i++) {
        MockServer.setConfigInfo(TAtomConstants.getGlobalDataId(DBKEY_0),
            "ip=10.232.31.154\r\nport=3306\r\ndbName=qatest_normal_0\r\ndbType=mysql\r\ndbStatus=R");
        TimeUnit.SECONDS.sleep(SLEEP_TIME);
    }

    // 主备切换之后，正常执行一条sql
    try {
        clearData(tddlJT, "delete from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
        Assert.fail();
    } catch (Exception e) {
        Assert.assertTrue(e.getMessage().indexOf("com.taobao.tddl.group.exception.NoMoreDataSourceException") != -1);
    }

    // 恢复
    MockServer.setConfigInfo(TAtomConstants.getGlobalDataId(DBKEY_0),
        "ip=10.232.31.154\r\nport=3306\r\ndbName=qatest_normal_0\r\ndbType=mysql\r\ndbStatus=WR");
    TimeUnit.SECONDS.sleep(SLEEP_TIME);
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Before
public void init() throws Exception {
    // 清数据防止干扰
    String sql = "delete from normaltbl_0001 where pk=?";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    clearData(tddlJT, sql, new Object[] { RANDOM_ID });
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
/**
 * 更新
 * 
 * @param index
 * @param value
 * @param name
 * @throws SequenceException
 * @throws SQLException
 */
private void adjustUpdate(int index, long value, String name) throws SequenceException, SQLException {
    long newValue = (value - value % outStep) + outStep + index * innerStep;// 设置成新的调整值
    TGroupDataSource tGroupDataSource = (TGroupDataSource) dataSourceMap.get(dbGroupKeys.get(index));
    Connection conn = null;
    PreparedStatement stmt = null;
    // ResultSet rs = null;
    try {
        conn = tGroupDataSource.getConnection();
        stmt = conn.prepareStatement(getUpdateSql());
        stmt.setLong(1, newValue);
        stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
        stmt.setString(3, name);
        stmt.setLong(4, value);
        GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
        int affectedRows = stmt.executeUpdate();
        if (affectedRows == 0) {
            throw new SequenceException("faild to auto adjust init value at  " + name + " update affectedRow =0");
        }
        logger.info(dbGroupKeys.get(index) + "更新初值成功!" + "sequence Name：" + name + "更新过程：" + value + "-->"
                    + newValue);
    } catch (SQLException e) { // 吃掉SQL异常，抛Sequence异常
        logger.error("由于SQLException,更新初值自适应失败！dbGroupIndex:" + dbGroupKeys.get(index) + "，sequence Name：" + name
                     + "更新过程：" + value + "-->" + newValue, e);
        throw new SequenceException("由于SQLException,更新初值自适应失败！dbGroupIndex:" + dbGroupKeys.get(index)
                                    + "，sequence Name：" + name + "更新过程：" + value + "-->" + newValue, e);
    } finally {
        closeDbResource(null, stmt, conn);
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void updateFromReadOnlyDSByDatasourceIndexTest() throws InterruptedException {
    // 将3个库全部设置为只读(确保推送成功)
    for (int i = 0; i < 2; i++) {
        MockServer.setConfigInfo(tds.getFullDbGroupKey(),
            "qatest_normal_0:r,qatest_normal_0_bac:r,qatest_normal_1_bac:r");
        TimeUnit.SECONDS.sleep(SLEEP_TIME);
    }

    // 插入数据
    String sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    int rs = tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    Assert.assertTrue(rs > 0);

    sql = "select * from normaltbl_0001 where pk=?";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    Map re = tddlJT.queryForMap(sql, new Object[] { RANDOM_ID });
    Assert.assertEquals(time, String.valueOf(re.get("gmt_create")));

    // 更新数据
    sql = "update normaltbl_0001 set gmt_create=? where pk=?";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    rs = tddlJT.update(sql, new Object[] { nextDay, RANDOM_ID });
    Assert.assertTrue(rs > 0);

    sql = "select * from normaltbl_0001 where pk=?";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    re = tddlJT.queryForMap(sql, new Object[] { RANDOM_ID });
    Assert.assertEquals(nextDay, String.valueOf(re.get("gmt_create")));
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入方法依赖的package包/类
@Test
public void dynamicChangeMSDSTest() throws Exception {
    // 主备切换之前，正常执行一条sql
    String sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    tddlJT.update(sql, new Object[] { RANDOM_ID, time });

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    Map re = tddlJT.queryForMap("select * from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
    Assert.assertEquals(time, String.valueOf(re.get("gmt_create")));
    // 清除数据
    clearData(tddlJT, "delete from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });

    // 主备切换(确保推送成功)
    for (int i = 0; i < 2; i++) {
        MockServer.setConfigInfo(tds.getFullDbGroupKey(),
            "qatest_normal_0:r,qatest_normal_0_bac:wr,qatest_normal_1_bac:r");
        TimeUnit.SECONDS.sleep(SLEEP_TIME);
    }

    // 主备切换之后，正常执行一条sql
    clearData(tddlJT, "delete from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
    sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    int rs = tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    Assert.assertEquals(1, rs);

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    re = tddlJT.queryForMap("select * from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });
    Assert.assertEquals(time, String.valueOf(re.get("gmt_create")));

    // 清除数据
    clearData(tddlJT, "delete from normaltbl_0001 where pk=?", new Object[] { RANDOM_ID });

    // 指定写库的dataSourceIndex
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    rs = tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    Assert.assertEquals(1, rs);
}