peacess

统计

留言簿(14)

积分与排名

阅读排行榜

评论排行榜

清空数据库数据(表)的一个解决方案


     在做数据库开发中,经常都要清空(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);
            }
        }
    }

}






posted on 2006-11-09 17:18 中东 阅读(9602) 评论(10)  编辑  收藏 所属分类: 数据库设计篇

评论

# re: 清空数据库数据(表)的一个解决方案 2006-11-09 17:39 马嘉楠

问一下
在哪里关闭连接(Connection)?

方法中声明的ResultSet也没有关闭
好像有点不妥吧

不需要关闭么?

  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-09 21:11 中东

记录集是应该关闭的,数据库连接就不用了,是从从面从入的  回复  更多评论   

# 不需要关闭么? 2006-11-09 21:22 中东

谢谢你的提示,我再修改了一上,减少它的错误  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-09 21:38 海边沫沫

标准SQL语法:
FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
其中:
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间,你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。

SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。

NO ACTION: 在ANSI SQL-92标准中,NO ACTION意味这不采取动作,就是如果有一个相关的外键值在被参考的表里,删除或更新主要键值的企图不被允许进行(Gruber, 掌握SQL, 2000:181)。 InnoDB拒绝对父表的删除或更新操作。

RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样,删除ON DELETE或ON UPDATE子句。(一些数据库系统有延期检查,并且NO ACTION是一个延期检查。在MySQL中,外键约束是被立即检查的,所以NO ACTION和RESTRICT是同样的)。

一句话顶你几百行代码,呵呵。  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-09 22:11 中东

让外键约束失效,再进行删除,这种方法很好。
我用的是软件方法,不破坏原来数据库的结构,各有优点啊!!!!!  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-09 23:22 stoneshao[匿名]

可以试一下,dbunit  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-10 11:06

我看你的封装就很失败。

truncate table table1

快速。不会产生回滚段。又能释放表空间。。  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-10 11:33 坏男孩

踩个脚印;truncate table 是oracle的吧,这儿是通用的吧  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-10 11:43 jeffjie

DBUnit是解决不了这个问题的。
这也是使用dbunit总不顺手的原因。
后来我自已也写了个类似的程序。
不过有个问题问问楼主:
如果一个表的主键外键都是自已,你的程序能处理这个问题吗?可以避免死循环?  回复  更多评论   

# re: 清空数据库数据(表)的一个解决方案 2006-11-13 11:12 peace

@jeffjie
这位提的很好,这个类有一个问题,就是表间的外键构成一个“环”(主外键自己的也是一个小环)时,程序会出现死循环。这个问题我再改进一下,看能不能找出解决方法,主外键自己的小环应该好办,主要是几个表之间的环,就不好处理了,不过,一般设计数据库应该不会有这样的情况,因为几个表之间构成环,也就是相互依懒,插入数据都插不进去?  回复  更多评论   


只有注册用户登录后才能发表评论。


网站导航: