多个数据库执行同一个sql的小脚本 - 原创

一、两个配置文件:

　　一个是连接数据库的配置文件，用XML可配置多个数据库连接参数（ database.xml）

　　一个是放置sql语句的配置文件，sql语句需按sql规范编写好（sql.sql）

 

二、所需jar包如下：

　　dom4j-1.6.1.jar

　　ibatis-2.3.4.726.jar

　　mysql-connector-java-5.1.8-bin.jar

 

三、Java代码如下:

MysqlOperation.java 

package sqlRun;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.ibatis.common.jdbc.ScriptRunner;

public class MysqlOperation {
    
    public static void main(String[] args) {
        actionSql();
    }
    
    @SuppressWarnings("rawtypes")
    public static void actionSql(){
        List<Map> dataSourceList = ReadSqlXml.xml();
        for(Map map : dataSourceList){
            Connection conn=null;
            try {
                Class.forName((String) map.get("driver"));
                String url = (String) map.get("url");
                String username = (String) map.get("userName");
                String password = (String) map.get("password");
                conn = DriverManager.getConnection(url,username,password);    //循环获取每一个数据的连接
                
                ScriptRunner runner = new ScriptRunner(conn, false, true);    
                try {
                    runner.setErrorLogWriter(null);
                    runner.setLogWriter(null);
                    runner.runScript(new FileReader("sql.sql"));     //每一个数据库都执行sql脚本
                    
                } catch (SQLException e) {
                    System.out.println(url);
                    System.out.println(e);
                }catch (IOException e) {
                    System.out.println(url);
                    System.out.println(e);
                }
                MysqlOperation.close(conn);   //关闭数据库操作
            } catch (ClassNotFoundException e) {
                System.out.println(e);
            } catch (SQLException e) {
                System.out.println(e);
            }
        }
    }
    public static void close(Connection conn){
        if(conn!=null)
        {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println(e);
            }
        }
    }
}

 

ReadSqlXml.java

package sqlRun;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;


public class ReadSqlXml {
    @SuppressWarnings("rawtypes")
    public static void main(String[] args) {
        List<Map> list = xml();
        for(Map mapping : list){
            System.out.println(mapping.get("driver"));
            System.out.println(mapping.get("url"));
            System.out.println(mapping.get("userName"));
            System.out.println(mapping.get("password"));
            System.out.println("=======================");
        }
    }
    //测试
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static List<Map> xml()  {
        List<Map> list = new ArrayList<Map>();
        SAXReader reader = new SAXReader();
        Document document = null;
        try {
            document = reader.read("F:/sqlRunTool/database.xml");
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        Element root = document.getRootElement();
        List<Element> childElements = root.elements();
        
        for (Element child1 : childElements) {
            List<Element> childElements2 = child1.elements();
            Map<Object,Object> param = new HashMap<Object,Object>();
            for (Element child2 : childElements2) {
                String key = child2.attributeValue("property");
                String value = child2.getText();
                param.put(key, value);
            }
            list.add(param);
        }
        return list;
    }
}