資料來源間表的複製–java實現(lp)

 

一、Table or Result To ArrayList    將資料來源connA中的tableName表的內容查詢出來放到一個ArrayList中。       private static ArrayList TableToArrayList(String tableName, Connection connA) {              Statement stmt = null;              ArrayList list = new ArrayList();               try {                     stmt = connA.createStatement();                     String sql = "select * from " + tableName;                     System.out.println(sql);                     ResultSet result = stmt.executeQuery(sql);                      int columnCount = result.getMetaData().getColumnCount();// 列數。                     Integer[] columnType = new Integer[columnCount];// 列的資料類型的數字表示。                      for (int i = 0; i < columnCount; i++) {                            columnType[i] = new Integer(result.getMetaData().getColumnType(                                          i + 1));                     }                     list.add(columnType);                      while (result.next()) {                            Object[] columnValue = new Object[columnCount];// 列值。                            for (int i = 0; i < columnCount; i++) {                                   columnValue[i] = result.getObject(i + 1);                            }                            list.add(columnValue);                     }               } catch (Exception e) {                     e.printStackTrace();              } finally {                     try {                            if (stmt != null)                                   stmt.close();                            if (connA != null)                                   connA.close();                     } catch (SQLException e) {                            e.printStackTrace();                     }              }              return list;       }二、ArrayList To Table    將list中的內容插入到資料來源connB的tableName表中。    private static void ArrayListToTable(ArrayList list, Connection connB,           String tableName) {              Statement stmt = null;       PreparedStatement pstmt = null;       try {           stmt = connB.createStatement();            // 把list中的資料插入到表tableName中.           // 如果tableName表中已經有資料，去掉資料。           String querySql = "Select * from " + tableName;           stmt = connB.createStatement();           ResultSet rs = stmt.executeQuery(querySql);           if (rs.next()) {              String deleteSql = "delete from " + tableName;              stmt.execute(deleteSql);           }            Integer[] columnType = (Integer[]) list.get(0);           Object[] columnValue = null;           int columnCountNew = columnType.length;            StringBuffer preSql = new StringBuffer("");           for (int i = 0; i < columnCountNew - 1; i++) {              preSql.append("?,");           }           preSql.append("?");           String insertSql = "insert into " + tableName + " values("                  + preSql + ")";           System.out.println("pre insertSql is:" + insertSql);           pstmt = connB.prepareStatement(insertSql);            System.out.println("ParameterCount："                  + pstmt.getParameterMetaData().getParameterCount());           // insert the data by row.           for (int i = 1; i < list.size(); i++) {               columnValue = (Object[]) list.get(i);              int type, j;               for (j = 0; j < columnCountNew; j++) {                  type = columnType[j].intValue();                  int temp = j + 1;                  if (columnValue[j] == null) {                     pstmt.setNull(temp, type);                  } else {                     switch (type) {                     case java.sql.Types.BIGINT:                         pstmt.setLong(temp, (Long) columnValue[j]);                         break;                     case java.sql.Types.INTEGER:                         pstmt.setLong(temp, (Long) columnValue[j]);                         break;                     case java.sql.Types.FLOAT:                         pstmt.setFloat(temp, (Float) columnValue[j]);                         break;                     case java.sql.Types.DOUBLE:                         pstmt.setDouble(temp, (Double) columnValue[j]);                         break;                     case java.sql.Types.DATE:                         pstmt.setDate(temp, (Date) columnValue[j]);                         break;                     case java.sql.Types.TIME:                         pstmt.setTime(temp, (Time) columnValue[j]);                         break;                     case java.sql.Types.TIMESTAMP:                         pstmt                                .setTimestamp(temp,                                       (Timestamp) columnValue[j]);                         break;                     default:                         pstmt.setString(temp, columnValue[j].toString());                         break;                     }                  }                }               System.out.println("The row " + i + " :" + insertSql);              pstmt.executeUpdate();           }       } catch (SQLException e) {           e.printStackTrace();       } finally {           try {              if (stmt != null)                  stmt.close();              if (connB != null)                  connB.close();           } catch (SQLException e) {              e.printStackTrace();           }       }    }三、表的拷貝，從一個資料來源到另一個資料來源。         private static void importData(String tableName, Connection connA,           Connection connB) {        ArrayList list = new ArrayList();        list = TableToArrayList(tableName, connA);        ArrayListToTable(list, connB, tableName);     }