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();
}
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();
}