Issue 1:
Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.0</version>
</dependency>

<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.0</version>
</dependency>

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

ShardingSphere-JDBC

Expected behavior

can insert and update

### Actual behavior
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLException: No value specified for parameter 6
### The error may exist in file [D:\myworkers\workspace\ocenter\ocenter-dao\target\classes\mapper\RecordMapper.xml]
### The error may involve com.thclouds.ocenter.mapper.RecordMapper.appends-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO transbill_records ( business_no, occurrence_time, gmt_create, gmt_modify, records ) values ( ?, ?, ?, ?, ? ) on duplicate key update records= CONCAT_WS(';',records,?)
### Cause: java.sql.SQLException: No value specified for parameter 6
; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 6

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy114.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
at com.sun.proxy.$Proxy118.appends(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy119.appends(Unknown Source)
at com.thclouds.ocenter.server.service.RecordService.appendByBusinessNo(RecordService.java:28)
at com.thclouds.ocenter.service.RecordServiceTest.TestappendByBusinessNo(RecordServiceTest.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.sql.SQLException: No value specified for parameter 6
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:398)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy152.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 51 more

Reason analyze (If you can)

proxy mybatis params，but not support one object can user more params。

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

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

use mysql 8 , use mybatis

not use shardingsphere can insert and update,after use can not use。

DROP TABLE IF EXISTS `transbill_records`;
CREATE TABLE `transbill_records` (
`business_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '对应业务主键',
`boot_type` int(10) NOT NULL COMMENT '对应的操作关联类型',
`occurrence_time` datetime(0) NULL DEFAULT NULL COMMENT '发生时间',
`gmt_create` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`gmt_modify` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
`records` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
PRIMARY KEY (`business_no`) USING BTREE,
UNIQUE INDEX `uniq_business_no`(`business_no`, `boot_type`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'spi接口配置表 ' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

businessNo is primary key

<insert id="appends" parameterType="com.thclouds.ocenter.domain.BillRecordInfoDO">
INSERT INTO transbill_records
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test='null != businessNo'>
business_no,
</if>
<if test='null != occurrenceTime'>
occurrence_time,
</if>
<if test='null != gmtCreate'>
gmt_create,
</if>
<if test='null != gmtModify'>
gmt_modify,
</if>
<if test='null != records'>
records
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test='null != businessNo'>
#{businessNo},
</if>
<if test='null != occurrenceTime'>
#{occurrenceTime},
</if>
<if test='null != gmtCreate'>
#{gmtCreate},
</if>
<if test='null != gmtModify'>
#{gmtModify},
</if>
<if test='null != records'>
#{records}
</if>
</trim>
on duplicate key update
records= CONCAT_WS(';',records,#{records})
</insert>

Issue 2:
Feature Request

For English only, other languages will not accept.

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!

Hi, shardingsphere community:

Now, some of MySQL DAL showXXX statement have the same filter segment, for example:

SHOW FUNCTION STATUS

SHOW CHARACTER SET

SHOW COLLATION

SHOW COLUMNS

SHOW DATABASES

SHOW EVENTS

SHOW OPEN TABLES

SHOW PROCEDURE STATUS

SHOW STATUS

SHOW TABLE STATUS

SHOW TABLES

SHOW TRIGGERS

SHOW VARIABLES

SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]

SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]

They have the same filter segment. But in the code , have the duplicate code, for example:

/**
* MySQL show procedure status statement.
*/
@Setter
@ToString
public final class MySQLShowProcedureStatusStatement extends AbstractSQLStatement implements DALStatement, MySQLStatement {

private ShowLikeSegment like;

private WhereSegment where;

/**
* Get like segment.
*
* @return like segment
*/
public Optional<ShowLikeSegment> getLike() {
return Optional.ofNullable(like);
}

/**
* Get where segment.
*
* @return where segment
*/
public Optional<WhereSegment> getWhere() {
return Optional.ofNullable(where);
}
}

@Setter
@ToString
public final class MySQLShowFunctionStatusStatement extends AbstractSQLStatement implements DALStatement, MySQLStatement {

private ShowLikeSegment like;

private WhereSegment where;

/**
* Get like segment.
*
* @return like segment
*/
public Optional<ShowLikeSegment> getLike() {
return Optional.ofNullable(like);
}

/**
* Get where segment.
*
* @return where segment
*/
public Optional<WhereSegment> getWhere() {
return Optional.ofNullable(where);
}
}

I want to define a ShowFilterSegment class, and compose the ShowLikeSegment and WhereSegment into ShowFilterSegment，like this:

public class ShowFilterSegment implements SQLSegment {

private final int startIndex;

private final int stopIndex;

private ShowLikeSegment like;

private WhereSegment where;
}

@Setter
@ToString
public final class MySQLShowFunctionStatusStatement extends AbstractSQLStatement implements DALStatement, MySQLStatement {

private ShowFilterSegment filter;

/**
* Get filter segment.
*
* @return filter segment
*/
public Optional<ShowFilterSegment> getFilter() {
return Optional.ofNullable(filter);
}
}

Do you think this is a good idea? please give me some advices.