So basically, I would like to avoid stored procedures, but at the same time I would’nt want multiple round-trips to database to execute sequential statements.
Apparently this blog says Facebook uses mysql’s multiple-statement-queries. Unfortunately, its a C API, is there a java equivalent of it?
So in brief, the question “is in java+mysql how can a second jdbc statement use the output of the first statement as input to execute, without a round-trip to database and without a storedproc” ?
If not how do other people approach this problem?
2
Answers
It sounds like you want to do batch processing.
here is a duplicate question with an good answer:
How to execute multiple SQL statements from java
Yes, the JDBC driver for MySQL support the multi-statement queries. It is however disabled by default for security reasons, as multi-statement queries significantly increase the risks associated with eventual SQL injections.
To turn on multi-statement queries support, simply add the
allowMultiQueries=true
option to your connection string (or pass the equivalent option in map format). You can get more information on that option here: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html.Once this option enabled, you can simply execute a call similar to:
statement.execute("select ... ; select ... ; select ...")
. ReturnedResultSet
s can be iterated from theStatement
object: each call tostatement.getResultSet()
return the next one. Usestatement.getMoreResults()
to determine if there are indeed moreResultSet
available.