Skip to content

Commit c8e5af3

Browse files
Chat2DB-ProChat2DB-Pro
authored
Merge pull request OtterMind#1436 from openai0229/fix-exportTableData
fix-exportTableData
2 parents 9421c3d + 2bfbbae commit c8e5af3

9 files changed

Lines changed: 128 additions & 149 deletions

File tree

‎chat2db-server/chat2db-plugins/chat2db-clickhouse/src/main/java/ai/chat2db/plugin/clickhouse/ClickHouseDBManage.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,7 @@ private void exportTablesOrViewsOrDictionaries(Connection connection,String data
5555
.append(";").append("\n").append(ddl).append(";").append("\n");
5656
asyncContext.write(sqlBuilder.toString());
5757
if (asyncContext.isContainsData() && dataFlag) {
58-
exportTableData(connection,schemaName, tableOrViewName, asyncContext);
58+
exportTableData(connection, databaseName,schemaName, tableOrViewName, asyncContext);
5959
}
6060
}
6161
}

‎chat2db-server/chat2db-plugins/chat2db-db2/src/main/java/ai/chat2db/plugin/db2/DB2DBManage.java‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -20,22 +20,22 @@ public class DB2DBManage extends DefaultDBManage implements DBManage {
2020

2121
@Override
2222
public void exportDatabase(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
23-
exportTables(connection, schemaName, asyncContext);
23+
exportTables(connection, databaseName, schemaName, asyncContext);
2424
exportViews(connection, schemaName, asyncContext);
2525
exportProceduresAndFunctions(connection, schemaName, asyncContext);
2626
exportTriggers(connection, schemaName, asyncContext);
2727
}
2828

29-
private void exportTables(Connection connection, String schemaName, AsyncContext asyncContext) throws SQLException {
29+
private void exportTables(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
3030
try (ResultSet resultSet = connection.getMetaData().getTables(null, schemaName, null, new String[]{"TABLE", "SYSTEM TABLE"})) {
3131
while (resultSet.next()) {
32-
exportTable(connection, schemaName, resultSet.getString("TABLE_NAME"), asyncContext);
32+
exportTable(connection, databaseName, schemaName, resultSet.getString("TABLE_NAME"), asyncContext);
3333
}
3434
}
3535
}
3636

3737

38-
private void exportTable(Connection connection, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
38+
private void exportTable(Connection connection, String databaseName, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
3939
try {
4040
SQLExecutor.getInstance().execute(connection, SQLConstant.TABLE_DDL_FUNCTION_SQL, resultSet -> null);
4141
} catch (Exception e) {
@@ -48,7 +48,7 @@ private void exportTable(Connection connection, String schemaName, String tableN
4848
sqlBuilder.append(resultSet.getString("sql")).append("\n");
4949
asyncContext.write(sqlBuilder.toString());
5050
if (asyncContext.isContainsData()) {
51-
exportTableData(connection, schemaName, tableName, asyncContext);
51+
exportTableData(connection, databaseName, schemaName, tableName, asyncContext);
5252
}
5353
}
5454
}

‎chat2db-server/chat2db-plugins/chat2db-dm/src/main/java/ai/chat2db/plugin/dm/DMDBManage.java‎

Lines changed: 16 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,5 @@
11
package ai.chat2db.plugin.dm;
22

3-
import java.sql.*;
4-
import java.util.Objects;
5-
63
import ai.chat2db.spi.DBManage;
74
import ai.chat2db.spi.jdbc.DefaultDBManage;
85
import ai.chat2db.spi.model.AsyncContext;
@@ -13,6 +10,11 @@
1310
import org.apache.commons.lang3.ObjectUtils;
1411
import org.apache.commons.lang3.StringUtils;
1512

13+
import java.sql.Connection;
14+
import java.sql.ResultSet;
15+
import java.sql.SQLException;
16+
import java.sql.Statement;
17+
1618
@Slf4j
1719
public class DMDBManage extends DefaultDBManage implements DBManage {
1820
private String format(String tableName) {
@@ -29,30 +31,30 @@ private String format(String tableName) {
2931

3032
@Override
3133
public void exportDatabase(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
32-
exportTables(connection, schemaName, asyncContext);
34+
exportTables(connection, databaseName, schemaName, asyncContext);
3335
exportViews(connection, schemaName, asyncContext);
3436
exportProcedures(connection, schemaName, asyncContext);
3537
exportTriggers(connection, schemaName, asyncContext);
3638
}
3739

38-
private void exportTables(Connection connection, String schemaName, AsyncContext asyncContext) throws SQLException {
40+
private void exportTables(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
3941
String sql = String.format("SELECT TABLE_NAME FROM ALL_TABLES where OWNER='%s' and TABLESPACE_NAME='MAIN'", schemaName);
4042
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
4143
while (resultSet.next()) {
4244
String tableName = resultSet.getString("TABLE_NAME");
43-
exportTable(connection, tableName, schemaName, asyncContext);
45+
exportTable(connection, databaseName, tableName, schemaName, asyncContext);
4446
}
4547
}
4648
}
4749

4850

49-
private void exportTable(Connection connection, String tableName, String schemaName, AsyncContext asyncContext) throws SQLException {
51+
private void exportTable(Connection connection, String databaseName, String tableName, String schemaName, AsyncContext asyncContext) throws SQLException {
5052
String sql = """
51-
SELECT
52-
(SELECT comments FROM user_tab_comments WHERE table_name = '%s') AS comments,
53-
(SELECT dbms_metadata.get_ddl('TABLE', '%s', '%s') FROM dual) AS ddl
54-
FROM dual;
55-
""";
53+
SELECT
54+
(SELECT comments FROM user_tab_comments WHERE table_name = '%s') AS comments,
55+
(SELECT dbms_metadata.get_ddl('TABLE', '%s', '%s') FROM dual) AS ddl
56+
FROM dual;
57+
""";
5658
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(String.format(sql, tableName, tableName, schemaName))) {
5759
String formatSchemaName = format(schemaName);
5860
String formatTableName = format(tableName);
@@ -70,14 +72,14 @@ private void exportTable(Connection connection, String tableName, String schemaN
7072
exportTableColumnComment(connection, schemaName, tableName, asyncContext);
7173
}
7274
if (asyncContext.isContainsData()) {
73-
exportTableData(connection, schemaName, tableName, asyncContext);
75+
exportTableData(connection, databaseName, schemaName, tableName, asyncContext);
7476
}
7577
}
7678
}
7779

7880
private void exportTableColumnComment(Connection connection, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
7981
String sql = String.format("select COLNAME,COMMENT$ from SYS.SYSCOLUMNCOMMENTS\n" +
80-
"where SCHNAME = '%s' and TVNAME = '%s'and TABLE_TYPE = 'TABLE';", schemaName, tableName);
82+
"where SCHNAME = '%s' and TVNAME = '%s'and TABLE_TYPE = 'TABLE';", schemaName, tableName);
8183
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
8284
while (resultSet.next()) {
8385
StringBuilder sqlBuilder = new StringBuilder();

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

Lines changed: 10 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -7,13 +7,14 @@
77
import ai.chat2db.spi.sql.SQLExecutor;
88
import org.springframework.util.StringUtils;
99

10-
import java.sql.*;
11-
import java.util.Objects;
10+
import java.sql.Connection;
11+
import java.sql.ResultSet;
12+
import java.sql.SQLException;
1213

1314
public class MysqlDBManage extends DefaultDBManage implements DBManage {
1415
@Override
1516
public void exportDatabase(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
16-
exportTables(connection, databaseName, asyncContext);
17+
exportTables(connection, databaseName, schemaName, asyncContext);
1718
exportViews(connection, databaseName, asyncContext);
1819
exportProcedures(connection, asyncContext);
1920
exportTriggers(connection, asyncContext);
@@ -35,22 +36,22 @@ private void exportFunction(Connection connection, String functionName, AsyncCon
3536
if (resultSet.next()) {
3637
StringBuilder sqlBuilder = new StringBuilder();
3738
sqlBuilder.append("DROP FUNCTION IF EXISTS ").append(functionName).append(";").append("\n")
38-
.append(resultSet.getString("Create Function")).append(";").append("\n");
39+
.append(resultSet.getString("Create Function")).append(";").append("\n");
3940
asyncContext.write(sqlBuilder.toString());
4041
}
4142
}
4243
}
4344

44-
private void exportTables(Connection connection, String databaseName, AsyncContext asyncContext) throws SQLException {
45+
private void exportTables(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
4546
try (ResultSet resultSet = connection.getMetaData().getTables(databaseName, null, null, new String[]{"TABLE", "SYSTEM TABLE"})) {
4647
while (resultSet.next()) {
47-
exportTable(connection, resultSet.getString("TABLE_NAME"), asyncContext);
48+
exportTable(connection, databaseName, schemaName, resultSet.getString("TABLE_NAME"), asyncContext);
4849
}
4950
}
5051
}
5152

5253

53-
private void exportTable(Connection connection, String tableName, AsyncContext asyncContext) throws SQLException {
54+
private void exportTable(Connection connection, String databaseName, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
5455
String sql = String.format("show create table %s ", tableName);
5556
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
5657
if (resultSet.next()) {
@@ -59,7 +60,7 @@ private void exportTable(Connection connection, String tableName, AsyncContext a
5960
.append(resultSet.getString("Create Table")).append(";").append("\n");
6061
asyncContext.write(sqlBuilder.toString());
6162
if (asyncContext.isContainsData()) {
62-
exportTableData(connection, null,tableName, asyncContext);
63+
exportTableData(connection, databaseName, schemaName, tableName, asyncContext);
6364
}
6465
}
6566
}
@@ -143,7 +144,7 @@ public void updateProcedure(Connection connection, String databaseName, String s
143144
} catch (Exception e) {
144145
connection.rollback();
145146
throw new RuntimeException(e);
146-
}finally {
147+
} finally {
147148
connection.setAutoCommit(true);
148149
}
149150

‎chat2db-server/chat2db-plugins/chat2db-oracle/src/main/java/ai/chat2db/plugin/oracle/OracleDBManage.java‎

Lines changed: 5 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -30,29 +30,25 @@ public class OracleDBManage extends DefaultDBManage implements DBManage {
3030
private static String TRIGGER_DDL_SQL = "SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name) AS ddl FROM all_triggers WHERE owner = '%s' AND trigger_name = '%s'";
3131
private static String FUNCTION_DDL_SQL = "SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name) as ddl FROM all_procedures WHERE owner = '%s' AND object_name = '%s'";
3232

33-
@Override
34-
public void exportDatabaseData(Connection connection, String databaseName, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
35-
exportTableData(connection, tableName, asyncContext);
36-
}
3733
public void exportDatabase(Connection connection, String databaseName, String schemaName,AsyncContext asyncContext) throws SQLException {
38-
exportTables(connection, schemaName, asyncContext);
34+
exportTables(connection,databaseName, schemaName, asyncContext);
3935
exportViews(connection, asyncContext, schemaName);
4036
exportProcedures(connection, schemaName, asyncContext);
4137
exportTriggers(connection, schemaName, asyncContext);
4238
exportFunctions(connection, schemaName, asyncContext);
4339
}
4440

45-
private void exportTables(Connection connection, String schemaName, AsyncContext asyncContext) throws SQLException {
41+
private void exportTables(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
4642
try (ResultSet resultSet = connection.getMetaData().getTables(null, schemaName, null, new String[]{"TABLE", "SYSTEM TABLE"})) {
4743
while (resultSet.next()) {
4844
String tableName = resultSet.getString("TABLE_NAME");
49-
exportTable(connection, schemaName, tableName, asyncContext);
45+
exportTable(connection,databaseName, schemaName, tableName, asyncContext);
5046
}
5147
}
5248
}
5349

5450

55-
private void exportTable(Connection connection, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
51+
private void exportTable(Connection connection, String databaseName, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
5652
String sql = String.format(TABLE_DDL_SQL, schemaName, tableName);
5753
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
5854
if (resultSet.next()) {
@@ -64,7 +60,7 @@ private void exportTable(Connection connection, String schemaName, String tableN
6460
exportTableComments(connection, tableName, asyncContext);
6561
exportTableColumnsComments(connection, tableName, asyncContext);
6662
if (asyncContext.isContainsData()) {
67-
exportTableData(connection, tableName, asyncContext);
63+
exportTableData(connection,databaseName,schemaName, tableName, asyncContext);
6864
}
6965
}
7066
}
@@ -91,36 +87,6 @@ private void exportTableColumnsComments(Connection connection, String tableName,
9187
}
9288
}
9389

94-
private void exportTableData(Connection connection, String tableName, AsyncContext asyncContext) throws SQLException {
95-
String sql = String.format("SELECT * FROM %s", tableName);
96-
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
97-
ResultSetMetaData metaData = resultSet.getMetaData();
98-
int columnCount = metaData.getColumnCount();
99-
while (resultSet.next()) {
100-
StringBuilder sqlBuilder = new StringBuilder();
101-
sqlBuilder.append("INSERT INTO ").append(tableName).append(" VALUES (");
102-
for (int i = 1; i <= columnCount; i++) {
103-
String columnValue = resultSet.getString(i);
104-
if (Objects.isNull(columnValue)) {
105-
sqlBuilder.append("NULL");
106-
} else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DATE")) {
107-
// 处理日期值格式
108-
columnValue = "TO_DATE('" + columnValue + "', 'YYYY-MM-DD HH24:MI:SS')";
109-
sqlBuilder.append(columnValue);
110-
} else {
111-
sqlBuilder.append("'").append(columnValue).append("'");
112-
}
113-
if (i < columnCount) {
114-
sqlBuilder.append(", ");
115-
}
116-
}
117-
sqlBuilder.append(");");
118-
sqlBuilder.append("\n");
119-
asyncContext.write(sqlBuilder.toString());
120-
}
121-
}
122-
}
123-
12490
private void exportViews(Connection connection, AsyncContext asyncContext, String schemaName) throws SQLException {
12591
try (ResultSet resultSet = connection.getMetaData().getTables(null, schemaName, null, new String[]{"VIEW"})) {
12692
while (resultSet.next()) {

‎chat2db-server/chat2db-plugins/chat2db-postgresql/src/main/java/ai/chat2db/plugin/postgresql/PostgreSQLDBManage.java‎

Lines changed: 17 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -8,11 +8,13 @@
88
import ai.chat2db.spi.sql.SQLExecutor;
99
import org.apache.commons.lang3.StringUtils;
1010

11-
import java.sql.*;
11+
import java.sql.Connection;
12+
import java.sql.ResultSet;
13+
import java.sql.SQLException;
14+
import java.sql.Statement;
1215
import java.util.ArrayList;
13-
import java.util.Objects;
1416

15-
import static ai.chat2db.plugin.postgresql.consts.SQLConst.*;
17+
import static ai.chat2db.plugin.postgresql.consts.SQLConst.ENUM_TYPE_DDL_SQL;
1618

1719
public class PostgreSQLDBManage extends DefaultDBManage implements DBManage {
1820

@@ -25,17 +27,18 @@ public void exportDatabase(Connection connection, String databaseName, String sc
2527
}
2628

2729
private void exportTypes(Connection connection, AsyncContext asyncContext) throws SQLException {
28-
try (ResultSet resultSet = connection.createStatement().executeQuery(ENUM_TYPE_DDL_SQL)) {
29-
while (resultSet.next()) {
30-
StringBuilder sqlBuilder = new StringBuilder();
31-
sqlBuilder.append(resultSet.getString("ddl")).append("\n");
32-
asyncContext.write(sqlBuilder.toString());
33-
}
30+
try (ResultSet resultSet = connection.createStatement().executeQuery(ENUM_TYPE_DDL_SQL)) {
31+
while (resultSet.next()) {
32+
StringBuilder sqlBuilder = new StringBuilder();
33+
sqlBuilder.append(resultSet.getString("ddl")).append("\n");
34+
asyncContext.write(sqlBuilder.toString());
35+
}
3436
}
3537
}
38+
3639
private void exportTables(Connection connection, String databaseName, String schemaName, AsyncContext asyncContext) throws SQLException {
3740
try (ResultSet resultSet = connection.getMetaData().getTables(databaseName, schemaName, null,
38-
new String[]{"TABLE", "SYSTEM TABLE","PARTITIONED TABLE"})) {
41+
new String[]{"TABLE", "SYSTEM TABLE", "PARTITIONED TABLE"})) {
3942
ArrayList<String> tableNames = new ArrayList<>();
4043
while (resultSet.next()) {
4144
String tableName = resultSet.getString("TABLE_NAME");
@@ -46,14 +49,14 @@ private void exportTables(Connection connection, String databaseName, String sch
4649
}
4750
if (asyncContext.isContainsData()) {
4851
for (String tableName : tableNames) {
49-
exportTableData(connection, schemaName, tableName, asyncContext);
52+
exportTableData(connection, databaseName, schemaName, tableName, asyncContext);
5053
}
5154
}
5255
}
5356
}
5457

5558
private void exportTable(Connection connection, String schemaName, String tableName, AsyncContext asyncContext) throws SQLException {
56-
String sql =String.format( "select pg_get_tabledef('%s','%s',true,'COMMENTS') as ddl;", schemaName,tableName);
59+
String sql = String.format("select pg_get_tabledef('%s','%s',true,'COMMENTS') as ddl;", schemaName, tableName);
5760
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
5861
if (resultSet.next()) {
5962
StringBuilder sqlBuilder = new StringBuilder();
@@ -65,10 +68,9 @@ private void exportTable(Connection connection, String schemaName, String tableN
6568
}
6669

6770

68-
6971
private void exportViews(Connection connection, String schemaName, AsyncContext asyncContext) throws SQLException {
7072

71-
String sql = String.format("SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = '%s'",schemaName);
73+
String sql = String.format("SELECT table_name, view_definition FROM information_schema.views WHERE table_schema = '%s'", schemaName);
7274
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
7375
while (resultSet.next()) {
7476
StringBuilder sqlBuilder = new StringBuilder();
@@ -82,7 +84,7 @@ private void exportViews(Connection connection, String schemaName, AsyncContext
8284

8385
private void exportFunctions(Connection connection, String schemaName, AsyncContext asyncContext) throws SQLException {
8486
String sql = String.format("SELECT proname, pg_get_functiondef(oid) AS function_definition FROM pg_proc " +
85-
"WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = '%s')", schemaName);
87+
"WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = '%s')", schemaName);
8688
try (ResultSet resultSet = connection.createStatement().executeQuery(sql)) {
8789
while (resultSet.next()) {
8890
StringBuilder sqlBuilder = new StringBuilder();

0 commit comments

Comments
 (0)