transmitdata.java

cwbbs 云网论坛源码

        Conn conn_mssql = 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_mssql.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")) 
                    continue;
                

                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 + ")";
                
                while (rs.next()) {
                    pstmt_ora = conn_mssql.prepareStatement(sql_insert);
                    int i = 0;
                    while (i < rowCount) {
                        

                        
                        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) { 
                            pstmt_ora.setString(i + 1, rs.getString(i + 1));
                        } else if (columnType[i] == java.sql.Types.TINYINT ||
                                   columnType[i] == java.sql.Types.INTEGER ||
                                   columnType[i] == java.sql.Types.BIT) {
                            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 if (columnType[i] == java.sql.Types.REAL) {
                            pstmt_ora.setFloat(i + 1, rs.getFloat(i + 1));
                        }
                        else if (columnType[i] == java.sql.Types.DOUBLE) {
                            pstmt_ora.setFloat(i + 1, rs.getFloat(i + 1));
                        }
                        else
                            throw new ErrMsgException(v.elementAt(i) + " 类型 " +
                                    columnType[i] + " 不支持！ ");

                        i++;
                    }
                    conn_mssql.executePreUpdate();

                }

            }
            conn_mssql.commit();
        } catch (SQLException e) {
            conn_mssql.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_mssql != null) {
                conn_mssql.close();
                conn_mssql = null;
            }

        }
    }

    
    public void oraToMysql(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;

        boolean isBegin = false;
        try {
            conn_ora.beginTrans();
            rs_table = getTableNames();
            Vector tableV = new Vector();
            while (rs_table.next()) {
                tableName = rs_table.getObject(3).toString(); 
                tableV.addElement(tableName);
            }

            Iterator tableIr = tableV.iterator();
            while (tableIr.hasNext()) {
                tableName = (String)tableIr.next(); 

                columns = "";
                columnValues = "";

                if (tableName.toLowerCase().equals("yearcount") || tableName.toLowerCase().equals("monthcount") || tableName.toLowerCase().equals("lastly") || tableName.toLowerCase().equals("daycount")) 
                    continue;

                
                
                
                if (tableName.equalsIgnoreCase("BIN$zZkhwwD1R2+zRgBqPnjWWg==$0")) {
                    isBegin = true;
                    continue;
                }

                if (!isBegin) {
                    continue;
                }
                
                
                

                

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

                sql_insert = "insert into " + tableName;
                rs_column = getColumns(tableName);
                int rowCount = 0;
                Vector v = new Vector();
                Vector vType = new Vector();
                while (rs_column.next()) {
                    String colName = rs_column.getObject(4).toString();
                    v.addElement(colName);
                    
                    
                    
                    
                    
                    
                    if (columns.equals(""))
                        columns = colName;
                    else
                        columns += "," + colName;

                    if (columnValues.equals(""))
                        columnValues = "?";
                    else
                        columnValues += ",?";

                    vType.addElement(rs_column.getObject(5).toString());
                    rowCount ++;
                }

                columnType = new int[rowCount];
                int k = 0;
                Iterator ir = vType.iterator();
                while (ir.hasNext()) {
                    columnType[k] = Integer.parseInt(
                            (String) ir.next());
                    k++;
                }

                
                sql_select = "select " + columns + " from " + tableName;
                System.out.println(sql_select);
                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));

                        
                        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) { 
                            pstmt_ora.setString(i+1, rs.getString(i + 1));
                        } else if (columnType[i] == java.sql.Types.TINYINT || columnType[i] == java.sql.Types.INTEGER || columnType[i] == java.sql.Types.BIT) {
                            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 if (columnType[i]==java.sql.Types.OTHER) {
                            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;
            }
        }
    }
}