SpringBoot - com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 ‘DUAL‘ 无效 | Druid双数据源MySQL+SQL ser

傷城~ 2024-03-17 13:46 9阅读 0赞

在SpringBoot项目中使用双数据源MySQL和SQLserver时，运行报错

com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效
    
    nested exception is org.apache.ibatis.exceptions.PersistenceException:
    
    ### Error querying database.
    
    Cause: org.springframework.jdbc.CannotGetJdbcConnectionException:
    
    Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。
    
    ### The error may exist in file [D:\workspace(IDEA)\xxxx\xxx\target\classes\mapper\xxx\xxx.xml]
    
    ### The error may involve com.xxx.xxx.mapper.xxx.countMaleXunwu
    
    ### The error occurred while executing a query
    
    ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection;
    
    nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。
    
    com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。
    
    ### Error querying database.
    
    Cause: org.springframework.jdbc.CannotGetJdbcConnectionException:
    
    Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。
    
    ### The error may exist in file [D:\workspace(IDEA)\xxx\xxx\target\classes\mapper\xxx\xxx.xml]
    
    ### The error may involve com.xxx.xxx.xxx.xxxx.countMaleXunwu
    
    ### The error occurred while executing a query
    
    ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 'DUAL' 无效。]
    
    15:24:52.057 [Druid-ConnectionPool-Create-1458706578] ERROR c.a.d.p.DruidDataSource - [run,2787] - create connection SQLException, url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test, errorCode 208, state S0002

我们在自己的代码里面全局搜索这个DUAL，只有在Druid里面使用了这个语句去验证数据库的连接信息

导致错误产生原因：[druid][] 检查sql 有效性，在application.prppertis 文件添加如下配置

# 配置检测连接是否有效，用来检测连接是否有效的sql，要求是一个查询语句(mybatis)
    validationQuery: SELECT 1 FROM DUAL

在MySQL下面可以正常的使用返回1，MySQL有对象DUAL

但是SQL server 没有对象DUAL,也就是这个语句在SQL server下面是不能运行的，所以报错了

解决方法

改成

validationQuery: SELECT 1

导入salserver驱动包

<dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>sqljdbc4</artifactId>
                <version>4.0</version>
            </dependency>

yml完整配置如下：

