Java GroupDataSourceRouteHelper类代码示例

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入依赖的package包/类
protected long queryOldValue(DataSource dataSource, String keyName) throws SQLException, SequenceException {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(getSelectSql());
        stmt.setString(1, keyName);
        GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
        rs = stmt.executeQuery();
        if (rs.next()) {
            return rs.getLong(1);
        } else {
            throw new SequenceException("找不到对应的sequence记录，请检查sequence : " + keyName);
        }
    } finally {
        closeDbResource(rs, stmt, conn);
    }
}
 

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:rw, ds1:r, ds2:r, ds3:r"));

    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"));

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

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(e, "由于SQLException,更新初值自适应失败！dbGroupIndex:" + dbGroupKeys.get(index)
                                       + "，sequence Name：" + name + "更新过程：" + value + "-->" + newValue);
    } finally {
        closeDbResource(null, stmt, conn);
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入依赖的package包/类
/**
 * 插入新值
 * 
 * @param index
 * @param name
 * @return
 * @throws SequenceException
 * @throws SQLException
 */
private void adjustInsert(int index, String name) throws SequenceException, SQLException {
    TGroupDataSource tGroupDataSource = (TGroupDataSource) dataSourceMap.get(dbGroupKeys.get(index));
    long newValue = index * innerStep;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = tGroupDataSource.getConnection();
        stmt = conn.prepareStatement(getInsertSql());
        stmt.setString(1, name);
        stmt.setLong(2, newValue);
        stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
        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) + "   name:" + name + "插入初值:" + name + "value:" + newValue);

    } catch (SQLException e) {
        logger.error("由于SQLException,插入初值自适应失败！dbGroupIndex:" + dbGroupKeys.get(index) + "，sequence Name：" + name
                     + "   value:" + newValue, e);
        throw new SequenceException(e, "由于SQLException,插入初值自适应失败！dbGroupIndex:" + dbGroupKeys.get(index)
                                       + "，sequence Name：" + name + "   value:" + newValue);
    } finally {
        closeDbResource(rs, stmt, conn);
    }
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入依赖的package包/类
@Test
public void oneConnCommitTest() 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.commit();

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    ResultSet rs2 = stat.executeQuery(sqlx);
    while (rs2.next()) {
        // 肯定相同
        Assert.assertEquals(time, DateUtil.formatDate(rs2.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包/类
@Ignore("事务被锁跑的时间太久，暂时屏蔽")
@Test
public void severalConnCommitTest() 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);
    sql = "select * from normaltbl_0001 where pk=" + RANDOM_ID;
    ResultSet rs = stat.executeQuery(sql);
    while (rs.next()) {
        Assert.assertEquals(time, DateUtil.formatDate(rs.getDate("gmt_create"), DateUtil.DATE_FULLHYPHEN));
    }

    // 未提交，使用不同连接失败
    Connection otherConn = tds.getConnection();
    otherConn.setAutoCommit(false);
    Statement otherStat = otherConn.createStatement();
    sql = "insert into normaltbl_0001 (pk,gmt_create) values (" + RANDOM_ID + ",'" + time + "')";
    try {
        otherStat.executeUpdate(sql);
        Assert.fail();
    } catch (Exception e) {
        // Lock wait timeout exceeded; try restarting transaction
    }

    // 提交
    conn.commit();

    sql = "select * from normaltbl_0001 where pk=" + RANDOM_ID;
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    rs = stat.executeQuery(sql);
    while (rs.next()) {
        // 肯定相同
        Assert.assertEquals(time, DateUtil.formatDate(rs.getDate("gmt_create"), DateUtil.DATE_FULLHYPHEN));
    }
}
 

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包/类
@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);
}
 

import com.taobao.tddl.common.GroupDataSourceRouteHelper; //导入依赖的package包/类
@Test
public void dynamicAddMasterDSTest() 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 });

    // 加库，并将写库转移到新加入的库
    dataMap = new HashMap<String, String>();
    initAtomConfig(ATOM_NORMAL_1_PATH, APPNAME, DBKEY_1); // 加库qatest_normal_1
    dataMap.put(tds.getFullDbGroupKey(),
        "qatest_normal_0:r,qatest_normal_0_bac:r,qatest_normal_1_bac:r,qatest_normal_1:wr");
    // 主备切换(确保推送成功)
    for (int i = 0; i < 3; i++) {
        MockServer.setConfigInfos(dataMap);
        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 (?,?)";
    // 主备切换之后，正常执行一条sql
    tddlJT.update(sql, new Object[] { RANDOM_ID, time });

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

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 {
    super.setUp();
    super.init();
    // 插入不同的数据到3个库
    String sql = "insert into normaltbl_0001 (pk,gmt_create) values (?,?)";
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(0);
    tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    tddlJT.update(sql, new Object[] { RANDOM_ID, nextDay });
    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    tddlJT.update(sql, new Object[] { RANDOM_ID, theDayAfterTomorow });
}
 

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

    // 插入同样的数据到3个库
    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);

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(1);
    rs = tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    Assert.assertTrue(rs > 0);

    GroupDataSourceRouteHelper.executeByGroupDataSourceIndex(2);
    rs = tddlJT.update(sql, new Object[] { RANDOM_ID, time });
    Assert.assertTrue(rs > 0);

    // 因为3个库都是写库，所以查询的时候抛NoMoreDataSourceException异常
    for (int i = 0; i < 6; i++) {
        try {
            tddlJT.queryForMap("select * 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);
        }
    }
}
 

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")));
}