package com.cn.xwy.wangk.db.util; |
002 | |
003 | import java.io.File; |
004 | import java.io.FileWriter; |
005 | import java.io.IOException; |
006 | import java.io.PrintWriter; |
007 | import java.sql.Connection; |
008 | import java.sql.DatabaseMetaData; |
009 | import java.sql.DriverManager; |
010 | import java.sql.ResultSet; |
011 | import java.sql.SQLException; |
012 | |
013 | import org.apache.commons.lang.StringUtils; |
014 | |
015 | /** |
016 | * |
017 | * @author Administrator |
018 | * |
019 | */ |
020 | public class Db2ddl { |
021 | public static boolean scwj(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( new FileWriter(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 | return false ; |
037 | } |
038 | |
039 | public Connection getconConnection(){ |
040 | try { |
041 | Class.forName( "net.sourceforge.jtds.jdbc.Driver" ); |
042 | return DriverManager.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 | return null ; |
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 | public void 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 | int counti= 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 | int displaySize = rscol.getInt( 7 ); |
098 | int scale = 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 | } else if (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 | public static void main(String[] args) { |
168 | |
169 | } |
170 | } |