Skip to content

Commit b999c25

Browse files
Chat2DB-ProChat2DB-Pro
committed
fix point 乱码问题
1 parent a64fead commit b999c25

13 files changed

Lines changed: 273 additions & 120 deletions

File tree

‎chat2db-server/chat2db-plugins/chat2db-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlMetaData.java‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,11 +14,11 @@
1414
import ai.chat2db.plugin.mysql.type.MysqlIndexTypeEnum;
1515
import ai.chat2db.spi.MetaData;
1616
import ai.chat2db.spi.SqlBuilder;
17+
import ai.chat2db.spi.ValueHandler;
1718
import ai.chat2db.spi.jdbc.DefaultMetaService;
1819
import ai.chat2db.spi.model.*;
1920
import ai.chat2db.spi.sql.SQLExecutor;
2021
import jakarta.validation.constraints.NotEmpty;
21-
import org.apache.commons.collections4.CollectionUtils;
2222
import org.apache.commons.lang3.StringUtils;
2323

2424
import static ai.chat2db.spi.util.SortUtils.sortDatabase;
@@ -294,4 +294,9 @@ public TableMeta getTableMeta(String databaseName, String schemaName, String tab
294294
public String getMetaDataName(String... names) {
295295
return Arrays.stream(names).filter(name -> StringUtils.isNotBlank(name)).map(name -> "`" + name + "`").collect(Collectors.joining("."));
296296
}
297+
298+
@Override
299+
public ValueHandler getValueHandler() {
300+
return new MysqlValueHandler();
301+
}
297302
}

‎chat2db-server/chat2db-plugins/chat2db-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlValueHandler.java‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
package ai.chat2db.plugin.mysql;
2+
3+
import ai.chat2db.plugin.mysql.type.MysqlColumnTypeEnum;
4+
import ai.chat2db.plugin.mysql.value.GeometryValueHandler;
5+
import ai.chat2db.spi.ValueHandler;
6+
import ai.chat2db.spi.jdbc.DefaultValueHandler;
7+
8+
import java.sql.ResultSet;
9+
import java.sql.SQLException;
10+
import java.util.Map;
11+
12+
public class MysqlValueHandler extends DefaultValueHandler {
13+
14+
private static final Map<String, ValueHandler> VALUE_HANDLER_MAP = Map.of(
15+
MysqlColumnTypeEnum.GEOMETRY.name(), new GeometryValueHandler()
16+
);
17+
18+
@Override
19+
public String getString(ResultSet rs, int index, boolean limitSize) throws SQLException {
20+
Object obj = rs.getObject(index);
21+
if (obj == null) {
22+
return null;
23+
}
24+
String columnTypeName = rs.getMetaData().getColumnTypeName(index);
25+
if (MysqlColumnTypeEnum.GEOMETRY.name().equalsIgnoreCase(columnTypeName)
26+
|| MysqlColumnTypeEnum.POINT.name().equalsIgnoreCase(columnTypeName)
27+
|| MysqlColumnTypeEnum.LINESTRING.name().equalsIgnoreCase(columnTypeName)
28+
|| MysqlColumnTypeEnum.POLYGON.name().equalsIgnoreCase(columnTypeName)
29+
|| MysqlColumnTypeEnum.MULTIPOINT.name().equalsIgnoreCase(columnTypeName)
30+
|| MysqlColumnTypeEnum.MULTILINESTRING.name().equalsIgnoreCase(columnTypeName)
31+
|| MysqlColumnTypeEnum.MULTIPOLYGON.name().equalsIgnoreCase(columnTypeName)
32+
|| MysqlColumnTypeEnum.GEOMETRYCOLLECTION.name().equalsIgnoreCase(columnTypeName)
33+
) {
34+
ValueHandler handler = VALUE_HANDLER_MAP.get(MysqlColumnTypeEnum.GEOMETRY.name());
35+
return handler.getString(rs, index, limitSize);
36+
} else {
37+
return super.getString(rs, index, limitSize);
38+
}
39+
}
40+
41+
}

