访问SQLServer(3)-springboot自定义DBUtil(从druid连接池获取连接)操作SQLServer数据库_druid mssql

Spring-Boot通过druid连接池获取连接操作SQLServer数据库
总结：【共3步】
第1步：添加druid和MSSQL Driver的依赖项；
第2步：配置连接字符串；
第3步：创建数据库操作类，使用@Bean注入DruidDataSource，
      然后根据DruidDataSource获取到空闲连接操作数据库；

1. 创建spring-boot工程，选择Web依赖项、SQLServer Driver依赖项：

2. 手动添加druid的依赖项：

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.0.18</version>
</dependency>

 3. application.properties配置：

spring.datasource.url=jdbc:sqlserver://localhost:1451;instanceName=sql2008;DatabaseName=duliang_db
spring.datasource.password=dul_lw
spring.datasource.username=sa
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

4. 创建全局上下文访问类：

package com.cx.common;
 
import org.springframework.context.ApplicationContext;
 
public class SpringContextUtil {
    private static ApplicationContext applicationContext;
 
    public static void setApplicationContext(ApplicationContext context) {
        applicationContext = context;
    }
 
    public static Object getBean(String beanId) {
        return applicationContext.getBean(beanId);
    }
}

5. 创建自定义数据库操作类DBUtilDruid：

package com.cx.common;
 
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
 
import javax.sql.DataSource;
import java.sql.*;
 
@Component
public class DBUtilDruid {
    @Autowired
    private Environment environment;
 
    @Bean(name = "getDataSource")
    public DataSource getDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        String surl = environment.getProperty("spring.datasource.url");
        System.out.println(surl);
        dataSource.setUrl(environment.getProperty("spring.datasource.url"));
        dataSource.setUsername(environment.getProperty("spring.datasource.username"));
        dataSource.setPassword(environment.getProperty("spring.datasource.password"));
        return dataSource;
    }
 
    //关闭资源
    public void close(Statement ps, Connection conn){
        try {
            if (ps != null && !ps.isClosed()) {
                ps.isClosed();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }catch (SQLException ex){
            System.out.println("[Error]" + ex.getMessage());
        }
    }
 
    public String executeScalar(String p_sql){
        String result="";
        Connection conn = null;
        Statement stmt = null;
        try {
            DataSource ds = (DataSource)SpringContextUtil.getBean("getDataSource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
            ResultSet set = stmt.executeQuery(p_sql);
            if (set.next()){
                result = set.getString(1);
            }
            stmt.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(stmt, conn);
        }
        return "";
    }
 
    public boolean executeSQL(String sql){
        Connection conn = null;
        Statement stmt = null;
        try {
            DataSource ds = (DataSource)SpringContextUtil.getBean("getDataSource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.close();
            conn.close();
            return true;
        }
        catch (Exception e){
            return  false;
        }
        finally {
            close(stmt, conn);
        }
    }
}

6. 创建控制器进行测试：

package com.cx;
 
import com.cx.common.DBUtilDruid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
 
@Controller
public class MyController {
    @Autowired
    DBUtilDruid dbUtilDruid;
 
    @ResponseBody
    @RequestMapping("/getScalar")
    public String getScalar(){
        String sSql = "SELECT ActualString FROM dbo.DictTable WHERE D_ID = 1";
        return dbUtilDruid.executeScalar(sSql);
    }
}

7. 在main函数中设置全局上下文：

8. 运行测试：

9. 遇到的问题，如果druid的版本不匹配将会有如下异常：

运行后产生异常：java.sql.SQLException: validateConnection false 恢复版本1.0.18即可正常运行；

附注，项目下载地址 访问SQLServer-springboot自定义DBUtil(druid连接池)操作SQLServer数据库-Java文档类资源-CSDN下载