package a.readme;
import java.sql.*;
import javax.swing.*;
import java.util.*;
public class TableReversorInteg {
private Connection conn = null;
private DatabaseMetaData dbmd = null;
private String tablename = null;
private String tablename_zw = null;
private Statement st = null;
private static String SCHEMA_NAME = "LYASJ";
private static String USERNAME = "LYASJ";
private static String PASSWORD = "LYASJ";
//格式化输出，不管列名长短，后面相应列应对齐
private static final int COLUMN_NAME_WIDTH = 24;
private static final int COLUMN_TYPE_WIDTH = 24;
private static final int COLUMN_NAME_BEGIN_WIDTH = 3;
public TableReversorInteg() throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.10.19:1521:czserver", USERNAME, PASSWORD);
dbmd = conn.getMetaData();
st = conn.createStatement();
}
public TableReversorInteg(String schema, String user, String password) throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.10.19:1521:czserver", user, password);
this.SCHEMA_NAME = schema;
dbmd = conn.getMetaData();
st = conn.createStatement();
}
public String getColumns() throws Exception {
ResultSet rs = dbmd.getColumns(null, SCHEMA_NAME,
tablename, null);
StringBuffer sbColumn = new StringBuffer();
while (rs.next()) {
StringBuffer sbColumn_line = new StringBuffer();
try {
String COLUMN_NAME = rs.getString("COLUMN_NAME");
int DATA_TYPE = rs.getInt("DATA_TYPE");
String TYPE_NAME = rs.getString("TYPE_NAME");
String notnull_Token = " ,";
String column_size_Token = ""; //"(30)" "" or "(11,2)"
int COLUMN_SIZE = rs.getInt("COLUMN_SIZE");
int DECIMAL_DIGITS = rs.getInt("DECIMAL_DIGITS");
//对于blob和clob以及date类型，column_size_Token应该为""
//对于char和varchar(varchar2)，column_size_Token应该为"(30)"
//对于number，column_size_Token应该为"(30)"或"(11,2)"
if (DATA_TYPE == Types.BLOB || DATA_TYPE == Types.CLOB ||
DATA_TYPE == Types.DATE || DATA_TYPE == Types.TIME ||
DATA_TYPE == Types.TIMESTAMP || DATA_TYPE == Types.BLOB) {
column_size_Token = "";
}
else if (DATA_TYPE == Types.CHAR || DATA_TYPE == Types.VARCHAR ||
DATA_TYPE == Types.LONGVARCHAR) {
column_size_Token = "(" + COLUMN_SIZE + ")";
}
else if (DATA_TYPE == Types.DOUBLE || DATA_TYPE == Types.BIGINT ||
DATA_TYPE == Types.DECIMAL || DATA_TYPE == Types.INTEGER ||
DATA_TYPE == Types.NUMERIC || DATA_TYPE == Types.REAL ||
DATA_TYPE == Types.SMALLINT) {
column_size_Token = "(" + COLUMN_SIZE + "," + DECIMAL_DIGITS + ")";
}
String IS_NULLABLE = rs.getString("IS_NULLABLE"); //"NO" or "YES"
if (IS_NULLABLE.equals("NO")) {
notnull_Token = "not null,";
}
else {
notnull_Token = " ,";
}
sbColumn_line.append(getNSpace(COLUMN_NAME_BEGIN_WIDTH));
sbColumn_line.append(COLUMN_NAME);
if (sbColumn_line.length() < COLUMN_NAME_WIDTH) {
sbColumn_line.append(getNSpace(COLUMN_NAME_WIDTH -
sbColumn_line.length()));
}
sbColumn_line.append(TYPE_NAME);
sbColumn_line.append(column_size_Token);
if (sbColumn_line.length() < COLUMN_NAME_WIDTH + COLUMN_TYPE_WIDTH) {
sbColumn_line.append(getNSpace(COLUMN_NAME_WIDTH + COLUMN_TYPE_WIDTH -
sbColumn_line.length()));
}
sbColumn_line.append(notnull_Token);
sbColumn_line.append("\n");
sbColumn.append(sbColumn_line);
}
catch (SQLException ex) {
System.err.println(ex.getMessage());
ex.printStackTrace();
}
}
if (rs != null) {
rs.close();
}
return sbColumn.toString();
}
public String getImportedKeys() throws Exception {
StringBuffer sb = new StringBuffer();
ArrayList[] resultFKInfo = getFKInfoList();
ArrayList fk_name_list = resultFKInfo[0];
ArrayList fk_column_list = resultFKInfo[1];
ArrayList pk_name_list = resultFKInfo[2];
ArrayList pk_column_list = resultFKInfo[3];
ArrayList KEY_SEQ_list = resultFKInfo[4];
ArrayList DELETE_RULE_list = resultFKInfo[5];
//记录key_seq_count的索引，比如1、2、3、1、1、1、2、1、1
//最多100个外键
int[] key_seq_count = new int[100];
String[] fk_column_tmp = new String[100];
String[] pk_column_tmp = new String[100];
int count = 0;
for (int i = 0; i < KEY_SEQ_list.size(); i++) {
int KEY_SEQ = ( (Integer) KEY_SEQ_list.get(i)).intValue();
if (KEY_SEQ == 1) {
key_seq_count[count] = i;
fk_column_tmp[count] = (String) fk_column_list.get(i);
pk_column_tmp[count] = (String) pk_column_list.get(i);
count++;
}
else {
fk_column_tmp[count - 1] = fk_column_tmp[count - 1] + "," +
(String) fk_column_list.get(i);
pk_column_tmp[count - 1] = pk_column_tmp[count - 1] + "," +
(String) pk_column_list.get(i);
}
}
for (int i = 0; i < count; i++) {
sb.append(" constraint ").append(fk_name_list.get(key_seq_count[i])).
append(
" foreign key (").
append(fk_column_tmp[i]).append(")\n");
String tmpDeleteRule = ",";
int nTmp = ( (Integer) DELETE_RULE_list.get(key_seq_count[i])).intValue();
if (nTmp == DatabaseMetaData.importedKeyCascade) {
tmpDeleteRule = "on delete cascade,";
}
sb.append(" references ").append(pk_name_list.get(key_seq_count[i])).
append(" (").
append(pk_column_tmp[i]).append(") " + tmpDeleteRule + "\n");
}
return sb.toString();
}
public ArrayList[] getFKInfoList() throws Exception {
ArrayList[] infoList = new ArrayList[6];
ResultSet rs = dbmd.getImportedKeys(null, SCHEMA_NAME, tablename);
ArrayList fk_name_list = new ArrayList();
ArrayList fk_column_list = new ArrayList();
ArrayList pk_name_list = new ArrayList();
ArrayList pk_column_list = new ArrayList();
ArrayList KEY_SEQ_list = new ArrayList();
ArrayList DELETE_RULE_list = new ArrayList();
while (rs.next()) {
try {
String PKTABLE_NAME = rs.getString("PKTABLE_NAME");
String FK_NAME = rs.getString("FK_NAME");
String PKCOLUMN_NAME = rs.getString("PKCOLUMN_NAME");
String FKCOLUMN_NAME = rs.getString("FKCOLUMN_NAME");
int KEY_SEQ = rs.getInt("KEY_SEQ");
int DELETE_RULE = rs.getInt("DELETE_RULE");
fk_name_list.add(FK_NAME);
fk_column_list.add(FKCOLUMN_NAME);
pk_name_list.add(PKTABLE_NAME);
pk_column_list.add(PKCOLUMN_NAME);
KEY_SEQ_list.add(new Integer(KEY_SEQ));
DELETE_RULE_list.add(new Integer(DELETE_RULE));
}
catch (SQLException ex) {
System.err.println(ex.getMessage());
ex.printStackTrace();
}
}
if (rs != null) {
rs.close();
}
infoList[0] = fk_name_list;
infoList[1] = fk_column_list;
infoList[2] = pk_name_list;
infoList[3] = pk_column_list;
infoList[4] = KEY_SEQ_list;
infoList[5] = DELETE_RULE_list;
return infoList;
}
public String getPrimaryKeys() throws Exception {
ResultSet rs = dbmd.getPrimaryKeys(null, SCHEMA_NAME, tablename);
StringBuffer sb_pk = new StringBuffer();
StringBuffer sb_pk_column = new StringBuffer();
String PK_NAME = null;
boolean hasPK = false;
while (rs.next()) {
hasPK = true;
try {
String COLUMN_NAME = rs.getString("COLUMN_NAME");
PK_NAME = rs.getString("PK_NAME");
sb_pk_column.append(COLUMN_NAME).append(",");
}
catch (SQLException ex) {
System.err.println(ex.getMessage());
ex.printStackTrace();
}
}
if (hasPK) {
formatComma(sb_pk_column);
sb_pk.append(" constraint ").append(PK_NAME).append(" primary key (").
append(sb_pk_column).append("),\n");
}
if (rs != null) {
rs.close();
}
return sb_pk.toString();
}
private void formatComma(StringBuffer sb_pk_column) {
if (sb_pk_column.indexOf(",") > 0)
sb_pk_column.deleteCharAt(sb_pk_column.length() - 1);
}
public void close() {
try {
if (st != null) {
st.close();
}
}
catch (SQLException ex) {
}
try {
if (conn != null) {
conn.close();
}
}
catch (SQLException ex1) {
}
}
public String getScript(String tablename) throws Exception {
StringBuffer sb = new StringBuffer();
setTablename(tablename);
sb.append(getScript());
sb.append("\n\n");
// close();
return sb.toString();
}
public String getScript(ArrayList tableList) throws Exception {
ArrayList tables = tableList;
StringBuffer sbContent = new StringBuffer();
for (int i = 0; i < tables.size(); i++) {
setTablename(tables.get(i).toString());
sbContent.append(getScript());
sbContent.append("\n\n");
}
// close();
return sbContent.toString();
}
public String getScript() throws Exception {
StringBuffer script = null;
script = new StringBuffer();
script.append("create table " + this.tablename + " (\n");
script.append(getColumns());
script.append(getPrimaryKeys());
script.append(getImportedKeys());
String tmpScript = script.toString().trim();
if (tmpScript.endsWith(",")) {
tmpScript = tmpScript.substring(0, tmpScript.length() - 1);
}
tmpScript = tmpScript + "\n)\n";
return tmpScript;
}
public String getScriptWithDrop(String tablename) throws Exception {
StringBuffer sb = new StringBuffer();
setTablename(tablename);
sb.append(getScriptWithDrop());
sb.append("\n\n");
// close();
return sb.toString();
}
public String getScriptWithDrop(ArrayList tableList) throws Exception {
ArrayList tables = tableList;
StringBuffer sbContent = new StringBuffer();
for (int i = 0; i < tables.size(); i++) {
setTablename(tables.get(i).toString());
sbContent.append(getScriptWithDrop());
sbContent.append("\n\n");
}
// close();
return sbContent.toString();
}
public String getScriptWithDrop() throws Exception {
StringBuffer sb = new StringBuffer();
sb.append("drop table " + tablename + " cascade constraints\n");
sb.append("/\n");
sb.append(
"/*==============================================================*/\n");
sb.append("/* Table: " + tablename + "------" +
"---------Begin------------*/\n");
sb.append(
"/*==============================================================*/\n");
sb.append(getScript());
sb.append("/\n");
sb.append(
"/*==============================================================*/\n");
sb.append("/* Table: " + tablename + "------" +
"-------------------------End*/\n");
sb.append(
"/*==============================================================*/\n");
sb.append("\n");
sb.append("\n");
return sb.toString();
}
public static void main(String[] args) throws Exception {
TableReversorInteg tableReversor = new TableReversorInteg("LYASJ","LYASJ","LYASJ");
ArrayList tables = tableReversor.getTables();
// String sbContent = tableReversor.getScriptWithDrop(tables);
String sbContent = tableReversor.getScriptWithDrop("AJJBQK");
FileUtil fileUtil = new FileUtil("c:\\tableReversor.sql3", sbContent);
tableReversor.close();
}
public String getNSpace(int n) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < n; i++) {
sb.append(" ");
}
return sb.toString();
}
public void setTablename(String tablename) {
this.tablename = tablename.trim().toUpperCase();
}
private HashMap getCNDescList() {
HashMap hmFieldName = new HashMap();
String tmpFiledName = null;
String tmpFieldName_ZW = null;
ResultSet rs = null;
try {
rs =
st.executeQuery(
"SELECT FIELDNAMELIST,FIELDNAMELIST_ZW FROM TAB_CHINESE_NAME where TABLENAME='"
+ tablename
+ "'");
while (rs.next()) {
tmpFiledName = rs.getString("FIELDNAMELIST");
tmpFieldName_ZW = rs.getString("FIELDNAMELIST_ZW");
}
}
catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
finally {
if (rs != null) {
try {
rs.close();
rs = null;
}
catch (SQLException ex) {
}
}
}
if (tmpFiledName == null) {
return hmFieldName;
}
String[] arrayField = tmpFiledName.split(",");
String[] arrayField_ZW = tmpFieldName_ZW.split(",");
if (arrayField.length != arrayField_ZW.length) {
JOptionPane.showMessageDialog(
null,
"致命错误,取出的英文字段和中文字段个数不匹配，程序将自动退出！",
"错误",
JOptionPane.ERROR_MESSAGE);
System.err.println("arrayField:" + java.util.Arrays.asList(arrayField));
System.err.println("arrayField_ZW:" +
java.util.Arrays.asList(arrayField_ZW));
System.err.println("arrayField.length:" + arrayField.length);
System.err.println("arrayField_ZW.length:" + arrayField_ZW.length);
System.exit(1);
}
for (int i = 0; i < arrayField.length; i++) {
hmFieldName.put(arrayField[i].trim(), arrayField_ZW[i].trim());
}
return hmFieldName;
}
private ArrayList getTables() throws Exception {
ArrayList listTables = new ArrayList();
ResultSet rs = dbmd.getTables(null, this.SCHEMA_NAME, null,
new String[] {"TABLE"});
while (rs.next()) {
String tmpTable = rs.getString("TABLE_NAME");
if (tmpTable != null) {
listTables.add(tmpTable);
}
}
if (rs != null) {
rs.close();
}
return listTables;
}
public String getSCHEMA_NAME() {
return SCHEMA_NAME;
}
public void setSCHEMA_NAME(String SCHEMA_NAME) {
this.SCHEMA_NAME = SCHEMA_NAME;
}
public String getUSERNAME() {
return USERNAME;
}
public void setUSERNAME(String USERNAME) {
this.USERNAME = USERNAME;
}
public String getPASSWORD() {
return PASSWORD;
}
public void setPASSWORD(String PASSWORD) {
this.PASSWORD = PASSWORD;
}
}
No comments:
Post a Comment