在Java代码中执行查询连接到Presto时出错

Question

我们正在尝试使用Java代码连接Presto并执行一些查询。我们使用的是MySQL目录。

• Presto安装在Linux服务器上。Presto在Linux上工作得很好。在Linux中启动Presto。
• MySQL也安装在Linux机器上。我们可以使用MySQL访问windows中的DbVisualizer。
• 我为Presto创建了一个MySQL连接器目录。我成功地使用Presto作为MySQL来查询presto --server localhost:8080 --catalog mysql --schema tutorials的数据。

在Windows机器上执行Java代码，我可以访问MySQL并执行查询，但我们无法查询数据。当我们试图从Presto运行一个查询时，它会给我们提供执行查询的错误。在下面的示例中，我使用了来自特罗舍尔的jar

package testdbPresto;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class PrestoJdbc {

  public static void main(String args[]) throws SQLException, ClassNotFoundException {
      
      try{ 
      //connect mysql server tutorials database here 
      Class.forName("com.facebook.presto.jdbc.PrestoDriver");  
      String url = "jdbc:trino://35.173.241.37:8080/mysql/tutorials";
      Properties properties = new Properties();
      properties.setProperty("user", "root");
      properties.setProperty("password", "Redcar88!");
      properties.setProperty("SSL", "true");
      Connection connection = DriverManager.getConnection(url, properties);

      Statement statement = null;  
      statement = connection.createStatement();
      //select mysql table author table two columns  
      String sql;  
      sql = "select auth_id, auth_name from mysql.tutorials.author"; 
     
      ResultSet resultSet = statement.executeQuery(sql);  

      //Extract data from result set
      while (resultSet.next()) {
        //Retrieve by column name
        String name = resultSet.getString("auth_name");
        //Display values
        System.out.println("name : " + name);
      }
      //Clean-up environment
      resultSet.close();
      statement.close();
      connection.close();
      }catch(Exception e){ e.printStackTrace();}  
    
    }
  }

输出：

    java.sql.SQLException: Error executing query
        at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:274)
        at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:227)
        at io.trino.jdbc.TrinoStatement.executeQuery(TrinoStatement.java:76)
        at testdbPresto.PrestoJdbc.main(PrestoJdbc.java:29)
    Caused by: java.io.UncheckedIOException: javax.net.ssl.SSLException: Unsupported or unrecognized SSL message
        at io.trino.jdbc.$internal.client.JsonResponse.execute(JsonResponse.java:154)
        at io.trino.jdbc.$internal.client.StatementClientV1.<init>(StatementClientV1.java:110)
        at io.trino.jdbc.$internal.client.StatementClientFactory.newStatementClient(StatementClientFactory.java:24)
        at io.trino.jdbc.QueryExecutor.startQuery(QueryExecutor.java:46)
        at io.trino.jdbc.TrinoConnection.startQuery(TrinoConnection.java:728)
        at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:239)
        ... 3 more
    Caused by: javax.net.ssl.SSLException: Unsupported or unrecognized SSL message
        at sun.security.ssl.SSLSocketInputRecord.handleUnknownRecord(SSLSocketInputRecord.java:448)
        at sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:174)
        at sun.security.ssl.SSLTransport.decode(SSLTransport.java:110)
        at sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1279)
        at sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1188)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:401)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:373)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.connectTls(RealConnection.java:299)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.establishProtocol(RealConnection.java:268)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.RealConnection.connect(RealConnection.java:160)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.StreamAllocation.findConnection(StreamAllocation.java:256)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.StreamAllocation.findHealthyConnection(StreamAllocation.java:134)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.StreamAllocation.newStream(StreamAllocation.java:113)
        at io.trino.jdbc.$internal.okhttp3.internal.connection.ConnectInterceptor.intercept(ConnectInterceptor.java:42)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
        at io.trino.jdbc.$internal.okhttp3.internal.cache.CacheInterceptor.intercept(CacheInterceptor.java:93)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
        at io.trino.jdbc.$internal.okhttp3.internal.http.BridgeInterceptor.intercept(BridgeInterceptor.java:93)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RetryAndFollowUpInterceptor.intercept(RetryAndFollowUpInterceptor.java:125)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
        at io.trino.jdbc.$internal.client.OkHttpUtil.lambda$basicAuth$1(OkHttpUtil.java:85)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
        at io.trino.jdbc.$internal.client.OkHttpUtil.lambda$userAgent$0(OkHttpUtil.java:71)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:147)
        at io.trino.jdbc.$internal.okhttp3.internal.http.RealInterceptorChain.proceed(RealInterceptorChain.java:121)
        at io.trino.jdbc.$internal.okhttp3.RealCall.getResponseWithInterceptorChain(RealCall.java:200)
        at io.trino.jdbc.$internal.okhttp3.RealCall.execute(RealCall.java:77)
        at io.trino.jdbc.$internal.client.JsonResponse.execute(JsonResponse.java:131)
        ... 8 more

Answer 1

这是一个很老的问题，但可能仍然是相关的。您正在尝试使用presto驱动程序连接到trino。PrestoSQL被改名为Trino。因此，为了通过jdb访问trino，您应该使用trino jdbc驱动程序。

在类路径中添加trino依赖项。如果使用maven，则在pom中添加此依赖项。

<dependency>
   <groupId>io.trino</groupId>
   <artifactId>trino-jdbc</artifactId>
   <version>${trino-jdbc.version}</version>
</dependency>

然后使用以下驱动程序

Class.forName("io.trino.jdbc.TrinoDriver");

Answer 2

下面是一个与Trino一起工作的代码。

fun main() {
    val trinoUrl = "jdbc:trino://myDomain:443"
    val properties = Properties()
    properties.setProperty("user", "noUserS")
//    properties.setProperty("password", "noPass")
    properties.setProperty("SSL", "true")

    DriverManager.getConnection(trinoUrl, properties).use { trinoConn ->
        trinoConn.createStatement().use { statement ->
            statement.connection.catalog = "catalog1"
            statement.connection.schema = "default"

            println("Executing query...")

            statement.executeQuery("""
                    select 
                      restaurantId, 
                      type,
                      time 
                    from table1 
                    where time > CURRENT_TIMESTAMP - INTERVAL '1' hour
                """.trimIndent()
            ).use { resultSet ->
                val list = mutableListOf<Map<String, String>>()

                while (resultSet.next()) {
                    val data = mapOf(
                        "restaurantId" to resultSet.getString("restaurantId"),
                        "type" to resultSet.getString("type"),
                        "time" to resultSet.getString("time")
                    )
                    list.add(data)
                }

                println("Records returned: ${list.size}")
                println(list)
            }
        }
    }
    exitProcess(0)
}

是Kotlin，但很容易理解。.use {..}是Java的try-with-resources。

希望这能有所帮助。