根据java数据源生成建表ddl

package com.cn.xwy.wangk.db.util;

002  

003import java.io.File;

004import java.io.FileWriter;

005import java.io.IOException;

006import java.io.PrintWriter;

007import java.sql.Connection;

008import java.sql.DatabaseMetaData;

009import java.sql.DriverManager;

010import java.sql.ResultSet;

011import java.sql.SQLException;

012  

013import org.apache.commons.lang.StringUtils;

014  

015/**

016 * 

017 * @author Administrator

018 * 

019 */

020public class Db2ddl {

021    publicstatic booleanscwj(String path, String FileName, String body) {

022        try{

023            File f =new File(path);

024            f.mkdirs();

025            path = path +"\\" + FileName;

026            f =new File(path);

027            PrintWriter out;

028            out =new PrintWriter(newFileWriter(f));

029            out.print(body +"\n");

030            out.close();

031        }catch (IOException e) {

032            e.printStackTrace();

033        }catch (Exception e) {

034            e.printStackTrace();

035        }

036        returnfalse;

037    }

038  

039    publicConnection getconConnection(){

040        try{

041            Class.forName("net.sourceforge.jtds.jdbc.Driver");

042            returnDriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/adwatch","sa","123456");       

043        }catch (ClassNotFoundException e) {

044            e.printStackTrace();

045        }catch (SQLException e) {

046            e.printStackTrace();

047        }

048        returnnull;

049    }

050      

051    /**

052     * ALTER TABLE table_name ADD INDEX index_name (column_list)

053     * ALTER TABLE table_name ADD UNIQUE (column_list)

054     * ALTER TABLE table_name ADD PRIMARY KEY (column_list)

055     */

056    publicvoid db2ddl(){

057        Connection conn =null;

058        try{

059            //DB2 com.ibm.db2.jcc.DB2Driver "jdbc:db2://localhost:50000/abc", "db2admin","123456"

060            //MYSQL com.mysql.jdbc.Driver "jdbc:mysql://localhost:3306/manager","root","root"

061            //MSSQLSERVER net.sourceforge.jtds.jdbc.Driver "jdbc:jtds:sqlserver://localhost:1433/abc","sa","123456"

062            conn = getconConnection();

063            DatabaseMetaData odmd = conn.getMetaData();

064//          String[] types = { "TABLE" };// 数组变量types

065            ResultSet rs = odmd.getTables(null,null, null,null);

066//          odmd.getMaxTableNameLength()

067            StringBuffer sql =new StringBuffer();

068            intcounti=1;

069            while(rs.next()) {

070                // 取表名

071                String Tablename = rs.getString(3);

072                if(StringUtils.equalsIgnoreCase(rs.getString(4),"TABLE")

073//      && StringUtils.equalsIgnoreCase(rs.getString(2),"abc")

074//      &&StringUtils.equalsIgnoreCase(rs.getString(3), "ADDetail")

075                ) {

076                    System.out.println(counti+"-"+Tablename);

077                    String commnt ="";

078                    String indexu ="";

079                    ResultSet pkRSet = odmd.getPrimaryKeys(null,null,Tablename);

080                    ResultSet rscol = odmd.getColumns(null,null,Tablename,null);

081                    ResultSet inset = odmd.getIndexInfo(null,null, Tablename,false ,true);

082                    String colstr ="";

083                    while(rscol.next()) {

084                        String ColumnName = rscol.getString(4);

085                        String ColumnTypeName = rscol.getString(6);

086                        String REMARKS  = rscol.getString(12);

087                        if(StringUtils.isNotBlank(REMARKS)){

088                            commnt = commnt+"COMMENT ON "+Tablename+" ( "+ColumnName+" IS '"+REMARKS+"' ); \n";

089                        }

090                        while(inset.next()){

091                            if(inset.getInt(7)==DatabaseMetaData.tableIndexOther){

092                                 indexu = indexu+"CREATE  UNIQUE  INDEX "+inset.getString(6)+" ON "+inset.getString(5)+"("+inset.getString(9)+");\n";

093                            }

094                            System.out.println();

095                        }

096                          

097                        intdisplaySize = rscol.getInt(7);

098                        intscale = rscol.getInt(9);

099                        // int Precision = displaySize-scale;

100                        if(StringUtils.isNotBlank(colstr)){

101                            colstr = colstr+",\n";

102                        }

103                        colstr =colstr+"\t"+ ColumnName + "\t";

104                        if(StringUtils.indexOf(ColumnTypeName,"identity")>=0){

105                            colstr =colstr+ColumnTypeName +"(1,1)";

106                        }elseif (StringUtils.equalsIgnoreCase(ColumnTypeName,"timestamp")

107                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"int")

108                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"datetime")

109                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"long")

110                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"date")

111                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"text")

112                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"image")

113                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"bit")

114                            ||StringUtils.equalsIgnoreCase(ColumnTypeName,"ntext")

115                            ) {

116                            colstr =colstr+ColumnTypeName +"";

117                        }else if(StringUtils.equalsIgnoreCase(ColumnTypeName,"decimal")

118                                || StringUtils.equalsIgnoreCase(ColumnTypeName,"number")

119                                || StringUtils.equalsIgnoreCase(ColumnTypeName,"double")) {

120                            if(scale == 0)

121                                colstr =colstr+ColumnTypeName +"(" + displaySize+")";

122                            else

123                                colstr =colstr+ColumnTypeName +"(" + displaySize+"," + scale + ")";

124                        }else {

125                            colstr =colstr+ColumnTypeName +"(" + displaySize +")";

126                        }

127                        String defaultstr = rscol.getString(13);

128                        if(defaultstr!=null)

129                        colstr =colstr+"\t default "+defaultstr;

130                        if(rscol.getInt(11) == DatabaseMetaData.columnNoNulls) {

131                            colstr =colstr+"\tnot null";

132                        }else if(rscol.getInt(11) == DatabaseMetaData.columnNullable) {

133                            // sql.append("\tnull");

134                        }

135                    }

136                    String pkcolstr ="";

137                    while(pkRSet.next()) {

138  

139                        if(StringUtils.isNotBlank(pkcolstr)){

140                            pkcolstr = pkcolstr+",\n";

141                        }else{

142                            if(StringUtils.isNotBlank(colstr)){

143                                colstr = colstr+",\n";

144                            }

145                        }

146                        pkcolstr = pkcolstr+"\tconstraint \""+ pkRSet.getObject(6)+"\" primary key (" + pkRSet.getObject(4)+")";

147                    }

148                    sql.append("create table "+ Tablename +"\n("+colstr+pkcolstr+"\n)\n\n");

149                    System.out.println("create table "+ Tablename +"\n("+colstr+pkcolstr+"\n);\n"+commnt+""+indexu+"\n");

150                }

151                counti++;

152            }

153            scwj("c:\\","abc.sql", sql.toString());

154            rs.close();

155            conn.close();

156        }catch (SQLException e) {

157            e.printStackTrace();

158        }finally{

159            try{

160                if(conn!=null) conn.close();

161            }catch (SQLException e) {

162            }

163        }

164    }

165      

166  

167    publicstatic void main(String[] args) {

168  

169    }

170}