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