‎chat2db-server/chat2db-plugins/chat2db-mysql/src/main/java/ai/chat2db/plugin/mysql/value/GeometryValueHandler.java‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
package ai.chat2db.plugin.mysql.value;
2+
3+
import ai.chat2db.spi.ValueHandler;
4+
import org.locationtech.jts.geom.Geometry;
5+
import org.locationtech.jts.io.WKBReader;
6+
7+
import java.io.InputStream;
8+
import java.io.ByteArrayOutputStream;
9+
import java.sql.ResultSet;
10+
import java.sql.SQLException;
11+
12+
public class GeometryValueHandler implements ValueHandler {
13+
@Override
14+
public String getString(ResultSet rs, int index, boolean limitSize) throws SQLException {
15+
try {
16+
InputStream inputStream = rs.getBinaryStream(index);
17+
Geometry dbGeometry = null;
18+
if (inputStream != null) {
19+
20+
//convert the stream to a byte[] array
21+
//so it can be passed to the WKBReader
22+
byte[] buffer = new byte[255];
23+
24+
int bytesRead = 0;
25+
ByteArrayOutputStream baos = new ByteArrayOutputStream();
26+
while ((bytesRead = inputStream.read(buffer)) != -1) {
27+
baos.write(buffer, 0, bytesRead);
28+
}
29+
30+
byte[] geometryAsBytes = baos.toByteArray();
31+
32+
if (geometryAsBytes.length < 5) {
33+
throw new Exception("Invalid geometry inputStream - less than five bytes");
34+
}
35+
36+
//first four bytes of the geometry are the SRID,
37+
//followed by the actual WKB. Determine the SRID
38+
//here
39+
byte[] sridBytes = new byte[4];
40+
System.arraycopy(geometryAsBytes, 0, sridBytes, 0, 4);
41+
boolean bigEndian = (geometryAsBytes[4] == 0x00);
42+
43+
int srid = 0;
44+
if (bigEndian) {
45+
for (int i = 0; i < sridBytes.length; i++) {
46+
srid = (srid << 8) + (sridBytes[i] & 0xff);
47+
}
48+
} else {
49+
for (int i = 0; i < sridBytes.length; i++) {
50+
srid += (sridBytes[i] & 0xff) << (8 * i);
51+
}
52+
}
53+
54+
//use the JTS WKBReader for WKB parsing
55+
WKBReader wkbReader = new WKBReader();
56+
57+
//copy the byte array, removing the first four
58+
//SRID bytes
59+
byte[] wkb = new byte[geometryAsBytes.length - 4];
60+
System.arraycopy(geometryAsBytes, 4, wkb, 0, wkb.length);
61+
dbGeometry = wkbReader.read(wkb);
62+
dbGeometry.setSRID(srid);
63+
}
64+
return dbGeometry.toString();
65+
} catch (Exception e) {
66+
return rs.getString(index);
67+
}
68+
}
69+
}

