Java集成presto查询
luoyuan323 人气:0Java集成presto查询
1.pom文件引入相关jar
<dependency> <groupId>com.facebook.presto</groupId> <artifactId>presto-jdbc</artifactId> <version>0.234.1</version> </dependency>
2.application.yml配置presto相关
presto: url: xxxxxx username: root password: root port: 8088
3.获取连接与测试
import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.sugon.xuanyuan.common.utils.StringUtils; import com.sugon.xuanyuan.service.dataprovider.utils.JdbcUtil; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Configuration; import java.sql.*; import java.util.Properties; /** * @Description: * @author: luoy * @date: 2020-06-24 09:45 */ @Configuration public class PrestoConnect { @Value("${presto.url}") private String server; @Value("${presto.port}") private String port; @Value("${presto.username}") private String username; @Value("${presto.password}") private String password; private Connection getConnection() throws Exception { /** * 功能:presto 通过 jdbc 连接 * 示例:jdbc:presto://host:port/ **/ String jdbcurl = "jdbc:presto://" + server + ":" + port + "/"; Connection conn ; Properties props = new Properties(); Class.forName("com.facebook.presto.jdbc.PrestoDriver"); props.setProperty("user", username); if (StringUtils.isNotBlank(password)) { props.setProperty("password", password); props.setProperty("SSL", "true"); //props.setProperty("SSLTrustStorePath", SSLTrustStorePath); //props.setProperty("SSLTrustStorePassword", SSLTrustStorePassword); jdbcurl = String.format("jdbc:presto://%s:%s/", server, port); } conn = DriverManager.getConnection(jdbcurl, props); /*设置连接的数据源类型 * 示例:mysql、hive */ conn.setCatalog("hive"); return conn; } public JSONArray getDataAll(String sql) throws Exception { JSONArray array = new JSONArray(); Statement ps = null; ResultSet rs = null; Connection con = null; try { con = getConnection(); ps = con.createStatement(); rs = ps.executeQuery(sql); // 获取列数 ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); // 遍历ResultSet中的每条数据 while (rs.next()) { JSONObject jsonObj = new JSONObject(); // 遍历每一列 for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnLabel(i); String value = StringUtils.isBlank(rs.getString(columnName)) ? "" : rs.getString(columnName); jsonObj.put(columnName, value); } array.add(jsonObj); } } catch (Exception e) { throw new Exception("ERROR:" + e.getMessage(), e); } finally { //关闭资源(先开后关) JdbcUtil.close(rs, ps, con); } return array; } }
Java程序访问presto
pom.xml中引入presto-jdbc
<dependency> <groupId>com.facebook.presto</groupId> <artifactId>presto-jdbc</artifactId> <version>0.267</version> </dependency>
/** * @ Author zhangsf * @CreateTime 2022/1/6 - 10:00 PM */ package presto; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class PrestoJdbcDemo { public static void main(String[] args) throws Exception{ //class.forname try { Class.forName("com.facebook.presto.jdbc.PrestoDriver"); }catch (ClassNotFoundException e){ e.printStackTrace(); } //若presto没有设置SSL认证,只需填写用户名,不需要填写密码。 Connection connection = DriverManager.getConnection("jdbc:presto://localhost:8080/mysql/tp_music","root",null); Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("select * from mysql.tp_music.singer limit 3"); while (rs.next()) { System.out.println("name:"+rs.getString(2)+" birth:"+rs.getString(5)+" location:"+rs.getString(6)); } rs.close(); connection.close(); } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容