Multiple queries executed in java in single statem

Question

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);

Answer 1

Why dont you try and write a Stored Procedure for this?

You can get the Result Set out and in the same Stored Procedure you can Insert what you want.

The only thing is you might not get the newly inserted rows in the Result Set if you Insert after the Select.

Answer 2

I think this is the easiest way for multy selection/update/insert/delete. You can run as many update/insert/delete as u want after select (you have to make a select first(a dummy if needed)) with executeUpdate(str) (just use new int(count1,count2,...)) and if u need a new selection close 'statement' and 'connection' and make new for next select. Like example:

String str1 = "select * from users";
String str9 = "INSERT INTO `port`(device_id, potition, port_type, di_p_pt) VALUE ('"+value1+"', '"+value2+"', '"+value3+"', '"+value4+"')";
String str2 = "Select port_id from port where device_id = '"+value1+"' and potition = '"+value2+"' and port_type = '"+value3+"' ";
try{  
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    theConnection=(Connection) DriverManager.getConnection(dbURL,dbuser,dbpassword);  
    theStatement = theConnection.prepareStatement(str1);
    ResultSet theResult = theStatement.executeQuery();
    int count8 = theStatement.executeUpdate(str9);
    theStatement.close();
    theConnection.close();
    theConnection=DriverManager.getConnection(dbURL,dbuser,dbpassword);
    theStatement = theConnection.prepareStatement(str2);
    theResult = theStatement.executeQuery();

    ArrayList<Port> portList = new ArrayList<Port>();
    while (theResult.next()) {
        Port port = new Port();
        port.setPort_id(theResult.getInt("port_id"));

        portList.add(port);
    }

I hope it helps

Answer 3

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

1. By setting database connection property to allow multiple queries, separated by a semi-colon by default.
2. 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:

1. Create a procedure with one or more select, and DML queries.
2. Call it from java using CallableStatement.
3. You can capture multiple ResultSets 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

Answer 4

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();

Answer 5

Hint: If you have more than one connection property then separate them with:

&

To give you somthing like:

url="jdbc:mysql://localhost/glyndwr?autoReconnect=true&allowMultiQueries=true"

I hope this helps some one.

Regards,

Glyn

Answer 6

Based on my testing, the correct flag is "allowMultiQueries=true"