Multiple queries executed in java in one expression

Question

Hi, I was wondering if something like this could be done using JDBC, since it currently provides an exception, even if it is possible in the MySQL query browser.

"SELECT FROM * TABLE;INSERT INTO TABLE;" 

Although I understand that this is possible when the SQL query string is split and the statement is executed twice, 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

I was wondering if it is possible to accomplish something similar using JDBC.

 "SELECT FROM * TABLE;INSERT INTO TABLE;" 

Yes it is possible. As far as I know, there are two ways. They

• By setting the database connection property to allow multiple queries, separated by the default semicolon.
• A call to a stored procedure that returns cursors implicitly.

The following examples show the two above possibilities.

Example 1 : (To allow multiple requests):

When sending a connection request, you need to add the connection property allowMultiQueries=true to the database URL. This is an additional connection property for those that already exist, for example, autoReConnect=true , etc. Valid values ​​for the allowMultiQueries property are true , false , yes and no . Any other value is rejected at runtime by using SQLException .

 String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true"; 

If such an instruction is not passed, an SQLException is thrown.

You should use execute( String sql ) or other options to get query results.

 boolean hasMoreResultSets = stmt.execute( multiQuerySqlString ); 

To repeat and process the results, you need the 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 complete:

• Create a procedure with one or more select and DML queries.
• Call it from java using CallableStatement .
• You can capture several ResultSet executed in a procedure.
  DML results cannot be captured, but may produce another select
  to find how rows are affected in the table.

Example 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 with 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 2

You can use batch update, but the requests must be valid (i.e. insert, update and delete) requests

 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 3

Hint. If you have several connection properties, separate them as follows:

 & 

To give you something like:

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

Hope this helps someone.

Hello,

Clay

Answer 4

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

Answer 5

Why aren't you trying to write a Stored Procedure for this?

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

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

Answer 6

I think this is the easiest way to multiple select / update / insert / delete. You can run as many updates / inserts / deletes as you want after selection (you need to make a choice first (if necessary, fictitious)) with executeUpdate (str) (just use the new int (count1, count2, ...)) and if you need new choice, close 'statement' and 'connection' and make a new one for the next choice. 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 this helps