transmitdata.java~17~

云网论坛CWBBS 源码,内容丰富,学习,参考,教学的好资料,具体见内说明,

                System.out.println("tableName=" + tableName);

                sql_insert = "insert into " + tableName;
                rs_column = getColumns(rs_table.getObject(3).toString());
                int rowCount = 0;
                Vector v = new Vector();
                while (rs_column.next()) {
                    String colName = rs_column.getObject(4).toString();
                    v.addElement(colName);
                    columns += colName; //获得字段名
                    columnValues += "?";
                    if (!rs_column.isLast()) {
                        columns += ",";
                        columnValues += ",";
                    }
                    rowCount ++;
                }
                columnType = new int[rowCount];
                int k = 0;
                rs_column.beforeFirst();
                    while (rs_column.next()) {
                        columnType[k] = Integer.parseInt(
                                rs_column.getObject(5).toString());
                        k ++;
                    }

                // 获得数据
                sql_select = "select " + columns + " from " + tableName;
                pstmt_mysql = conn_mysql.prepareStatement(sql_select);
                rs = conn_mysql.executePreQuery();

                sql_insert = "insert into " + tableName + " (" + columns +
                             ") values (" + columnValues + ")";
                System.out.println("sql_insert:" + sql_insert);
                while (rs.next()) {
                    pstmt_ora = conn_ora.prepareStatement(sql_insert);
                    int i = 0;
                    while (i < rowCount) {
                        System.out.println(v.elementAt(i) + " " + columnType[i] + " = " + rs.getString(i + 1));

                        // tinyint(1)在getString()后得到的为布尔值true或false
                        if (columnType[i] == java.sql.Types.VARCHAR) {
                            pstmt_ora.setString(i+1, rs.getString(i + 1));
                        }
                        else if (columnType[i] == java.sql.Types.BOOLEAN) {
                            pstmt_ora.setInt(i+1, rs.getInt(i + 1));
                        }
                        else if (columnType[i] == java.sql.Types.TIMESTAMP) {
                            pstmt_ora.setTimestamp(i + 1, rs.getTimestamp(i + 1));
                        } else if (columnType[i] == java.sql.Types.DATE) {
                            pstmt_ora.setDate(i + 1, rs.getDate(i + 1));
                        } else if (columnType[i] == java.sql.Types.LONGVARCHAR) { // text类型
                            pstmt_ora.setString(i+1, rs.getString(i + 1));
                        } else if (columnType[i] == java.sql.Types.TINYINT || columnType[i] == java.sql.Types.INTEGER) {
                            pstmt_ora.setInt(i+1, rs.getInt(i + 1));
                        } else if (columnType[i] == java.sql.Types.BIGINT) {
                            pstmt_ora.setLong(i+1, rs.getLong(i + 1));
                        } else if (columnType[i] == java.sql.Types.DECIMAL) {
                            pstmt_ora.setFloat(i+1, rs.getFloat(i + 1));
                        } else if (columnType[i] == java.sql.Types.CHAR) {
                            pstmt_ora.setString(i+1, rs.getString(i + 1));
                        }
                        else
                            throw new ErrMsgException(v.elementAt(i) + " 类型 " + columnType[i] + " 不支持！ ");

                        i++;
                    }
                    conn_ora.executePreUpdate();

                }

            }
            conn_ora.commit();
        } catch (SQLException e) {
            conn_ora.rollback();
            System.out.println(e.getMessage());
            throw new ErrMsgException(StrUtil.trace(e));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {}
                rs = null;
            }
            if (conn_mysql != null) {
                conn_mysql.close();
                conn_mysql = null;
            }
            if (conn_ora != null) {
                conn_ora.close();
                conn_ora = null;
            }

        }
    }

    public void mysqlToMSSql(JspWriter out, String conn_name) throws ErrMsgException, SQLException, IOException {
    Conn conn_mysql = new Conn(connname);
    PreparedStatement pstmt_mysql = null;
    Conn conn_ora = new Conn(conn_name);
    PreparedStatement pstmt_ora = null;
    ResultSet rs_table = null;
    ResultSet rs_column = null;
    ResultSet rs = null;
    String sql_select = "";
    String sql_insert = "";
    String tableName = "";
    String columns = "";
    String columnValues = "";
    int[] columnType = null;
    try {
        conn_ora.beginTrans();
        rs_table = getTableNames();
        while (rs_table.next()) {
            columns = "";
            columnValues = "";

            tableName = rs_table.getObject(3).toString(); //获得表名
            if (tableName.toLowerCase().equals("yearcount") || tableName.toLowerCase().equals("monthcount") || tableName.toLowerCase().equals("lastly") || tableName.toLowerCase().equals("daycount") || tableName.toLowerCase().equals("bc")) // 表的列名为数字，oracle中非法
               continue;
            //sql_insert:insert into lastly (BC,OS,IP,Date) values (?,?,?,?)

            System.out.println("tableName=" + tableName);

            sql_insert = "insert into " + tableName;
            rs_column = getColumns(rs_table.getObject(3).toString());
            int rowCount = 0;
            Vector v = new Vector();
            while (rs_column.next()) {
                String colName = rs_column.getObject(4).toString();
                v.addElement(colName);
                columns += colName; //获得字段名
                columnValues += "?";
                if (!rs_column.isLast()) {
                    columns += ",";
                    columnValues += ",";
                }
                rowCount ++;
            }
            columnType = new int[rowCount];
            int k = 0;
            rs_column.beforeFirst();
                while (rs_column.next()) {
                    columnType[k] = Integer.parseInt(
                            rs_column.getObject(5).toString());
                    k ++;
                }

            // 获得数据
            sql_select = "select " + columns + " from " + tableName;
            pstmt_mysql = conn_mysql.prepareStatement(sql_select);
            rs = conn_mysql.executePreQuery();

            sql_insert = "insert into " + tableName + " (" + columns +
                         ") values (" + columnValues + ")";
            System.out.println("sql_insert:" + sql_insert);
            while (rs.next()) {
                pstmt_ora = conn_ora.prepareStatement(sql_insert);
                int i = 0;
                while (i < rowCount) {
                    System.out.println(v.elementAt(i) + " " + columnType[i] + " = " + rs.getString(i + 1));

                    // tinyint(1)在getString()后得到的为布尔值true或false
                    if (columnType[i] == java.sql.Types.VARCHAR) {
                        pstmt_ora.setString(i+1, rs.getString(i + 1));
                    }
                    else if (columnType[i] == java.sql.Types.BOOLEAN) {
                        pstmt_ora.setInt(i+1, rs.getInt(i + 1));
                    }
                    else if (columnType[i] == java.sql.Types.TIMESTAMP) {
                        pstmt_ora.setTimestamp(i + 1, rs.getTimestamp(i + 1));
                    } else if (columnType[i] == java.sql.Types.DATE) {
                        pstmt_ora.setDate(i + 1, rs.getDate(i + 1));
                    } else if (columnType[i] == java.sql.Types.LONGVARCHAR) { // text类型
                        pstmt_ora.setString(i+1, rs.getString(i + 1));
                    } else if (columnType[i] == java.sql.Types.TINYINT || columnType[i] == java.sql.Types.INTEGER) {
                        pstmt_ora.setInt(i+1, rs.getInt(i + 1));
                    } else if (columnType[i] == java.sql.Types.BIGINT) {
                        pstmt_ora.setLong(i+1, rs.getLong(i + 1));
                    } else if (columnType[i] == java.sql.Types.DECIMAL) {
                        pstmt_ora.setFloat(i+1, rs.getFloat(i + 1));
                    } else if (columnType[i] == java.sql.Types.CHAR) {
                        pstmt_ora.setString(i+1, rs.getString(i + 1));
                    }
                    else
                        throw new ErrMsgException(v.elementAt(i) + " 类型 " + columnType[i] + " 不支持！ ");

                    i++;
                }
                conn_ora.executePreUpdate();

            }

        }
        conn_ora.commit();
    } catch (SQLException e) {
        conn_ora.rollback();
        System.out.println(e.getMessage());
        throw new ErrMsgException(StrUtil.trace(e));
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {}
            rs = null;
        }
        if (conn_mysql != null) {
            conn_mysql.close();
            conn_mysql = null;
        }
        if (conn_ora != null) {
            conn_ora.close();
            conn_ora = null;
        }

    }
}


}