Jdbc batch delete is not supported

[ICannerxy]

Bug Report

Which version of ShardingSphere did you use?

5.1.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

Support JDBC Statement addBatch() and executeBatch();

Actual behavior

When using jdbc to batch delete data, the expected and actual results are inconsistent.

Reason analyze (If you can)

Maybe is a bug.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1. Table t1 has 2 sub databases and 2 sub tables

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(222) DEFAULT NULL
) ENGINE=InnoDB

2. insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

public static void main(String[] args) {
        Connection con = null;
        PreparedStatement statement = null;
        try {
            String user = "root";
            String password = "servyou";
            String url = "jdbc:mysql://10.199.157.228:3307/datarktest?useUnicode=true&useSSL=true&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true&useServerPrepStmts=true";
            String driver = "com.mysql.cj.jdbc.Driver";
            //2.加载驱动
            Class.forName(driver);
            //3.获取连接
            con = DriverManager.getConnection(url, user, password);
            con.setAutoCommit(false);
            statement = con.prepareStatement("delete from t1 where id = ?");
            statement.setInt(1, 1);
            statement.addBatch();
            statement.setInt(1, 2);
            statement.addBatch();
            statement.setInt(1, 3);
            statement.addBatch();
            statement.setInt(1, 4);
            statement.addBatch();
            statement.setInt(1, 5);
            statement.addBatch();
            statement.setInt(1, 6);
            statement.addBatch();
            statement.setInt(1, 7);
            statement.addBatch();
            statement.setInt(1, 8);
            statement.addBatch();
            statement.setInt(1, 9);
            statement.addBatch();
            //6.执行操作
            statement.executeBatch();
            con.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.资源的关闭
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

It is expected that all the data in the table will be deleted, but it is not

Example codes for reproduce this issue (such as a github link).

[TeslaCN]

Please try removing the allowMultiQueries=true.

[ICannerxy]

After deleting the parameter allowMultiQueries=true , an error occurred

[TeslaCN]

Could you provide the result of bin/start.sh -v?

[TeslaCN]

We have handled MySQL multi statement in #16174 #16268.