StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em>I was wondering if it is possible to execute something like this using JDBC.</em></p> <pre><code>"SELECT FROM * TABLE;INSERT INTO TABLE;" </code></pre> <p>Yes it is possible. There are two ways, as far as I know. They are</p> <ol> <li>By setting database connection property to allow multiple queries, separated by a semi-colon by default.</li> <li>By calling a stored procedure that returns cursors implicit.</li> </ol> <p>Following examples demonstrate the above two possibilities. </p> <p><strong>Example 1</strong>: ( To allow multiple queries ): </p> <p>While sending a connection request, you need to append a connection property <code>allowMultiQueries=true</code> to the database url. This is additional connection property to those if already exists some, like <code>autoReConnect=true</code>, etc.. Acceptable values for <code>allowMultiQueries</code> property are <code>true</code>, <code>false</code>, <code>yes</code>, and <code>no</code>. Any other value is rejected at runtime with an <code>SQLException</code>. </p> <pre><code>String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true"; </code></pre> <p>Unless such instruction is passed, an <code>SQLException</code> is thrown.</p> <p>You have to use <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#execute%28java.lang.String%29" rel="noreferrer"><code>execute( String sql )</code></a> or its other variants to fetch results of the query execution. </p> <pre><code>boolean hasMoreResultSets = stmt.execute( multiQuerySqlString ); </code></pre> <p>To iterate through and process results you require following steps: </p> <pre><code>READING_QUERY_RESULTS: // label while ( hasMoreResultSets || stmt.getUpdateCount() != -1 ) { if ( hasMoreResultSets ) { Resultset rs = stmt.getResultSet(); // handle your rs here } // if has rs else { // if ddl/dml/... int queryResult = stmt.getUpdateCount(); if ( queryResult == -1 ) { // no more queries processed break READING_QUERY_RESULTS; } // no more queries processed // handle success, failure, generated keys, etc here } // if ddl/dml/... // check to continue in the loop hasMoreResultSets = stmt.getMoreResults(); } // while results </code></pre> <p><strong>Example 2</strong>: Steps to follow: </p> <ol> <li>Create a procedure with one or more <code>select</code>, and <code>DML</code> queries.</li> <li>Call it from java using <code>CallableStatement</code>.</li> <li>You can capture multiple <code>ResultSet</code>s executed in procedure.<br> DML results can't be captured but can issue another <code>select</code><br> to find how the rows are affected in the table. </li> </ol> <p><em>Sample table and procedure</em>: </p> <pre class="lang-none prettyprint-override"><code>mysql> create table tbl_mq( i int not null auto_increment, name varchar(10), primary key (i) ); Query OK, 0 rows affected (0.16 sec) mysql> delimiter // mysql> create procedure multi_query() -> begin -> select count(*) as name_count from tbl_mq; -> insert into tbl_mq( names ) values ( 'ravi' ); -> select last_insert_id(); -> select * from tbl_mq; -> end; -> // Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> call multi_query(); +------------+ | name_count | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) +---+------+ | i | name | +---+------+ | 1 | ravi | +---+------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) </code></pre> <p><em>Call Procedure from Java</em>: </p> <pre><code>CallableStatement cstmt = con.prepareCall( "call multi_query()" ); boolean hasMoreResultSets = cstmt.execute(); READING_QUERY_RESULTS: while ( hasMoreResultSets ) { Resultset rs = stmt.getResultSet(); // handle your rs here } // while has more rs </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload