数据库连接池-配置 wallfilter问题解决-UncategorizedSQLException

...

解决办法，关闭语法检测-----StrictSyntaxCheck(false)

 wallConfig.setStrictSyntaxCheck(<span style="color: #0000ff;">false</span>);<span style="color: #008000;">//</span><span style="color: #008000;">是否进行严格的语法检测，Druid SQL Parser在某些场景不能覆盖所有的SQL语法，出现解析SQL出错，可以临时把这个选项设置为false，同时把SQL反馈给Druid的开发者。</span>

配置示例：

<span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.filter.Filter;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.pool.DruidDataSource;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.support.http.StatViewServlet;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.support.http.WebStatFilter;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.wall.WallConfig;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.alibaba.druid.wall.WallFilter;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.sql.SQLException;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.ArrayList;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> java.util.List;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> javax.sql.DataSource;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.slf4j.Logger;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.slf4j.LoggerFactory;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.beans.factory.annotation.Autowired;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.beans.factory.annotation.Value;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.boot.web.servlet.FilterRegistrationBean;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.boot.web.servlet.ServletRegistrationBean;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.context.annotation.Bean;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.context.annotation.Configuration;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.context.annotation.DependsOn;
</span><span style="color: #0000ff;">import</span><span style="color: #000000;"> org.springframework.core.io.support.PathMatchingResourcePatternResolver;
</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * ClassName: DruidConfig <br>
 * Function: Druid连接池初始化 <br>
 * </span><span style="color: #808080;">@version</span><span style="color: #008000;">
 * </span><span style="color: #808080;">@since</span><span style="color: #008000;"> JDK 1.8
 </span><span style="color: #008000;">*/</span><span style="color: #000000;">
@Configuration
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> DruidConfig {
    </span><span style="color: #0000ff;">private</span> Logger logger = LoggerFactory.getLogger(DruidConfig.<span style="color: #0000ff;">class</span><span style="color: #000000;">);
    @Value(</span>"${spring.datasource.url}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String dbUrl;
    @Value(</span>"${spring.datasource.username}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String username;
    @Value(</span>"${spring.datasource.password}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String password;
    @Value(</span>"${spring.datasource.driverClassName}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String driverClassName;
    @Value(</span>"${spring.datasource.initialSize}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> initialSize;
    @Value(</span>"${spring.datasource.minIdle}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> minIdle;
    @Value(</span>"${spring.datasource.maxActive}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> maxActive;
    @Value(</span>"${spring.datasource.maxWait}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> maxWait;
    @Value(</span>"${spring.datasource.timeBetweenEvictionRunsMillis}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> timeBetweenEvictionRunsMillis;
    @Value(</span>"${spring.datasource.minEvictableIdleTimeMillis}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> minEvictableIdleTimeMillis;
    @Value(</span>"${spring.datasource.validationQuery}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String validationQuery;
    @Value(</span>"${spring.datasource.testWhileIdle}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">boolean</span><span style="color: #000000;"> testWhileIdle;
    @Value(</span>"${spring.datasource.testOnBorrow}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">boolean</span><span style="color: #000000;"> testOnBorrow;
    @Value(</span>"${spring.datasource.testOnReturn}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">boolean</span><span style="color: #000000;"> testOnReturn;
    @Value(</span>"${spring.datasource.filters}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String filters;
    @Value(</span>"${mybatis-plus.mapper-locations}"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">private</span><span style="color: #000000;"> String mapperLocations;
    @Autowired
    WallFilter wallFilter;
    @Bean
    </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ServletRegistrationBean();
        reg.setServlet(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> StatViewServlet());
        reg.addUrlMappings(</span>"/druid/*"<span style="color: #000000;">);
        reg.addInitParameter(</span>"loginUsername"<span style="color: #000000;">, username);
        reg.addInitParameter(</span>"loginPassword"<span style="color: #000000;">, password);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> reg;
    }
    @Bean
    </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> FilterRegistrationBean();
        filterRegistrationBean.setFilter(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> WebStatFilter());
        filterRegistrationBean.addUrlPatterns(</span>"/*"<span style="color: #000000;">);
        filterRegistrationBean
            .addInitParameter(</span>"exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"<span style="color: #000000;">);
        filterRegistrationBean.addInitParameter(</span>"profileEnable", "true"<span style="color: #000000;">);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> filterRegistrationBean;
    }
    @Bean
    </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> DataSource druidDataSource() {
        DruidDataSource datasource </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        </span><span style="color: #008000;">//</span><span style="color: #008000;"> filter</span>
        List<Filter> filterArrayList = <span style="color: #0000ff;">new</span> ArrayList<><span style="color: #000000;">();
        filterArrayList.add(wallFilter);
        datasource.setProxyFilters(filterArrayList);
        </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
            datasource.setFilters(filters);
        } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
            logger.error(</span>"druid configuration initialization filter"<span style="color: #000000;">, e);
        }
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> datasource;
    }
    @Bean(name </span>= "wallFilter"<span style="color: #000000;">)
    @DependsOn(</span>"wallConfig"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> WallFilter wallFilter(WallConfig wallConfig) {
        WallFilter wallFilter </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> WallFilter();
        wallFilter.setConfig(wallConfig);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> wallFilter;
    }
    @Bean(name </span>= "wallConfig"<span style="color: #000000;">)
    </span><span style="color: #0000ff;">public</span><span style="color: #000000;"> WallConfig wallConfig() {
        WallConfig wallConfig </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> WallConfig();
        wallConfig.setMultiStatementAllow(</span><span style="color: #0000ff;">true</span>);<span style="color: #008000;">//</span><span style="color: #008000;">允许一次执行多条语句</span>
        wallConfig.setNoneBaseStatementAllow(<span style="color: #0000ff;">true</span>);<span style="color: #008000;">//</span><span style="color: #008000;">允许一次执行多条语句</span>
        wallConfig.setStrictSyntaxCheck(<span style="color: #0000ff;">false</span>);<span style="color: #008000;">//</span><span style="color: #008000;">是否进行严格的语法检测，Druid SQL Parser在某些场景不能覆盖所有的SQL语法，出现解析SQL出错，可以临时把这个选项设置为false，同时把SQL反馈给Druid的开发者。</span>
        <span style="color: #0000ff;">return</span><span style="color: #000000;"> wallConfig;
    }
}</span>

数据库连接池-配置 wallfilter问题解决-UncategorizedSQLException

标签：enable   public   Nid   state   tween   matching   database   初始   问题