ClickHouse【SpringBoot集成】clickhouse+mybatis-plus配置及使用问题说明（含建表语句、demo源码、测试说明

INSERT INTO tb_stat VALUES( ‘1’,‘1232364’, ‘111’, 32, 2, ‘2021-07-09 12:56:00’ );
INSERT INTO tb_stat VALUES( ‘2’,‘1232364’, ‘111’, 34, 44, ‘2021-07-09 12:21:00’ );
INSERT INTO tb_stat VALUES( ‘3’,‘1232364’, ‘111’, 54, 12, ‘2021-07-09 12:20:00’ );
INSERT INTO tb_stat VALUES( ‘4’,‘1232364’, ‘222’, 45, 11, ‘2021-07-09 12:13:00’ );
INSERT INTO tb_stat VALUES( ‘5’,‘1232364’, ‘222’, 32, 33, ‘2021-07-09 12:44:00’ );
INSERT INTO tb_stat VALUES( ‘6’,‘1232364’, ‘222’, 12, 23, ‘2021-07-09 12:22:00’ );
INSERT INTO tb_stat VALUES( ‘7’,‘1232364’, ‘333’, 54, 54, ‘2021-07-09 12:11:00’ );
INSERT INTO tb_stat VALUES( ‘8’,‘1232364’, ‘333’, 22, 74, ‘2021-07-09 12:55:00’ );
INSERT INTO tb_stat VALUES( ‘9’,‘1232364’, ‘333’, 12, 15, ‘2021-07-09 12:34:00’ );

### 2.依赖


这里只粘贴相关依赖：

    <!-- https://mvnrepository.com/artifact/ru.yandex.clickhouse/clickhouse-jdbc -->
    <dependency>
        <groupId>ru.yandex.clickhouse</groupId>
        <artifactId>clickhouse-jdbc</artifactId>
        <version>0.2.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.21</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.2</version>
    </dependency>

### 3.配置


application.yml 配置：SpringBoot默认是不注入 clickhouse 属性值的，需要自己绑定。

server:
port: 8080
spring:
datasource:
# 数据源选择
type: com.alibaba.druid.pool.DruidDataSource
# clickhouse配置
click:
driverClassName: ru.yandex.clickhouse.ClickHouseDriver
url: jdbc:clickhouse://172.81.205.216:8123/default
username: default
password:
initialSize: 10
maxActive: 100
minIdle: 10
maxWait: 6000

Bean配置：用了druid监控所以在这里边初始化了，这个 DataSource 也可以在启动类里初始化。

@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties(prefix = “spring.datasource.click”)
public DataSource druidDataSource() {
return new DruidDataSource();
}
}
// 或者用
@SpringBootApplication
@MapperScan(value = “com.example.demo.**.mapper”)
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
@Bean
@ConfigurationProperties(prefix = “spring.datasource.click”)
public DataSource druidDataSource() {
return new DruidDataSource();
}
}

### 3.使用


**尝试使用mybatis-plus-generator代码生成报错：**

DB::Exception: Syntax error: failed at position 6 (‘table’) (line 1, col 6): table status WHERE 1=1 AND NAME IN (‘tb_stat’)

所以entity、mapper、service、controller使用的是其他库表生成的代码然后修改的。这里只贴出重要的类：  
 **entity代码：** statDate字段要使用@JsonFormat格式化日期字符串。

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName(value = “tb_stat”)
@ApiModel(value = “Stat对象”, description = “”)
public class Stat implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = “ID”)
private String id;
@ApiModelProperty(value = “区域”)
private String region;
@ApiModelProperty(value = “分组”)
private String group;
@ApiModelProperty(value = “昨天”)
private Integer yesterday;
@ApiModelProperty(value = “今天”)
private Integer today;
@ApiModelProperty(value = “时间”)
@JsonFormat(locale=“zh”, timezone=“GMT+8”, pattern=“yyyy-MM-dd HH:mm:ss”)
private Date statDate;
}

**controller代码：**

@RestController
@RequestMapping(“/stat”)
public class StatController {
@Autowired
private IStatService statService;
@PostMapping(“/add”)
public boolean addStat(@RequestBody Stat stat) {
return statService.save(stat);
}
@GetMapping(“/del/{id}”)
public boolean delStatById(@PathVariable String id) {
return statService.removeById(id);
}
@PostMapping(“/update”)
public boolean updateStat(@RequestBody Stat stat) {
return statService.updateById(stat);
}
@PostMapping(“/list”)
public List getStatList() {
LambdaQueryWrapper query = Wrappers.lambdaQuery(Stat.class);
return statService.list(query);
}
}

**测试结果说明：**  
 添加和查询可以正藏使用mybatis-plus的api，删除和更新时会报错：


![img](https://img-blog.csdnimg.cn/img_convert/7b94a284516962bba3f1a9211fee9b94.png)
![img](https://img-blog.csdnimg.cn/img_convert/5e564b7bc6fb36aa71b7f7e76bc3f488.png)
![img](https://img-blog.csdnimg.cn/img_convert/9f61226ee6acbd3fb4daaed0cf9a5276.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！**

3w1aUb-1719211685523)]
[外链图片转存中...(img-0l6x3TBh-1719211685524)]
[外链图片转存中...(img-juGo2dYC-1719211685525)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！**