数据源间表的复制--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);

 

    }