基于Java访问Hive的JUnit5测试代码实现

package com.jerry;
           
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
           
public class HiveClient {
    private static final String DRIVER_CLASS = "org.apache.hive.jdbc.HiveDriver";
    private static final String CONNECTION_URL = "jdbc:hive2://192.168.31.184:10000/demo";
    private static PreparedStatement preparedstatement;
    private static Statement statement;
    private static ResultSet resultSet = null;    
           
    //建立连接
    Connection getConnection() throws SQLException {
        try {
            Class.forName(DRIVER_CLASS);
            Connection con = DriverManager.getConnection(CONNECTION_URL);
            statement = con.createStatement();
            return con;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new SQLException(e.getMessage());
        }
    }
    
    //断开连接
    public void disconnect(Connection con) throws SQLException {
           // Close resources
        resultSet.close();
        statement.close();
        con.close();
    }    
    
    //根据query查询
    public ResultSet query(String query) throws SQLException {
           // Execute a query
        resultSet = statement.executeQuery(query);
        return resultSet;
    }
    
    //条件查询
    public ResultSet query(Connection con,String query,Mapcondition) throws SQLException {
           String where = " where ";
           int i = 0;
           int length = condition.size(); 
           String[] valuearray= new String[length];
           for (String key : condition.keySet()) {
                   String value = condition.get(key);
                   where = where+key+" = ? AND ";
                   valuearray[i] = value;
                   i++;
           }
           where = where + "1=1";    
           query = query + where;
           PreparedStatement preparedStatement = con.prepareStatement(query);
           for(int j=0;j
                  preparedStatement.setString(j+1, valuearray[j]);
           }
           resultSet = preparedStatement.executeQuery();
           return resultSet;
    }
    
    //打印查询记录
    public void printQueryResult(Connection con,String query,Mapcondition) throws SQLException {
           ResultSet resultSet = query(con,query,condition);
           //获取 ResultSet 的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 获取列数
        int columnCount = metaData.getColumnCount();
              while (resultSet.next()) {
                     for (int i=1;i<=columnCount;i++) {
                            System.out.print(resultSet.getString(i)+",");
                     }    
                     System.out.println("");
        }
    }
    
    //查询并且打印数据
    public void printQueryResult(String query) throws SQLException {
           ResultSet resultSet = query(query);
           //获取 ResultSet 的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 获取列数
        int columnCount = metaData.getColumnCount();
              while (resultSet.next()) {
                     for (int i=1;i<=columnCount;i++) {
                            System.out.print(resultSet.getString(i)+",");
                     }
                     System.out.println("");
        }
    }
    
    //查询并且打印数据
    public void queryAndPrint(String query) throws SQLException {
           printQueryResult(query);    
    }
    
    //查询并且打印数据
    public void queryAndPrint(Connection con,String query,Mapcondition) throws SQLException {
           printQueryResult(con,query,condition);
    }
    
    //添加数据
    public void addDataToHiveTable(Connection con,String tableName,String[] newValue) {
        try {
               String insertSql = "INSERT INTO person SELECT ?,?,?,"+newValue[3]+","+newValue[4];
               preparedstatement = con.prepareStatement(insertSql);
               preparedstatement.setInt(1, Integer.parseInt(newValue[0]));
               preparedstatement.setString(2, newValue[1]);
               preparedstatement.setInt(3, Integer.parseInt(newValue[2]));
               preparedstatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }    
    }
    
    //将文件中的数据加载到表中
    public void loadDataForLocal(String tableName,String path) throws SQLException {
           String query = "LOAD DATA LOCAL INPATH '"+path+"' INTO TABLE "+tableName;
           statement.execute(query);
           
    }
    
    //清空数据表
    public void truncateTable(Connection con,String tableName) throws SQLException {
           String query = "truncate table "+tableName;
           con.setAutoCommit(true); 
           Statement statement = con.createStatement();
           statement.execute(query);
    } 
}

public class HiveClientTest {
    //被测类
    private static HiveClient hive = new HiveClient();
    //数据库连接
    private static Connection con;
    //存在的表名
    private static String tableName = "person";
    //不存在的表名，用于错误测试用例开发
    private static String errorTableName = "myperson";
    //正确的查询语句
    private static String query = "SELECT * FROM "+tableName;
    //错误的查询语句（查询语句中的表不存在）
    private static String errorQuery = "SELECT * FROM "+errorTableName;
    //导入数据路径
    private static String inpath = "/home/jerry/hive/person";    

  //所有测试用例执行之前运行
  @BeforeClass
  public static void setUpBeforeClass() throws Exception {
    //建立连接
    con = hive.getConnection();    
    //从导入数据路径中导入数据，作为基础测试数据
    hive.loadDataForLocal(tableName,inpath);
  }
           
  //所有测试用例执行之后运行
   @AfterClass
    public static void tearDownAfterClass() throws Exception {
    //清空测试数据
    hive.truncateTable(con,tableName);
    //断开链接
    hive.disconnect(con);
  }

//测试根据Query查询
@Test
public void testQuery() {
       try {
        //获得查询结果
        ResultSet resultSet = hive.query(query);
        //遍历查询结果
        while (resultSet.next()) {
            //验证编号行是不是数字    
            assertThat(resultSet.getInt(1),instanceOf(Integer.class));
           //验证姓名行是不是包含字符串"elite"
            assertTrue(resultSet.getString(2), resultSet.getString(2).contains("elite"));
            //验证年龄行是不是数字
            assertThat(resultSet.getInt(3),instanceOf(Integer.class));
            //验证爱好行是不是为"[\"basketball\",\"music\",\"dance\"]"
            assertEquals("[\"basketball\",\"music\",\"dance\"]",resultSet.getString(4));
            //验证地址行是不是为"{\"address\":\"xx\"}"
            assertEquals("{\"address\":\"xx\"}",resultSet.getString(5));
          }
         } catch (SQLException e) {
            // TODO Auto-generated catch block
             e.printStackTrace();
          }
          //测试错误的Query
          Assertions.assertThrows(SQLException.class, () -> hive.query(errorQuery));
       }
           
//测试根据条件查询-一个条件    
@Test
public void testQueryWitchOneCondition() {
  //构建查询条件
  Mapcondition = new HashMap();
  condition.put("name","elite0");
  try {
    //条件查询
    ResultSet resultSet = hive.query(con,query,condition);
    //遍历查询结果
    while (resultSet.next()) {
        //验证第一个匹配项
       assertEquals("elite0",resultSet.getString(2));
      //验证第二个匹配项
       assertEquals("10",resultSet.getString(3));
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
           
//测试根据条件查询-两个条件
 @Test
public void testQueryWitchTwoCondition() {
  //构建查询条件
    Mapcondition = new HashMap();
    condition.put("name","elite0");
    condition.put("age","50");
    try {
        //条件查询
        ResultSet resultSet = hive.query(con,query,condition);
        //遍历查询结果
        while (resultSet.next()) {
               //验证第一个匹配项
               assertEquals("elite0",resultSet.getString(2));
                //验证第二个匹配项
               assertEquals("50",resultSet.getString(3));
         }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  //测试错误的Query    
  Assertions.assertThrows(SQLException.class, ()-> hive.query(con,errorQuery,condition));
}
              
//测试根据条件查询-三个条件
@Test
public void testQueryWitchThreeCondition() {
    //构建查询条件
    Mapcondition = new HashMap();
    condition.put("id","0");
    condition.put("name","elite0");
    condition.put("age","50");
    try {
      //条件查询
       ResultSet resultSet = hive.query(con,query,condition);
      //遍历查询结果
      while (resultSet.next()) {
          //验证第一个匹配项
          assertEquals("0",resultSet.getString(1));
           //验证第一个匹配项
          assertEquals("elite0",resultSet.getString(2));    
           //验证第二个匹配项
          assertEquals("50",resultSet.getString(3));
        }
    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
  }

       @Test
       public void testPrintQueryResult_1() {
              assertDoesNotThrow(() -> {
                     hive.printQueryResult(query);
              });
       }
       
       @Test
       public void testPrintQueryResult_2() {
              Mapcondition = new HashMap();
              condition.put("name","elite0");
              condition.put("age","50");
              assertDoesNotThrow(() -> {    
                     hive.printQueryResult(con,query,condition);
              });
       }
       
       @Test
       public void testQueryAndPrint_1() {
              assertDoesNotThrow(() -> {
                     hive.queryAndPrint(query);
              });
       }
       
       @Test
       public void testqueryAndPrint_2() {
              Mapcondition = new HashMap();
              condition.put("name","elite0");
              condition.put("age","50");
              assertDoesNotThrow(() -> {
                     hive.queryAndPrint(con,query,condition);
              });
       }

              @Test    
            public void testAddDataToHiveTable() {
            //构造插入数据
            String like = "array('basketball', 'music', 'dance')";
           String map = "map('address','xx')";
              String[] newAddValue = {"10","elite0","50",like,map};
              //插入数据
              hive.addDataToHiveTable(con,tableName,newAddValue);
              //检查插入数据是否存到数据库中
              Mapcondition = new HashMap();
              condition.put("id","10");
              condition.put("name","elite0");
              condition.put("age","50");
              ResultSet resultSet;
              try {
                     resultSet = hive.query(con,query,condition);
                     while (resultSet.next()) {
                            assertEquals("10",resultSet.getString(1));
                            assertEquals("elite0",resultSet.getString(2));
                            assertEquals(50,resultSet.getInt(3));
                            assertEquals("[\"basketball\",\"music\",\"dance\"]",resultSet.getString(4));
                            assertEquals("{\"address\":\"xx\"}",resultSet.getString(5));    
                     }
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              String[] newAddErrorValue = {"ab","elite0","cd",like,map};
              //测试ErrorValue
              Assertions.assertThrows(NumberFormatException.class, () -> hive.addDataToHiveTable(con,tableName,newAddErrorValue));
              String[] newAddErrorValue_1 = {"11","elite0","50",like};
              //测试ErrorValue
              Assertions.assertThrows(ArrayIndexOutOfBoundsException.class, () -> hive.addDataToHiveTable(con,tableName,newAddErrorValue_1));
       }