1

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

Community
  • 1
  • 1
Shisoft
  • 4,197
  • 7
  • 44
  • 61
  • Get it working with `createStatement().executeQuery`, still wondering how to get it working with preparedStatement – Shisoft Mar 18 '15 at 16:50

1 Answers1

2

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'];
Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • Thank you. When and where should I execute this create function statement? Before every query of only once? Is it persistent？ – Shisoft Mar 19 '15 at 07:02
  • @Shisoft Only once (although it won't fail if run it more than once). If you don't want to create a permanent function you can also take a look at temporary solutions: http://stackoverflow.com/a/9981540/2115135 – Jakub Kania Mar 19 '15 at 08:26