数据库比较工具

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBFunction
{
    
    public static Connection getConn(DbInfo dbInfo)
    {
        Connection conn = null;
        try
        {
            if (null == conn)
            {
                Class.forName(dbInfo.getDriver());
                conn = DriverManager.getConnection(dbInfo.getUrl(), dbInfo.getUserName(), dbInfo.getPwd());
            }
        }
        catch (ClassNotFoundException cnfe)
        {
            System.out.println("ClassNotFoundException");
        }
        catch (SQLException sqle)
        {
            System.out.println("conn exception ");
        }
        return conn;
    }
    
    /**
     * <一句话功能简述>执行sql语句并得到结果
     * <功能详细描述>
     * @param sql
     * @return
     * @throws SQLException 
     * @see [类、类#方法、类#成员]
     */
    public static List executeSql(String sql, Connection conn)
        throws SQLException
    {
        
        Statement st = null;
        ResultSet rs = null;
        List result = new ArrayList();
        
        try
        {
            
            sql = sql.toUpperCase();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            String columName = "";
            String values = "";
            
            // 只取select 和 from中间的字段信息
            String[] columns = sql.substring(7, sql.indexOf(" FROM")).split(",");
            while (rs.next())
            {
                values = "";
                
                for (int i = 0; i < columns.length; i++)
                {
                    columName = columns[i].trim();
                    
                    // 这个字段的值是<long>型要特殊处理
                    if (columName.trim().equalsIgnoreCase("DATA_DEFAULT"))
                    {
                        values = values + Tools.streamToString(rs.getBinaryStream(i + 1));
                    }
                    else
                    {
                        values = values + rs.getString(i + 1);
                    }
                    
                    if (i < columns.length - 1)
                    {
                        values = values + ",";
                    }
                    
                }
                
                result.add(values);
            }
        }
        catch (Exception ex)
        {
            System.out.println("sql:" + sql);
            System.out.println("Excute sql occur exception:" + ex.getMessage());
        }
        finally
        {
            try
            {
                if (null != st)
                {
                    st.close();
                    
                }
                if (null != rs)
                {
                    rs.close();
                }
            }
            catch (Exception ex)
            {
                ex.printStackTrace();
            }
        }
        
        return result;
    }
}

 

评论

2 楼 arne3166 2011-03-30  

import java.io.File;
import java.sql.Connection;
import java.util.List;

public class GetDBDatas
{
    
    /** <一句话功能简述>
     * <功能详细描述>
     * @param args
     * @see [类、类#方法、类#成员]
     */
    public static void main(String[] args)
    {
        System.out.println("if you have some advise,please send notes to zKF15950 ");
        System.out.println("you using version now:1.2");
        
        File dbConfigXmlFile = new File("./db-cfg.xml");
        
        // 如果文件不存在，退出
        if (!dbConfigXmlFile.exists())
        {
            System.out.println("db-cfg.xml file not exist , program will exit!!");
        }
        
        List dbConfList = ParseXml.parseXml(dbConfigXmlFile);
        
        int num = 0;
        
        // 循环处理每个连接
        for (int i = 0; i < dbConfList.size(); i++)
        {
            DbInfo dbInfo = new DbInfo((String[])dbConfList.get(i));
            
            String path = System.getProperty("user.dir")+"/data"+(++num)+"/";
            
            // 数据库配置有效使用
            if (dbInfo.isUse())
            {
                System.out.println("***********************************************************************");
                System.out.println("Prepare connect to:"+dbInfo.toString());
                
                Connection conn = DBFunction.getConn(dbInfo);
                
                if(null == conn)
                {
                    System.out.println("Db Connect fail!");
                    continue;
                }
                
                File filePath  = new File(path);
                if( ! filePath.exists())
                {
                    filePath.mkdirs();
                }
                else
                {
                    File[] files = filePath.listFiles();
                    for(int j=0;j<files.length;j++)
                    {
                        files[j].delete();
                    }
                }
                
                // 解析库对象
                 ReaderSqls.parseStructure(path,conn);
                 
                 // 获取表数据
                 ReaderSqls.getDatas(path,conn);
            }
        }
        

    }
    
}

1 楼 arne3166 2011-03-30  

/**
 * 
 * 数据库对象信息
 * 包含驱动、URL、UserName、PWD、isUse是否使用
 * 
 * @author  zKF15950
 * @version  [版本号, 2011-3-29]
 * @see  [相关类/方法]
 * @since  [产品/模块版本]
 */
public class DbInfo
{
    private String driver;
    
    private String url;
    
    private String userName;
    
    private String pwd;
    
    private boolean isUse;
    
    public DbInfo(String[] dbInfos)
    {
        url = dbInfos[0];
        driver = dbInfos[1];
        userName = dbInfos[2];
        pwd = dbInfos[3];
        isUse = dbInfos[4].equalsIgnoreCase("yes");
    }
    
    public String getDriver()
    {
        return driver;
    }
    
    public void setDriver(String driver)
    {
        this.driver = driver;
    }
    
    public String getUrl()
    {
        return url;
    }
    
    public void setUrl(String url)
    {
        this.url = url;
    }
    
    public String getUserName()
    {
        return userName;
    }
    
    public void setUserName(String userName)
    {
        this.userName = userName;
    }
    
    public String getPwd()
    {
        return pwd;
    }
    
    public void setPwd(String pwd)
    {
        this.pwd = pwd;
    }
    
    public boolean isUse()
    {
        return isUse;
    }
    
    public void setUse(boolean isUse)
    {
        this.isUse = isUse;
    }
    
    public String toString()
    {
        StringBuffer sbf = new StringBuffer();
        sbf.append("\r\n{").append("\r\n")
        .append("url:").append(url).append("\r\n")
        .append("driver:").append(driver).append("\r\n")
        .append("userName:").append(userName).append("\r\n")
        .append("password:").append(pwd).append("\r\n")
        .append("isUse:").append(isUse).append("\r\n")
        .append("}\r\n");
        return sbf.toString();
    }
}