java代码查看表ddl_根据java数据源生成建表ddl

根据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) {

}

}