根据java数据源生成建表ddl

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

}