Multiple queries executed in java in single statement

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

put into a stored procedure, call the stored procedure. means you also dont have to redeploy your code when you want to make a change. — Chris
– Chris, Commented May 29, 2012 at 11:02
There is a property you have to set in connection string allowMultiQueries=true. — Rahul
– Rahul, Commented May 29, 2012 at 11:03
probable duplicate : How to execute composite sql queries in java?[1] [1]: stackoverflow.com/questions/6773393/… — prayagupadhyay
– prayagupadhyay, Commented May 29, 2012 at 11:06
Hi Rahul, for this project I am using a plain old connection object and do you know where I should set "allowMultiQueries=true". Have added the connection object code in the question — MilindaD
– MilindaD, Commented May 29, 2012 at 11:48
When I used "allowMultiQueries=true", it gave me DisconnectNonTransientException. Why? — Jagruuti
– Jagruuti, Commented Mar 16, 2023 at 9:25

Majid Hajibaba · Accepted Answer · 2023-02-28 12:19:57Z

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

Unfortunately, this does not work with the embedded version of Derby.
@user2428118: Reason? Any errors observed? Did you check if Driver supports this feature?

zessx · Accepted Answer · 2014-07-02 10:10:41Z

40

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

edited Jul 2, 2014 at 10:10

zessx

68.9k29 gold badges139 silver badges166 bronze badges

answered May 29, 2012 at 11:46

Himanshu Mohta

1,0439 silver badges15 bronze badges

1 Comment

sebnukem Over a year ago

You can't use this approach for "call sprocname('abc',984)" queries?

Glyn · Accepted Answer · 2015-01-25 23:30:45Z

22

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

&amp;

To give you somthing like:

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

I hope this helps some one.

Regards,

Glyn

answered Jan 25, 2015 at 23:30

Glyn

2,0157 gold badges43 silver badges70 bronze badges

Comments

muye · Accepted Answer · 2012-11-08 07:18:15Z

12

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

answered Nov 8, 2012 at 7:18

muye

1711 silver badge8 bronze badges

Comments

JHS · Accepted Answer · 2012-05-29 11:04:49Z

2

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.

answered May 29, 2012 at 11:04

JHS

7,8812 gold badges34 silver badges54 bronze badges

1 Comment

pedram bashiri Over a year ago

You don't always have the permission to write a Stored Procedure. E.g. it's a client's database that you're only allowed to select from.

Alexandros Topalidis · Accepted Answer · 2017-06-28 10:28:55Z

1

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

answered Jun 28, 2017 at 10:28

Alexandros Topalidis

452 bronze badges

2 Comments

Arun Kumar Mudraboyina Over a year ago

Opening a DB connection is very costlier. Not a good practice doing that everytime. The which you have given should makes n number DB hits for n number of queries which leads to poor performance.

Basil Bourque Over a year ago

Seems incorrect. Statement#executeUpdate( String sql ) cannot be called on a PreparedStatement, as explicitly stated in the Javadoc. So, your line int count8 = theStatement.executeUpdate(str9); seems impossible.

Collectives™ on Stack Overflow

Multiple queries executed in java in single statement

6 Answers 6

3 Comments

1 Comment

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

1 Comment

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related