[转]清空数据库数据（表）的一个解决方案

清空数据库数据（表）的一个解决方案

     在做数据库开发中，经常都要清空（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 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 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 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 tableMetaList = new ArrayList();
            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 iterator = tableMetaList.iterator();
            TableMeta tableMeta = iterator.next();
            List deleteOrder = new ArrayList();
            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 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 tableMetaList = new ArrayList();
            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 iterator = tableMetaList.iterator();
            TableMeta tableMeta = iterator.next();
            List exportedKeysLoop = new ArrayList();
            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 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 repeatExportedKeys = new ArrayList();
            //取得表
            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 fkTable = new ArrayList(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);
            }
        }
    }

}