Java Crosstab - preparedstatement query

java postgresql prepared-statement crosstab
blue01 picture blue01 · Oct 15, 2011 · Viewed 7.4k times · Source

I have a typical crosstab query with static parameters. It works fine with createStatement. I want to use preparestatement to query instead.

         String query = "SELECT * FROM crosstab(
                          'SELECT rowid, a_name, value 
                           FROM test WHERE a_name = ''att2'' 
                                        OR a_name = ''att3''
                           ORDER BY 1,2'
         ) AS ct(row_name text, category_1 text, category_2 text, category_3 text);";
         PreparedStatement stat = conn.prepareStatement(query);
         ResultSet rs = stat.getResultSet();

     stat.executeQuery(query);
     rs = stat.getResultSet();

     while (rs.next()) {
             //TODO
         }

But it does not seem to work.

I get a PSQLException - Can't use query methods that take a query string on a PreparedStatement.

Any ideas what I am missing?

Answer

Philipp Reichart picture Philipp Reichart · Oct 15, 2011

You have fallen for the confusing type hierarchy of PreparedStatement extends Statement:

PreparedStatement has the same execute*(String) methods like Statement, but they're not supposed to be used, just use the parameterless execute*() methods of PreparedStatement --- you already have given the actual query string to execute using conn.prepareStatement().

Related questions

How to set list of parameters on prepared statement?

i have a list of names e.g.: List<String> names = ... names.add('charles'); ... and a statement: PreparedStatement stmt = conn.prepareStatement('select * from person where name in ( ? )'); how to do the following: stmt.setParameterList(1,names); Is there …

Read More
How can I Insert JSON object into Postgres using Java preparedStatement?

I’m struggling to insert a JSON object into my postgres v9.4 DB. I have defined the column called "evtjson" as type json (not jsonb). I am trying to use a prepared statement in Java (jdk1.8) to insert a Json …

Read More
Inserting into custom SQL types with prepared statements in java

I have some custom types. They are all basically enums. Here is an example of what they look like: CREATE TYPE card_suit AS ENUM ('spades', 'clubs', 'hearts', 'diamonds'); And I have some prepared statements in Java, which look something …

Read More