SSM环境下，获取指定数据库连接执行sql文件

1.需求场景

多数据源下，获取指定数据源连接，执行原生sql语句。

2.项目环境

spring，spring mvc， mybatis，MySQL

3.实现方法

(1)利用spring 环境，获取数据源连接

package com.jlc.util.jdbc;

import java.sql.Connection;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.web.context.ContextLoader;
import org.springframework.web.context.WebApplicationContext;



/**
 * 数据库链接帮助类
 * 
 * @author cch
 * @date 2014-09-11
 */
public class DBConnection {
	private Connection connection = null;
	private static DBConnection instance = new DBConnection();

	private DBConnection() {
	}

	/**
	 * 单实例
	 */
	public static DBConnection getInstance() {
		return instance;
	}

	/**
	 * 获得数据库链接
	 */
	public Connection getConnection(String dataSource) throws Exception {
		if (this.connection == null || this.connection.isClosed()) {
		
			WebApplicationContext ct = ContextLoader.getCurrentWebApplicationContext();
			BasicDataSource datasource = (BasicDataSource) ct
					.getBean(dataSource);
			this.connection = datasource.getConnection();
		}

		return this.connection;
	}


	/**
	 * 关闭数据库连接
	 */
	public static void closeConnection(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放资源
	 **/
	private void closeAll(ResultSet rs, PreparedStatement st) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if (st != null) {
				st.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

(2)执行原生sql语句

package com.sql.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.jdbc.ScriptRunner;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.jlc.util.jdbc.DBConnection;

public class SqlRunDemo {
	private final static SimpleDateFormat sdfymdhm = new SimpleDateFormat("yyyyMMddHHmmss");
	 
	@RequestMapping(value = "/insertdata")  
	public void runsql(@RequestParam(value = "file", required = false) MultipartFile file,
	 HttpServletRequest request){
		String fileName="";
		String filePath="";
		Connection connection =null;
		ScriptRunner runner =null;
			String path = request.getSession().getServletContext().getRealPath("path");
			  //上传脚本文件
				fileName =  FileUtil.uploadFileSQL(file,request,path);
				filePath = path+File.separator+fileName;//上传文件的真实路径
				try{
				//获取指定数据源连接
				connection = DBConnection.getInstance().getConnection("dataSource8");
				runner = new ScriptRunner(connection);
				runner.setErrorLogWriter(null);
			  runner.setLogWriter(null);
			  runner.runScript(new InputStreamReader(new FileInputStream(filePath),"UTF-8")); 
			  }catch(Exception e){
			   e.printStackTrace();
			  }finally {
				
				DBConnection.closeConnection(connection);
				
			}
	}
}