Java怎么集成presto查询
Java集成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();
}
}
