transmitdata.java

cwbbs 云网论坛源码

package com.redmoon.forum.util;

import cn.js.fan.base.ObjectDb;
import cn.js.fan.db.*;
import cn.js.fan.util.ErrMsgException;
import java.sql.*;
import cn.js.fan.web.Global;
import javax.servlet.jsp.JspWriter;
import java.io.IOException;
import cn.js.fan.util.StrUtil;
import java.util.Vector;
import java.util.Hashtable;
import java.util.Iterator;



public class TransmitData extends ObjectDb {
    public int id;

    public TransmitData() {
        connname = Global.defaultDB;
    }

    public TransmitData(int id) {
        this.id = id;
        init();
        load();
    }

    public TransmitData getTransmitData(int id) {
        return (TransmitData) getObjectDb(new Integer(id));
    }

    public ObjectDb getObjectRaw(PrimaryKey pk) {
        return new TransmitData(pk.getIntValue());
    }

    public void initDB() {
        primaryKey = new PrimaryKey("id", PrimaryKey.TYPE_INT);

        isInitFromConfigDB = false;
    }

    public boolean save() throws ErrMsgException {
        return true;
    }

    public void load() {
    }

    public boolean del() throws ErrMsgException {
        return true;
    }

    public boolean create() throws ErrMsgException {
        return true;
    }

    
    public Vector getTableStruct(String table) {
        Conn conn = new Conn(connname);
        Connection con = conn.getCon();

        Vector vect = new Vector();
        try {
            
            DatabaseMetaData dmd = con.getMetaData();
            ResultSet rs = dmd.getColumns(null, "XNC", table.toUpperCase(), null);
            ResultSetMetaData rmd = rs.getMetaData();
            int cols = rmd.getColumnCount();
            System.out.println(cols + "gggHHH");
            while (rs.next()) {
                Hashtable hash = new Hashtable();
                hash.put("列定义大小", rs.getString("CHAR_OCTET_LENGTH") + "");
                String f = rs.getString("COLUMN_NAME");
                ResultSet r = conn.executeQuery("select " + f + " from " +
                                                table);
                ResultSetMetaData rm = r.getMetaData();
                hash.put("字段名", f + "");
                System.out.println(f + "");
                hash.put("列类型编号", rm.getColumnType(1) + "");
                hash.put("列标准类型名", rm.getColumnTypeName(1) + "");
                hash.put("是否可为空", rm.isNullable(1) + "");
                hash.put("是否数字", rm.isSigned(1) + "");
                hash.put("列定义大小", rm.getColumnDisplaySize(1) + "");
                hash.put("列精确度", rs.getString("NUM_PREC_RADIX") + "");
                r.close();
                Statement stst = r.getStatement();
                if (stst != null)
                    stst.close();
                vect.add(hash);
            }
            Statement stmt = rs.getStatement();
            rs.close();
            if (stmt != null)
                stmt.close();
            System.out.println("____" + vect);
        } catch (SQLException sqle) {
            System.out.println("调用DataBase.getTableStruct()函数错误:\r\n" + sqle);
        } catch (AbstractMethodError e) {
            System.out.println("调用DataBase.getTableStruct()函数错误:\r\n" + e);
        }
        return vect;
    }

    public ResultSet getTableNames() throws SQLException {
        Conn conn = new Conn(connname);
        Connection con = conn.getCon();
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE"});
        if (rs != null) {
            return rs;
        } else {
            return null;
        }
    }

    public ResultSet getTableNames(String conName) throws SQLException {
        Conn conn = null;
        conn = new Conn(conName);
        Connection con = conn.getCon();
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getTables(null, null, null, new String[] {"TABLE"});
        if (rs != null) {
            return rs;
        } else {
            return null;
        }
    }

    public ResultSet getColumns(String tableName, String conName) throws
            SQLException {
        Conn conn = new Conn(conName);
        Connection con = conn.getCon();
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getColumns(null, null, tableName, null);
        if (rs != null) {
            return rs;
        } else {
            return null;
        }
    }

    public ResultSet getColumns(String tableName) throws SQLException {
        Conn conn = new Conn(connname);
        Connection con = conn.getCon();
        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getColumns(null, null, tableName.toUpperCase(), null);
        con.close();
        con = null;
        if (rs != null) {
            return rs;
        } else {
            return null;
        }
    }

    public void mysqlToOra(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")) 
                    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 += "?";
                    
                    
                        columns += ",";
                        columnValues += ",";
                    
                    rowCount++;
                }
                if (columns.lastIndexOf(",")==columns.length()-1) {
                    columns = columns.substring(0, columns.length() - 1);
                    columnValues = columnValues.substring(0, columnValues.length() - 1);
                }
                columnType = new int[rowCount];
                int k = 0;

                
                rs_column = getColumns(rs_table.getObject(3).toString());

                while (rs_column.next()) {
                    columnType[k] = Integer.parseInt(
                            rs_column.getObject(5).toString());
                    k++;
                }

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