This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 3d4b9567cb1 Enhance ResultSetUtils to support flexible string
date/time conversions (#37424)
3d4b9567cb1 is described below
commit 3d4b9567cb1adc119e028eef927179ead8d1d61f
Author: Cong Hu <[email protected]>
AuthorDate: Thu Dec 18 16:26:18 2025 +0800
Enhance ResultSetUtils to support flexible string date/time conversions
(#37424)
* Enhance ResultSetUtils to support flexible string date/time conversions
* Add release note.
---
RELEASE-NOTES.md | 1 +
.../impl/driver/jdbc/type/util/ResultSetUtils.java | 32 ++++
.../driver/jdbc/type/util/ResultSetUtilsTest.java | 161 +++++++++++++++++++++
3 files changed, 194 insertions(+)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 95ba9477d4e..3115ad24959 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -67,6 +67,7 @@
1. Pipeline: Improve "alter transmission rule": verify STREAM_CHANNEL TYPE
NAME - [#36864](https://github.com/apache/shardingsphere/pull/36864)
1. Pipeline: InventoryDumperContextSplitter supports multi-columns unique key
first integer column splitting -
[#36935](https://github.com/apache/shardingsphere/pull/36935)
1. Encrypt: Support handling show create view result decoration in encrypt -
[#37299](https://github.com/apache/shardingsphere/pull/37299)
+1. JDBC: Enhance ResultSetUtils to support flexible string date/time
conversions - [37424](https://github.com/apache/shardingsphere/pull/37424)
### Bug Fixes
diff --git
a/infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtils.java
b/infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtils.java
index a69984341b3..908599bd110 100644
---
a/infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtils.java
+++
b/infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtils.java
@@ -37,7 +37,10 @@ import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZoneId;
+import java.time.format.DateTimeFormatter;
+import java.time.temporal.TemporalAccessor;
import java.util.Date;
+import java.util.Optional;
/**
* Result set utility class.
@@ -45,6 +48,14 @@ import java.util.Date;
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public final class ResultSetUtils {
+ private static final DateTimeFormatter LOOSE_DATE_TIME_FORMATTER =
DateTimeFormatter.ofPattern(
+ "[yyyy-MM-dd][yyyy_MM_dd][yyyyMMdd][yyyy-M-d][MM/dd/yy][yyMMdd]"
+ + "['T'][ ]"
+ + "[HH:mm:ss][HHmmss][HH:mm][HHmm]"
+ +
"[.SSSSSSSSS][.SSSSSSSS][.SSSSSSS][.SSSSSS][.SSSSS][.SSSS][.SSS][.SS][.S]"
+ + "[ ]"
+ + "[XXXXX][XXXX][XXX][XX][X]");
+
/**
* Convert value via expected class type.
*
@@ -88,6 +99,12 @@ public final class ResultSetUtils {
if (String.class.equals(convertType)) {
return value.toString();
}
+ if (value instanceof String) {
+ Optional<Object> result = convertStringValue((String) value,
convertType);
+ if (result.isPresent()) {
+ return result.get();
+ }
+ }
try {
return convertType.cast(value);
} catch (final ClassCastException ignored) {
@@ -95,6 +112,21 @@ public final class ResultSetUtils {
}
}
+ private static Optional<Object> convertStringValue(final String value,
final Class<?> convertType) {
+ if (Timestamp.class.equals(convertType)) {
+ TemporalAccessor temporalAccessor =
LOOSE_DATE_TIME_FORMATTER.parseBest(value, LocalDateTime::from,
LocalDate::from);
+ LocalDateTime localDateTime = (temporalAccessor instanceof
LocalDateTime) ? (LocalDateTime) temporalAccessor : ((LocalDate)
temporalAccessor).atStartOfDay();
+ return Optional.of(Timestamp.valueOf(localDateTime));
+ }
+ if (java.sql.Date.class.equals(convertType)) {
+ return
Optional.of(java.sql.Date.valueOf(LocalDate.from(LOOSE_DATE_TIME_FORMATTER.parse(value))));
+ }
+ if (java.sql.Time.class.equals(convertType)) {
+ return
Optional.of(java.sql.Time.valueOf(LocalTime.from(LOOSE_DATE_TIME_FORMATTER.parse(value))));
+ }
+ return Optional.empty();
+ }
+
private static Object convertNullValue(final Class<?> convertType) {
switch (convertType.getName()) {
case "boolean":
diff --git
a/infra/executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtilsTest.java
b/infra/executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtilsTest.java
index 2a77a5962ed..13cba861a56 100644
---
a/infra/executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtilsTest.java
+++
b/infra/executor/src/test/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtilsTest.java
@@ -199,4 +199,165 @@ class ResultSetUtilsTest {
java.sql.Date sqlDate = new java.sql.Date(now.getTime());
assertThat(result, is(sqlDate.toLocalDate()));
}
+
+ @Test
+ void assertConvertStringValueToTimestamp() throws SQLException {
+ String dateTimeStr = "2021-12-23 19:30:45";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45))));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithISOFormat() throws
SQLException {
+ String dateTimeStr = "2021-12-23T19:30:45";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45))));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithMilliseconds() throws
SQLException {
+ String dateTimeStr = "2021-12-23 19:30:45.123";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ Timestamp expected = Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45, 123000000));
+ assertThat(result, is(expected));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithDateOnly() throws SQLException
{
+ String dateStr = "2021-12-23";
+ Timestamp result = (Timestamp) ResultSetUtils.convertValue(dateStr,
Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
0, 0, 0))));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithCompactFormat() throws
SQLException {
+ String dateTimeStr = "20211223 193045";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45))));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithUnderscoreFormat() throws
SQLException {
+ String dateTimeStr = "2021_12_23 19:30:45";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDate() throws SQLException {
+ String dateStr = "2021-12-23";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(2021, 12,
23))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDateWithCompactFormat() throws
SQLException {
+ String dateStr = "20211223";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(2021, 12,
23))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDateWithUnderscoreFormat() throws
SQLException {
+ String dateStr = "2021_12_23";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(2021, 12,
23))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDateWithSlashFormat() throws
SQLException {
+ String dateStr = "12/23/21";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(2021, 12,
23))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDateWithSingleDigitMonth() throws
SQLException {
+ String dateStr = "2021-1-5";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(2021, 1,
5))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlTime() throws SQLException {
+ String timeStr = "19:30:45";
+ Time result = (Time) ResultSetUtils.convertValue(timeStr, Time.class);
+ assertThat(result, is(Time.valueOf(LocalTime.of(19, 30, 45))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlTimeWithMilliseconds() throws
SQLException {
+ String timeStr = "19:30:45.123";
+ Time result = (Time) ResultSetUtils.convertValue(timeStr, Time.class);
+ LocalTime localTime = LocalTime.of(19, 30, 45, 123000000);
+ assertThat(result, is(Time.valueOf(localTime)));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlTimeWithShortFormat() throws
SQLException {
+ String timeStr = "19:30";
+ Time result = (Time) ResultSetUtils.convertValue(timeStr, Time.class);
+ assertThat(result, is(Time.valueOf(LocalTime.of(19, 30, 0))));
+ }
+
+ @Test
+ void assertConvertStringValueWithUnsupportedTypeReturnsString() throws
SQLException {
+ String value = "test string";
+ String result = (String) ResultSetUtils.convertValue(value,
String.class);
+ assertThat(result, is("test string"));
+ }
+
+ @Test
+ void assertConvertStringValueWithInvalidTimestampFormat() {
+ String invalidDateStr = "invalid-date-time";
+ assertThrows(Exception.class, () ->
ResultSetUtils.convertValue(invalidDateStr, Timestamp.class));
+ }
+
+ @Test
+ void assertConvertStringValueWithInvalidDateFormat() {
+ String invalidDateStr = "not-a-date";
+ assertThrows(Exception.class, () ->
ResultSetUtils.convertValue(invalidDateStr, java.sql.Date.class));
+ }
+
+ @Test
+ void assertConvertStringValueWithInvalidTimeFormat() {
+ String invalidTimeStr = "not-a-time";
+ assertThrows(Exception.class, () ->
ResultSetUtils.convertValue(invalidTimeStr, Time.class));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithEpoch() throws SQLException {
+ String dateTimeStr = "1970-01-01 00:00:00";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ assertThat(result, is(Timestamp.valueOf(LocalDateTime.of(1970, 1, 1,
0, 0, 0))));
+ }
+
+ @Test
+ void assertConvertStringValueToTimestampWithNanoseconds() throws
SQLException {
+ String dateTimeStr = "2021-12-23 19:30:45.123456789";
+ Timestamp result = (Timestamp)
ResultSetUtils.convertValue(dateTimeStr, Timestamp.class);
+ Timestamp expected = Timestamp.valueOf(LocalDateTime.of(2021, 12, 23,
19, 30, 45, 123456789));
+ assertThat(result, is(expected));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlDateWithEpoch() throws SQLException {
+ String dateStr = "1970-01-01";
+ java.sql.Date result = (java.sql.Date)
ResultSetUtils.convertValue(dateStr, java.sql.Date.class);
+ assertThat(result, is(java.sql.Date.valueOf(LocalDate.of(1970, 1,
1))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlTimeMidnight() throws SQLException {
+ String timeStr = "00:00:00";
+ Time result = (Time) ResultSetUtils.convertValue(timeStr, Time.class);
+ assertThat(result, is(Time.valueOf(LocalTime.of(0, 0, 0))));
+ }
+
+ @Test
+ void assertConvertStringValueToSqlTimeEndOfDay() throws SQLException {
+ String timeStr = "23:59:59";
+ Time result = (Time) ResultSetUtils.convertValue(timeStr, Time.class);
+ assertThat(result, is(Time.valueOf(LocalTime.of(23, 59, 59))));
+ }
}
