7

I am passing a parameter to a PreparedStatement like this :

public void getNodes(String runId, File file, Connection conn) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = conn.prepareStatement(Mat.queries.get("NettingNode.QUERY"));

        ps.setString(1, runId);
        ps.setFetchSize(10000);
        rs = ps.executeQuery();

        // etc.
    } catch (Exception e) {
        logger.error(e, e);
    } finally {
        close(rs, ps);
    }
}

And the query looks like this :

select * from table_1 where run_id = ?

Now I want to modify my query like this, and reuse the first parameter (both ? would use the runId parameter) :

select * from table_1 where run_id = ?
union
select * from table_2 where run_id = ?

Is that possible without doing this :

ps.setString(1, runId);
ps.setString(2, runId);
l0r3nz4cc10
  • 1,237
  • 6
  • 27
  • 50
  • Off-topic: In case of C/C++ it is possible. Functions OCIDefineByPos, OCIDefineByName behave as expected. I think that in case of JDBC this feature was sacrificed in order to be compatible with databases, which do not support it. So maybe Oracle JDBC drivers have some proprietary extension for it. – ibre5041 May 28 '15 at 09:10

2 Answers2

10

This cannot be done with plain JDBC. You could instead use Spring's JDBCTemplate, which would support what you want with Named Parameters, and re-use the same name wherever it's needed in the statement.

See Named parameters in JDBC

Community
  • 1
  • 1
NickJ
  • 9,380
  • 9
  • 51
  • 74
0

The following works fine with oracle. However it may not be a good choice if the tables are huge.

Select * from ( SELECT ? run_id FROM DUAL ) A
JOIN (  select * from table_1
        union
        select * from table_2 
     ) B ON A.run_id = B.run_id
Sreekumar Sekharan
  • 1
  • 1