Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I use multiple statements in a JDBC prepared query?

Tags:

java

sql

mysql

jdbc

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

like image 833
Joshua Martell Avatar asked Jul 29 '10 19:07

Joshua Martell


People also ask

Can I use same PreparedStatement multiple times?

Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.

How do I run multiple SQL statements in JDBC?

Demonstrating execution of multiple SQL commands on a database simultaneously using the addBatch() and executeBatch() commands of JDBC. The addBatch() command is used to queue the SQL statements and executeBatch() command is used to execute the queued SQL statements all at once.

How do you run multiple statements in Java?

Something like this eg: String query="select * from tab1;insert into tab1 values(...);update tab1..;delete from tab1...;" Statement st = con1. createStatement(); ResultSet rs = st. executeQuery(query);

How many statements are there in JDBC?

There are three types of statements in JDBC namely, Statement, Prepared Statement, Callable statement.


2 Answers

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 ?

like image 173
ddimitrov Avatar answered Oct 07 '22 01:10

ddimitrov


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

like image 29
Mchl Avatar answered Oct 07 '22 03:10

Mchl