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

来源：互联网发布：淘宝怎样改店名编辑：程序博客网时间：2024/06/01 07:21

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

阅读全文

0 0