0

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?

Suraj Chandran
  • 24,433
  • 12
  • 63
  • 94
  • I've actually never heard of multiple statement queries. However, if you want to execute multiple MySQL statements from Java, you can always use a transaction. What do you have in mind here? – Tim Biegeleisen Feb 21 '17 at 14:53
  • @TimBiegeleisen I could have used jdbc batching, but the problem is different. Consider the simple problem mentioned in that Facebook blog. How does the second statement use the output of the first statement as input to execute, without a round-trip to database and without a storedproc? – Suraj Chandran Feb 21 '17 at 14:57
  • 1
    I believe it has been answered here: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Quintium Feb 21 '17 at 15:05
  • No, Please see my comment in question for Tim, The problem with batching is how can the second statement in sequence use the output of first statement as input? – Suraj Chandran Feb 21 '17 at 15:08
  • 1
    Just use stored procedures. They are available in mysql. – DwB Feb 21 '17 at 15:16
  • @DwB I specifically mentioned without storedprocs. Please read the linked Facebook blog in question, about why stored proc can be an operational mess – Suraj Chandran Feb 21 '17 at 17:22

2 Answers2

2

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 ..."). Returned ResultSets can be iterated from the Statement object: each call to statement.getResultSet() return the next one. Use statement.getMoreResults() to determine if there are indeed more ResultSet available.

James
  • 4,211
  • 1
  • 18
  • 34
  • See example code here: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – James Feb 21 '17 at 18:30
0

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

Community
  • 1
  • 1
ChrisThompson
  • 1,998
  • 12
  • 17
  • No, Please see my comment in question for Tim, The problem with batching is how can the second statement in sequence use the output of first statement as input? – Suraj Chandran Feb 21 '17 at 15:04
  • I'm sorry i missed that part. Would it be possible to accomplish what you want using nested select statements? – ChrisThompson Feb 21 '17 at 15:08
  • In quite a few cases yes(by using joins), but not always. Mysql explicitly provides a C api for this(link in question), I wanted to if there's something similar in java. – Suraj Chandran Feb 21 '17 at 15:11