shardingsphere引发 java.lang.String cannot be cast to java.lang.Integer异常

错误描述：

mysql数据库查询sql在数据库连接工具中可以正常运行，在加入了shardingsphere的jar包的项目中抛如下异常：

java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer
	at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.getValue(PaginationContext.java:57)
	at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext.<init>(PaginationContext.java:50)
	at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.LimitPaginationContextEngine.createPaginationContext(LimitPaginationContextEngine.java:38)
	at org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.engine.PaginationContextEngine.createPaginationContext(PaginationContextEngine.java:48)
	at org.apache.shardingsphere.sql.parser.binder.statement.dml.SelectStatementContext.<init>(SelectStatementContext.java:100)
	at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:103)
	at org.apache.shardingsphere.sql.parser.binder.SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:87)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:99)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
	at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeQuery(ShardingPreparedStatement.java:116)
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:216)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:223)

报错原因：

sql查询条件中的字段使用了日期转换函数，引发该错误的写法如下：

if (map.containsKey("startTime")) {
    sql.append(" and DATE_FORMAT(create_time,'%Y-%m-%d') >=:startTime ");
    paramMap.put("startTime",map.get("startTime"));
}
if (map.containsKey("stopTime")) {
    sql.append(" and DATE_FORMAT(create_time,'%Y-%m-%d') <=:stopTime ");
    paramMap.put("stopTime",map.get("stopTime"));
}

解决办法：

弃用日期函数，改变日期的比较方式，修改后写法如下：

if (map.containsKey("startTime")) {
    sql.append(" and create_time >=:startTime ");
    paramMap.put("startTime",String.format("%s%s",map.get("startTime").toString()," 00:00:00"));
}
if (map.containsKey("stopTime")) {
    sql.append(" and create_time <=:stopTime ");
    paramMap.put("stopTime",String.format("%s%s",map.get("stopTime").toString()," 23:59:59"));
}

备注：使用的shardingsphere 版本为4.1.1 ，也许更高版本的已解决该问题

---

2023年4月13日补充：

报错原因：

select 语句使用了 case when 语句，这个语句shrding sphere不支持

解决办法：

case when 替换为
if(“判断条件返回布尔值”，“为true时的值”，“为false时的值”)