Multiple queries executed in java in single statement
I was wondering if it is possible to execute something like this using JDBC.
"SELECT FROM * TABLE;INSERT INTO TABLE;"
Yes it is possible. There are two ways, as far as I know. They are
- By setting database connection property to allow multiple queries,separated by a semi-colon by default.
- By calling a stored procedure that returns cursors implicit.
Following examples demonstrate the above two possibilities.
Example 1: ( To allow multiple queries ):
While sending a connection request, you need to append a connection property allowMultiQueries=true
to the database url. This is additional connection property to those if already exists some, like autoReConnect=true
, etc.. Acceptable values for allowMultiQueries
property are true
, false
, yes
, and no
. Any other value is rejected at runtime with an SQLException
.
String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";
Unless such instruction is passed, an SQLException
is thrown.
You have to use execute( String sql )
or its other variants to fetch results of the query execution.
boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );
To iterate through and process results you require following steps:
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
Example 2: Steps to follow:
- Create a procedure with one or more
select
, andDML
queries. - Call it from java using
CallableStatement
. - You can capture multiple
ResultSet
s executed in procedure.
DML results can't be captured but can issue anotherselect
to find how the rows are affected in the table.
Sample table and procedure:
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)
Call Procedure from Java:
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
You can use Batch update but queries must be action(i.e. insert,update and delete) queries
Statement s = c.createStatement();String s1 = "update emp set name='abc' where salary=984";String s2 = "insert into emp values ('Osama',1420)"; s.addBatch(s1);s.addBatch(s2); s.executeBatch();