用java在一条语句中执行多个查询

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.

你好，我想知道是否可以使用JDBC执行这样的操作，因为它目前提供了一个异常，即使在MySQL查询浏览器中也是可能的。

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

虽然我确实意识到可以拆分SQL查询字符串并执行语句两次，但是我想知道是否有一种一次性方法。

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

6 个解决方案

#1

I was wondering if it is possible to execute something like this using JDBC.

我想知道是否有可能使用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. 通过设置数据库连接属性来允许多个查询，默认情况下用分号分隔。
3. By calling a stored procedure that returns cursors implicit.
4. 通过调用一个隐式返回游标的存储过程。

Following examples demonstrate the above two possibilities.

下面的示例演示了上述两种可能性。

Example 1: ( To allow multiple queries ):

示例1:(允许多个查询):

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.

在发送连接请求时，需要将连接属性allowMultiQueries=true附加到数据库url。这是附加的连接属性，如果已经存在的话，比如autoReConnect=true等。允许多查询属性的可接受值为真、假、是、否。任何其他值在运行时都被SQLException异常所拒绝。

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

Unless such instruction is passed, an SQLException is thrown.

除非通过此类指令，否则将抛出SQLException。

You have to use execute( String sql ) or its other variants to fetch results of the query execution.

您必须使用execute(String sql)或其他变体来获取查询执行的结果。

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:

例2:步骤如下:

1. Create a procedure with one or more select, and DML queries.
2. 使用一个或多个select和DML查询创建一个过程。
3. Call it from java using CallableStatement.
4. 使用CallableStatement从java调用它。
5. 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.
6. 您可以捕获在过程中执行的多个结果集。不能捕获DML结果，但可以发出另一个select来查找表中的行如何受到影响。

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:

从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

#2

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

#3

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

格林

#4

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

根据我的测试，正确的标志是“allowMultiQueries=true”

#5

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.

唯一的问题是，如果在Select之后插入，您可能不会在结果集中获得新插入的行。

#6

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:

我认为这是选择/更新/插入/删除的最简单的方式。您可以在select之后运行任意多的update/insert/delete(如果需要的话，您必须先做一个select(一个哑的))和executeUpdate(str)(只要使用新的int(count1,count2，…))，如果您需要一个新的select close 'statement'和'connection'，并为下一次select创建新的。像例子:

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

我希望这有助于