时间戳

首页 > 解决方案 > 无法在 Java 中准备 Postgres 语句 (java.sql.PreparedStatement)

java - 无法在 Java 中准备 Postgres 语句 (java.sql.PreparedStatement)

问题描述

考虑两个我可能想从 Java 发送到我的服务器的语句。

  1. 简单 SQL：这是一个典型的插入语句。
insert into table_things (thing_1_value, thing_2_value) values(?, ?);
  1. PlPgSQL：我想通过在数据库中进行一些登录来避免往返数据库。我们不允许在数据库中使用存储过程或函数（原因似乎是有道理的）。
do $$
declare
    my_thing1 varchar(100) = ?;
    my_thing2 varchar(100) = ?;
begin
    insert into table_things
    (
          thing_1_value
        , thing_2_value
    )
    values
    (
          my_thing1
        , my_thing2
    )
    ;
end
$$;

执行这些语句的代码在 Java8 测试用例中如下所示：

package com.somecompany.someservice.test.database;

import org.apache.commons.dbcp2.BasicDataSource;
import org.junit.Assert;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;

public class PreparedStatementDatabaseTest {
    private static final String CONNECTION_URI = "jdbc:postgresql://localhost:5432/somedb?user=someuser&password=somepass";

    private static final String PLPGSQL_STATEMENT = "" +
            "do $$\n" +
            "declare\n" +
            "    my_thing1 varchar(100) = ?;\n" +
            "    my_thing2 varchar(100) = ?;\n" +
            "begin\n" +
            "    insert into table_things\n" +
            "    (\n" +
            "          thing_1_value\n" +
            "        , thing_2_value\n" +
            "    )\n" +
            "    values\n" +
            "    (\n" +
            "          my_thing1\n" +
            "        , my_thing2\n" +
            "    )\n" +
            "    ;\n" +
            "end\n" +
            "$$;";

    private static final String EASY_SQL_STATEMENT = "insert into table_things (thing_1_value, thing_2_value) values(?, ?);";

    @Test
    public void testPlpgsqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }

    @Test
    public void testEasySqlStatement() throws Exception {
        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(EASY_SQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);
    }
}

testEasySqlStatement有效，但testPlpgsqlStatement抛出异常：

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128)
    at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:996)
    at org.postgresql.jdbc.PgPreparedStatement.setString(PgPreparedStatement.java:326)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:528)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:881)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:185)
    at com.somecompany.someservicetest.database.PreparedStatementDatabaseTest.testPlpgsqlStatement(PreparedStatementDatabaseTest.java:44)
    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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

问题：如何将代码发送PLPGSQL_STATEMENT到 Postgres 数据库？

我可以这样做，但由于 SQL 注入风险，这是不好的做法：

    @Test
    public void testSqlInjectionRisk() throws Exception {
        String hello = "hello-testSqlInjectionRisk";
        String world = "world-testSqlInjectionRisk";

        String PLPGSQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    my_thing1 varchar(100) = '" + hello + "';\n" +
                "    my_thing2 varchar(100) = '" + world + "';\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          thing_1_value\n" +
                "        , thing_2_value\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        Connection conn = basicDataSource.getConnection();
        PreparedStatement statement = conn.prepareStatement(PLPGSQL_STATEMENT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);

问题重述：我准备的方式有问题PLPGSQL_STATEMENT吗？可以PLPGSQL_STATEMENT准备吗？

更新：@Izruo 指出我应该使用prepareCall，这似乎是答案的一部分。但不幸的是，以下代码因相同的异常而失败：

    @Test
    public void testEasySqlStatement2() throws Exception {
        final String SQL_STATEMENT = "" +
                "do $$\n" +
                "declare\n" +
                "    x varchar(100) = ?;\n" +
                "    y varchar(100) = ?;\n" +
                "begin\n" +
                "    insert into table_things\n" +
                "    (\n" +
                "          my_thing1\n" +
                "        , my_thing2\n" +
                "    )\n" +
                "    values\n" +
                "    (\n" +
                "          x\n" +
                "        , y\n" +
                "    )\n" +
                "    ;\n" +
                "end\n" +
                "$$;";

        Class.forName("org.postgresql.Driver");
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(CONNECTION_URI);
        System.out.println(SQL_STATEMENT);
        Connection conn = basicDataSource.getConnection();
        CallableStatement statement = conn.prepareCall(SQL_STATEMENT);
        statement.setObject(1, "hello", Types.VARCHAR);
        statement.setObject(2, "world", Types.VARCHAR);
        boolean isResultSet = statement.execute();
        conn.close();
        Assert.assertFalse(isResultSet);

如果我将打印的 sql 语句复制System.out.println(SQL_STATEMENT);到 DataGrip（JetBrains 的数据库 IDE）中并运行它，然后 DataGrip 要求我填写两个参数值（用于两个问号）并成功运行 sql 语句。换句话说，plpgsql 代码在语法上是有效的（一旦参数被替换）。

这里似乎有三种可能性，我无法判断哪个是真的：

  1. 不支持此功能（创建包含 plpgsql 变量的 CallableStatement/PreparedStatement）。
  2. 支持此功能，但我做错了。
  3. 支持该功能，我正确使用它，但是有一个错误。

标签： javapostgresqlprepared-statementplpgsql

解决方案

您不能直接调用动态过程。

您必须首先创建过程（手动或通过语句调用动态），然后按名称调用过程。

Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION myfunc(x text, y text) RETURNS refcursor AS '"
        + "do $$\n"
        + "begin\n"
        + "    insert into table_things\n"
        + "    (\n"
        + "          my_thing1\n"
        + "        , my_thing2\n"
        + "    )\n"
        + "    values\n"
        + "    (\n"
        + "          x\n"
        + "        , y\n"
        + "    )\n"
        + "    ;\n"
        + "end\n"
        + "$$;"
        + "' language plpgsql");
stmt.close();

// We must be inside a transaction for cursors to work.
conn.setAutoCommit(false);

// Procedure call.
CallableStatement proc = conn.prepareCall("{ call myfunc(?,?)}");
CallableStatement statement = conn.prepareCall(SQL_STATEMENT);
statement.setObject(1, "hello", Types.VARCHAR);
statement.setObject(2, "world", Types.VARCHAR);

推荐阅读