getCreateTableStmt

KwanChai

private String getCreateTableStmt(Connection oraConn, SqlRecord record)
throws Exception {
Statement stmt = oraConn.createStatement();
String sql = "select column_name,data_type,data_length,NULLABLE,data_scale from cols "
+ "where table_name='"
+ record.getTableName()
+ "' order by column_id";
ResultSet cols = stmt.executeQuery(sql);

StringBuffer resultSql = new StringBuffer();

// System.out.println(user+" :: "+tabName);

// HSQLDB不支持对象名中带$，将其替换为_S_
resultSql.append("create cached table "
+ record.getTableName().replaceAll("\\$", "_S_") + "\n(\n");

int colIndex = 0;
while (cols.next()) {
// 少量有问题的数据类型，要跳过去
if (cols.getString("data_type").equals("NVARCHAR2")
|| cols.getString("column_name").equals("COUNT")) {
continue;
}

if (0 != colIndex) {
resultSql.append(",\n");
}

resultSql.append("  " + cols.getString("column_name"));

if (cols.getString("data_type").equals("VARCHAR2")) {
resultSql.append(" VARCHAR(" + cols.getString("data_length")
+ ")");
} else if (cols.getString("data_type").equals("VARCHAR")
|| cols.getString("data_type").equals("CHAR")) {
resultSql.append(" " + cols.getString("data_type") + "("
+ cols.getString("data_length") + ")");
} else if (cols.getString("data_type").equals("NUMBER")) {
// 如果是整数（即小数位数为0）
cols.getInt("data_scale");
if (cols.wasNull()) {
resultSql.append(" NUMERIC");
} else if (cols.getInt("data_scale") == 0) {
resultSql.append(" BIGINT");
} else {
resultSql.append(" DECIMAL");
}
} else if (cols.getString("data_type").equals("DATE")) {
resultSql.append(" TIMESTAMP");
} else {
resultSql.append(" " + cols.getString("data_type"));
}

// 是否可以为NULL
if (cols.getString("NULLABLE").equals("N")) {
resultSql.append(" NOT NULL");
}

colIndex++;

}
resultSql.append("\n);\n");
stmt.close();

return resultSql.toString();
}