一、配置dubbo的Bean文件：

配置spring－dubbo.xml文件：

　<dubbo:reference interface="com.datatrees.basisdata.bankbill.service.BillService" id="billService" url="dubbo://121.43.177.8:20100" timeout="10000"/>

将dubbo配置文件引入上下文spring-context.xml：

<import resource="spring-dubbo.xml" />

二、定义基础类：

@ContextConfiguration(locations = {"classpath:/spring-context.xml"})
@Configuration
public class BaseTestNGTest extends AbstractTestNGSpringContextTests {
//    protected TestContextManager testContextManager;
//    @BeforeTest
//    public void setUpContext() throws Exception {
//        this.testContextManager = new TestContextManager(getClass());
//        this.testContextManager.prepareTestInstance(this);
//    }
}

二、创建测试类，集成基础类：
1.excel参数化管理测试数据
2.excel管理sql数据

package com.tree.autotest.testcase.BillService;

import com.datatrees.basisdata.bankbill.service.BillService;
import com.tree.autotest.BaseTestNGTest;
import com.tree.autotest.commons.CommonUtil_2;
import com.tree.autotest.commons.TestCase;
import com.tree.autotest.demo.ExcelHandle;
import com.tree.autotest.jdbc.DBIdConstant;
import com.tree.autotest.jdbc.ManagedSqlUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.testng.Assert;
import org.testng.annotations.*;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lyh on 17/2/14.
 */
public class TestGetUserEmailNameCount2 extends BaseTestNGTest {
    @Autowired
    private BillService billService;

    private boolean handleOk;
    private String insertSql1,insertSql2;
    private static String excel;
    private static Map allCases;
    private Integer operatorUserId;
    private Integer exceptedvalue;
    private TestCase testcase;

    @BeforeClass
    public void init() throws SQLException,IOException {
        //获取excel用例列表
        excel = "src/main/resources/case/BillService/TestGetUserEmailNameCount.xlsx";
        allCases = CommonUtil_2.getAllCases(excel);
        //获取用户ID
        testcase = CommonUtil_2.getTestCaseBean(allCases, "case1", "case1-1");
        operatorUserId = Integer.valueOf(testcase.param1);
        //获取SQL
        insertSql1 = testcase.stepSql;
        insertSql2 = CommonUtil_2.getTestCaseBean(allCases, "case1", "case1-2").stepSql;
        //删除，添加数据
        handleOk &= deleteData(operatorUserId);
        handleOk &= ManagedSqlUtil.insertBySql(insertSql1, DBIdConstant.MySql_Basisdata_ID);
        handleOk &= ManagedSqlUtil.insertBySql(insertSql2, DBIdConstant.MySql_Basisdata_ID);
        System.out.println("setUp方法执行");

    }

    @DataProvider(name = "testdata")
    public Object[][] dataprovide()throws IOException{
        System.out.println("dataprovide方法执行");
//        return new Object[][]{{2000020013,2},{2001000138,0},{2001000139,2}};
        Object[][] testData =ExcelHandle.readXlsx(excel, "工作表2");
        return testData;
    }

    @Test(dataProvider="testdata")
    public void test_case_1(HashMap<String, String> map) throws Exception {

        operatorUserId=Integer.valueOf(map.get("userId"));
        exceptedvalue =Integer.valueOf(map.get("excepted"));

        //++++++++++++++实际值+++++++++++++
        Integer actual_value =
                billService.getUserEmailNameCount(operatorUserId);

        //预期值
//        Integer excepted_value =get_excepted_value(operatorUserId);
        //++++++++++++++验证+++++++++++++
        Assert.assertEquals(actual_value,exceptedvalue);

    }

    @AfterClass
    public void tearDown() {

        operatorUserId = Integer.valueOf(testcase.param1);
        deleteData(operatorUserId);
        System.out.println("tearDown方法执行");
    }

    //定义数据清理
    public boolean deleteData(Integer userId) {
        String deleteSql1 = "delete from t_bill where UserId="+userId+";";
        String deleteSql2 ="delete from t_user_email where userid="+userId+";";
        handleOk &= ManagedSqlUtil.deleteBySql(deleteSql1, DBIdConstant.MySql_Basisdata_ID);
        handleOk &= ManagedSqlUtil.deleteBySql(deleteSql2, DBIdConstant.MySql_Basisdata_ID);

        return handleOk;
    }
    public Integer get_excepted_value(Integer userId) throws SQLException{
        String searchSql="select count(distinct(NameOnCard)) from t_bill where EmailId in (select distinct(EmailId) from t_user_email where UserId="+userId+")" +
                " and NameOnCard != '';";
        ResultSet rs =ManagedSqlUtil.selectBySql(searchSql,DBIdConstant.MySql_Basisdata_ID);
        List<Integer> list = new ArrayList<Integer>();
        while (rs.next()){
            list.add(rs.getInt(1));
        }
        return list.get(0);
    }
}

四、测试数据文件：
1.准备数据表格

2.测试执行参数表格：

表格：工作表2 中保存测试执行参数数据

五：运行结果：

出现：