Can I use multiple statements in a JDBC prepared query?

Question

I'd like to execute something like this on my MySQL server:

SET @id=(SELECT id FROM lookupTable WHERE field=?);
(SELECT * FROM table2 WHERE id=@id)
UNION
(SELECT * FROM table3 WHERE id=@id)
UNION
(SELECT * FROM table4 WHERE id=@id);

This works fine from the console, but not from my Java PreparedStatement. It throws an exception with a syntax error at the ';' separating the statements. I like the variable because I don't have to repeat the lookup clause, but I could rewrite it if necessary. The equivalent JOIN is a little awkward with the UNION clauses too.

Thanks,

Joshua

As @ddimitrov says, this doesn't seem possible directly. I didn't try `PreparedStatement.addBatch()`. A stored procedure would likely work for this too. — Joshua Martell, Aug 28 '10 at 03:52
If you're interested in other vendors, then CUBRID Database supports this feature. See this forum post http://www.cubrid.org/forum/534638. — esengineer, Dec 31 '12 at 02:17

score 5 · Accepted Answer · answered Aug 04 '10 at 14:58

5

JDBC has never supported parsing delimited queries. Each invocation is one trip to the database. Perhaps you can achieve what you meant to doing PreparedStatement.addBatch() for each separate query, then executing and retrieving the two resultsets ?

answered Aug 04 '10 at 14:58

ddimitrov

3,293
3
31
46

score 2 · Answer 2 · answered Jul 29 '10 at 19:26

2

Just running this as two separate queries (within one connection) should give you same results.

answered Jul 29 '10 at 19:26

Mchl

61,444
9
118
120

I was hoping to avoid the network time twice, but it seems unavoidable here. Maybe this is a security feature to avoid SQL injection. – Joshua Martell Jul 29 '10 at 21:58
1

@Joshua, you can batch queries to the server and avoid the multiple network trips. – ddimitrov Aug 08 '10 at 15:22

Can I use multiple statements in a JDBC prepared query?

2 Answers2

Linked