When `Mode` uses the memory mode of `H2database`, `JDBCRepository` has a logical problem in processing Delete and Create SQL statements

[linghengqian]

Feature Request

For English only, other languages will not be accepted.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot make decision by current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Is your feature request related to a problem?

• See Support connecting to OpenGauss via DistSQL in Proxy Native #33139 .

Describe the feature you would like.

• When Mode uses the memory mode of H2database, org.apache.shardingsphere.mode.repository.standalone.jdbc.JDBCRepository has a logical problem in processing Delete and Create SQL statements.
• For org.apache.shardingsphere.mode.repository.standalone.jdbc.JDBCRepository#init(java.util.Properties),

try (
                Connection connection = dataSource.getConnection();
                Statement statement = connection.createStatement()) {
            // TODO remove it later. Add for reset standalone test e2e's env. Need to close DataSource to release H2's memory data
            if (jdbcRepositoryProps.<String>getValue(JDBCRepositoryPropertyKey.JDBC_URL).contains("h2:mem:")) {
                try {
                    statement.execute("TRUNCATE TABLE `repository`");
                } catch (final SQLException ignored) {
                }
            }
            // Finish TODO
            statement.execute(repositorySQL.getCreateTableSQL());
        }

• CREATE TABLE IF NOT EXISTS repository(id varchar(36) PRIMARY KEY, key TEXT, value TEXT, parent TEXT) is executed later than TRUNCATE TABLE repository. The TRUNCATE SQL should not be executed before the table is created. This leads to issues similar to those detected in https://www.yuque.com/linghengqian/meve2v/kezd0g2m3lfuz38q . The picture comes from my local device.
• 
• For org.apache.shardingsphere.mode.repository.standalone.jdbc.JDBCRepository#delete(java.lang.String),

public void delete(final String key) {
        try (
                Connection connection = dataSource.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(repositorySQL.getDeleteSQL())) {
            preparedStatement.setString(1, key + "%");
            preparedStatement.executeUpdate();
        } catch (final SQLException ex) {
            log.error("Delete {} data by key: {} failed", getType(), key, ex);
        }
    }

• For a closed normal database connection, this processing is fine. However, for H2database's memory mode, once the database connection is closed, the data will be automatically lost, and it is meaningless to continue throwing exceptions. For nativeTest involved in https://github.com/apache/shardingsphere/actions/runs/11371301147/job/31633062477, the relevant unit test is starting multiple ShardingSphere Proxy Native instances in a single JVM process or a single GraalVM Native Image. Forcibly shutting down the ShardingSphere Proxy Native instance will result in an error similar to the following.

Test run finished after 252188 ms
[        19 containers found      ]
[         0 containers skipped    ]
[        19 containers started    ]
[         0 containers aborted    ]
[        19 containers successful ]
[         0 containers failed     ]
[        18 tests found           ]
[         0 tests skipped         ]
[        18 tests started         ]
[         0 tests aborted         ]
[        18 tests successful      ]
[         0 tests failed          ]

Error:  2024-10-16 18:25:17.896 [ForkJoinPool.commonPool-worker-1] o.a.s.m.r.s.jdbc.JDBCRepository - Delete JDBC data by key: /nodes/compute_nodes/online/proxy/d5904fbf-9528-417a-8c59-913700c8777f failed
java.sql.SQLException: HikariDataSource HikariDataSource (HikariPool-7) has been closed.
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:96)
	at org.apache.shardingsphere.mode.repository.standalone.jdbc.JDBCRepository.delete(JDBCRepository.java:191)
	at org.apache.shardingsphere.mode.persist.service.ComputeNodePersistService.offline(ComputeNodePersistService.java:191)
	at org.apache.shardingsphere.mode.manager.ContextManager.close(ContextManager.java:212)
	at org.apache.shardingsphere.proxy.frontend.ShardingSphereProxy.close(ShardingSphereProxy.java:153)
	at org.apache.shardingsphere.proxy.frontend.ShardingSphereProxy.start(ShardingSphereProxy.java:80)
	at org.apache.shardingsphere.proxy.Bootstrap.main(Bootstrap.java:70)
	at org.apache.shardingsphere.test.natived.commons.proxy.ProxyTestingServer.lambda$new$0(ProxyTestingServer.java:48)
	at java.base@22.0.2/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
	at java.base@22.0.2/java.util.concurrent.CompletableFuture$AsyncRun.exec(CompletableFuture.java:1796)
	at java.base@22.0.2/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:507)
	at java.base@22.0.2/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1489)
	at java.base@22.0.2/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:2071)
	at java.base@22.0.2/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:2033)
	at java.base@22.0.2/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:187)
	at org.graalvm.nativeimage.builder/com.oracle.svm.core.thread.PlatformThreads.threadStartRoutine(PlatformThreads.java:853)
	at org.graalvm.nativeimage.builder/com.oracle.svm.core.thread.PlatformThreads.threadStartRoutine(PlatformThreads.java:829)

• I believe this can be improved.

[linghengqian]

• I am working on the current issue.