AWS Athena JDBC PreparedStatement [英] AWS Athena JDBC PreparedStatement

问题描述

我无法使AWS Athena JDBC驱动程序与PreparedStatement和绑定变量一起使用.如果我将所需的列值直接放在SQL字符串中，它将起作用.但是，如果我使用占位符'?'并且我将变量与PreparedStatement的setter绑定在一起，它不起作用.当然，我们知道我们必须使用第二种方式(用于缓存，避免SQL注入等).

I don't manage to make AWS Athena JDBC driver working with PreparedStatement and binded variables. If I put the desired value of a column directly in the SQL string, it works. But if I use placeholders '?' and I bind variables with setters of PreparedStatement, it does not work. Of course, we know we have to use the second way of doing (for caching, avoid SQL injection and so on).

我使用JDBC驱动程序AthenaJDBC42_2.0.2.jar.尝试使用占位符'?'时出现以下错误在SQL字符串中.当我从JDBC连接获取PreparedStatement时，抛出该错误.它抱怨找不到参数.但是我在代码中设置了它们.如何在获取PreparedStatement之前设置参数:-)?

I use JDBC Driver AthenaJDBC42_2.0.2.jar. I get the following error when trying to use placeholders '?' in the SQL String. The error is thrown when I get the PreparedStatement from the JDBC Connection. It complains about parameters not being found. But I set them after in the code. How can I set the parameters before getting the PreparedStatement :-) ?

java.sql.SQLException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 1 but found 0

at com.simba.athena.athena.api.AJClient.executeQuery(Unknown Source)
at com.simba.athena.athena.dataengine.AJQueryExecutor.<init>(Unknown Source)
at com.simba.athena.athena.dataengine.AJDataEngine.prepare(Unknown Source)
at com.simba.athena.jdbc.common.SPreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc42.S42PreparedStatement.<init>(Unknown Source)
at com.simba.athena.jdbc.jdbc42.JDBC42ObjectFactory.createPreparedStatement(Unknown Source)
at com.simba.athena.athena.jdbc42.AJJDBC42ObjectFactory.createPreparedStatement(Unknown Source)
at com.simba.athena.jdbc.common.SConnection.prepareStatement(Unknown Source)
at com.simba.athena.jdbc.common.SConnection.prepareStatement(Unknown Source)
at ****************************************************
Caused by: com.simba.athena.support.exceptions.GeneralException: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 1 but found 0
... 37 more

我做错什么了吗?这是代码

Am I doing something wrong ? Here is the code

    @Test
public void testWhichFails() throws SQLException {
    try (Connection connection = athenaConnexion()) {
        String sql = "select * from my_table where col = ? limit 10";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, 30);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("rs.getString(1) = " + rs.getString(1));
                }
            }
        }
    }
}

@Test
public void testWhichWorks() throws SQLException {
    try (Connection connection = athenaConnexion()) {
        String sql = "select * from my_table where col = 30 limit 10";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            //ps.setInt(1, 30);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    System.out.println("rs.getString(1) = " + rs.getString(1));
                }
            }
        }
    }
}

推荐答案

Athena仅支持此处列出的SQL函数函数和运算符Presto版本0.172 ，其中包含 Presto文档中使用预准备的语句.但是，Athena尚不支持此新版本.您随时可以写信给Athena支持团队，要求添加PREPARE功能.

Athena supports only SQL functions listed here Athena SQL functions which in turn are based on Functions and Operators Presto version 0.172 with the following list of Athena's SQL related limitations. Prepared statements can be used in the new version of Presto Presto Documentation. However, Athena does not support this new version yet. You can always write to the Athena support team to ask for the PREPARE function to be added.

这篇关于AWS Athena JDBC PreparedStatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！