# 数据源配置
    spring:
        datasource:
            # 数据库连接池 统一使用com.zaxxer.hikari.HikariDataSource；不使用com.alibaba.druid.pool.DruidDataSource
            type: com.zaxxer.hikari.HikariDataSource
            driverClassName: com.mysql.cj.jdbc.Driver
            druid:
                # 主库数据源
                master:
                    url: jdbc:mysql://${DB_HOST:XX}:${DB_PORT:3306}/${DB_NAME:XX}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
                    username: ${DB_USER:XX}
                    password: ${DB_PWD:XX}
                # 从库数据源
                sqlserver:
                    # 从数据源开关/默认关闭
                    enabled: true
                    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver  # :${DB_PORT:1433}
                    url: jdbc:sqlserver://XX:1433;DatabaseName=XX
                    username: XX
                    password: XX
                # 初始连接数
                initialSize: 5
                # 最小连接池数量
                minIdle: 10
                # 最大连接池数量
                maxActive: 20
                # 配置获取连接等待超时的时间
                maxWait: 60000
                # 配置连接超时时间
                connectTimeout: 30000
                # 配置网络超时时间
                socketTimeout: 60000
                # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
                timeBetweenEvictionRunsMillis: 60000
                # 配置一个连接在池中最小生存的时间，单位是毫秒
                minEvictableIdleTimeMillis: 300000
                # 配置一个连接在池中最大生存的时间，单位是毫秒
                maxEvictableIdleTimeMillis: 900000
                # 配置检测连接是否有效
                validationQuery: SELECT 1 #SELECT 1 FROM DUAL
                testWhileIdle: true
                testOnBorrow: false
                testOnReturn: false
                webStatFilter:
                    enabled: true
                statViewServlet:
                    enabled: true
                    # 设置白名单，不填则允许所有访问
                    allow:
                    url-pattern: /druid/*
                    # 控制台管理用户名和密码
                    login-username: admin
                    login-password: 123456
                filter:
                    stat:
                        enabled: true
                        # 慢SQL记录
                        log-slow-sql: true
                        slow-sql-millis: 1000
                        merge-sql: true
                    wall:
                        config:
                            multi-statement-allow: true

动态数据源配置：

package com.ceekee.hotel.config;
    
    import java.io.IOException;
    import java.util.HashMap;
    import java.util.Map;
    import javax.servlet.Filter;
    import javax.servlet.FilterChain;
    import javax.servlet.ServletException;
    import javax.servlet.ServletRequest;
    import javax.servlet.ServletResponse;
    import javax.sql.DataSource;
    
    import com.ceekee.hotel.config.properties.DruidProperties;
    import com.ceekee.hotel.datasource.DynamicDataSource;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
    import com.alibaba.druid.util.Utils;
    import com.ceekee.hotel.enums.DataSourceType;
    import com.ceekee.hotel.utils.spring.SpringUtils;
    
    /**
     * druid 配置多数据源
     *
     * @author zhangming
     */
    @Configuration
    public class DruidConfig {
        @Bean
        @ConfigurationProperties("spring.datasource.druid.master")
        public DataSource masterDataSource(DruidProperties druidProperties)
        {
            DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
            return druidProperties.dataSource(dataSource);
        }
    
        @Bean
        @ConfigurationProperties("spring.datasource.druid.slave")
        @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
        public DataSource slaveDataSource(DruidProperties druidProperties) {
            DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    
            //!!! druid 检查sql 有效性
            // 不加报错：com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'DUAL'.
            dataSource.setValidationQuery("SELECT 1");
    
            return druidProperties.dataSource(dataSource);
        }
    
        
        @Bean(name = "dynamicDataSource")
        @Primary
        public DynamicDataSource dataSource(DataSource masterDataSource)
        {
            Map<Object, Object> targetDataSources = new HashMap<>();
            targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
            setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
            
            return new DynamicDataSource(masterDataSource, targetDataSources);
        }
    
        /**
         * 设置数据源
         *
         * @param targetDataSources 备选数据源集合
         * @param sourceName 数据源名称
         * @param beanName bean名称
         */
        public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
        {
            try
            {
                DataSource dataSource = SpringUtils.getBean(beanName);
                targetDataSources.put(sourceName, dataSource);
            }
            catch (Exception e)
            {
            }
        }
    
        /**
         * 去除监控页面底部的广告
         */
        @SuppressWarnings({ "rawtypes", "unchecked" })
        @Bean
        @ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
        public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
        {
            // 获取web监控页面的参数
            DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
            // 提取common.js的配置路径
            String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
            String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
            final String filePath = "support/http/resources/js/common.js";
            // 创建filter进行过滤
            Filter filter = new Filter()
            {
                @Override
                public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
                {
                }
                @Override
                public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
                        throws IOException, ServletException
                {
                    chain.doFilter(request, response);
                    // 重置缓冲区，响应头不会被重置
                    response.resetBuffer();
                    // 获取common.js
                    String text = Utils.readFromResource(filePath);
                    // 正则替换banner, 除去底部的广告信息
                    text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                    text = text.replaceAll("powered.*?shrek.wang</a>", "");
                    response.getWriter().write(text);
                }
                @Override
                public void destroy()
                {
                }
            };
            FilterRegistrationBean registrationBean = new FilterRegistrationBean();
            registrationBean.setFilter(filter);
            registrationBean.addUrlPatterns(commonJsPattern);
            return registrationBean;
        }
    }

[druid]: https://so.csdn.net/so/search?q=druid&spm=1001.2101.3001.7020