Java应用-自动执行tpcds的99个SQL

1、实现的功能
读取、写入Excel
写入文件
执行SQL
2、java基础知识、实现的基本功能
List、ArrayList
泛型
类、对象、构造器
实体类
mysql操作，连接数据库、执行SQL
写文件
读写Excel
3、List、ArrayList、泛型
由于后面用到了泛型，所以先介绍泛型。泛型就是参数化类型，格式为<>，目前我用的比较多的是在链表、集合中，简单理解就是数据类型不只是8大基本类型了，还可以进行扩展，比如实体类、引用类型等。本次代码是将Excel实体类作为ArrayList的元素。
ArrayList继承自AbstractList，并实现了List的接口。
初始化时用到了多态：
List dataList_w = new ArrayList<>();
4、类、对象、构造器
执行SQL单独写了一个类，涉及到了类变量、方法、构造器
类变量：类中、方法之外、static类型的变量。
成员变量：类中、方法之外的变量。在创建对象的时候实例化。可以被类中方法、构造方法和特定类型的语句块访问。
构造方法：构造方法名称与类同名，使用关键字new来创建一个对象时会调用构造方法初始化对象。
5、实体类
读写Excel用到了实体类SqlDataBean，包含3个成语变量id、SQLFile、result，分别为序号、SQL文件名、测试结果，是@Data注解可以节省get、set等方法的编写。
6、mysql连接数据库、执行查询

// 声明并初始化jdbcDriver、dbUrl、dbUser、dbPassword，进行数据库连接
Class.forName(jdbcDriver);
Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);  // 执行查询
int columnCount = rs.getMetaData().getColumnCount();  //获取查询结果的列数
while(rs.next()){
    String result = "";
    for (int i=0; i<columnCount; i++){
        result += "\t" + rs.getString(i+1); 	//查询结果的每一行进行拼接，列直接以\t进行分割
    }
    System.out.println(result);
}
rs.close();
stmt.close();
conn.close();

7、写文件

//生成filePath+fileName文件，讲result追加到文件中并换行
File dir = new File(filePath);
if(!dir.exists()){
    dir.mkdirs();
}
File checkFile = new File(filePath + fileName);
FileWriter writer = null;
if(!checkFile.exists()){
    checkFile.createNewFile();
}
writer = new FileWriter(checkFile, true); 
writer.append(result+"\r\n");
writer.flush();
writer.close();

8、读写Excel
需要用到easyexcel中的读、写、关闭写方法，可以新建maven项目，在pom.xml中添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.9</version>
</dependency>

9、源码
配置文件：db.properties

# mysql in localhost
JDCB_DRIVER = com.mysql.jdbc.Driver
DB_URL = jdbc:mysql://10.1.1.190:3306/tpcds_test?useSSL=false&allowPublicKeyRetrieval=true
DB_USER = root
DB_PASSWORD = lianshi

TpcdsTest类，包含main方法

public class TpcdsTest {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        //配置文件
        String propFile = "D:/Git/tpdcsTest/db.properties";
        //SQL文件所在目录
        String sourcePath = "D:/Work/tx/tpcds/MySQL/query/";
        //执行SQL信息的excel及sheet
        String excelFile = "src/data/sql.xlsx";
        String sheet = "Sheet1";

        // 执行完成后，写入执行结果（pass或报错信息）的excel及sheet
        String resultFile = "src/data/sql_result.xlsx";
        String resultSheet = "SQL执行结果";

        List<SqlDataBean> dataList_w = new ArrayList<>();  //记录结果
        ExecSQL execSQL = new ExecSQL(propFile);
        List<SqlDataBean> listExcel = ExcelUtil.readExcel(excelFile, sheet);
        for(SqlDataBean data:listExcel){
            execSQL.execFirstInFile(sourcePath, data, dataList_w);  // 执行SQL
        }
        ExcelWriter excelWriter = EasyExcel.write(resultFile).build();
        ExcelUtil.excelWriter(excelWriter, dataList_w, resultSheet);

        // 关闭excel写
        ExcelUtil.closeWriter(excelWriter);
    }
} 

SQLDataBean类

@Data
public class SqlDataBean {
    private static final long serialVersionUID = -4399387770290088934L;
    @ExcelProperty(value = "序号", order = 0)
    private int id;
    @ExcelProperty(value = "SQL文件", order = 1)
    private String sqlFile;
    @ExcelProperty(value = "测试结果", order = 2)
    private String result;

}

