ClassInstance

今天遇到一个druid不支持clickhouse的问题，druid执行clickhouse报错：sql injection violation, dbType clickhouse , druid-version 1.2.2, syntax error

错误如下：

	Caused by: java.sql.SQLException: sql injection violation, dbType clickhouse, , druid-version 1.2.2, syntax error: syntax error, error in :'icle r where first_appear_time    <, pos 36, line 1, column 36, token IDENTIFIER r : delete from bd_first_visit_vehicle r where first_appear_time    <   ?;
	at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:849)
	at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:292)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
	at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
	at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:295)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:351)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
	at com.sun.proxy.$Proxy229.prepareStatement(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:213)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)

注释如下：

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
import java.util.Properties;


@Configuration
@MapperScan(basePackages = "com.xx.clickhouse", sqlSessionFactoryRef = "clickHouseSqlSessionFactory")
public class ClickHouseDataSourceConfig {

    /**
     * clickhouse数据库名，每个sql的表都要带上库名，否则分布式查询语句可能会报错
     */
    @Value("${spring.clickhouse.db}")
    private String clickHouseDb;

    @Value("${spring.clickhouse.clusterName}")
    private String clusterName;

    @Bean("clickHouseDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.clickhouse")
    public DataSource clickHouseDataSource() {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();

//        List<Filter> filterList = new ArrayList<>();
//        filterList.add(ckWallFilter());
//        filterList.add(ckStatFilter());
//        dataSource.setProxyFilters(filterList);

        return dataSource;
    }

    @Bean("clickHouseSqlSessionFactory")
    public SqlSessionFactory clickHouseSqlSessionFactory(@Qualifier("clickHouseDataSource") DataSource clickhouseDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(clickhouseDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:/clickhouse/*.xml"));
        Properties properties = new Properties();
        properties.put("dbname", clickHouseDb);
        properties.put("clusterName", clusterName);
        bean.setConfigurationProperties(properties);

        return bean.getObject();
    }

    @Bean
    public SqlSessionTemplate clickhouseSqlSessionTemplate(@Qualifier("clickHouseSqlSessionFactory") SqlSessionFactory clickHouseSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(clickHouseSqlSessionFactory);
    }

    //配置过滤
//    @Bean
//    public WallFilter ckWallFilter() {
//        WallFilter wallFilter = new WallFilter();
//        wallFilter.setConfig(ckWallConfig());
//        wallFilter.setDbType(DbType.clickhouse);
//        return wallFilter;
//    }
//
//    @Bean
//    public StatFilter ckStatFilter(){
//        StatFilter statFilter = new StatFilter();
//        return statFilter;
//    }
//
//    @Bean
//    public WallConfig ckWallConfig() {
//        WallConfig config = new WallConfig();
//        config.setMultiStatementAllow(true);//允许一次执行多条语句
//        return config;
//    }

}