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

问题描述

您好我想知道是否可以使用JDBC执行此类操作，因为它目前提供异常，即使它可以在MySQL查询浏览器中使用。

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

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

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

推荐答案

我想知道是否有可能使用JDBC执行类似的操作。

"SELECT FROM * TABLE;INSERT INTO TABLE;"

是的，这是可能的。据我所知，有两种方法。它们是

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

1. 通过设置数据库连接属性以允许多个查询，
   默认用分号分隔。


2. 通过调用隐式返回游标的存储过程。

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

Following examples demonstrate the above two possibilities.

示例1 :(允许多次查询）：

Example 1: ( To allow multiple queries ):

发送时连接请求，您需要将连接属性 allowMultiQueries = true 附加到数据库URL。这是附加连接属性，如果已经存在的那些，如 autoReConnect = true 等。 allowMultiQueries 的可接受值属性 true ， false ，是和没有。运行时使用 SQLException 拒绝任何其他值。

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

除非传递了这样的指令，否则 SQLException 抛出。

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

示例2 ：步骤按照：

1. 使用一个或多个选择和<$ c创建一个程序$ c> DML 查询。


2. 使用 CallableStatement 从java中调用它。


3. 您可以捕获在程序中执行的多个 ResultSet 。
   
   无法捕获DML结果但可以发出另一个选择
   
   查找表中行的影响方式。

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.

示例表和程序：

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)

从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

这篇关于在单个语句中用java执行多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！