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();
- }
- }