Search code examples
postgresqljdbchstore

Any way to disable parameters or execute SQL statements directly in JDBC?

I am trying to ues JDBC PreparedStatement to execute a SQL statement which has a question mark ? as operator. (It's the hstore ?| operator)

It throws an exception tells me that I have not specified the parameter org.postgresql.util.PSQLException: No value specified for parameter 1.. Which it should be the operator. Is there any way to by pass the parameter check and execute the statement directly?

I have seen database feature in IntelliJ can directly execute SQL with JDBC driver, I think there should be a way

There is an question (Escaping hstore contains operators in a JDBC Prepared statement) about this issue, but I really need this operator to make index working on large data sets.

Thank you

Solution

  • You can use SQL function inlining. A simple SQL function will get rewritten (almost always).

    CREATE OR REPLACE FUNCTION hstore_contains(hstore, text[]) RETURNS boolean AS $$
    SELECT $1 ?| $2;
$$ LANGUAGE SQL;

    So the two queries below will get identical query plan and will both take advantage of indexes:

    SELECT * FROM tbl WHERE hstore_contains(col1,array['a','b']);

SELECT * FROM tbl WHERE col1 ?| array['a','b'];