一个数据库连接Java工具类和数据迁移类

java 代码

/**
 *
 * @author Administrator
 *
 */
public class DataBaseManager {

    private String driver = "com.mysql.jdbc.Driver";

    private Connection conn = null;
    private Statement statement = null;

    private String path = "";
    // jdbc:oracle:thin:@10.1.1.1:1521:mydb
    // jdbc:mysql://10.1.1.1:3306/
    // mydb?useUnicode=true&characterEncoding=utf-8&autoReconnect=true
    private String user = "";
    private String pwd = "";

    /**
     * Constructor
     *
     */
    public DataBaseManager() {
        init();
    }

    public DataBaseManager(String driver, String url, String user, String pwd) {
        this.driver = driver;
        this.path = url;
        this.user = user;
        this.pwd = pwd;

        init();
    }

    private void init() {
        //
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            conn = java.sql.DriverManager.getConnection(path, user, pwd);
            statement = conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            System.out.println("can not connect db!");
            e.printStackTrace();
        }
    }

    /**
     * close Connection
     *
     * @param sql
     * @return
     */
    public ResultSet getResultSet(String sql) {
        ResultSet rs = null;
        try {
            rs = statement.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }

    /**
     *
     * @param hql
     * @return
     */
    public boolean exe(String hql) {
        try {
            return statement.execute(hql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return false;
    }

    /**
     * close Connection
     *
     */
    public void closeConnection() {
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        if (statement != null)
            try {
                statement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

java 代码

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class DataTransferFromSqlScript {
    // private String dateFormat = "dd-mm-yyyy hh24:mi:ss.ff";

    // private DataBaseManager dm1 = new
    // DataBaseManager("oracle.jdbc.driver.OracleDriver",
    // "jdbc:oracle:thin:@10.1.1.1:1521:mydb", "username", "pwd");
    // //
    // private DataBaseManager dm2 = new
    // DataBaseManager("oracle.jdbc.driver.OracleDriver",
    // "jdbc:oracle:thin:@10.1.1.1:1521:mydb", "username", "pwd");

    // private DataBaseManager dm1 = new DataBaseManager(
    // "com.mysql.jdbc.Driver",
    // "jdbc:mysql://10.1.1.1:3306/
    // mydb?useUnicode=true&characterEncoding=utf-8&autoReconnect=true",
    // "username", "pwd");

    private DataBaseManager dm1 = new DataBaseManager(
            "com.mysql.jdbc.Driver",
            "jdbc:mysql://10.1.1.1:3306/"
            + "mydb?useUnicode=true&characterEncoding=utf-8&autoReconnect=true",
            "username", "pwd");

    public void readAndSaveResumeDate() {

        System.out.println("Translate date starting......");

        List<string></string> files = new ArrayList<string></string>();
        files.add("c://dbtable1.sql");
        files.add("c://dbtable2.sql");

        for (String fileName : files) {
            File f = new File(fileName);

            List<string></string> sqls = new ArrayList<string></string>();

            try {
                FileReader in = new FileReader(f);
                BufferedReader br = new BufferedReader(in);
                String s;
                String s1 = "";
                while ((s = br.readLine()) != null) {
                    if (s.startsWith("insert into")) {
                        s1 = s;
                    } else if (s.startsWith("values (")) {

                        s = getSql(s);

                        sqls.add((s1 + s).replace(";", ""));
                    }
                }
                br.close();
                in.close();
            } catch (IOException e) {
                // Auto-generated catch block
                e.printStackTrace();
            }

            // Object[] sqlsSorted = sortSqls(sqls, -1);

            Object[] sqlsSorted = sqls.toArray();

            System.out.println(fileName + " total: " + sqlsSorted.length);

            for (Object sql : sqlsSorted) {
                System.out.println(sql);

                //
                dm1.exe((String) sql);
            }

            System.out.println(fileName + " total: " + sqlsSorted.length);
        }
    }

    /**
     *
     * @param positions
     */
    private static void swap(Object[] positions, int i, int j) {
        Object tem = positions[i];
        positions[i] = positions[j];
        positions[j] = tem;
    }

    /**
     * n > 0 or n <= 0
     *
     * @param positions
     * @param n
     */
    public static Object[] sortSqls(List<string></string> sqls, int n) {

        Object[] array = sqls.toArray();

        for (int i = 0; i < array.length; i++) {
            for (int j = array.length - 1; j > i; j--) {
                if (n > 0) {
                    if (getId((String) array[j]) > getId((String) array[j - 1]))
                        swap(array, j, j - 1);
                } else if (n < 0) {
                    if (getId((String) array[j]) < getId((String) array[j - 1]))
                        swap(array, j, j - 1);
                }
            }
        }

        return array;
    }

    /**
     *
     * @param sql
     * @return
     */
    public static int getId(String sql) {
        int start = sql.indexOf("values (");
        String s = sql.substring(start).split(",")[0].substring(8);

        return Integer.parseInt(s);
    }

    public String getSql(String s) {
        int i = s.indexOf("to_timestamp");
        if (i > 0) {
            return s.replace(s.substring(i, i + 70), "null");
        } else
            return s;
    }

    public static void main(String[] args) {

        DataTransferFromSqlScript rd = new DataTransferFromSqlScript();
        rd.readAndSaveResumeDate();

        rd.dm1.closeConnection();
    }
}

评论

5 楼 zyu67 2017-11-17  

这个类运行不起来呀  这类如何导出数据呀

4 楼 di1984HIT 2013-12-26  

鞋的不错啊。呵呵额

3 楼 Jameslyy 2009-07-04  

jackytang520 写道

直接用insert的话,在大数据量的情况下,产生大量日志问题,效率也低,我也在想这方面的办法

可以考虑批量保存数据！

2 楼 jackytang520 2009-07-04  

直接用insert的话,在大数据量的情况下,产生大量日志问题,效率也低,我也在想这方面的办法

1 楼 dd2086 2008-04-27  

  不错，加油，能写成工具类就好了。