Springboot+Mybatis+Druid整合ClickHouse

你的名字 2022-12-27 12:58 93阅读 0赞

### 文章目录 ###

*   *  核心依赖
     *  配置类
     *  测试

## 核心依赖 ##

<dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis-spring</artifactId>
                <version>1.3.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.4</version>
            </dependency>
             
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.10</version>
            </dependency>
            
            <dependency>
                <groupId>ru.yandex.clickhouse</groupId>
                <artifactId>clickhouse-jdbc</artifactId>
                <version>0.1.53</version>
            </dependency>

> 说明druid连接池版本需要升级到 1.1.7以上才能支持ClickHouse驱动

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70 1]

## 配置类 ##

ClickHouseDataSourceConfig

@Configuration
    @EnableApolloConfig("clickhouse")
    @MapperScan(basePackages = { "com.sinoxk.order.dao.clickhouse"}, sqlSessionFactoryRef = "clickHouseSqlSessionFactoryBean")
    public class ClickHouseDataSourceConfig { 
    
        @Value("${clickhouse.jdbc.datasource.username}")
        private String username;
    
        @Value("${clickhouse.jdbc.datasource.driver-class-name}")
        private String driverName;
    
        @Value("${clickhouse.jdbc.datasource.password}")
        private String password;
    
        @Value("${clickhouse.jdbc.datasource.url}")
        private String url;
    
        @Value("${clickhouse.jdbc.datasource.type}")
        private String type;
    
        @Value("${clickhouse.jdbc.datasource.max-wait:10000}")
        private long maxWait;
        @Value("${clickhouse.validationQuery}")
        private String validationQuery;
    
    
        @Bean(name = Constant.CLICKHOUSE_JDBC_DATA_SOURCE_NAME)
        public DataSource clickhouseDataSource() throws Exception { 
            Class classes = Class.forName(type);
            DruidDataSource dataSource = (DruidDataSource) DataSourceBuilder
                    .create()
                    .driverClassName(driverName)
                    .type(classes)
                    .url(url)
                    .username(username)
                    .password(password)
                    .build();
            dataSource.setMaxWait(maxWait);
            dataSource.setValidationQuery(validationQuery);
            return dataSource;
        }
    
        @Bean
        public SqlSessionFactory clickHouseSqlSessionFactoryBean() throws Exception { 
            SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
            factory.setDataSource(clickhouseDataSource());
            // 实体 model的 路径 比如 com.order.model
            factory.setTypeAliasesPackage(MODEL_PACKAGE);
            //添加XML目录
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            factory.setMapperLocations(resolver.getResources("classpath:mapper/clickhouse/*.xml"));
            //开启驼峰命名转换
            factory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
            return factory.getObject();
        }
    
    
    }

> 说明：  
> @EnableApolloConfig(“clickhouse”) 表示从apollo获取配置信息，如果使用本地配置文件请忽略  
> 配置 sqlSessionFactoryRef 名字不使用默认名字是因为配置了多数据源，如果只是单数据源也请忽略

配置文件,不用apollo直接将配置文件写在 application.properties

clickhouse.jdbc.datasource.url = jdbc:clickhouse://192.168.50.112:8123/test
    clickhouse.jdbc.datasource.username = test
    clickhouse.jdbc.datasource.password = 123456
    clickhouse.jdbc.datasource.driver-class-name = ru.yandex.clickhouse.ClickHouseDriver
    clickhouse.jdbc.datasource.type = com.alibaba.druid.pool.DruidDataSource
    clickhouse.jdbc.datasource.max-wait = -1
    validationQuery = SELECT 1

## 测试 ##

新增测试表

create table test_order
    (
        id    String,
        sales Int32,
        month Int32
    )
        engine = MergeTree() PRIMARY KEY id ORDER BY (id, month) SETTINGS index_granularity = 8192;

插入测试数据

INSERT INTO test_order2 (id, sales, month) VALUES ('1', 203, 201901);

*  测试 model

@Data
    @ToString
    public class TestOrder { 
    
        private String id;
    
        private Integer sales;
    
        private Integer month;
    }

*  Mapper 接口

@Repository
    public interface TestOrderMapper { 
    
        List<TestOrder> list();
    
    }

*  xml

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.sinoxk.order.dao.clickhouse.TestOrderMapper">
    
    
        <select id="list" resultType="com.sinoxk.order.model.TestOrder">
            select * from test_order;
        </select>
    </mapper>

*  测试类

@RunWith(SpringJUnit4ClassRunner.class)
    @SpringBootTest(classes = Application.class)
    @WebAppConfiguration
    public class TestOrderMapperTest extends SpringbootTestBase { 
    
        @Autowired
        TestOrderMapper testOrderMapper;
    
        @Test
        public void list() { 
            List<TestOrder> list = testOrderMapper.list();
            list.forEach(System.out::println);
        }
    
    
    }

运行结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]: /images/20221120/eaf82f513be443739b40c758243459a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70 1]: /images/20221120/f5e4e0e808174e10a359d8087a965b10.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201211140807233.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0,size_16,color_FFFFFF,t_70