‎chat2db-server/chat2db-server-domain/chat2db-server-domain-core/src/main/java/ai/chat2db/server/domain/core/impl/DlTemplateServiceImpl.java‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111
import ai.chat2db.server.domain.api.service.TableService;
1212
import ai.chat2db.server.domain.core.util.MetaNameUtils;
1313
import ai.chat2db.spi.MetaData;
14+
import ai.chat2db.spi.ValueHandler;
1415
import ai.chat2db.spi.model.*;
1516
import ai.chat2db.spi.sql.ConnectInfo;
1617
import com.alibaba.druid.DbType;
@@ -36,7 +37,6 @@
3637
import com.google.common.collect.Lists;
3738
import lombok.extern.slf4j.Slf4j;
3839
import org.apache.commons.collections4.CollectionUtils;
39-
import org.apache.commons.collections4.ListUtils;
4040
import org.apache.commons.lang3.ObjectUtils;
4141
import org.apache.commons.lang3.StringUtils;
4242
import org.springframework.beans.factory.annotation.Autowired;
@@ -436,7 +436,8 @@ private List<Header> setColumnInfo(List<Header> headers, String tableName, Strin
436436
private ExecuteResult execute(String sql, Integer offset, Integer count) {
437437
ExecuteResult executeResult;
438438
try {
439-
executeResult = SQLExecutor.getInstance().execute(sql, Chat2DBContext.getConnection(), true, offset, count);
439+
ValueHandler valueHandler = Chat2DBContext.getMetaData().getValueHandler();
440+
executeResult = SQLExecutor.getInstance().execute(sql, Chat2DBContext.getConnection(), true, offset, count,valueHandler);
440441
} catch (SQLException e) {
441442
log.warn("执行sql:{}异常", sql, e);
442443
executeResult = ExecuteResult.builder()

‎chat2db-server/chat2db-server-web/chat2db-server-web-api/src/main/java/ai/chat2db/server/web/api/controller/rdb/RdbDmlExportController.java‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@
77
import java.time.LocalDateTime;
88
import java.util.List;
99

10+
import ai.chat2db.spi.jdbc.DefaultValueHandler;
1011
import com.alibaba.druid.DbType;
1112
import com.alibaba.druid.sql.SQLUtils;
1213
import com.alibaba.druid.sql.SQLUtils.FormatOption;
@@ -130,7 +131,7 @@ private void doExportCsv(String sql, HttpServletResponse response, String fileNa
130131
List<List<String>> writeDataList = Lists.newArrayList();
131132
writeDataList.add(dataList);
132133
excelWrapper.getExcelWriter().write(writeDataList, excelWrapper.getWriteSheet());
133-
}, false);
134+
}, false,new DefaultValueHandler());
134135
} finally {
135136
if (excelWrapper.getExcelWriter() != null) {
136137
excelWrapper.getExcelWriter().finish();
@@ -161,7 +162,7 @@ private void doExportInsert(String sql, HttpServletResponse response, String fil
161162
sqlInsertStatement.setValues(valuesClause);
162163

163164
printWriter.println(SQLUtils.toSQLString(sqlInsertStatement, dbType, INSERT_FORMAT_OPTION) + ";");
164-
}, false);
165+
}, false,new DefaultValueHandler());
165166
}
166167
}
167168

