db2常见建表脚本生成
DB2数据库建表脚本生成程序，支持常见建表方式，参数暂不支持非表名称;
另此程序所用数据库连接方式为连接池，根据各自喜好使用连接池
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package move;
/**
*
* @author liuzf
*/
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import move.bean.ConstraintStatement;
import move.bean.FunctionStatement;
import move.bean.IndexStatement;
import move.bean.TriggerStatement;
import move.bean.ViewStatement;
import bass.DBManager;
import bass.util.dbbean;
public class Table {
private static Log log = LogFactory.getLog(Table.class);
private static DBManager dbm = DBManager.getInstance();
private static String poolName = "dfpool";
private String tabFullName = null;
private String tabSchema = null;
private String tabname = null;
private String tmpTabFullName = null;
private String tmpTabSchema = null;
private String tmpTabname = null;
private String newTbspace = null;
private String newindexTbspace = null;
public Table(String tableFullName)
{
this.tabFullName = tableFullName.trim().toUpperCase();
String[] arrayTemp = this.tabFullName.split("\.");
this.tabSchema = arrayTemp[0];
this.tabname = arrayTemp[1];
this.tmpTabFullName = this.tabFullName;
this.tmpTabSchema = this.tabSchema;
this.tmpTabname = this.tabname;
}
public Table(String tableFullName, String tmpTabFullName, String newTbspace, String newindexTbspace)
{
this.tabFullName = tableFullName.trim().toUpperCase();
String[] arrayTemp = this.tabFullName.split("\.");
this.tabSchema = arrayTemp[0];
this.tabname = arrayTemp[1];
this.tmpTabFullName = tmpTabFullName.trim().toUpperCase();
String[] arrayTemp2 = this.tmpTabFullName.split("\.");
this.tmpTabSchema = arrayTemp2[0];
this.tmpTabname = arrayTemp2[1];
this.newTbspace = newTbspace;
this.newindexTbspace = newindexTbspace;
}
/**
* 将类似pt.area的表全名，拆分成两个字段pt area，并全部转化为大写
*/
private String[] splitTableFullName(String tableFullName) {
tableFullName = tableFullName.trim().toUpperCase();
return tableFullName.split("\.");
}
/**
* 获取建表脚本，包括建表语句，表注释、字段注释
* @param tabFullName
* @return
*/
public ArrayList<String> getTableSqlList()
{
ArrayList<String> tableSqlList = new ArrayList<String>();
dbbean colQuery = new dbbean(Table.poolName);
dbbean tableQuery = new dbbean(Table.poolName);
// 表
String tableSql = "Select tbspace,index_tbspace,compression,partition_mode,pctfree,append_mode,locksize,volatile,remarks from syscat.tables where tabschema=""
+ this.tabSchema + "" and tabname="" + this.tabname + "" with ur";
//System.out.println(tableSql);
tableQuery.execute(tableSql);
if(tableQuery.getRowCount()>0)
{
//QHESOP.V_SYS_OP_LVL_AUTH 物化视图，建表方式不一样：Select * from syscat.tables where tabname in ("V_SYS_OP_LVL_AUTH","AREA") with ur
//syscat.tables type字段
//A = Alias
//G = Global temporary table
//H = Hierarchy table
//L = Detached table
//N = Nickname
//S = Materialized query table
//T = Table (untyped)
//U = Typed table
//V = View (untyped)
//W = Typed view
// 字段，自增字段
String sql = "Select colno,colname,typename,length,scale,default,nulls,logged,compact,partkeyseq,identity,generated from syscat.columns where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" order by colno with ur";
//System.out.println(sql);
colQuery.execute(sql);
// System.out.println(query.getRowCount());
StringBuffer sb = new StringBuffer("CREATE TABLE ").append(this.tmpTabFullName).append(" (
");
for (int i = 0; i < colQuery.getRowCount(); i++) {
//字段类型、字段长度
String typename = colQuery.getFieldValue("typename", i);
if (typename.equals("CHARACTER")) {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ").append("CHAR")
.append("(").append(colQuery.getFieldValue("length", i)).append(")");
} else if(typename.equals("VARCHAR")) {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ")
.append(colQuery.getFieldValue("typename", i))
.append("(")
.append(colQuery.getFieldValue("length", i))
.append(")");
} else if (colQuery.getFieldValue("typename", i).equals("DECIMAL")) {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ")
.append(colQuery.getFieldValue("typename", i))
.append("(")
.append(colQuery.getFieldValue("length", i))
.append(",").append(colQuery.getFieldValue("scale", i))
.append(")");
} else if (colQuery.getFieldValue("typename", i).equals("CLOB")) {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ")
.append(colQuery.getFieldValue("typename", i))
.append("(")
.append(colQuery.getFieldValue("length", i))
.append(")");
//logged
char[] logged = Table.getChar(colQuery.getFieldValue("logged", i));
if (logged.length > 0) {
switch (logged[0]) {
case "Y":
sb.append(" LOGGED ");
break;
case "N":
sb.append(" NOT LOGGED ");
break;
default:
sb.append("");
}
}
//compact
char[] compact = Table.getChar(colQuery.getFieldValue("compact", i));
if (compact.length > 0) {
switch (compact[0]) {
case "Y":
sb.append(" ");
break;
case "N":
sb.append(" NOT COMPACT ");
break;
default:
sb.append("");
}
}
} else if (colQuery.getFieldValue("typename", i).equals("BLOB")) {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ")
.append(colQuery.getFieldValue("typename", i))
.append("(")
.append(colQuery.getFieldValue("length", i))
.append(")");
//logged
char[] logged = Table.getChar(colQuery.getFieldValue("logged", i));
if (logged.length > 0) {
switch (logged[0]) {
case "Y":
sb.append(" LOGGED ");
break;
case "N":
sb.append(" NOT LOGGED ");
break;
default:
sb.append("");
}
}
//compact
char[] compact = Table.getChar(colQuery.getFieldValue("compact", i));
if (compact.length > 0) {
switch (compact[0]) {
case "Y":
sb.append(" ");
break;
case "N":
sb.append(" NOT COMPACT ");
break;
default:
sb.append("");
}
}
} else {
sb.append(" ").append(colQuery.getFieldValue("colname", i))
.append(" ")
.append(colQuery.getFieldValue("typename", i));
}
//是否非空
if (colQuery.getFieldValue("nulls", i).equals("N")) {
sb.append(" NOT NULL");
}
//缺省值
if (colQuery.getFieldValue("default", i).length() > 0) {
sb.append(" WITH DEFAULT ");
sb.append(colQuery.getFieldValue("default", i));
}
//自增字段
if (colQuery.getFieldValue("identity", i).equals("Y"))
{
sb.append(" GENERATED");
if (colQuery.getFieldValue("generated", i).equals("A"))
{
sb.append(" ALWAYS");
}
else if(colQuery.getFieldValue("generated", i).equals("D"))
{
sb.append(" BY DEFAULT");
}
else
{
sb.append(" ALWAYS");
}
sb.append(" AS IDENTITY (");
String identitySql = "Select colname, start, increment, minvalue, maxvalue, cycle, cache, order, nextcachefirstvalue, seqid from SYSCAT.COLIDENTATTRIBUTES where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" and colname=""
+ colQuery.getFieldValue("colname", i) +
"" with ur";
//System.out.println(identitySql);
dbbean identityQuery = new dbbean(Table.poolName);
identityQuery.execute(identitySql);
sb.append(" START WITH ").append(identityQuery.getFieldValue("start", 0));
sb.append(" INCREMENT BY ").append(identityQuery.getFieldValue("increment", 0));
sb.append(" MINVALUE ").append(identityQuery.getFieldValue("minvalue", 0));
sb.append(" MAXVALUE ").append(identityQuery.getFieldValue("maxvalue", 0));
if (identityQuery.getFieldValue("cycle", 0).equals("Y"))
{
sb.append(" CYCLE");
} else if(identityQuery.getFieldValue("cycle", 0).equals("N")){
sb.append(" NO CYCLE");
} else {
}
sb.append(" CACHE ").append(identityQuery.getFieldValue("cache", 0));
if (identityQuery.getFieldValue("order", 0).equals("Y"))
{
sb.append(" ORDER");
} else if(identityQuery.getFieldValue("order", 0).equals("N")){
sb.append(" NO ORDER");
} else {
}
sb.append(")");
}
sb.append(" ,
");
}
sb.deleteCharAt(sb.length() - 2);
sb.append(")
");
//是否压缩
char[] compress = Table.getChar(tableQuery.getFieldValue("compression", 0));
if (compress.length > 0) {
switch (compress[0]) {
case "B":
sb.append("COMPRESS YES VALUE COMPRESSION
");
break;
case "R":
sb.append("COMPRESS YES
");
break;
case "V":
sb.append("VALUE COMPRESSION
");
break;
default:
sb.append("");
}
}
// 分区键
String partitionSql = "Select colno,colname,partkeyseq from syscat.columns where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" and partkeyseq>0 order by partkeyseq with ur";
//System.out.println(partitionSql);
dbbean partitionQuery = new dbbean(Table.poolName);
partitionQuery.execute(partitionSql);
StringBuffer partitionSb = new StringBuffer();
for (int i = 0; i < partitionQuery.getRowCount(); i++) {
if (i > 0) {
partitionSb.append(", ");
partitionSb.append(partitionQuery.getFieldValue("colname", i));
} else
partitionSb.append(partitionQuery.getFieldValue("colname", i));
}
char[] partition_mode = Table.getChar(tableQuery.getFieldValue(
"partition_mode", 0));
if (partition_mode.length > 0) {
// System.out.println(partition_mode);
switch (partition_mode[0]) {
case "H":
sb.append("DISTRIBUTE BY HASH (");
sb.append(partitionSb);
sb.append(")
");
break;
case "R":
log.error("不支持");// Replicated across database
// partitions
break;
default:
sb.append("");
}
}
// 表空间
sb.append("IN ");
if( (this.newTbspace!=null) && (this.newTbspace.length()>0) )
{
sb.append(this.newTbspace);
if (this.newindexTbspace!=null && this.newindexTbspace.length()>0) {
sb.append(" INDEX IN ");
sb.append(this.newindexTbspace);
}
} else {
sb.append(tableQuery.getFieldValue("tbspace", 0));
if (tableQuery.getFieldValue("index_tbspace", 0).length() > 0) {
sb.append(" INDEX IN ");
sb.append(tableQuery.getFieldValue("index_tbspace", 0));
}
}
// 最后添加不记录日志？
sb.append(";
");
tableSqlList.add(sb.toString());
//PCTFREE
String pctfree = tableQuery.getFieldValue("pctfree", 0);
if (!pctfree.equals("-1")) {
StringBuffer pctfreeSb = new StringBuffer();
pctfreeSb.append("ALTER TABLE ").append(this.tmpTabFullName)
.append(" PCTFREE ").append(pctfree).append(";
");
tableSqlList.add(pctfreeSb.toString());
}
//APPEND ON
char[] append_mode = Table.getChar(tableQuery.getFieldValue("append_mode", 0));
if (append_mode.length > 0) {
switch (append_mode[0]) {
case "Y":
StringBuffer appendSb = new StringBuffer();
appendSb.append("ALTER TABLE ").append(this.tmpTabFullName).append(" APPEND ON;
");
tableSqlList.add(appendSb.toString());
break;
case "N":
break;
default:
}
}
//LOCKSIZE
char[] locksize = Table.getChar(tableQuery.getFieldValue("locksize", 0));
if (locksize.length > 0) {
switch (locksize[0]) {
case "T":
StringBuffer locksizeSb = new StringBuffer();
locksizeSb.append("ALTER TABLE ").append(this.tmpTabFullName).append(" LOCKSIZE TABLE;
");
tableSqlList.add(locksizeSb.toString());
break;
case "R":
break;
case "I":
break;
default:
}
}
//ALTER TABLE "NWH "."DW_AI_CS_OUTCALL_DM_200903" VOLATILE CARDINALITY;
char[] volatileChar = Table.getChar(tableQuery.getFieldValue("volatile", 0));
if (volatileChar.length > 0) {
switch (volatileChar[0]) {
case "C":
StringBuffer volatileSb = new StringBuffer();
volatileSb.append("ALTER TABLE ").append(this.tmpTabFullName).append(" VOLATILE CARDINALITY;
");
tableSqlList.add(volatileSb.toString());
break;
default:
}
}
//表注释
if(tableQuery.getFieldValue("remarks", 0).length()>0)
{
sb = new StringBuffer();
sb.append("COMMENT ON TABLE ");
sb.append(this.tmpTabFullName);
sb.append(" IS "");
sb.append(tableQuery.getFieldValue("remarks", 0).replaceAll(""", """"));
sb.append("";
");
tableSqlList.add(sb.toString());
}
// 字段注释
String remarkColSql = "Select tabschema,tabname,colname,remarks from syscat.columns where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" and remarks is not null order by partkeyseq with ur";
//System.out.println(remarkColSql);
dbbean remarkColQuery = new dbbean(Table.poolName);
remarkColQuery.execute(remarkColSql);
for (int i = 0; i < remarkColQuery.getRowCount(); i++) {
StringBuffer remarkColSb = new StringBuffer();
remarkColSb.append("COMMENT ON COLUMN ");
remarkColSb.append(this.tmpTabFullName).append(".");
remarkColSb.append(remarkColQuery.getFieldValue("colname", i));
remarkColSb.append(" IS "");
remarkColSb.append(remarkColQuery.getFieldValue("remarks", i).replaceAll(""", """"));
remarkColSb.append("";
");
tableSqlList.add(remarkColSb.toString());
}
}
return tableSqlList;
}
/**
* 主键 & 约束 等用alter体现的语句，不包括外键等涉及其他表的操作
* unique referential check
* @param tabFullName
* @return
*/
public ArrayList<ConstraintStatement> getPrimaryUniqueConstraintSqlList()
{
//Select tabschema,tabname,PARENTS,CHILDREN,SELFREFS,KEYUNIQUE,CHECKCOUNT from syscat.tables where tabname="STAT_REP_CONTENT" with ur;
//PARENTS 该表的父表数目（该表在其中充当子表的参照约束数目）
//CHILDREN 该表的子表数目（该表在其中充当父表的参照约束数目）
//SELFREFS 该表的自引用参照约束数目（该表在其中既充当父表又充当子表的参照约束数目）
//KEYUNIQUE 在该表上所定义的惟一约束（除了主键）的数目
//CHECKCOUNT 在该表上所定义的检查约束的数目
//判断表中是否存在约束：SYSCAT.TABCONST，惟一约束（U）、主键约束（P）、外键约束（F）或表检查约束（K）
//SYSCAT.KEYCOLUSE 为惟一、主键或外键约束定义的键中所包含的每个列包含一行记录
// 主键约束 & 唯一性约束
ArrayList<ConstraintStatement> constraintSqlList = new ArrayList<ConstraintStatement>();
String constraintSql = "Select * from SYSCAT.TABCONST where type in ("P","U") and tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" order by type with ur";
//System.out.println(constraintSql);
dbbean constraintQuery = new dbbean(Table.poolName);
constraintQuery.execute(constraintSql);
for (int i = 0; i < constraintQuery.getRowCount(); i++) {
ConstraintStatement constraintStmt = new ConstraintStatement();
StringBuffer constraintDropSb = new StringBuffer();
constraintDropSb.append("ALTER TABLE ").append(this.tabSchema).append(".").append(this.tabname).append("
");
StringBuffer constraintSb = new StringBuffer();
constraintSb.append("ALTER TABLE ");
constraintSb.append(this.tabFullName).append("
");
char[] uniquerule = Table.getChar(constraintQuery.getFieldValue("type", i));
String constname = constraintQuery.getFieldValue("constname", i);
constraintStmt.fullName = constname;
switch(uniquerule[0])
{
case "P":
constraintDropSb.append(" DROP PRIMARY KEY ").append(";");
if(constname.startsWith("SQL") && constname.length()==18)
{
constraintSb.append(" ADD PRIMARY KEY
");
}
else
{
constraintSb.append(" ADD CONSTRAINT ");
constraintSb.append(constname);
constraintSb.append(" PRIMARY KEY
");
}
break;
case "U":
constraintDropSb.append(" DROP UNIQUE ").append(constraintQuery.getFieldValue("constname", i)).append(";");
if(constname.startsWith("SQL") && constname.length()==18)
{
constraintSb.append(" ADD UNIQUE
");
}
else
{
constraintSb.append(" ADD CONSTRAINT ");
constraintSb.append(constname);
constraintSb.append(" UNIQUE
");
}
break;
default:
log.error("不支持");
}
constraintSb.append(" (");
constraintSb.append(this.getConstraintCol(constname,this.tabSchema,this.tabname));
constraintSb.append(");");
constraintStmt.dropSql = constraintDropSb.toString();
constraintStmt.createSql = constraintSb.toString();
constraintSqlList.add(constraintStmt);
}
return constraintSqlList;
}
/**
* 获取惟一、主键或外键约束定义的列
* SYSCAT.KEYCOLUSE 为惟一、主键或外键约束定义的键中所包含的每个列包含一行记录
* @param constname
* @return
*/
private String getConstraintCol(String constname, String tabSchema, String tabname)
{
String constraintColSql = "Select * from SYSCAT.KEYCOLUSE " +
"where constname="" + constname +
"" and tabschema="" + tabSchema +
"" and tabname="" + tabname +
"" order by colseq with ur";
//System.out.println(constraintColSql);
dbbean constraintColQuery = new dbbean(Table.poolName);
constraintColQuery.execute(constraintColSql);
StringBuffer constraintColSb = new StringBuffer();
for (int i = 0; i < constraintColQuery.getRowCount(); i++) {
if(i>0)
constraintColSb.append(", ").append(constraintColQuery.getFieldValue("colname", i));
else
constraintColSb.append(constraintColQuery.getFieldValue("colname", i));
}
return constraintColSb.toString();
}
/**
* 外键、检查等约束的语句
* @param tabFullName
* @return
*/
public ArrayList<ConstraintStatement> getForeignCheckConstraintSqlList()
{
//判断表中是否存在约束：SYSCAT.TABCONST，惟一约束（U）、主键约束（P）、外键约束（F）或表检查约束（K）
//Select tabschema,tabname,PARENTS,CHILDREN,SELFREFS,KEYUNIQUE,CHECKCOUNT from syscat.tables where tabname="STAT_REP_CONTENT" with ur;
//PARENTS 该表的父表数目（该表在其中充当子表的参照约束数目）
//CHILDREN 该表的子表数目（该表在其中充当父表的参照约束数目）
//SELFREFS 该表的自引用参照约束数目（该表在其中既充当父表又充当子表的参照约束数目）
//KEYUNIQUE 在该表上所定义的惟一约束（除了主键）的数目
//CHECKCOUNT 在该表上所定义的检查约束的数目
//外键
//SYSCAT.REFERENCES 目录视图中包含引用完整性（外键）约束信息。
ArrayList<ConstraintStatement> constraintSqlList = new ArrayList<ConstraintStatement>();
//SYSCAT.REFERENCES : Each row represents a referential integrity (foreign key) constraint.
String foreignKeySql = "Select constname, tabschema,tabname, reftabschema,reftabname, deleterule, updaterule, fk_colnames, pk_colnames from SYSCAT.REFERENCES " +
"where (tabschema="" + this.tabSchema + "" and tabname="" + this.tabname + "") "
+ "or "
+ "(reftabschema="" + this.tabSchema + "" and reftabname="" + this.tabname + "") "
+ "with ur";
//System.out.println(foreignKeySql);
dbbean foreignKeyQuery = new dbbean(Table.poolName);
foreignKeyQuery.execute(foreignKeySql);
for(int i=0; i<foreignKeyQuery.getRowCount(); i++) {
ConstraintStatement foreignKeyStmt = new ConstraintStatement();
foreignKeyStmt.fullName = foreignKeyQuery.getFieldValue("constname", i);
StringBuffer foreignKeyDropSb = new StringBuffer();
foreignKeyDropSb.append("ALTER TABLE ").append(foreignKeyQuery.getFieldValue("tabschema", i)).append(".").append(foreignKeyQuery.getFieldValue("tabname", i)).append("
");
foreignKeyDropSb.append(" DROP FOREIGN KEY ").append(foreignKeyQuery.getFieldValue("constname", i)).append(";");
foreignKeyStmt.dropSql = foreignKeyDropSb.toString();
StringBuffer foreignKeySb = new StringBuffer();
foreignKeySb.append("ALTER TABLE ").append(foreignKeyQuery.getFieldValue("tabschema", i)).append(".").append(foreignKeyQuery.getFieldValue("tabname", i)).append("
");
foreignKeySb.append(" ADD CONSTRAINT ").append(foreignKeyQuery.getFieldValue("constname", i)).append(" FOREIGN KEY (");
String[] fk_colnames = foreignKeyQuery.getFieldValue("fk_colnames", i).split(" ");
for (int j = 0; j < fk_colnames.length; j++) {
if(fk_colnames[j].length()==0)
continue;
if(j==0)
foreignKeySb.append(fk_colnames[j]);
else
foreignKeySb.append(", ").append(fk_colnames[j]);
}
foreignKeySb.append(")
");
foreignKeySb.append(" REFERENCES ").append(foreignKeyQuery.getFieldValue("reftabschema", i))
.append(".").append(foreignKeyQuery.getFieldValue("reftabname", i));
foreignKeySb.append(" (");
String[] pk_colnames = foreignKeyQuery.getFieldValue("pk_colnames", i).split(" ");
for (int j = 0; j < pk_colnames.length; j++) {
if(pk_colnames[j].length()==0)
continue;
if(j==0)
foreignKeySb.append(pk_colnames[j]);
else
foreignKeySb.append(", ").append(pk_colnames[j]);
}
foreignKeySb.append(")
");
char[] deleterule = Table.getChar(foreignKeyQuery.getFieldValue("deleterule", i));
if (deleterule.length > 0) {
switch (deleterule[0]) {
case "A":
foreignKeySb.append(" ON DELETE NO ACTION
");
break;
case "C":
foreignKeySb.append(" ON DELETE CASCADE
");
break;
case "N":
foreignKeySb.append(" ON DELETE SET NULL
");
break;
case "R":
foreignKeySb.append(" ON DELETE RESTRICT
");
break;
default:
foreignKeySb.append("");
}
}
char[] updaterule = Table.getChar(foreignKeyQuery.getFieldValue("updaterule", i));
if (updaterule.length > 0) {
switch (updaterule[0]) {
case "A":
foreignKeySb.append(" ON UPDATE NO ACTION
");
break;
case "R":
foreignKeySb.append(" ON UPDATE RESTRICT
");
break;
default:
foreignKeySb.append("");
}
}
foreignKeySb.append(" ENFORCED
");//syscat.tabconst ENFORCED
foreignKeySb.append(" ENABLE QUERY OPTIMIZATION;");//syscat.tabconst ENABLEQUERYOPT
foreignKeyStmt.createSql = foreignKeySb.toString();
constraintSqlList.add(foreignKeyStmt);
}
//check约束
String checkSql = "Select constname,tabschema,tabname,text from SYSCAT.CHECKS " +
"where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" with ur";
//System.out.println(checkSql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = Table.dbm.getConnection(Table.poolName);
stmt = conn.createStatement();
rs = stmt.executeQuery(checkSql);
while(rs.next()) {
ConstraintStatement checkStmt = new ConstraintStatement();
checkStmt.fullName = rs.getString("constname");
StringBuffer checkDropSb = new StringBuffer();
checkDropSb.append("ALTER TABLE ").append(this.tabSchema).append(".").append(this.tabname).append("
");
checkDropSb.append(" DROP CHECK ").append(rs.getString("constname")).append(";");
checkStmt.dropSql = checkDropSb.toString();
StringBuffer checkSb = new StringBuffer();
checkSb.append("ALTER TABLE ").append(rs.getString("tabschema")).append(".").append(rs.getString("tabname")).append("
");
checkSb.append(" ADD CONSTRAINT ").append(rs.getString("constname")).append(" CHECK
");
Clob text_clob = rs.getClob("text");
String text_str = text_clob.getSubString( 1, (int)(text_clob.length()) );
checkSb.append(" (").append(text_str).append(")
");
checkSb.append(" ENFORCED").append("
");
checkSb.append(" ENABLE QUERY OPTIMIZATION").append(";
");
checkStmt.createSql = checkSb.toString();
constraintSqlList.add(checkStmt);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
dbbean checkQuery = new dbbean(Table.poolName);
checkQuery.execute(checkSql);
return constraintSqlList;
}
/**
* 索引
* @param tabFullName
* @return
*/
public ArrayList<IndexStatement> getIndexSqlList()
{
//索引
//SYSCAT.INDEXES 视图的 UNIQUERULE 列指示索引的特征。如果此列的值为 P，那么索引为主键；如果该值为 U，那么索引是唯一索引（但不是主键）。
ArrayList<IndexStatement> indexSqlList = new ArrayList<IndexStatement>();
String indexSql = "Select indschema, indname,tabschema,tabname,colnames,uniquerule,made_unique,pctfree,iid,reverse_scans from SYSCAT.INDEXES " +
"where tabschema=""
+ this.tabSchema
+ "" and tabname=""
+ this.tabname
+ "" and user_defined=1 order by iid with ur";
//+ "" and indname not in (Select constname from SYSCAT.TABCONST) order by iid with ur";
//"" and uniquerule<>"P" and indname not in (Select constname from SYSCAT.TABCONST) order by iid with ur";
//System.out.println(indexSql);
dbbean indexQuery = new dbbean(Table.poolName);
indexQuery.execute(indexSql);
for (int i = 0; i < indexQuery.getRowCount(); i++) {
String indname = indexQuery.getFieldValue("indname", i);
/**
if(indexQuery.getFieldValue("uniquerule", i).equals("P"))
{
//经测试，不管是不是sql开头的索引，只要uniquerule=P都不能够删除，但是db2look生成sql的时候，这些创建sql有些会生成
continue;
}
**/
IndexStatement indexStmt = new IndexStatement();
indexStmt.fullName = indexQuery.getFieldValue("indschema", i) + "." + indexQuery.getFieldValue("indname", i);
indexStmt.dropSql = "drop index " + indexStmt.fullName + ";";
StringBuffer indexSb = new StringBuffer();
char[] uniquerule = Table.getChar(indexQuery.getFieldValue("uniquerule", i));
indexSb.append("CREATE ");
/**
Unique rule.
D = Permits duplicates
U = Unique
P = Implements primary key
**/
switch(uniquerule[0])
{
case "D":
indexSb.append("INDEX ");
break;
case "U":
if(indexQuery.getFieldValue("indschema", i).startsWith("SYSIBM"))
{
//System.out.println("系统建立UNIQUE，归属UNIQUE，此处不予处理");//这样此处会报错
}
else
{
indexSb.append("UNIQUE INDEX ");
}
break;
case "P":
//if(indname.startsWith("SQL") && indname.length()==18)//此种索引考虑屏蔽掉，此类索引不能删除
char[] madeUnique = Table.getChar(indexQuery.getFieldValue("made_unique", i));
switch(madeUnique[0])
{
case "Y"://通过此种方式更好，存在一些不是SQL开头的照样没有unique修饰词
indexSb.append("INDEX ");
break;
case "N":
indexSb.append("UNIQUE INDEX ");
break;
default:
log.error("不支持" + madeUnique[0]);
}
break;
default:
log.error("不支持" + uniquerule[0]);
}
indexSb.append(indexQuery.getFieldValue("indschema", i));
indexSb.append(".");
indexSb.append(indexQuery.getFieldValue("indname", i));
indexSb.append(" ON ");
indexSb.append(this.tabSchema).append(".").append(this.tabname).append("
");
String indexColSql = "select indschema, indname, colname, colseq, colorder, collationschema, collationname from SYSCAT.INDEXCOLUSE " +
"where indschema="" +
indexQuery.getFieldValue("indschema", i) +
""and indname="" +
indexQuery.getFieldValue("indname", i) +
"" order by colseq with ur";
//System.out.println(indexColSql);
dbbean indexColQuery = new dbbean(Table.poolName);
indexColQuery.execute(indexColSql);
indexSb.append(" (");
for (int j = 0; j < indexColQuery.getRowCount(); j++) {
if(j!=0)
indexSb.append(", ");
indexSb.append(indexColQuery.getFieldValue("colname", j));
char[] colorder = Table.getChar(indexColQuery.getFieldValue("colorder", j));
switch(colorder[0])
{
case "A":
indexSb.append(" ASC");
break;
case "D":
indexSb.append(" DESC");
break;
case "I":
log.error("不支持的类型");
break;
default:
log.error("不支持的类型");
}
}
indexSb.append(")
");
int pctfree = Integer.parseInt(indexQuery.getFieldValue("pctfree", i));
if(pctfree!=-1)
{
indexSb.append(" PCTFREE ");
indexSb.append(pctfree);
indexSb.append(" ");
}
char[] reverse_scans = Table.getChar(indexQuery.getFieldValue("reverse_scans", i));
switch(reverse_scans[0])
{
case "Y":
indexSb.append("ALLOW REVERSE SCANS");
break;
case "N":
indexSb.append("DISALLOW REVERSE SCANS");
break;
default:
log.error("不支持的类型");
}
indexSb.append(";
");
indexStmt.createSql = indexSb.toString();
indexSqlList.add(indexStmt);
}
return indexSqlList;
}
/**
* 视图
* @param tabFullName
* @return
*/
public ArrayList<ViewStatement> getViewSqlList()
{
ArrayList<ViewStatement> viewSqlList = new ArrayList<ViewStatement>();
String viewSql = "Select a.viewschema, a.viewname, a.dtype, a.owner, a.btype, a.bschema, a.bname, a.tabauth, a.definer from SYSCAT.VIEWDEP a,SYSCAT.TABLES b " +
"where a.viewschema=b.tabschema and a.viewname=b.tabname and a.dtype="V" and a.bschema=""
+ this.tabSchema
+ "" and a.bname=""
+ this.tabname
+ "" order by b.create_time with ur";
//System.out.println(viewSql);
dbbean viewQuery = new dbbean(Table.poolName);
viewQuery.execute(viewSql);
for (int i = 0; i < viewQuery.getRowCount(); i++) {
viewSqlList.add( this.getViewDDL(viewQuery.getFieldValue("viewschema", i), viewQuery.getFieldValue("viewname", i)) );
}
return viewSqlList;
}
/**
* 获取建视图脚本
*/
public ViewStatement getViewDDL(String viewFullName)
{
String[] viewNameArray = this.splitTableFullName(viewFullName);
return this.getViewDDL(viewNameArray[0],viewNameArray[1]);
}
/**
* 获取建视图脚本
*/
public ViewStatement getViewDDL(String viewSchema, String viewName)
{
//ArrayList<String> viewSqlList = new ArrayList<String>();
String viewSql = "Select viewschema, viewname, owner, ownertype, seqno, viewcheck, readonly, valid, qualifier, func_path, text, definer from SYSCAT.VIEWS where viewschema=""
+ viewSchema
+ "" and viewname=""
+ viewName
+ "" with ur";
//System.out.println(viewSql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ViewStatement viewStatement = new ViewStatement();
viewStatement.fullName = viewSchema.trim() + "." + viewName.trim();
try {
conn = dbm.getConnection(Table.poolName);
stmt = conn.createStatement();
rs = stmt.executeQuery(viewSql);
while (rs.next()) {
Clob func_path_clob = rs.getClob("func_path");
Clob text_clob = rs.getClob("text");
String qualifier_str = rs.getString("qualifier");
String func_path_str = func_path_clob.getSubString( 1, (int)(func_path_clob.length()) );
String text_str = text_clob.getSubString( 1, (int)(text_clob.length()) );
viewStatement.schemaSql = "SET CURRENT SCHEMA = " + qualifier_str + ";";
viewStatement.pathSql = "SET CURRENT PATH = " + func_path_str.replaceAll(""", "") + ";";
viewStatement.createSql = text_str + ";";
viewStatement.dropSql = "drop view " + viewStatement.fullName + ";";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return viewStatement;
}
/**
* materialized query table 物化查询表
* @param tabFullName
* @return
*/
public ArrayList<ViewStatement> getMQTSqlList()
{
//相关视图，物化视图在此表关系中都可以体现，另外物化视图还有表注释等信息
ArrayList<ViewStatement> viewSqlList = new ArrayList<ViewStatement>();
String viewSql = "Select viewschema, viewname, dtype, owner, btype, bschema, bname, tabauth, definer from SYSCAT.VIEWDEP " +
"where dtype="S" and bschema=""
+ this.tabSchema
+ "" and bname=""
+ this.tabname
+ "" with ur";
//System.out.println(viewSql);
dbbean viewQuery = new dbbean(Table.poolName);
viewQuery.execute(viewSql);
for (int i = 0; i < viewQuery.getRowCount(); i++) {
viewSqlList.add( this.getMQTDDL(viewQuery.getFieldValue("viewschema", i), viewQuery.getFieldValue("viewname", i)) );
}
return viewSqlList;
}
/**
* 获取建物化视图脚本
*/
public ViewStatement getMQTDDL(String viewSchema, String viewName)
{
//ArrayList<String> viewSqlList = new ArrayList<String>();
String viewSql = "Select viewschema, viewname, owner, ownertype, seqno, viewcheck, readonly, valid, qualifier, func_path, text, definer from SYSCAT.VIEWS where viewschema=""
+ viewSchema
+ "" and viewname=""
+ viewName
+ "" with ur";
//System.out.println(viewSql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ViewStatement viewStatement = new ViewStatement();
viewStatement.fullName = viewSchema.trim() + "." + viewName.trim();
try {
conn = dbm.getConnection(Table.poolName);
stmt = conn.createStatement();
rs = stmt.executeQuery(viewSql);
while (rs.next()) {
Clob func_path_clob = rs.getClob("func_path");
Clob text_clob = rs.getClob("text");
String qualifier_str = rs.getString("qualifier");
String func_path_str = func_path_clob.getSubString( 1, (int)(func_path_clob.length()) );
String text_str = text_clob.getSubString( 1, (int)(text_clob.length()) );
viewStatement.schemaSql = "SET CURRENT SCHEMA = " + qualifier_str + ";";
viewStatement.pathSql = "SET CURRENT PATH = " + func_path_str.replaceAll(""", "") + ";";
viewStatement.createSql = text_str + ";";
viewStatement.dropSql = "drop table " + viewStatement.fullName + ";";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return viewStatement;
}
/**
* 获取建触发器脚本
* 暂未实现
*/
public ArrayList<TriggerStatement> getTriggerSqlList()
{
//Select * from SYSCAT.TRIGDEP with ur;
//Select * from SYSCAT.TRIGGERS with ur;
/**
* 目前只有整个仓库只有两张表有触发器，暂不考虑：
* "PT " "USER_GROUP_RELATION"
* "PT " "USER"
*/
ArrayList<TriggerStatement> triggerSqlList = new ArrayList<TriggerStatement>();
String triggerSql = "Select trigschema, trigname, btype, bschema, bname, tabauth from SYSCAT.TRIGDEP where bschema=""
+ this.tabSchema
+ "" and bname=""
+ this.tabname
+ "" with ur";
//System.out.println(viewSql);
dbbean triggerQuery = new dbbean(Table.poolName);
triggerQuery.execute(triggerSql);
for (int i = 0; i < triggerQuery.getRowCount(); i++) {
triggerSqlList.add( this.getTriggerDDL(triggerQuery.getFieldValue("trigschema", i), triggerQuery.getFieldValue("trigname", i)) );
}
return triggerSqlList;
}
/**
* 获取建触发器脚本
*/
public TriggerStatement getTriggerDDL(String trigSchema, String trigName)
{
//ArrayList<String> viewSqlList = new ArrayList<String>();
String trigSql = "Select trigschema,trigname,owner,ownertype,tabschema,tabname,trigtime,trigevent,func_path,qualifier,text from SYSCAT.TRIGGERS where trigschema=""
+ trigSchema
+ "" and trigname=""
+ trigName
+ "" with ur";
//System.out.println(viewSql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
TriggerStatement trigStatement = new TriggerStatement();
trigStatement.fullName = trigSchema.trim() + "." + trigName.trim();
trigStatement.dropSql = "DROP TRIGGER " + trigStatement.fullName + ";";
try {
conn = dbm.getConnection(Table.poolName);
stmt = conn.createStatement();
rs = stmt.executeQuery(trigSql);
while (rs.next()) {
Clob func_path_clob = rs.getClob("func_path");
Clob text_clob = rs.getClob("text");
String qualifier_str = rs.getString("qualifier");
String func_path_str = func_path_clob.getSubString( 1, (int)(func_path_clob.length()) );
String text_str = text_clob.getSubString( 1, (int)(text_clob.length()) );
trigStatement.schemaSql = "SET CURRENT SCHEMA = " + qualifier_str + ";";
trigStatement.pathSql = "SET CURRENT PATH = " + func_path_str.replaceAll(""", "") + ";";
trigStatement.createSql = text_str + ";";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return trigStatement;
}
/**
* 获取引用表函数脚本
*
*/
public ArrayList<FunctionStatement> getFunctionSqlList()
{
//Select * from SYSCAT.FUNCDEP with ur;
//Select * from SYSCAT.FUNCTIONS with ur;
//Select * from SYSCAT.ROUTINES with ur;
//Select * from SYSCAT.ROUTINEDEP with ur;
//Select * from SYSCAT.ROUTINEPARMS with ur;
ArrayList<FunctionStatement> funcSqlList = new ArrayList<FunctionStatement>();
// String funcSql = "Select routineschema, specificname, btype, bschema, bname, tabauth, routinename from SYSCAT.ROUTINES where bschema=""
// + this.tabSchema
// + "" and bname=""
// + this.tabname
// + "" with ur";
String funcSql = "Select funcschema, funcname, btype, bschema, bname, tabauth from SYSCAT.FUNCDEP where bschema=""
+ this.tabSchema
+ "" and bname=""
+ this.tabname
+ "" with ur";
//System.out.println(viewSql);
dbbean funcQuery = new dbbean(Table.poolName);
funcQuery.execute(funcSql);
for (int i = 0; i < funcQuery.getRowCount(); i++) {
funcSqlList.add( this.getFunctionDDL(funcQuery.getFieldValue("funcschema", i), funcQuery.getFieldValue("funcname", i)) );
}
return funcSqlList;
}
/**
* 获取建函数脚本
*/
public FunctionStatement getFunctionDDL(String routineSchema, String routineName)
{
//ArrayList<String> viewSqlList = new ArrayList<String>();
String funcSql = "Select routineschema,routinename,routinetype,specificname,func_path,qualifier,text from SYSCAT.ROUTINES where routinetype="F" and routineschema=""
+ routineSchema
+ "" and specificname=""
+ routineName
+ "" with ur";
//System.out.println(viewSql);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
FunctionStatement funcStatement = new FunctionStatement();
funcStatement.fullName = routineSchema.trim() + "." + routineName.trim();
funcStatement.dropSql = "DROP SPECIFIC FUNCTION " + funcStatement.fullName + ";";
try {
conn = dbm.getConnection(Table.poolName);
stmt = conn.createStatement();
rs = stmt.executeQuery(funcSql);
while (rs.next()) {
Clob func_path_clob = rs.getClob("func_path");
Clob text_clob = rs.getClob("text");
String qualifier_str = rs.getString("qualifier");
String func_path_str = func_path_clob.getSubString( 1, (int)(func_path_clob.length()) );
String text_str = text_clob.getSubString( 1, (int)(text_clob.length()) );
funcStatement.schemaSql = "SET CURRENT SCHEMA = " + qualifier_str + ";";
funcStatement.pathSql = "SET CURRENT PATH = " + func_path_str.replaceAll(""", "") + ";";
funcStatement.createSql = text_str + ";";
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return funcStatement;
}
/**
*
* @param sql
* @return
*/
public boolean createTable(String tabFullName) {
// 创建表，如果存在但没有数据，则删除重建，否则报异常处理
dbbean createBean = new dbbean(Table.poolName);
createBean.setUpdateFlag(true);
return createBean.execute("");
}
public static char[] getChar(String str) {
if ((str == null) || (str.length() == 0))
return new char[0];
else
return str.toCharArray();
}
/**
* 主函数，测试
*
* @param args
*/
public static void main(String[] args) {
Table ob = new Table("NWH.DEPART","NWH.DEPART",null,null);
//ob.getTableSql("QHESOP.EXPLAIN_STATEMENT");
ArrayList<String> list = ob.getTableSqlList();
//ArrayList<String> list = ob.getPrimaryUniqueConstraintSqlList();
//ArrayList<String> list = ob.getForeignCheckConstraintSqlList();
//ArrayList<String> list = ob.getIndexSqlList();
for (String str : list) {
System.out.println(str);
// for (char c : str.toCharArray()) {
// System.out.print(c);
// System.out.print("----");
// System.out.println((int)c);
// }
// System.out.println("------------------------");
// for (byte b : str.getBytes()) {
// System.out.println(b);
// }
// System.out.println("------------------------");
// String pstr = "(.|
|
)*;\s*";
// Pattern p = Pattern.compile(pstr);
// Matcher m = p.matcher(str);
// while(m.find())
// System.out.println("gggg----" + m.group());
//
// System.out.println(Pattern.matches(pstr, str));
//System.out.println("sql=" + str);
//System.out.println("tightSql=" + str.replaceAll("\s|"", ""));
}
ArrayList<ConstraintStatement> primaryUniqueConstraintList = ob.getPrimaryUniqueConstraintSqlList();
for (ConstraintStatement str : primaryUniqueConstraintList) {
System.out.println(str);
}
ArrayList<ConstraintStatement> foreignCheckConstraintSqlList = ob.getForeignCheckConstraintSqlList();
for (ConstraintStatement str : foreignCheckConstraintSqlList) {
System.out.println(str);
}
ArrayList<IndexStatement> indexList = ob.getIndexSqlList();
for (IndexStatement str : indexList) {
System.out.println(str);
}
ArrayList<ViewStatement> viewList = ob.getViewSqlList();
for (ViewStatement str : viewList) {
System.out.println(str);
}
ArrayList<FunctionStatement> funcList = ob.getFunctionSqlList();
for (FunctionStatement str : funcList) {
System.out.println(str);
}
ArrayList<TriggerStatement> trigList = ob.getTriggerSqlList();
for (TriggerStatement str : trigList) {
System.out.println(str);
}
ArrayList<ViewStatement> mqtList = ob.getMQTSqlList();
for (ViewStatement str : mqtList) {
System.out.println(str);
}
// ob.createTable("PT.USER");
// char[] cc = new char[0];
// System.out.println(cc[0]);
// ob.createTable("NWH.ACCT_ITEM_200709");
}
}
package move.bean;
public class ConstraintStatement extends StatementBean {
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.createSql).append("
");
sb.append(" ").append(this.dropSql).append("
");
return sb.toString();
}
}
package move.bean;
public class FunctionStatement extends StatementBean {
//public String fullName = null;
public String schemaSql = null;
public String pathSql = null;
//public String createSql = null;
//public String dropSql = null;
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.schemaSql).append("
");
sb.append(" ").append(this.pathSql).append("
");
sb.append(" ").append(this.createSql).append("
");
return sb.toString();
}
}
package move.bean;
public class IndexStatement extends StatementBean {
//public String indexFullName = null;
public String createSql = null;
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.createSql).append("
");
sb.append(" ").append(this.dropSql).append("
");
return sb.toString();
}
}
package move.bean;
public class StatementBean {
public String fullName = null;
public String createSql = null;
public String dropSql = null;
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.createSql).append("
");
sb.append(" ").append(this.dropSql).append("
");
return sb.toString();
}
}
package move.bean;
public class TriggerStatement extends StatementBean {
//public String triggerFullName = null;
public String schemaSql = null;
public String pathSql = null;
//public String createSql = null;
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.schemaSql).append("
");
sb.append(" ").append(this.pathSql).append("
");
sb.append(" ").append(this.createSql).append("
");
sb.append(" ").append(this.dropSql).append("
");
return sb.toString();
}
}
package move.bean;
public class ViewStatement extends StatementBean {
//public String viewFullName = null;
public String schemaSql = null;
public String pathSql = null;
//public String createSql = null;
public String toString()
{
StringBuffer sb = new StringBuffer();
sb.append(this.fullName).append("
");
sb.append(" ").append(this.schemaSql).append("
");
sb.append(" ").append(this.pathSql).append("
");
sb.append(" ").append(this.createSql).append("
");
sb.append(" ").append(this.dropSql).append("
");
return sb.toString();
}
}
声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
- 上一篇： 构架Hadoop集群
- 下一篇： 区域设置和代码页
