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);
}
}
}