根据java数据源生成建表ddl
1.[代码][Java]代码
package com.cn.xwy.wangk.db.util;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.lang.StringUtils;
/**
*
* @author Administrator
*
*/
public class Db2ddl {
public static boolean scwj(String path, String FileName, String body) {
try {
File f = new File(path);
f.mkdirs();
path = path + "\\" + FileName;
f = new File(path);
PrintWriter out;
out = new PrintWriter(new FileWriter(f));
out.print(body + "\n");
out.close();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
public Connection getconConnection(){
try {
Class.forName("net.sourceforge.jtds.jdbc.Driver");
return DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/adwatch","sa","123456");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* ALTER TABLE table_name ADD INDEX index_name (column_list)
* ALTER TABLE table_name ADD UNIQUE (column_list)
* ALTER TABLE table_name ADD PRIMARY KEY (column_list)
*/
public void db2ddl(){
Connection conn = null;
try {
//DB2 com.ibm.db2.jcc.DB2Driver "jdbc:db2://localhost:50000/abc", "db2admin","123456"
//MYSQL com.mysql.jdbc.Driver "jdbc:mysql://localhost:3306/manager","root","root"
//MSSQLSERVER net.sourceforge.jtds.jdbc.Driver "jdbc:jtds:sqlserver://localhost:1433/abc","sa","123456"
conn = getconConnection();
DatabaseMetaData odmd = conn.getMetaData();
//String[] types = { "TABLE" };// 数组变量types
ResultSet rs = odmd.getTables(null, null, null, null);
//odmd.getMaxTableNameLength()
StringBuffer sql = new StringBuffer();
int counti=1;
while (rs.next()) {
// 取表名
String Tablename = rs.getString(3);
if (StringUtils.equalsIgnoreCase(rs.getString(4), "TABLE")
//&& StringUtils.equalsIgnoreCase(rs.getString(2),"abc")
//&&StringUtils.equalsIgnoreCase(rs.getString(3), "ADDetail")
) {
System.out.println(counti+"-"+Tablename);
String commnt = "";
String indexu = "";
ResultSet pkRSet = odmd.getPrimaryKeys(null, null,Tablename);
ResultSet rscol = odmd.getColumns(null, null,Tablename, null);
ResultSet inset = odmd.getIndexInfo(null, null, Tablename, false ,true );
String colstr = "";
while (rscol.next()) {
String ColumnName = rscol.getString(4);
String ColumnTypeName = rscol.getString(6);
String REMARKS = rscol.getString(12);
if(StringUtils.isNotBlank(REMARKS)){
commnt = commnt+"COMMENT ON "+Tablename+" ( "+ColumnName+" IS '"+REMARKS+"' ); \n";
}
while(inset.next()){
if(inset.getInt(7)==DatabaseMetaData.tableIndexOther){
indexu = indexu+"CREATE UNIQUE INDEX "+inset.getString(6)+" ON "+inset.getString(5)+"("+inset.getString(9)+");\n";
}
System.out.println();
}
int displaySize = rscol.getInt(7);
int scale = rscol.getInt(9);
// int Precision = displaySize-scale;
if(StringUtils.isNotBlank(colstr)){
colstr = colstr+",\n";
}
colstr =colstr+"\t" + ColumnName + "\t";
if (StringUtils.indexOf(ColumnTypeName,"identity")>=0){
colstr =colstr+ColumnTypeName + "(1,1)";
}else if (StringUtils.equalsIgnoreCase(ColumnTypeName,"timestamp")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"int")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"datetime")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"long")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"date")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"text")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"image")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"bit")
||StringUtils.equalsIgnoreCase(ColumnTypeName,"ntext")
) {
colstr =colstr+ColumnTypeName + "";
} else if (StringUtils.equalsIgnoreCase(ColumnTypeName,"decimal")
|| StringUtils.equalsIgnoreCase(ColumnTypeName,"number")
|| StringUtils.equalsIgnoreCase(ColumnTypeName,"double")) {
if (scale == 0)
colstr =colstr+ColumnTypeName + "(" + displaySize+ ")";
else
colstr =colstr+ColumnTypeName + "(" + displaySize+ "," + scale + ")";
} else {
colstr =colstr+ColumnTypeName + "(" + displaySize + ")";
}
String defaultstr = rscol.getString(13);
if(defaultstr!=null)
colstr =colstr+"\t default "+defaultstr;
if (rscol.getInt(11) == DatabaseMetaData.columnNoNulls) {
colstr =colstr+"\tnot null";
} else if (rscol.getInt(11) == DatabaseMetaData.columnNullable) {
// sql.append("\tnull");
}
}
String pkcolstr = "";
while (pkRSet.next()) {
if(StringUtils.isNotBlank(pkcolstr)){
pkcolstr = pkcolstr+",\n";
}else{
if(StringUtils.isNotBlank(colstr)){
colstr = colstr+",\n";
}
}
pkcolstr = pkcolstr+"\tconstraint \"" + pkRSet.getObject(6)+ "\" primary key (" + pkRSet.getObject(4)+ ")";
}
sql.append("create table "+ Tablename + "\n("+colstr+pkcolstr+"\n)\n\n");
System.out.println("create table "+ Tablename + "\n("+colstr+pkcolstr+"\n);\n"+commnt+""+indexu+"\n");
}
counti++;
}
scwj("c:\\", "abc.sql", sql.toString());
rs.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null) conn.close();
} catch (SQLException e) {
}
}
}
public static void main(String[] args) {
}
}