‎chat2db-server/chat2db-spi/pom.xml‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,5 +74,10 @@
7474
<artifactId>jsqlparser</artifactId>
7575
<version>4.6</version>
7676
</dependency>
77+
<dependency>
78+
<groupId>org.locationtech.jts</groupId>
79+
<artifactId>jts-core</artifactId>
80+
<version>1.18.1</version> <!-- 确保使用最新的版本 -->
81+
</dependency>
7782
</dependencies>
7883
</project>

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/MetaData.java‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -206,4 +206,12 @@ List<TableIndex> indexes(Connection connection, @NotEmpty String databaseName, S
206206
*/
207207
String getMetaDataName(String ...names);
208208

209+
210+
/**
211+
* Get column builder.
212+
*
213+
* @return
214+
*/
215+
ValueHandler getValueHandler();
216+
209217
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/ValueHandler.java‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
package ai.chat2db.spi;
2+
3+
import java.sql.ResultSet;
4+
import java.sql.SQLException;
5+
6+
public interface ValueHandler {
7+
8+
/**
9+
* 处理结果集中的列值
10+
* @param rs
11+
* @param index
12+
* @param limitSize
13+
* @return
14+
* @throws SQLException
15+
*/
16+
String getString(ResultSet rs, int index, boolean limitSize)throws SQLException;
17+
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/jdbc/DefaultMetaService.java‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@
88

99
import ai.chat2db.spi.MetaData;
1010
import ai.chat2db.spi.SqlBuilder;
11+
import ai.chat2db.spi.ValueHandler;
1112
import ai.chat2db.spi.model.*;
1213
import ai.chat2db.spi.sql.SQLExecutor;
1314
import org.apache.commons.collections4.CollectionUtils;
@@ -123,6 +124,8 @@ public String getMetaDataName(String... names) {
123124
return Arrays.stream(names).filter(name -> StringUtils.isNotBlank(name)).collect(Collectors.joining("."));
124125
}
125126

126-
127-
127+
@Override
128+
public ValueHandler getValueHandler() {
129+
return new DefaultValueHandler();
130+
}
128131
}

‎chat2db-server/chat2db-spi/src/main/java/ai/chat2db/spi/jdbc/DefaultValueHandler.java‎

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
package ai.chat2db.spi.jdbc;
2+
3+
import ai.chat2db.server.tools.common.util.I18nUtils;
4+
import ai.chat2db.spi.ValueHandler;
5+
import cn.hutool.core.io.unit.DataSizeUtil;
6+
import lombok.extern.slf4j.Slf4j;
7+
8+
import java.math.BigDecimal;
9+
import java.sql.Blob;
10+
import java.sql.Clob;
11+
import java.sql.ResultSet;
12+
import java.sql.SQLException;
13+
14+
@Slf4j
15+
public class DefaultValueHandler implements ValueHandler {
16+
17+
private static final long MAX_RESULT_SIZE = 256 * 1024;
18+
19+
@Override
20+
public String getString(ResultSet rs, int index, boolean limitSize) throws SQLException {
21+
Object obj = rs.getObject(index);
22+
if (obj == null) {
23+
return null;
24+
}
25+
try {
26+
if (obj instanceof BigDecimal bigDecimal) {
27+
return bigDecimal.toPlainString();
28+
} else if (obj instanceof Double d) {
29+
return BigDecimal.valueOf(d).toPlainString();
30+
} else if (obj instanceof Float f) {
31+
return BigDecimal.valueOf(f).toPlainString();
32+
} else if (obj instanceof Clob) {
33+
return largeString(rs, index, limitSize);
34+
} else if (obj instanceof byte[]) {
35+
return largeString(rs, index, limitSize);
36+
} else if (obj instanceof Blob blob) {
37+
return largeStringBlob(blob, limitSize);
38+
}
39+
return rs.getString(index);
40+
} catch (Exception e) {
41+
log.warn("解析数失败:{},{}", index, obj, e);
42+
return obj.toString();
43+
}
44+
}
45+
46+
private String largeStringBlob(Blob blob, boolean limitSize) throws SQLException {
47+
if (blob == null) {
48+
return null;
49+
}
50+
int length = Math.toIntExact(blob.length());
51+
if (limitSize && length > MAX_RESULT_SIZE) {
52+
length = Math.toIntExact(MAX_RESULT_SIZE);
53+
}
54+
byte[] data = blob.getBytes(1, length);
55+
String result = new String(data);
56+
57+
if (length > MAX_RESULT_SIZE) {
58+
return "[ " + DataSizeUtil.format(MAX_RESULT_SIZE) + " of " + DataSizeUtil.format(length)
59+
+ " ,"
60+
+ I18nUtils.getMessage("execute.exportCsv") + " ] " + result;
61+
}
62+
return result;
63+
}
64+
65+
private static String largeString(ResultSet rs, int index, boolean limitSize) throws SQLException {
66+
String result = rs.getString(index);
67+
if (result == null) {
68+
return null;
69+
70+
}
71+
if (!limitSize) {
72+
return result;
73+
}
74+
75+
if (result.length() > MAX_RESULT_SIZE) {
76+
return "[ " + DataSizeUtil.format(MAX_RESULT_SIZE) + " of " + DataSizeUtil.format(result.length()) + " ,"
77+
+ I18nUtils.getMessage("execute.exportCsv") + " ] " + result.substring(0,
78+
Math.toIntExact(MAX_RESULT_SIZE));
79+
}
80+
return result;
81+
}
82+
}

0 commit comments

Comments
 (0)