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

Question

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

Get it working with createStatement().executeQuery, still wondering how to get it working with preparedStatement — Shisoft
– Shisoft, Commented Mar 18, 2015 at 16:50

Jakub Kania · Accepted Answer · 2015-03-18 20:01:27Z

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'];

answered Mar 18, 2015 at 20:01

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shisoft Over a year ago

Thank you. When and where should I execute this create function statement? Before every query of only once? Is it persistent？

Jakub Kania Over a year ago

@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: stackoverflow.com/a/9981540/2115135

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related