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

2022-01-30 00:00:00 sql mysql jdbc 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:(允许多个查询):

发送连接请求时，您需要将连接属性 allowMultiQueries=true 附加到数据库 url.这是对那些已经存在的连接属性的附加属性，例如 autoReConnect=true 等.allowMultiQueries 属性的可接受值为 true, false、yes 和 no.任何其他值在运行时都会被拒绝并返回 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.

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

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. 使用一个或多个 select 和 DML 查询创建一个过程.
  2. 使用 CallableStatement 从 java 调用它.
  3. 您可以捕获多个在过程中执行的ResultSet.
    无法捕获 DML 结果，但可以发出另一个 select
    找出表中的行是如何受到影响的.
  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

相关文章