databaseutil.java

TestDataBuilder是一个采用Java编写的

package com.testDataBuilder.core;

import java.io.File;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import com.testDataBuilder.config.DatabaseConfig;
import com.testDataBuilder.dbMetaInfo.Column;
import com.testDataBuilder.dbMetaInfo.Database;
import com.testDataBuilder.dbMetaInfo.ForeignKey;
import com.testDataBuilder.dbMetaInfo.PrimaryKey;
import com.testDataBuilder.dbMetaInfo.Table;
import com.testDataBuilder.exception.BaseException;
import com.testDataBuilder.util.Global;

/**
 * 数据库源.
 * <p>Title：Database.java</p>
 * @author LiuXiaojie 2007-4-16
 * @version 1.0
 */
public class DatabaseUtil{
 
    static Logger logger = Logger.getLogger(DatabaseUtil.class);
    
    //只得到DB信息。
    public static final int LEVEL_DB = 1;
    
    //得到DB和table信息.
    public static final int LEVEL_TABLE = 2;
    
    public static final int LEVEL_COLUMN = 3;

    private static Connection conn;
    
    private DatabaseConfig config;
    
    public DatabaseUtil(){
        
    }

    public boolean connect()  throws SQLException{
        DatabaseConfig dbConfig = this.getConfig();
        if(this.conn == null || this.conn.isClosed()){
            this.conn = this.getConnectin(dbConfig.getDriverClass(),
                            dbConfig.getURL(), dbConfig.getUserName(), dbConfig.getPassword());
        }
        return true;
    }

    public static boolean testConnect(DatabaseConfig databaseConfig) throws SQLException{
        Connection tempConn = null;
        try{
        tempConn = getConnectin(databaseConfig.getDriverClass(),
                        databaseConfig.getURL(), databaseConfig.getUserName(), databaseConfig.getPassword());
        
        }finally{
            if(tempConn != null){
                try{
                tempConn.close();
                }catch(SQLException ex){
                    logger.error("tempConn.close", ex);
                }
            }
        }
        return true;
    }
    
//    public List getArchitecture()  throws BaseException{
//        
//        try {
//            return getCatalogs(getConn(), LEVEL_COLUMN);
//        } catch (SQLException e) {
//            throw new BaseException(e);
//        }
//    }
//
//    public List getArchitecture(int level)  throws BaseException{        
//        try {
//            return getCatalogs(getConn(), level);
//        } catch (SQLException e) {
//            throw new BaseException(e);
//        }
//    }

    /**
     * 得到当前连接的数据库的结构。
     * <p><code>getDefArchitecture</code></p>
     * @param level
     * @return
     * @throws BaseException
     * @author LiuXiaojie 2008-1-24
     */
    public Database getDefArchitecture(int level)  throws BaseException{        
        try {
            Connection conn = this.getConn();
            return getCatalog(conn,conn.getCatalog(), level);
        } catch (SQLException e) {
            throw new BaseException(e);
        }
    }
    
    public ResultSet getData(String tableName) throws BaseException {
        String sql = "select * from " + tableName;        
        return getDataFromSQL(sql);
    }
    
    public ResultSet getDataFromSQL(String sql) throws BaseException{
        try {
            PreparedStatement statement = getConn().prepareStatement(sql);
            return statement.executeQuery();
        } catch (SQLException e) {
           throw new BaseException(e);
        }
    }
    public static Connection getConnection(DatabaseConfig config) throws SQLException{
        return getConnectin(config.getDriverClass(), config.getURL(), config.getUserName(), config.getPassword());
    }
    
    public static Connection getConnectin(String driver,String url, String userName, String pwd ) throws SQLException {
        try {
            File lib = new File("lib");
            ClassLoader classLoader = null;
            if(lib.exists()){
                logger.info("load driverClass from " + lib.getAbsolutePath());
                URL[] urls = null;
                try {
                    urls = new URL[]{lib.toURL()};
                } catch (MalformedURLException e) {
                    logger.error("加载SQL驱动时出错", e);
                }
                classLoader = new URLClassLoader(urls, DatabaseUtil.class.getClassLoader());
            }else{
                classLoader = DatabaseUtil.class.getClassLoader();
            }
            Class.forName(driver, true, classLoader);
            
        } catch (ClassNotFoundException e) {
            logger.error("加载SQL驱动时出错", e);
        }
        return DriverManager.getConnection(url, userName, pwd);
    }
    
    
   public static void main(String[] args) throws IOException, SQLException, BaseException {
//       System.out.println(" =================== sql server ==================");
//       DatabaseUtil dbUtil = new DatabaseUtil();
//       DatabaseConfig config = new DatabaseConfig();
//       config.setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//       config.setURL("jdbc:sqlserver://localhost:1433;databaseName=drmpolicy20");
//       config.setUserName("sa");
//       config.setPassword("sa");
//       dbUtil.setConfig(config);
//       Connection conn = dbUtil.getConn();
//       Statement stat = conn.createStatement();
//       String fileName = "E:\\workspace\\TestDataBuilder\\res\\res\\demo\\tdbDemoForSqlserver.sql";
//       String sqls= FileUtils.readFileToString(new File(fileName));
//       
//       String[] strSQL = sqls.split("go");
//       for(String sql : strSQL){
//    	   if(sql != null){
//    		   sql = sql.trim();
//    	   }
//    	   if(StringUtils.isNotEmpty(sql)){
//    		   stat.addBatch(sql);
//    	   }
//       }
//       stat.executeBatch();
//       
//       if(conn != null){
//    	   conn.close();
//       }
       
       testHSQLDB();
//       String name = getHSQLDBName("jdbc:hsqldb:file:testdb;shutdown=true");
//       System.out.println(">" + name);
//       System.out.println(" =================== oracle ==================");
//       testOracle();
    }
   
