为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:
DM Database Server 64 V8
DB Version: 0x7000c
DM JDBC DRIVER:
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.2.192</version>
</dependency>
【操作系统】:CentOS 7
【CPU】: GenuineIntel
【问题描述】*:
现在需要把查询结果序列化,其他数据库如mysql的驱动都没问题,用达梦驱动时,无法把TEXT类型的值正确序列化,ResultSet调用getObject时,返回的DmdbNClob对象。
报错日志:
Infinite recursion (StackOverflowError) (through reference chain: dm.jdbc.desc.EPGroup["props"]->dm.jdbc.desc.DmProperties["properties"]->java.util.Properties["epgroup"]->dm.jdbc.desc.EPGroup["props"]->dm.jdbc.desc.DmProperties["properties"]->java.util.Properties["epgroup"]->dm.jdbc.desc.EPGroup["props"]->dm.jdbc.desc.DmProperties["properties"]
JUNIT单元测试:
import static com.fasterxml.jackson.databind.DeserializationFeature.ACCEPT_EMPTY_ARRAY_AS_NULL_OBJECT;
import static com.fasterxml.jackson.databind.DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES;
import static com.fasterxml.jackson.databind.DeserializationFeature.READ_UNKNOWN_ENUM_VALUES_AS_NULL;
import static com.fasterxml.jackson.databind.MapperFeature.REQUIRE_SETTERS_FOR_GETTERS;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.JsonDeserializer;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializerProvider;
import com.fasterxml.jackson.databind.module.SimpleModule;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.TimeZone;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
/**
* @author clouds3n
* @since 2023-06-27
*/
public class DmTest {
// 定义DM JDBC驱动串
private String jdbcString = "dm.jdbc.driver.DmDriver";
// 定义DM URL连接串
private String urlString = "jdbc:dm://10.106.250.145:5236?schema=DATASOURCE_MANAGER";
// 定义连接用户名
private String userName = "SYSDBA";
// 定义连接用户口令
private String password = "SYSDBA001";
// 定义连接对象
private Connection conn = null;
private static final String YYYY_MM_DD_HH_MM_SS = "YYYY-MM-DD HH:mm:ss";
private static class LocalDateTimeDeserializer extends JsonDeserializer<LocalDateTime> {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(YYYY_MM_DD_HH_MM_SS);
@Override
public LocalDateTime deserialize(JsonParser p, DeserializationContext context) throws IOException {
return LocalDateTime.parse(p.getValueAsString(), formatter);
}
}
private static class LocalDateTimeSerializer extends JsonSerializer<LocalDateTime> {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(YYYY_MM_DD_HH_MM_SS);
@Override
public void serialize(LocalDateTime value,
JsonGenerator gen,
SerializerProvider serializers) throws IOException {
gen.writeString(value.format(formatter));
}
}
private static final SimpleModule LOCAL_DATE_TIME_MODULE = new SimpleModule()
.addSerializer(LocalDateTime.class, new LocalDateTimeSerializer())
.addDeserializer(LocalDateTime.class, new LocalDateTimeDeserializer());
private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper()
.configure(FAIL_ON_UNKNOWN_PROPERTIES, false)
.configure(ACCEPT_EMPTY_ARRAY_AS_NULL_OBJECT, true)
.configure(READ_UNKNOWN_ENUM_VALUES_AS_NULL, true)
.configure(REQUIRE_SETTERS_FOR_GETTERS, true)
.registerModule(LOCAL_DATE_TIME_MODULE)
.setTimeZone(TimeZone.getDefault())
.setDateFormat(new SimpleDateFormat(YYYY_MM_DD_HH_MM_SS));
@Test
void jackson_serialize() {
Assertions.assertDoesNotThrow(() -> {
loadJdbcDriver();
connect();
// 查询语句
String sql = "select * from common_datasource";
// 序列化结果
ArrayNode resultJSONArray = OBJECT_MAPPER.createArrayNode();
try (// 创建语句对象
Statement stmt = conn.createStatement();
// 执行查询
ResultSet rs = stmt.executeQuery(sql);) {
ResultSetMetaData md = rs.getMetaData();
int colNum = md.getColumnCount();
while (rs.next()) {
ObjectNode mapOfColValues = OBJECT_MAPPER.createObjectNode();
for (int i = 1; i <= colNum; i++) {
mapOfColValues.set(md.getColumnLabel(i), OBJECT_MAPPER.valueToTree(rs.getObject(i)));
}
resultJSONArray.add(mapOfColValues);
}
}
System.out.println(resultJSONArray.toPrettyString());
});
}
public void loadJdbcDriver() throws SQLException {
try {
System.out.println("Loading JDBC Driver...");
// 加载JDBC驱动程序
Class.forName(jdbcString);
} catch (ClassNotFoundException e) {
throw new SQLException("Load JDBC Driver Error : " + e.getMessage());
} catch (Exception ex) {
throw new SQLException("Load JDBC Driver Error : "
+ ex.getMessage());
}
}
public void connect() throws SQLException {
try {
System.out.println("Connecting to DM Server...");
// 连接DM数据库
conn = DriverManager.getConnection(urlString, userName, password);
} catch (SQLException e) {
throw new SQLException("Connect to DM Server Error : "
+ e.getMessage());
}
}
public void disConnect() throws SQLException {
try {
// 关闭连接
conn.close();
} catch (SQLException e) {
throw new SQLException("close connection error : " + e.getMessage());
}
}
}
建表语句:
CREATE TABLE "common_datasource"
(
"id" BIGINT NOT NULL,
"create_by" VARCHAR(255) DEFAULT '-1' NOT NULL,
"update_by" VARCHAR(255) DEFAULT '-1' NOT NULL,
"create_time" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
"update_time" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
"name" VARCHAR(64) NOT NULL,
"note" VARCHAR(255),
"type" TINYINT NOT NULL,
"connection_params" TEXT NOT NULL,
"status" TINYINT DEFAULT 1 NOT NULL,
"heartbeat_time" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL,
"version" BIGINT DEFAULT 1 NOT NULL,
"dir_id" BIGINT NOT NULL,
NOT CLUSTER PRIMARY KEY("id")
) STORAGE(ON "MAIN", CLUSTERBTR);
COMMENT
ON TABLE "common_datasource" IS '通用数据源管理表';
COMMENT
ON COLUMN "common_datasource"."name" IS '名称';
COMMENT
ON COLUMN "common_datasource"."note" IS '备注';
COMMENT
ON COLUMN "common_datasource"."type" IS '数据库类型';
COMMENT
ON COLUMN "common_datasource"."connection_params" IS '连接参数json字符串';
COMMENT
ON COLUMN "common_datasource"."status" IS '状态:1-健康;0-异常';
COMMENT
ON COLUMN "common_datasource"."heartbeat_time" IS '最近一次心跳检测时间';
COMMENT
ON COLUMN "common_datasource"."version" IS '乐观锁版本';
COMMENT
ON COLUMN "common_datasource"."dir_id" IS '所属目录id';
CREATE INDEX "common_datasource_type_IDX" ON "common_datasource" ("type" ASC) STORAGE(ON "MAIN", CLUSTERBTR);
CREATE INDEX "common_datasource_ts_IDX" ON "common_datasource" ("type" ASC, "status" ASC) STORAGE(ON "MAIN", CLUSTERBTR);
CREATE INDEX "common_datasource_ntd_IDX" ON "common_datasource" ("name" ASC, "type" ASC, "dir_id" ASC) STORAGE(ON "MAIN", CLUSTERBTR);
测试数据:
INSERT INTO common_datasource (id, create_by, update_by, create_time, update_time, "name", "note", "type", connection_params, "status", heartbeat_time, "version", dir_id) VALUES(3054702449, '00145f3f8ec440aba212608ce31cdd22', '00145f3f8ec440aba212608ce31cdd22', '2023-06-09 10:20:33.118', '2023-06-13 18:09:03.096', '新增DORIS', '', 12, '{"address":"jdbc:mysql://10.73.13.52:9030","database":"dw_dev","driverClassName":"com.mysql.cj.jdbc.Driver","httpPort":8050,"jdbcUrl":"jdbc:mysql://10.73.13.52:9030/dw_dev","other":"useSSL=false&","password":"IUAjJCVeJipRMmx6WkdsQU1USXpORFUy","props":{"useSSL":"false"},"user":"dwbuilder_dev","validationQuery":"select 1"}', 1, '2023-06-09 10:20:33.198099', 0, -1);
单独 判断下大字段类型后,将
mapOfColValues.set(md.getColumnLabel(i), OBJECT_MAPPER.valueToTree(rs.getObject(i)));
转成普通字符串
mapOfColValues.set(md.getColumnLabel(i), OBJECT_MAPPER.valueToTree(rs.getObject(i).toString()));
你把TEXT定义成varchar(2000),一般connection__params不会太长