最新项目中有哦使用impala,在这里记录下,后续使用过程踩空较多再写一个博客记录,顺便共享下网友供参考 驱动包下载 Download Impala JDBC Connector 2.5.42 我用的是这个ImpalaJDBC42.jar,看需求选择41、42都可以 实战代码,可用 @Value 中的参数是配置在 application.yml 配置参考
##数据库驱动 ImpalaJDBC42.jar impalaDriverName: com.cloudera.impala.jdbc.Driver #数据库Ip impalaUrl: jdbc:impala://ip:端口/数据库;AuthMech=3 #数据库端口 impalaPort: 数据库端口 #数据库用户名 impalaUserName: 数据库用户名 #数据库MM impalaPwd: 数据库密码 #数据库名 schema impalaDatabase: 表的schema
代码: import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Component; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; @Component @Slf4j public class ImpalaJdbcUtil { /** * impalaUrl数据库连接IP */ private static String impalaUrl; @Value("${impalaUrl}") public void setImpalaUrl(String impalaUrl) { ImpalaJdbcUtil.impalaUrl = impalaUrl; } /** * impalaUrl数据库连接IP */ private static String impalaDriverName; @Value("${impalaDriverName}") public void setImpalaDriverName(String impalaDriverName) { ImpalaJdbcUtil.impalaDriverName = impalaDriverName; } /** * impalaUrl数据库端口 */ private static int impalaPort; @Value("${impalaPort}") public void setImpalaPort(int impalaPort) { ImpalaJdbcUtil.impalaPort = impalaPort; } /** * impalaUrl数据库用户 */ private static String impalaUserName; @Value("application.ymlimpalaUserName}") public void setImpalaUserName(String impalaUserName) { ImpalaJdbcUtil.impalaUserName = impalaUserName; } /** * impalaUrl数据库密码 */ @Value("${impalaPwd}") private static String impalaPwd; @Value("${impalaPwd}") public void setImpalaPwd(String impalaPwd) { ImpalaJdbcUtil.impalaPwd = impalaPwd; } /** * impalaUrl默认数据库 */ private static String impalaDatabase; @Value("${impalaDatabase}") public void setImpalaDatabase(String impalaDatabase) { ImpalaJdbcUtil.impalaDatabase = impalaDatabase; } public static void main(String[] args) throws ClassNotFoundException, SQLException { Connection conn = null; Statement stmt = null; ResultSet rs = null; Class.forName("com.cloudera.impala.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:impala://ip:端口/[数据库名]", "用户名", "密码"); stmt = conn.createStatement(); System.out.println(conn); System.out.println(stmt); } /*** * 执行查询返回结果 * @param sql * @return */ public static Map<String,Object> executeQuery(String sql) { log.debug("impala->executeQuery->:"+sql); Map<String, Object> reMap = new HashMap<String, Object>(); Connection conn = null; Statement stmt = null; ResultSet rs = null; Class impalaDriverClass = null; try { try { if(impalaDriverClass ==null){ impalaDriverClass = Class.forName(impalaDriverName); } if(conn ==null){ conn = DriverManager.getConnection(impalaUrl, impalaUserName, impalaPwd); } if(stmt == null){ stmt = conn.createStatement(); } } catch (Exception e) { reMap.put("type", "error"); reMap.put("message", "impala初始化错误:" + e); } try { rs = stmt.executeQuery(sql); } catch (SQLException e) { reMap.put("type", "error"); reMap.put("message", "impala-sql执行错误:" + e); } ResultSetMetaData rsmd = null; try { rsmd = rs.getMetaData(); } catch (SQLException e) { reMap.put("type", "error"); reMap.put("message", "impala-获取数据库的信息失败:" + e); } int cls = 0; if (rsmd != null) { try { cls = rsmd.getColumnCount(); } catch (SQLException e) { e.printStackTrace(); reMap.put("type", "error"); reMap.put("message", "impala-获取表字段列表失败:" + e); } } List<String> cnameList = new ArrayList<String>(); for (int i = 0; i < cls; i++) { String cname; try { cname = rsmd.getColumnName(i + 1); cnameList.add(cname); } catch (SQLException e) { reMap.put("type", "error"); reMap.put("message", "impala-获取表字段列表失败:" + e); } } List<List<Object>> reList = new ArrayList<List<Object>>(); // 获取返回列数 int columnCount; try { columnCount = rsmd.getColumnCount(); // 将数据封装为Map List<Map<String, Object>> list = new ArrayList<>(); try { while (rs.next()) { Map<String, Object> columnMap = new HashMap<>(); // 注:列名的索引 起始是 1不是 0 for (int i = 1; i <= columnCount; i++) { String key = rsmd.getColumnName(i); Object value = rs.getObject(key); columnMap.put(key, value); } list.add(columnMap); } reMap.put("type", "result"); reMap.put("data", list); reMap.put("cnameList", cnameList); } catch (SQLException e) { reMap.put("type", "error"); reMap.put("message", "impala-返回信息获取失败:" + e); } } catch (SQLException e) { reMap.put("type", "error"); reMap.put("message", "impala-返回信息获取失败:" + e); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { } } return reMap; } }