   public static void testSQLServer(){
       try {
           DatabaseUtil dbUtil = new DatabaseUtil();
           DatabaseConfig config = new DatabaseConfig();
           config.setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           config.setURL("jdbc:sqlserver://localhost:1433;databaseName=school");
           config.setUserName("sa");
           config.setPassword("sa");
           dbUtil.setConfig(config);
           Database db = dbUtil.getDefArchitecture(DatabaseUtil.LEVEL_COLUMN);
           dbUtil.setBasePath("E:\\");
           
               dbUtil.writeDBInfo(db);
               System.out.println("DBName:" + db.getDbName());
              
       }catch (BaseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
   }
   
   public static void testHSQLDB() throws SQLException, BaseException, IOException{
      
           DatabaseUtil dbUtil = new DatabaseUtil();
           DatabaseConfig config = new DatabaseConfig();
           config.setDriverClass("org.hsqldb.jdbcDriver");
           config.setURL("jdbc:hsqldb:file:testdb;shutdown=true");
           config.setUserName("sa");
           config.setPassword("");
           dbUtil.setConfig(config);
           Connection conn = dbUtil.getConn();
           Statement stat = conn.createStatement();
           String fileName = "E:\\workspace\\TestDataBuilder\\res\\res\\demo\\tdbDemoForSqlserver.sql";
           String sqls= FileUtils.readFileToString(new File(fileName));
           String[] strSQL = sqls.split("go");
           for(String sql : strSQL){
        	   if(sql != null){
        		   sql = sql.trim();
        	   }
        	   if(StringUtils.isNotEmpty(sql)){
        		   stat.addBatch(sql);
        	   }
           }
           stat.executeBatch();
           
           conn.close();

   }
   
   public static void testOracle(){
       try {
           DatabaseUtil dbUtil = new DatabaseUtil();
           DatabaseConfig config = new DatabaseConfig();
           config.setDriverClass("oracle.jdbc.driver.OracleDriver");
           config.setURL("jdbc:oracle:thin:@192.168.0.16:1521:orcl2");
           config.setUserName("drmpolicyuser");
           config.setPassword("drmpolicypwd");
           dbUtil.setConfig(config);
           Database db = dbUtil.getDefArchitecture(DatabaseUtil.LEVEL_COLUMN);
           
           System.out.println("DBName:" + db.getDbName());
           for(Table table :db.getTables()){
               System.out.println(table.getTableName());
           }
             
       }catch (BaseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
   }
   
    private String basePath = "";
    
    public static final String DB_CACHE_FILE_SUFFIX = ".metaInfo.xml";
    
    public boolean writeDBInfo(Database database){
        if(database != null){
            File file = new File(getBasePath() +  Global.SEP + "DB" + DB_CACHE_FILE_SUFFIX);
            try {
                String xml = database.toXmlString();
                FileUtils.writeByteArrayToFile(file, xml.getBytes("utf-8"));
                logger.info("write metainfo to [" + file.getAbsolutePath());
                return true;
            } catch (UnsupportedEncodingException e) {
                logger.error("writeDBInfo", e);
            } catch (IOException e) {
                logger.error("writeDBInfo", e);
            }
        }
        return false;
    }
    
    static FilenameFilter cacheFileFilter = new FilenameFilter(){
        public boolean accept(File file, String fileName) {
            return fileName.endsWith(DatabaseUtil.DB_CACHE_FILE_SUFFIX);
        }
    };
    
    public Database readDBInfoFromBasePath() throws IOException{
        File dir = new File(this.getBasePath());
        Database database = null;
        if(dir.exists()){
            File[] files = dir.listFiles(cacheFileFilter);
            if(files.length > 0){
                String xml = null;
                try{
                    xml = FileUtils.readFileToString(files[0], "utf-8");               
                    database = Database.fromXml(xml);
                }catch(Throwable ex){
                    logger.error(String.format("加载工程[%s]下的配置时出错, xml [\n%s", this.getBasePath(),xml), ex);
                }
            }
        }
        return database;
    }
    
    public boolean break_ = false;
    
    public synchronized boolean getBreak() {
        return this.break_;
    }
    
    protected void finalize() throws Throwable {
        super.finalize();
        if(this.conn != null && !this.conn.isClosed()){