CODEWITHSUNDEEP
jdbcprepared-statement
Thomas Kessler
Thomas Kessler

Reputation: 1807

How to set JDBC prepared statement parameters when querying Amazon Redshift

I'm using JDBC to execute queries on Amazon Redshift. I have a java.sql.PreparedStatement object. When I set the parameters on the PreparedStatement object and then call:

preparedStatement.executeQuery();

with this query:

SELECT start_date from sometable_:year order by start_date desc limit 1

in the end, the query should look like this:

SELECT start_date from sometable_2010 order by start_date desc limit 1

I get this exception:

org.postgresql.util.PSQLException: ERROR: relation "sometable_$1" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:477)
at com.sun.proxy.$Proxy159.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at com.example.persistence.dao.impl.Redshift01JdbcDaoImpl.getSomeTableLastArchiveDate(Redshift01JdbcDaoImpl.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at com.sun.proxy.$Proxy314.getSomeTableLastArchiveDate(Unknown Source)
at com.example.service.impl.someTableArchiveServiceImpl.process(SomeTableArchiveServiceImpl.java:69)
at com.example.service.impl.AbstractBatchServiceImpl.run(AbstractBatchServiceImpl.java:106)
at com.example.service.jms.JmsMessageListener.callBatchService(JmsMessageListener.java:72)
at com.example.service.jms.JmsMessageListener.onMessage(JmsMessageListener.java:136)
at org.springframework.jms.listener.adapter.MessageListenerAdapter.onMessage(MessageListenerAdapter.java:339)
at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:535)
at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:495)
at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467)
at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325)
at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263)
at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058)
at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050)
at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947)
at java.lang.Thread.run(Thread.java:722)

I would rather not do string concatenation, what am I doing wrong? The PreparedStatement setParameters method works for all other JDBC queries in my code base.

Thank you,

Tom

Upvotes: 1

Views: 4065

Answers (1)

JB Nizet
JB Nizet

Reputation: 691765

You can't use a prepared statement to replace arbitrary portions of the query dynamically. Only values, that don't affect the query plan, can be bound as parameters. You'll have to use String concatenation to do this.

Explanation: a PreparedStatement, as its name indicates, is a statement that is prepared by the database and can then be executed multiple times after with various parameters. Preparing the statement consists in parsing the query to examine which tables and columns are used, and build a plan to execute the query (which index to use, etc.).

The same plan is then used each time the statement is executed with various parameters. If the table from which the query must select data isn't even known when the statement is prepared, no plan can be computed. This is why the following query is fine:

select f.* from foo f where f.name = ?

but these ones aren't fine:

select f.* from ? f where f.name = 'bar' -- unknown table: the plan can't be computed
select f.? from foo f where f.name = 'bar -- unknown column: the plan can't be computed
select f.* from foo f where f.name ? 'bar' -- unknown operator: the plan can't be computed

Upvotes: 1

Related Questions