访问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=sql;DatabaseName=duliang_dbspring.datasource.password=dul_lwspring.datasource.username=saspring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

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

package mon;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 mon;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.ponent;import javax.sql.DataSource;import java.sql.*;@Componentpublic class DBUtilDruid {@Autowiredprivate 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 mon.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;@Controllerpublic class MyController {@AutowiredDBUtilDruid 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下载