根据上一篇中的TableKind类型可以写相应的show table 语句。
TableKind | 类型 | show语句 |
---|---|---|
T | SET Table | show table 表名; |
O | MULTISET Table | show table 表名; |
V | View | show view 视图名; |
M | Macro | show macro 名; |
P | SQL Procedure | show procedure 名; |
E | External Stored Procedure | show procedure 名; |
D | JAR | |
R | Table function | show function 名; |
F | Standard function | show function 名; |
G | TRIGGER | SHOW TRIGGER trigger_name; |
I | Join Index | SHOW JOIN INDEX join_index_name; |
N | Hash Index | SHOW HASH INDEX hash_index_name; |
其他 |
下面是一段示例代码,代码实现使用show语句获取各种Table(Object)的DDL(Create语句等)以及对应的Drop语句:
/**
*
* @param TableName : The name of Production DB's table
* @param TableKind : The kind of table
* @return
* @throws SQLException
*/
private String showTable(String TableName, String TableKind)
throws SQLException {
String sqlCREATE = "";
PreparedStatement ps = null;
ResultSet rs = null;
String sql = null;
switch(TableKind) {
case "T":
sql = CommonConfig.sqlShowTable + "\"" + TableName + "\"";
this.sqlListDrop.add(CommonConfig.sqlDropTable +
"\"" + TableName + "\"");
break;
case "V":
sql = CommonConfig.sqlShowView + "\"" + TableName + "\"";
this.sqlListDrop.add(CommonConfig.sqlDropView +
"\"" + TableName + "\"");
break;
case "M":
sql = CommonConfig.sqlShowMacro + "\"" + TableName + "\"";
this.sqlListDrop.add(CommonConfig.sqlDropMacro +
"\"" + TableName + "\"");
break;
case "P":
case "E":
sql = CommonConfig.sqlShowProcedure + "\"" + TableName + "\"";
this.sqlListDrop.add(CommonConfig.sqlDropProcedure +
"\"" + TableName + "\"");
break;
case "D":
logger.info(" -- TableKind is D, SKIP. -- ");
break;
case "R":
case "F":
sql = CommonConfig.sqlShowFunction + "\"" + TableName + "\"";
this.sqlListDrop.add(CommonConfig.sqlDropFunction +
"\"" + TableName + "\"");
break;
default:
break;
}
logger.info(sql);
if(sql == null) return null;
ps = conn.prepareStatement(sql);
//logger.info(sql);
rs = ps.executeQuery();
while(rs.next()) {
// Be careful, replace = with +=
sqlCREATE += rs.getString(1);
}
// Remove the DB name in the CREATE statement
sqlCREATE = sqlCREATE.replace(DBConn.getDatabase() + ".", "");
sqlCREATE = sqlCREATE.replace(DBConn.getDatabase().toLowerCase() + ".", "");
sqlCREATE = sqlCREATE.replace(DBConn.getDatabase().toUpperCase() + ".", "");
sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase() + "\".", "");
sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase().toLowerCase() + "\".", "");
sqlCREATE = sqlCREATE.replace("\"" + DBConn.getDatabase().toUpperCase() + "\".", "");
rs.close();
ps.close();
return sqlCREATE;
}
注意:
1. CommonConfig.sqlShowTable是字符串"show table ",其他类似。
2. 最值得注意的情况是
sqlCREATE += rs.getString(1);
这句必须使用+=,而不能使用=;因为存在show procedure的结果集(ResultSet)有可能是当作多行返回的。即如下图的情况(使用=时的返回值):
INFO [main] (DDLTransfer.java:93) - show procedure "wikiproc"
**************************************
replace procedure
**************************************
wikiproc
**************************************
(IN RUNID INTEGER)
dynamic result sets 1
main:begin
declare cur_report cursor with return only for
sel *
from pct_run r
where r.run_id=RUNID;
open cur_report;
end;
每行用一行星号隔开,可见返回了三行。