Java一个通用的数据库导出程序

在民生做项目的时候，由于操作两种类型的数据库（informix和DB2），上线之前用哪种数据库还不确定，虽然有客户端可以导出数据，但是各种数据库导出成sql文件都稍微有差异，况且执行效率（数据量大的时候）也不高，因此写了这个程序。

功能很简单，大致流程：生成表结构---->生成表数据

/*
 *    Copyright (c) 2001-2010 ADTEC Ltd.
 *    All rights reserved
 *
 *    This is unpublished proprietary source code of ADTEC Ltd.
 *    The copyright notice above does not evidence any actual
 *    or intended publication of such source code.
 *
 *    NOTICE: UNAUTHORIZED DISTRIBUTION, ADAPTATION OR USE MAY BE
 *    SUBJECT TO CIVIL AND CRIMINAL PENALTIES.
 */

package com.adtec.dbload;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * <p>DBTool</p>
 * <p>数据导出工具</p>
 * <p>Copyright: Copyright (c) 2010</p>
 * <p>Company: 北京先进数通公司</p>
 * @author  Luochang
 * @version 1.0 2010-7-6 Luochang
 * <p>          修改者姓名 修改内容说明</p>
 * @see     参考类1
 */
public class DBLoadOutTool {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
        generateInformixScript();
    }
   
    /**
     * 得到连接
     * @return
     * @throws Exception
     */
 protected static Connection getConnection() throws Exception {
  InputStream in = DBLoadOutTool.class
    .getResourceAsStream("/sqlserver.properties");
  Properties props = new Properties();
  props.load(in);

  Class.forName(props.getProperty("driver"));

  Connection connection = DriverManager.getConnection(props
    .getProperty("url"), props.getProperty("username"), props
    .getProperty("password"));

  return connection;
 }

    /**
     * 根据uri生成表和数据脚本
     * @param uri
     * @throws Exception
     */
    public static void generateInformixScript() throws Exception {
        String path = DBLoadOutTool.class.getClassLoader().getResource(".").getPath()+
    "/ECBuilder.sql" ;
        path = path.replace("/bin/", "/");
       
        path = path.substring(1, path.length() );
        BufferedWriter writer = new BufferedWriter(new FileWriter(new File(path)));

        Connection connection = getConnection() ;
        try{
            DatabaseMetaData md = connection.getMetaData();
            ResultSet rs = md.getTables(null, null, null, new String[] { "TABLE" });
           
            // 贷款申请数据备份 //
//            List<String> list = getFields(connection,"ect_format_item") ;
//            generateTableDatas(connection,writer,list,"ect_format_item") ;
           
            while (rs.next()) {
                // System.out.println(rs.getString(1) + ":" + rs.getString(3));
                String tableName =  rs.getString(3) ;
//                System.out.println(tableName);
                List<String> list = getFields(connection,tableName) ;
                // 生成表
//                generateTable(md,connection, writer,tableName);
                // 生成数据
                generateTableDatas(connection,writer,list,tableName) ;
            }
        } catch ( Exception e ){
            throw e ;
        } finally {
            writer.close();
            connection.close() ;
        }
       
        System.out.println("#################生成结束#####################");
    }

    /**
     * 得到表数据
     * @param connection
     * @param writer
     * @param fileds
     * @param tableName
     * @throws Exception
     */
    private static void generateTableDatas(Connection connection,
            BufferedWriter writer, List<String> fileds, String tableName)
            throws Exception {
        StringBuffer tmpStr = new StringBuffer() ;
        for (int i = 0; i<fileds.size();i++){
            if (i == (fileds.size()-1)){
                tmpStr.append(fileds.get(i)) ;
            } else{
                tmpStr.append(fileds.get(i)+",") ;
            }
        }
        String start = "insert into "+tableName+" ("+tmpStr.toString()+") values" ;
        ResultSet rs = connection.createStatement().executeQuery(
            "select * from " + tableName);
        while(rs.next()){
            StringBuffer vals = new StringBuffer() ;
            for (int i = 1; i<=fileds.size();i++){
                Object value = rs.getObject(i) ;
                if(value instanceof String){
                    value = ((String)value).trim();
                    if (i == (fileds.size())){
                        vals.append("'"+value+"'") ;
                    } else{
                        vals.append("'"+value+"',") ;
                    }
                } else{
                    if (i == (fileds.size())){
                        vals.append(""+value+"") ;
                    } else{
                        vals.append(""+value+",") ;
                    }
                }
               
            }
            System.err.println("SQL:"+start+"("+vals.toString()+");\n");
            writer.write(start+"("+vals.toString()+");\n") ;
        }
    }
   
    /**
     * 得到表字段
     * @param connection
     * @param tableName
     * @return
     * @throws Exception
     */
    private static List<String> getFields(Connection connection,String tableName) throws Exception{
        List<String> list = new ArrayList<String>() ;
        ResultSet rs = connection.createStatement().executeQuery(
            "select * from " + tableName);
        ResultSetMetaData meta = rs.getMetaData();
        int columnCount = meta.getColumnCount();
        for (int i = 0; i < columnCount; i++) {
            StringBuffer tmpStr = new StringBuffer();
            int cursor = i + 1;
            tmpStr.append(meta.getColumnName(cursor) + " ");
            // System.out.println(meta.getColumnName(cursor));
//            int sqlType = meta.getColumnType(cursor);
            list.add(meta.getColumnName(cursor)) ;
        }
        return list ;
    }

    /**
     * 得到表结构
     * @param connection
     * @param writer
     * @param tableName
     * @return
     * @throws Exception
     */
    private static List<String> generateTable(DatabaseMetaData md,Connection connection,
            BufferedWriter writer, String tableName) throws Exception {
        List<String> list = new ArrayList<String>() ;
        System.out.println("############## table-->" + tableName
                + " ############");
        ResultSet rs = connection.createStatement().executeQuery(
            "select * from " + tableName);
        ResultSetMetaData meta = rs.getMetaData();
        // 删除表
//        writer.write("--drop table " + tableName + "\n");
//        writer.write("drop table " + tableName + ";\n");
       
        // 创建表
//        writer.write("--create table " + tableName + "\n");
        writer.write("create table " + tableName + "(\n");
        int columnCount = meta.getColumnCount();
        for (int i = 0; i < columnCount; i++) {
            StringBuffer tmpStr = new StringBuffer();
            int cursor = i + 1;
            tmpStr.append(meta.getColumnName(cursor) + " ");
            // System.out.println(meta.getColumnName(cursor));
            int sqlType = meta.getColumnType(cursor);
            list.add(meta.getColumnName(cursor)) ;
            if (i == (columnCount - 1)) {
//                addParimaryKey(md,tableName,writer) ;
                addLastLine(md,tableName, sqlType, writer, meta, cursor);
                break;
            }
            switch (sqlType) {
            case Types.CHAR: {

                writer.write("      " + meta.getColumnName(cursor) + "  CHAR("
                        + meta.getColumnDisplaySize(cursor) + ")");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.VARCHAR: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  VARCHAR(" + meta.getColumnDisplaySize(cursor)
                        + ")");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.DECIMAL: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  DECIMAL("
                        + (meta.getColumnDisplaySize(cursor) - 2) + ","
                        + meta.getScale(cursor) + ")");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.INTEGER: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  INTEGER ");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.SMALLINT: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  SMALLINT ");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.FLOAT: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  FLOAT ");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            case Types.BIGINT: {
                writer.write("      " + meta.getColumnName(cursor)
                        + "  BIGINT ");
                if (0 == meta.isNullable(cursor)) {
                    writer.write(" NOT NULL,\n");
                } else {
                    writer.write(" ,\n");
                }
                break;
            }
            }
            // System.out.println(meta.getColumnType(cursor));
            // System.out.println(meta.getColumnDisplaySize(cursor));
            // System.out.println(meta.getScale(cursor));
            // System.out.println(meta.isNullable(cursor));

        }
        writer.write("\n");
        System.out.println("############## table-->" + tableName
                + " END############");
        return list ;
    }
   
    /**
     * 得到表索引信息
     * @param md
     * @param table
     * @param writer
     * @return
     * @throws Exception
     */
    private static String addIndexInfo(DatabaseMetaData md, String table,
        BufferedWriter writer) throws Exception {
        String indexInfo = "" ;
        ResultSet rs = md.getIndexInfo(null, null, table, true, false) ;
       
        while (rs.next()) {
            System.err.println("****** IndexComment ******");
            System.err.println("TABLE_CAT : " + rs.getObject(1));
            System.err.println("TABLE_SCHEM: " + rs.getObject(2));
            System.err.println("TABLE_NAME : " + rs.getObject(3));
            System.err.println("NON_UNIQUE : " + rs.getObject(4));
            System.err.println("INDEX_QUALIFIER  : " + rs.getObject(5));
            System.err.println("INDEX_NAME  : " + rs.getObject(6));
            System.err.println("TYPE   : " + rs.getObject(7));
            System.err.println("ORDINAL_POSITION  : " + rs.getObject(8));
            System.err.println("COLUMN_NAME  : " + rs.getObject(9));
            System.err.println("ASC_OR_DESC  : " + rs.getObject(10));
            System.err.println("CARDINALITY  : " + rs.getObject(11));
            System.err.println("PAGES   : " + rs.getObject(12));
            System.err.println("FILTER_CONDITION  : " + rs.getObject(13));
            System.err.println("****** ******* ******");
           
            indexInfo += rs.getObject(9)+",";
        }
       
        if (!"".equals(indexInfo)){
            indexInfo = indexInfo.substring(0, indexInfo.lastIndexOf(',')) ;
        }
       
        return indexInfo ;
    }
   
    /**
     * 得到表主键信息
     * @param md
     * @param table
     * @param writer
     * @return
     * @throws Exception
     */
    private static String addParimaryKey(DatabaseMetaData md, String table,
            BufferedWriter writer) throws Exception {
        ResultSet rs = md.getPrimaryKeys(null, null, table);
        String primaryKey = "" ;
        while (rs.next()) {
            System.err.println("****** Comment ******");
            System.err.println("TABLE_CAT : " + rs.getObject(1));
            System.err.println("TABLE_SCHEM: " + rs.getObject(2));
            System.err.println("TABLE_NAME : " + rs.getObject(3));
            System.err.println("COLUMN_NAME: " + rs.getObject(4));
            System.err.println("KEY_SEQ : " + rs.getObject(5));
            System.err.println("PK_NAME : " + rs.getObject(6));
            System.err.println("****** ******* ******");
            primaryKey += rs.getObject(4)+",";
        }
        if (!"".equals(primaryKey)){
            primaryKey = primaryKey.substring(0, primaryKey.lastIndexOf(',')) ;
        }
        return primaryKey ;

    }
   
    private static void addLastLine(DatabaseMetaData md,String tableName,int sqlType, BufferedWriter writer,
            ResultSetMetaData meta, int cursor) throws Exception {
        String primaryKey = addParimaryKey(md,tableName,writer) ;   // 主键
        addIndexInfo(md,tableName,writer) ; //索引
        switch (sqlType) {
        case Types.CHAR: {
            writer.write("      " + meta.getColumnName(cursor) + "  CHAR("
                    + meta.getColumnDisplaySize(cursor) + ")");
           
            break;
        }
        case Types.VARCHAR: {
            writer.write("      " + meta.getColumnName(cursor) + "  VARCHAR("
                    + meta.getColumnDisplaySize(cursor) + ")");
           
            break;
        }
        case Types.DECIMAL: {
            writer.write("      " + meta.getColumnName(cursor) + "  DECIMAL("
                    + (meta.getColumnDisplaySize(cursor) - 2) + ","
                    + meta.getScale(cursor) + ")");
           
            break;
        }
        case Types.INTEGER: {
            writer.write("      " + meta.getColumnName(cursor) + "  INTEGER ");
           
            break;
        }
        case Types.SMALLINT: {
            writer.write("      " + meta.getColumnName(cursor) + "  SMALLINT ");
           
            break;
        }
        case Types.BIGINT: {
            writer.write("      " + meta.getColumnName(cursor) + "  BIGINT ");
           
            break;
        }
        }
        if (0 == meta.isNullable(cursor)) {
            if(!"".equals(primaryKey)){
                writer.write(" NOT NULL,\n      primary key("+primaryKey+")\n);\n");
            } else{
                writer.write(" NOT NULL\n);\n");
            }
        } else {
            if(!"".equals(primaryKey)){
                writer.write(", \n      primary key("+primaryKey+")\n);\n");
            } else{
                writer.write(" \n);\n");
            }
        }
    }
}