根据《用Java、Python来开发Hive应用》一文,建立了使用Java、来开发Hive应用的方法,产生的代码如下(做了修改):
代码语言:javascript
复制
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); } }
现在,使用JUnit5来进行测试。
1)建立类全局变量
代码语言:javascript
复制
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";
2)建立@BeforeClass和@AfterClass函数
代码语言:javascript
复制
//所有测试用例执行之前运行 @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); }
3)测试查询
代码语言:javascript
复制
//测试根据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(); } }
4)测试打印结果和查询+打印结果不发生异常
代码语言:javascript
复制
@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); }); }
5)测试添加数据
代码语言:javascript
复制
@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)); }