Skip to content

Commit e9fcac4

Browse files
pdxzzzpdxzzz
committed
oracle schema export
1 parent ecec0f4 commit e9fcac4

1 file changed

Lines changed: 159 additions & 3 deletions

File tree

  • chat2db-server/chat2db-plugins/chat2db-oracle/src/main/java/ai/chat2db/plugin/oracle

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

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

3-
import java.sql.Connection;
4-
import java.sql.SQLException;
5-
63
import ai.chat2db.spi.DBManage;
74
import ai.chat2db.spi.jdbc.DefaultDBManage;
85
import ai.chat2db.spi.sql.Chat2DBContext;
@@ -11,7 +8,166 @@
118
import org.apache.commons.lang3.ObjectUtils;
129
import org.apache.commons.lang3.StringUtils;
1310

11+
import java.sql.*;
12+
import java.util.Objects;
13+
1414
public class OracleDBManage extends DefaultDBManage implements DBManage {
15+
public String exportDatabase(Connection connection, String databaseName, String schemaName, boolean containData) throws SQLException {
16+
StringBuilder sqlBuilder = new StringBuilder();
17+
exportTables(connection, databaseName, sqlBuilder, containData);
18+
exportViews(connection, sqlBuilder, databaseName);
19+
exportProcedures(connection, databaseName, sqlBuilder);
20+
exportTriggers(connection, databaseName, sqlBuilder);
21+
exportFunctions(connection, databaseName, sqlBuilder);
22+
return sqlBuilder.toString();
23+
}
24+
25+
private void exportTables(Connection connection, String databaseName, StringBuilder sqlBuilder, boolean containData) throws SQLException {
26+
try (Statement statement = connection.createStatement(); ResultSet tables = statement.executeQuery("SELECT TABLE_NAME FROM USER_TABLES")) {
27+
while (tables.next()) {
28+
String tableName = tables.getString("TABLE_NAME");
29+
exportTable(connection, databaseName, tableName, sqlBuilder, containData);
30+
}
31+
}
32+
}
33+
34+
35+
private void exportTable(Connection connection, String databaseName, String tableName, StringBuilder sqlBuilder, boolean containData) throws SQLException {
36+
String createTableSql = "SELECT DBMS_METADATA.GET_DDL('TABLE', table_name) FROM all_tables WHERE owner = '" + databaseName + "' AND table_name = '" + tableName + "'";
37+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(createTableSql)) {
38+
if (resultSet.next()) {
39+
sqlBuilder.append("DROP TABLE ").append(databaseName).append(".").append(tableName).append(";").append(resultSet.getString(1)).append(";").append("\n");
40+
}
41+
exportTableComments(connection, tableName, sqlBuilder);
42+
exportTableColumnsComments(connection, tableName, sqlBuilder);
43+
if (containData) {
44+
exportTableData(connection, tableName, sqlBuilder);
45+
}
46+
}
47+
}
48+
49+
private void exportTableComments(Connection connection, String tableName, StringBuilder sqlBuilder) throws SQLException {
50+
String tableCommentSql = "SELECT 'COMMENT ON TABLE ' || table_name || ' IS ''' || comments || ''';' AS table_comment_ddl FROM user_tab_comments WHERE table_name = '" + tableName + "'";
51+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(tableCommentSql)) {
52+
if (resultSet.next()) {
53+
sqlBuilder.append(resultSet.getString(1)).append("\n");
54+
}
55+
}
56+
sqlBuilder.append("\n");
57+
}
58+
59+
private void exportTableColumnsComments(Connection connection, String tableName, StringBuilder sqlBuilder) throws SQLException {
60+
String tableColumnsCommentSql = "SELECT 'COMMENT ON COLUMN ' || table_name || '.' || column_name || ' IS ''' || comments || ''';' AS column_comment_ddl " +
61+
"FROM user_col_comments " +
62+
"WHERE table_name = '" + tableName + "' " +
63+
"AND comments IS NOT NULL";
64+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(tableColumnsCommentSql)) {
65+
while (resultSet.next()) {
66+
sqlBuilder.append(resultSet.getString(1)).append("\n");
67+
}
68+
}
69+
sqlBuilder.append("\n");
70+
}
71+
72+
private void exportTableData(Connection connection, String tableName, StringBuilder sqlBuilder) throws SQLException {
73+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM " + tableName)) {
74+
ResultSetMetaData metaData = resultSet.getMetaData();
75+
int columnCount = metaData.getColumnCount();
76+
while (resultSet.next()) {
77+
StringBuilder insertSql = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
78+
for (int i = 1; i <= columnCount; i++) {
79+
String columnValue = resultSet.getString(i);
80+
if (Objects.isNull(columnValue)) {
81+
insertSql.append("NULL");
82+
} else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DATE")) {
83+
// 处理日期值格式
84+
columnValue = "TO_DATE('" + columnValue + "', 'YYYY-MM-DD HH24:MI:SS')";
85+
insertSql.append(columnValue);
86+
} else {
87+
insertSql.append("'").append(columnValue).append("'");
88+
}
89+
if (i < columnCount) {
90+
insertSql.append(", ");
91+
}
92+
}
93+
insertSql.append(");\n");
94+
sqlBuilder.append(insertSql);
95+
}
96+
}
97+
}
98+
99+
private void exportViews(Connection connection, StringBuilder sqlBuilder, String databaseName) throws SQLException {
100+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT VIEW_NAME FROM USER_VIEWS")) {
101+
while (resultSet.next()) {
102+
String viewName = resultSet.getString(1);
103+
exportView(connection, sqlBuilder, databaseName, viewName);
104+
}
105+
}
106+
}
107+
108+
private void exportView(Connection connection, StringBuilder sqlBuilder, String databaseName, String viewName) throws SQLException {
109+
String createViewSql = "SELECT DBMS_METADATA.GET_DDL('VIEW', view_name) FROM all_views WHERE owner = '" + databaseName + "'" + "AND view_name = '" + viewName + "'";
110+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(createViewSql)) {
111+
if (resultSet.next()) {
112+
sqlBuilder.append(resultSet.getString(1)).append(";").append("\n");
113+
}
114+
}
115+
}
116+
117+
private void exportProcedures(Connection connection, String databaseName, StringBuilder sqlBuilder) throws SQLException {
118+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT object_name FROM all_procedures where OWNER = '" + databaseName + "' and OBJECT_TYPE='PROCEDURE'")) {
119+
while (resultSet.next()) {
120+
String procedureName = resultSet.getString(1);
121+
exportProcedure(connection, databaseName, procedureName, sqlBuilder);
122+
}
123+
}
124+
}
125+
126+
private void exportProcedure(Connection connection, String databaseName, String procedureName, StringBuilder sqlBuilder) throws SQLException {
127+
String createProcedureSql = "SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name) FROM all_procedures WHERE owner = '" + databaseName + "' AND object_name = '" + procedureName + "'";
128+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(createProcedureSql)) {
129+
if (resultSet.next()) {
130+
sqlBuilder.append(resultSet.getString(1)).append("\n");
131+
}
132+
}
133+
}
134+
135+
private void exportTriggers(Connection connection, String databaseName, StringBuilder sqlBuilder) throws SQLException {
136+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT TRIGGER_NAME FROM all_triggers where OWNER='" + databaseName + "'")) {
137+
while (resultSet.next()) {
138+
String triggerName = resultSet.getString(1);
139+
exportTrigger(connection, databaseName, triggerName, sqlBuilder);
140+
}
141+
}
142+
}
143+
144+
private void exportTrigger(Connection connection, String databaseName, String triggerName, StringBuilder sqlBuilder) throws SQLException {
145+
String createTriggerSql = "SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name) AS trigger_ddl FROM all_triggers WHERE owner = '" + databaseName + "' AND trigger_name = '" + triggerName + "'";
146+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(createTriggerSql)) {
147+
if (resultSet.next()) {
148+
sqlBuilder.append(resultSet.getString(1)).append(";").append("\n");
149+
}
150+
}
151+
}
152+
153+
private void exportFunctions(Connection connection, String databaseName, StringBuilder sqlBuilder) throws SQLException {
154+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION'")) {
155+
while (resultSet.next()) {
156+
String functionName = resultSet.getString(1);
157+
exportFunction(connection, databaseName, functionName, sqlBuilder);
158+
}
159+
}
160+
}
161+
162+
private void exportFunction(Connection connection, String databaseName, String functionName, StringBuilder sqlBuilder) throws SQLException {
163+
String createFunctionSql ="SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name) FROM all_procedures WHERE owner = '"+databaseName+"' AND object_name = '"+functionName+"'";
164+
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(createFunctionSql)) {
165+
if (resultSet.next()) {
166+
sqlBuilder.append(resultSet.getString(1)).append("\n");
167+
}
168+
}
169+
}
170+
15171

16172
@Override
17173
public void connectDatabase(Connection connection, String database) {

0 commit comments

Comments
 (0)