该代码段展示了如何通过JDBC连接获取达梦数据库的表结构（DDL）和插入数据的SQL语句。它首先建立数据库连接，然后获取表的元数据，包括主键和列信息，用于构建CREATETABLE语句和INSERTINTO语句。对于每个表，它遍历列并根据数据类型处理插入语句，同时处理了自增列和日期类型的特殊情况。最后，生成的DDL和数据语句被添加到列表中返回。
获取connection方法见上一篇：
注意：TODO中的说明，欢迎大家补充；
public List<String> getDmDatabaseDDLAndInsert(String[] tables) {
StopWatch stopWatch = new StopWatch();
stopWatch.start();
List<String> ddls = new ArrayList<>();
List<String> insertList = new ArrayList<>();
Connection conn = null;
try {
conn = SQLUtilImpl.connection();
} catch (SQLException e) {
log.info("获取达梦数据库连接失败");
throw new RuntimeException(e);
}
try {
// 加载达梦数据库的JDBC驱动
String schema = conn.getSchema();
// 获取数据库的元数据
DatabaseMetaData metaData = conn.getMetaData();
Statement statement = conn.createStatement();
// 获取所有表
for (int i = 0; i < tables.length; i++) {
List<String> typeList = new ArrayList<>();
String tableName = tables[i];
ResultSet resultSet1 = statement.executeQuery("SELECT * FROM \"" + schema + "\".\"" + tableName + "\"");
String primaryKey = "";
String tableRemarks = "";
StringBuffer sql = new StringBuffer();
sql.append("\r\n");
sql.append("CREATE TABLE ")
.append("\"").append(schema.toUpperCase()).append("\"")
.append(".").append("\"").append(tableName.toUpperCase())
.append("\"").append("\r\n").append("(").append("\r\n");
ResultSet rs = metaData.getTables(null, schema, tableName, new String[]{"TABLE"});
if (rs.next()) {
tableRemarks = rs.getString("REMARKS");
}
ResultSet primaryKeys = metaData.getPrimaryKeys(null, schema, tableName);
if (primaryKeys.next()) {
primaryKey = primaryKeys.getString("COLUMN_NAME");
}
log.info("正在生成DDL和数据的表->{}", tableName);
ResultSet columns = metaData.getColumns(null, schema, tableName, null);
//有数的情况,第一次获取数据和DDL，之后不再获取DDL
boolean isAutoincrement = false;
if (resultSet1.next()) {
int num = 0;
ResultSet resultSet = statement.executeQuery("SELECT * FROM \"" + schema + "\".\"" + tableName + "\"");
while (resultSet.next()) {
if (columns.next()) {
isAutoincrement = "YES".equals(columns.getString("IS_AUTOINCREMENT"));
}
List<Object> date = new ArrayList<>();
StringBuffer colume = new StringBuffer();
if (num == 0 && isAutoincrement) {
colume.append("set identity_insert \"").append(schema).append("\".\"").append(tableName).append("\" on;\r\n");
}
colume.append("INSERT INTO \"").append(schema).append("\".\"").append(tableName).append("\"").append(" (");
// 重置columns指针
columns.beforeFirst();
//第二次循环限制sql执行
while (columns.next()) {
String val = columns.getString("COLUMN_NAME").toUpperCase();
String typeName = columns.getString("TYPE_NAME").toUpperCase();
typeList.add(typeName);
if (num == 0) {
sql.append("\"").append(columns.getString("COLUMN_NAME").toUpperCase()).append("\"").append(" ");
}
colume.append("\"").append(val).append("\",");
if ("INT".equalsIgnoreCase(columns.getString("TYPE_NAME")) || "BIGINT".equals(columns.getString("TYPE_NAME").toUpperCase()) || "TINYINT".equals(columns.getString("TYPE_NAME").toUpperCase()) || "SMALLINT".equals(columns.getString("TYPE_NAME").toUpperCase()) || "DECIMAL".equals(columns.getString("TYPE_NAME").toUpperCase())) {
if (num == 0) {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append(" ");
}
BigDecimal bigDecimal = resultSet.getBigDecimal(val);
date.add(bigDecimal);
} else if ("TIMESTAMP".equalsIgnoreCase(columns.getString("TYPE_NAME")) || "DATE".equals(columns.getString("TYPE_NAME").toUpperCase())) {
if (num == 0) {
sql.append("TIMESTAMP").append("(")
.append("0").append(") ");
}
Timestamp timestamp = resultSet.getTimestamp(val);
date.add(timestamp);
} else if ("DATETIME".equalsIgnoreCase(columns.getString("TYPE_NAME"))) {
if (num == 0) {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append("(")
.append("6").append(") ");
}
Timestamp timestamp = resultSet.getTimestamp(val);
date.add(timestamp);
} else {
if (num == 0) {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append("(")
.append(columns.getString("COLUMN_SIZE")).append(") ");
}
String value = resultSet.getString(val);
date.add(value);
}
//TODO:缺少自增列的初始值和自增值
// 无法获取到关联主键的信息:如NOT CLUSTER PRIMARY KEY("CLT_ID", "RULE_ID")) STORAGE(ON "AML", CLUSTERBTR) ;
// (只能先通过是否自增获取到主键，将自增的列设置为主键，其他的暂不设置主键，否则
// 会因无法生成关联主键导致数据导入失败)
if (num == 0) {
sql.append("YES".equals(columns.getString("IS_AUTOINCREMENT")) ? "IDENTITY " : "");
sql.append("0".equals(columns.getString("NULLABLE")) ? "NOT NULL " : "NULL ");
if (primaryKey.equals(columns.getString("COLUMN_NAME").toUpperCase()) && "YES".equals(columns.getString("IS_AUTOINCREMENT"))) {
sql.append("PRIMARY KEY ");
}
sql.append(StringUtils.isEmpty(columns.getString("REMARKS")) ? "" : "COMMENT '" + columns.getString("REMARKS") + "'").append(",").append("\r\n");
}
}
num++;
colume.deleteCharAt(colume.length() - 1).append(") VALUES (");
for (int j = 0; j < date.size(); j++) {
//此处使用toString会有报错情况
String string = null;
String str = ObjectUtils.toString(date.get(j), "");
if ("TIMESTAMP".equals(typeList.get(j)) || "DATA".equals(typeList.get(j))) {
string = StringUtils.isEmpty(str) ? "NULL" : "'" + String.valueOf(date.get(j)) + "'";
} else if ("VARCHAR".equals(typeList.get(j)) || "CHAR".equals(typeList.get(j)) || "TEXT".equals(typeList.get(j))) {
//处理数据中存在单引号的情况，将其进行转义
if (!StringUtils.isEmpty(str)&& date.get(j).toString().contains("'")) {
string = "'" + date.get(j).toString().replaceAll("'","''") + "'";
} else {
string = StringUtils.isEmpty(str) ? "NULL" : "'" + date.get(j).toString() + "'";
}
} else {
string = StringUtils.isEmpty(str) ? "NULL" : String.valueOf(date.get(j));
}
colume.append(string).append(j == date.size() - 1 ? ");\r\n " : " ,");
}
insertList.add(colume.toString());
}
if (isAutoincrement) {
insertList.add("set identity_insert \"" + schema + "\".\"" + tableName + "\" OFF; \r\n");
}
} else {
while (columns.next()) {
sql.append("\"").append(columns.getString("COLUMN_NAME").toUpperCase()).append("\"").append(" ");
if ("INT".equalsIgnoreCase(columns.getString("TYPE_NAME")) || "BIGINT".equals(columns.getString("TYPE_NAME").toUpperCase()) || "TINYINT".equals(columns.getString("TYPE_NAME").toUpperCase()) || "SMALLINT".equals(columns.getString("TYPE_NAME").toUpperCase())) {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append(" ");
} else if ("TIMESTAMP".equalsIgnoreCase(columns.getString("TYPE_NAME")) || "DATE".equals(columns.getString("TYPE_NAME").toUpperCase())) {
sql.append("TIMESTAMP").append("(")
.append("0").append(") ");
} else if ("DATETIME".equalsIgnoreCase(columns.getString("TYPE_NAME"))) {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append("(")
.append("6").append(") ");
} else {
sql.append(columns.getString("TYPE_NAME").toUpperCase()).append("(")
.append(columns.getString("COLUMN_SIZE")).append(") ");
}
//TODO:缺少自增列的初始值和自增值
sql.append("YES".equals(columns.getString("IS_AUTOINCREMENT")) ? "IDENTITY " : "");
sql.append("0".equals(columns.getString("NULLABLE")) ? "NOT NULL " : "NULL ");
if (primaryKey.equals(columns.getString("COLUMN_NAME").toUpperCase())) {
sql.append("PRIMARY KEY ");
}
sql.append(StringUtils.isEmpty(columns.getString("REMARKS")) ? "" : "COMMENT '" + columns.getString("REMARKS") + "'").append(",").append("\r\n");
}
}
//去除最后一个换行符之前的逗号，一个换行符两个字符
sql.deleteCharAt(sql.length() - 3);
sql.append(");").append("\r\n");
sql.append(StringUtils.isEmpty(tableRemarks) ? "" : "COMMENT ON TABLE \"" + schema.toUpperCase() + "\".\"" + tableName.toUpperCase() + "\" IS '" + tableRemarks + "';\r\n");
ddls.add(sql.toString());
}
ddls.addAll(insertList);
stopWatch.stop();
log.info("DDL和数据--获取完成--耗时：{}s", stopWatch.getTotalTimeSeconds());
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭数据库连接
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return ddls;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
