java - preparedStatement.getString() 方法不适用于嵌套 sql 查询

Question

我尝试使用下面的方法查询我的 HBase，SQL 查询有一个嵌套查询，问题是当使用 setString() 方法添加内联参数时，它没有替换 ?在查询中。

我遇到异常

Parameter value unbound. Parameter at index 1 is unbound

我正在使用 phoenix queryserver 和 avatica JDBC 驱动程序连接到我的 HBase

下面是我的函数。

public ResultSet getTopRiskEntitiesCount(String tenantId, String entityType){
    ResultSet resultSet = null;
            try{String query = "SELECT TID,ENTITYTYPE, ENTITYID AS USER,SCORE*100 AS RISK,TIMESTAMP FROM ANALYTICS_SCORES WHERE TID = ? AND ENTITYTYPE = ? AND TIMESTAMP >= (SELECT MAX(TIMESTAMP) FROM ANALYTICS_SCORES) ORDER BY RISK DESC LIMIT 100";
                PreparedStatement preparedStatement = getConnection().prepareStatement(query);
                preparedStatement.setString(1, tenantId);
                preparedStatement.setString(2, entityType);
                resultSet = preparedStatement.executeQuery();
            } catch (Exception e) {
                e.printStackTrace();
            }
            closeConnection();
            return resultSet;
        }

以下是异常(exception)情况。

org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT TID,ENTITYTYPE, ENTITYID AS USER,SCORE*100 AS RISK,TIMESTAMP FROM ANALYTICS_SCORES WHERE TID = ? AND ENTITYTYPE = ? AND TIMESTAMP >= (SELECT MAX(TIMESTAMP) FROM ANALYTICS_SCORES) ORDER BY RISK DESC LIMIT 100
    at org.apache.calcite.avatica.Helper.createException(Helper.java:53)
    at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
    at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:327)
    at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:580)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:716)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:988)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
    at org.testng.TestRunner.privateRun(TestRunner.java:648)
    at org.testng.TestRunner.run(TestRunner.java:505)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
    at org.testng.SuiteRunner.run(SuiteRunner.java:364)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1208)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1137)
    at org.testng.TestNG.runSuites(TestNG.java:1049)
    at org.testng.TestNG.run(TestNG.java:1017)
    at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:114)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
java.lang.RuntimeException: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound
    at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:683)
    at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:709)
    at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:209)
    at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1199)
    at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1170)
    at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
    at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
    at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:127)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.Server.handle(Server.java:499)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
    at org.apache.phoenix.shaded.org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
    at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
    at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
    at java.lang.Thread.run(Thread.java:748)

Answer 1

尝试在您的代码中使用此 block 。

for (int i = 1; i <= pmetadata.getParameterCount(); i++) {
System.out.println("Type: " + pmetadata.getParameterType(i));
}