???? 在做數據庫開發中，經常都要清空（delete）數據或刪除（drop）所有的表。然而，外鍵的存在，給這個工作帶來了很大的不便。這里用jdbc寫一個通用的類，產生出清空或刪除表的順序。
以下代碼在mysql與oracle下面運行正常
(在同行的建議下作了一些改進，又增加了一些功能，見類注釋)
代碼：
package createData.tryDemo;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
?* 外鍵工具
?* 1，返回清空（刪除）數據庫表的先后順序
?* 2，找出數據庫中的外鍵環
?* 3，找出數據庫中的對同一個表的重復外鍵關聯
?*
?* 注：外鍵環（不知道在數據庫中，對此情況有沒有別的命名）
?*? 情況1，外鍵表是自己 （自外鍵環、小外鍵環、單外鍵環）
?*? 情況2，aTable的外鍵表是bTable，而bTable的外鍵表又是aTable（雙外鍵環）
?*? 情況3，就是多個表之間形成的外鍵環（多外鍵環）
?* @author wpeace
?*
?*/
public class ExportedKeysTools
{
??? public static void main(String[] args)
??? {
??????? //得到一個數據庫的連接，這里就不細說了
??????? Connection connection = null;
???????
??????? List<String> tables = ExportedKeysTools.getDeleteOrder(connection);
???????
??????? System.out.print(tables);
??????? //清空數據,以oracle為例
??????? StringBuilder scrip = new StringBuilder();
??????? for(String it : tables)
??????? {
??????????? scrip.append(String.format("delete from %s;\r\n", it));
??????? }
??????? System.out.print(scrip);
??????? //刪除所有表,以oracle為例
???????
??????? for(String it : tables)
??????? {
??????????? scrip.append(String.format("drop table %s;\r\n", it));
??????? }
??????? System.out.print(scrip);
???????
??????? //提示關閉連接！！！
??? }
??? public static List<String> getDeleteOrder(Connection connection)
??? {
??????? String userName = null;
??????? try
??????? {
??????????? userName = connection.getMetaData().getUserName();
??????? }
??????? catch (SQLException e)
??????? {
??????????? e.printStackTrace();
??????? }
??????? return ExportedKeysTools.getDeleteOrder(connection, userName);
??? }
??? /**
???? * 返回清空（刪除）數據庫表的先后順序
???? * @param connection jdbc連接
???? * @param userName 如果為null的話，在oracle數據庫下面是所有的用戶的所有表。
???? * 在mysql下是當前用用戶的，這個與數據庫廠商的jdbc驅動有關系
???? * @return 返回清空（刪除）數據庫表的先后順序
???? */
??? public static List<String> getDeleteOrder(Connection connection,String userName)
??? {
??????? ResultSet reTables = null;
??????? ResultSet refk = null;
??????? try
??????? {
??????????? DatabaseMetaData dm = connection.getMetaData();
??????????? //如果是oracle數據庫的話，清空閃回表
??????????? if(dm.getDatabaseProductName().toUpperCase().indexOf("ORACLE")>=0)
??????????? {
??????????????? Statement state = connection.createStatement();
??????????????? state.execute("PURGE RECYCLEBIN");
??????????????? state.close();
??????????? }
??????????? //取得表
??????????? reTables = dm.getTables(null, userName, null, new String[]{"TABLE","VIEW"});
??????????? List<TableMeta> tableMetaList = new ArrayList<TableMeta>();
??????????? while(reTables.next())
??????????? {
??????????????? String tableName = reTables.getString("TABLE_NAME").trim();
??????????????? if(tableName == null || tableName.length()<1)
??????????????? {
??????????????????? continue;
??????????????? }
??????????????? TableMeta tem = new TableMeta(tableName);
??????????????? tableMetaList.add(tem);
??????????????? //取得外鍵表
??????????????? refk = dm.getExportedKeys(null, userName, tableName);
??????????????? while(refk.next())
??????????????? {
??????????????????? String fkTableName = refk.getString("FKTABLE_NAME").trim();
??????????????????? if(fkTableName == null || fkTableName.length() < 1 ||
??????????????????????????? fkTableName.equals(tableName)) //去掉主外鍵是自己的小環
??????????????????? {
??????????????????????? continue;
??????????????????? }
??????????????????? tem.addFK(fkTableName);
??????????????? }
??????????????? if(refk != null)refk.close();
??????????? }
???????????
??????????? Iterator<TableMeta> iterator = tableMetaList.iterator();
??????????? TableMeta tableMeta = iterator.next();
??????????? List<String> deleteOrder = new ArrayList<String>();
??????????? int counts = tableMetaList.size();
??????????? for(;true;)
??????????? {
??????????????? //沒有外鍵了
??????????????? if(!tableMeta.isFKTable())
??????????????? {
??????????????????? iterator.remove();
??????????????????? deleteOrder.add(tableMeta.tableName);
???????????????????
??????????????????? //清除表所使用的所有外鍵表
??????????????????? for(TableMeta it : tableMetaList)
??????????????????? {
??????????????????????? it.deleteFK(tableMeta.tableName);
??????????????????? }
??????????????? }
??????????????? if(!iterator.hasNext())
??????????????? {
??????????????????? //一次循環完成后，如果tableMeta的長度（也不為零）沒有減少，
??????????????????? //那么說明在tableMeta中的表之間有循環外鍵關聯的“環”，要退出整個循環
??????????????????? //不然此處就會有一個死循環，此時在tableMeta中的表的設計也許是有問題的
??????????????????? //如果要分析
??????????????????? if(tableMetaList.size() == counts || tableMetaList.size() < 1)
??????????????????? {
??????????????????????? break;
??????????????????? }
??????????????????? iterator = tableMetaList.iterator();
??????????????? }
??????????????? tableMeta = iterator.next();
??????????? }
?????????? return deleteOrder;
??????? }
??????? catch (SQLException e)
??????? {
??????????? if(refk != null)
??????????????? try
??????????????? {
??????????????????? refk.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? if(reTables != null)
??????????????? try
??????????????? {
??????????????????? reTables.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? e.printStackTrace();
??????? }
??????? return null;
??? }
??? /**
???? * 返回外鍵環
???? * @param connection 連接
???? * @param userName 用戶名，可以為空
???? * @return 返回外鍵環
???? */
??? public static List<String> getExportedKeysLoop(Connection connection,String userName)
??? {
??????? ResultSet reTables = null;
??????? ResultSet refk = null;
??????? try
??????? {
??????????? DatabaseMetaData dm = connection.getMetaData();
??????????? //如果是oracle數據庫的話，清空閃回表
??????????? if(dm.getDatabaseProductName().toUpperCase().indexOf("ORACLE")>=0)
??????????? {
??????????????? Statement state = connection.createStatement();
??????????????? state.execute("PURGE RECYCLEBIN");
??????????????? state.close();
??????????? }
??????????? //取得表
??????????? reTables = dm.getTables(null, userName, null, new String[]{"TABLE","VIEW"});
??????????? List<TableMeta> tableMetaList = new ArrayList<TableMeta>();
??????????? while(reTables.next())
??????????? {
??????????????? String tableName = reTables.getString("TABLE_NAME").trim();
??????????????? if(tableName == null || tableName.length()<1)
??????????????? {
??????????????????? continue;
??????????????? }
??????????????? TableMeta tem = new TableMeta(tableName);
??????????????? tableMetaList.add(tem);
??????????????? //取得外鍵表
??????????????? refk = dm.getExportedKeys(null, userName, tableName);
??????????????? while(refk.next())
??????????????? {
??????????????????? String fkTableName = refk.getString("FKTABLE_NAME").trim();
??????????????????? if(fkTableName == null || fkTableName.length() < 1 ||
??????????????????????????? fkTableName.equals(tableName)) //去掉主外鍵是自己的小環
??????????????????? {
??????????????????????? continue;
??????????????????? }
??????????????????? tem.addFK(fkTableName);
??????????????? }
??????????????? if(refk != null)refk.close();
??????????? }
???????????
??????????? Iterator<TableMeta> iterator = tableMetaList.iterator();
??????????? TableMeta tableMeta = iterator.next();
??????????? List<String> exportedKeysLoop = new ArrayList<String>();
??????????? int counts = tableMetaList.size();
??????????? for(;true;)
??????????? {
??????????????? //沒有外鍵了
??????????????? if(!tableMeta.isFKTable())
??????????????? {
??????????????????? iterator.remove();???????????????????
??????????????????? //清除表所使用的所有外鍵表
??????????????????? for(TableMeta it : tableMetaList)
??????????????????? {
??????????????????????? it.deleteFK(tableMeta.tableName);
??????????????????? }
??????????????? }
??????????????? if(!iterator.hasNext())
??????????????? {
??????????????????? //一次循環完成后，如果tableMeta的長度（也不為零）沒有減少，
??????????????????? //那么說明在tableMeta中的表之間有循環外鍵關聯的“環”，要退出整個循環
??????????????????? //不然此處就會有一個死循環，此時在tableMeta中的表的設計也許是有問題的
??????????????????? //如果要分析
??????????????????? if(tableMetaList.size() == counts || tableMetaList.size() < 1)
??????????????????? {
??????????????????????? for(TableMeta it : tableMetaList)
??????????????????????? {
??????????????????????????? exportedKeysLoop.add(it.tableName);
??????????????????????? }
??????????????????????? break;
??????????????????? }
??????????????????? iterator = tableMetaList.iterator();
??????????????? }
??????????????? tableMeta = iterator.next();
??????????? }
?????????? return exportedKeysLoop;
??????? }
??????? catch (SQLException e)
??????? {
??????????? if(refk != null)
??????????????? try
??????????????? {
??????????????????? refk.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? if(reTables != null)
??????????????? try
??????????????? {
??????????????????? reTables.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? e.printStackTrace();
??????? }
??????? return null;
??? }
??? /**
???? * 有重復外鍵的表
???? * @param connection 連接
???? * @param userName 用戶名，可以為空
???? * @return 有重復外鍵的表
???? */
??? public static List<String> getRepeatExportedKeys(Connection connection,String userName)
??? {
??????? ResultSet reTables = null;
??????? ResultSet refk = null;
??????? try
??????? {
??????????? DatabaseMetaData dm = connection.getMetaData();
??????????? //如果是oracle數據庫的話，清空閃回表
??????????? if(dm.getDatabaseProductName().toUpperCase().indexOf("ORACLE")>=0)
??????????? {
??????????????? Statement state = connection.createStatement();
??????????????? state.execute("PURGE RECYCLEBIN");
??????????????? state.close();
??????????? }
???????????
??????????? List<String> repeatExportedKeys = new ArrayList<String>();
??????????? //取得表
??????????? reTables = dm.getTables(null, userName, null, new String[]{"TABLE","VIEW"});
??????????? while(reTables.next())
??????????? {
??????????????? String tableName = reTables.getString("TABLE_NAME").trim();
??????????????? if(tableName == null || tableName.length()<1)
??????????????? {
??????????????????? continue;
??????????????? }
??????????????? TableMeta tem = new TableMeta(tableName);
??????????????? //取得外鍵表
??????????????? refk = dm.getExportedKeys(null, userName, tableName);
??????????????? while(refk.next())
??????????????? {
??????????????????? String fkTableName = refk.getString("FKTABLE_NAME").trim();
??????????????????? if(fkTableName == null || fkTableName.length() < 1)
??????????????????? {
??????????????????????? continue;
??????????????????? }
??????????????????? if(tem.findFK(fkTableName))
??????????????????? {
??????????????????????? repeatExportedKeys.add(tem.tableName);
??????????????????????? break;
??????????????????? }
???????????????? }
??????????????? if(refk != null)refk.close();
??????????? }
?????????? return repeatExportedKeys;
??????? }
??????? catch (SQLException e)
??????? {
??????????? if(refk != null)
??????????????? try
??????????????? {
??????????????????? refk.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? if(reTables != null)
??????????????? try
??????????????? {
??????????????????? reTables.close();
??????????????? }
??????????????? catch (SQLException e1)
??????????????? {
??????????????????? e1.printStackTrace();
??????????????? }
??????????? e.printStackTrace();
??????? }
??????? return null;
??? }
??? public static class TableMeta{
??????? //表名
??????? public String tableName;
??????? //外鍵表
??????? private List<String> fkTable = new ArrayList<String>(1);
??????? public TableMeta(String table)
??????? {
??????????? this.tableName = table;
??????? }
??????? public boolean findFK(String table)
??????? {
??????????? return fkTable.contains(table);
??????? }
??????? public void deleteFK(String table)
??????? {
??????????? fkTable.remove(table);
??????? }
??????? //是否存在外鍵表
??????? public boolean isFKTable()
??????? {
??????????? return fkTable.size() > 0;
??????? }
??????? public void addFK(String table)
??????? {
??????????? //重名處理
??????????? if(!findFK(table))
??????????? {
??????????????? fkTable.add(table);
??????????? }
??????? }
??? }
}