ExcelUtil类，实现Excel的读、写、关闭写

public class ExcelUtil {
    /**
     * 读取excel
     * @param fileName excel文件名
     * @param sheetName sheet名称
     * @return
     */
    public static List<SqlDataBean> readExcel(String fileName, String sheetName){
        List<SqlDataBean> dataList = new ArrayList<>();
        EasyExcel.read(fileName, SqlDataBean.class, new AnalysisEventListener<SqlDataBean>() {
            @Override
            public void invoke(SqlDataBean data, AnalysisContext context) {
                dataList.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                System.out.println("Excel 读取完成，文件名"+fileName+", sheet："+sheetName+"，行数："+dataList.size());
            }
        }).sheet(sheetName).doRead();
        return dataList;
    }

    /**
     * 写Excel
     * @param excelWriter
     * @param datalist 写入的数据
     * @param sheetName sheet名称
     */
    public static void excelWriter(ExcelWriter excelWriter, List<SqlDataBean> datalist, String sheetName){
        //指定不同的sheet
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(SqlDataBean.class).build();
        //写入excel
        excelWriter.write(datalist, writeSheet);
    }

    public static void closeWriter(ExcelWriter excelWriter){
        if(excelWriter != null){
            excelWriter.finish();
        }
    }

}
ExecSQL类，连接数据库、读取Excel中SQL文件名、拼接SQL、进行查询、查询结果写入文件、执行SQL结果写入Excel
public class ExecSQL{
    static String jdbcDriver = null;
    static String dbUrl = null;
    static String dbUser = null;
    static String dbPassword = null;
    static String query = null;
    static String filePath = "src/data/queryResult/";

    public ExecSQL(String propFile) throws IOException {
        Properties prop = new Properties();
        BufferedReader bufferedReader = new BufferedReader(new FileReader(propFile));
        prop.load(bufferedReader);
        jdbcDriver = prop.getProperty("JDCB_DRIVER");
        dbUrl = prop.getProperty("DB_URL");
        dbUser = prop.getProperty("DB_USER");
        dbPassword = prop.getProperty("DB_PASSWORD");
    }

    /**
     * 定位到要执行的SQL的文件，并完成SQL拼接
     * @param sourcePath SQL文件路径
     * @param data sql文件中每一行数据
     * @param dataList_w 执行完成后需写入SQL文件的数据
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void execFirstInFile(String sourcePath, SqlDataBean data, List<SqlDataBean> dataList_w) throws IOException, ClassNotFoundException, SQLException {
        String queryResult = "";
        String fileName = data.getSqlFile();
        System.out.println(fileName);
        File dir = new File(filePath);
        if(!dir.exists()){
            dir.mkdirs();
        }

        //拼接SQL
        try (BufferedReader in = new BufferedReader(new FileReader(sourcePath + fileName))){
            String str;
            query = "";
            while((str = in.readLine()) != null){
                if(str.length()>0 && !str.startsWith("--")){
                    query = query + " " + str.trim();
                }
            }
            if(query == null){
                return;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        try{
            Class.forName(jdbcDriver);
            Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);  // 执行查询
            File checkFile = new File(filePath + fileName);
            FileWriter writer = null;
            if(!checkFile.exists()){
                checkFile.createNewFile();
            }
            writer = new FileWriter(checkFile, true);
            int columnCount = rs.getMetaData().getColumnCount();
            while(rs.next()){
                String result = "";
                for (int i=0; i<columnCount; i++){
                    result += "\t" + rs.getString(i+1);
                }
                // System.out.println(result);
                writer.append(result+"\r\n");
                writer.flush();
            }
            writer.close();
            rs.close();
            stmt.close();
            conn.close();
            queryResult = "pass";
        }catch (Exception e){
            if(e.getMessage().contains("Communiations link failure")){
                System.out.println("Communiations link failure");
            }else{
                queryResult = e.getMessage();
            }
        }

        System.out.println(queryResult);
        if(dataList_w != null){
            SqlDataBean dataW = new SqlDataBean();
            //存储执行结果的数据
            dataW.setId(data.getId());
            dataW.setSqlFile(data.getSqlFile());
            dataW.setResult(queryResult);
            dataList_w.add(dataW);
        }
    }
}