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

63 阅读 0 评论 42 点赞

我是靠谱客的博主发嗲魔镜，最近开发中收集的这篇文章主要介绍访问SQLServer(3)-springboot自定义DBUtil(从druid连接池获取连接)操作SQLServer数据库，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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函数中设置全局上下文：