Multiple queries executed in java in single statement

java, jdbc, mysql, sql

Solution

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`, and `DML` 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 another `select` 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

Problem

Hi I was wondering if it is possible to execute something like this using JDBC as it currently provides an exception even though it is possible in the MySQL query browser. ``` "SELECT FROM * TABLE;INSERT INTO TABLE;" ``` While I do realize that it is possible with having the SQL query string being split and the statement executed twice but I was wondering if there is a one time approach for this. ``` String url = "jdbc:mysql://localhost:3306/"; String dbName = "databaseinjection"; String driver = "com.mysql.jdbc.Driver"; String sqlUsername = "root"; String sqlPassword = "abc"; Class.forName(driver).newInstance(); connection = DriverManager.getConnection(url+dbName, sqlUsername, sqlPassword); ```

Original source