上一篇( Post not found: ClickHouse数据库 ClickHouse数据库 ) 我写了关于ClickHouse数据库的安装以及数据到,接下来就是实际的用代码操作查询了。
1.pom.xml中添加相关的依赖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId> org.springframework.boot </groupId> <artifactId> spring-boot-configuration-processor </artifactId> <optional> true </optional> </dependency>
<dependency> <groupId>ru.yandex.clickhouse</groupId> <artifactId>clickhouse-jdbc</artifactId> <version>0.1.53</version> </dependency>
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.21</version> </dependency>
|
2.修改application.yml配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| spring: datasource: type: com.alibaba.druid.pool.DruidDataSource click: driverClassName: ru.yandex.clickhouse.ClickHouseDriver url: jdbc:clickhouse://192.168.1.53:8123/default initialSize: 10 maxActive: 100 minIdle: 10 maxWait: 6000 mybatis: configuration: map-underscore-to-camel-case: true type-aliases-package: com.bibichuan.Entity mapper-locations: classpath:mapper/*.xml
|
如果要输入用户名密码的话,使用:jdbc:clickhouse://192.168.1.53:8123/lsmfhx?user=admin&password=1q2w3e4r
3.添加Druid的两个配置类
JdbcParamConfig配置类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| package com.bibichuan.Durid;
import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component;
@Component @ConfigurationProperties(prefix = "spring.datasource.click") public class JdbcParamConfig { private String driverClassName ; private String url ; private Integer initialSize ; private Integer maxActive ; private Integer minIdle ; private Integer maxWait ; }
|
DruidConfig的配置类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| package com.bibichuan.Durid;
import com.alibaba.druid.pool.DruidDataSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;
import javax.annotation.Resource; import javax.sql.DataSource;
@Configuration public class DruidConfig { @Resource private JdbcParamConfig jdbcParamConfig; @Bean public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(jdbcParamConfig.getUrl()); datasource.setDriverClassName(jdbcParamConfig.getDriverClassName()); datasource.setInitialSize(jdbcParamConfig.getInitialSize()); datasource.setMinIdle(jdbcParamConfig.getMinIdle()); datasource.setMaxActive(jdbcParamConfig.getMaxActive()); datasource.setMaxWait(jdbcParamConfig.getMaxWait()); return datasource; } }
|
注意
这里有一点我想提醒一下,@Component注解所在的包名尽量和main函数即@SpringBoot注解所在的包名相同,否则就要在main使用@ComponentScan(basePackages = {“com.**”})添加扫描的包路径,我时常犯这个错误,明明写了注解,就是找不到这个Bean所在的位置,搞了好长时间才找到原因。
4.resources下新建mybatis-config.xml文件(这一步不要也行)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <typeAliases> <typeAlias alias="Integer" type="java.lang.Integer" /> <typeAlias alias="Long" type="java.lang.Long" /> <typeAlias alias="HashMap" type="java.util.HashMap" /> <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" /> <typeAlias alias="ArrayList" type="java.util.ArrayList" /> <typeAlias alias="LinkedList" type="java.util.LinkedList" /> </typeAliases> </configuration>
|
5.新建实体类,映射数据表
1 2 3 4 5 6 7 8 9 10
| package com.bibichuan.Entity;
import javax.persistence.Entity; import javax.persistence.Id;
public class RealTime { private Long id; }
|
6.在resource文件夹下新建mapper文件夹
在resource文件夹下新建mapper文件夹并添加一个实体类的映射文件RealTime.xml
1 2 3 4 5 6 7 8 9 10 11
| <?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.proheng.gis.mapper.VClientMapper"> <select id="searchAll" resultType="com.bibichuan.Entity.RealTime"> select * from realtime; </select>
</mapper>
|
这里的映射文件还是不太熟悉。
7.在springboot的启动类上,添加扫描注解
在springboot的启动类main上,添加@MapperScan注解,指定上面创建的mapper接口。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication @MapperScan("com.bibichuan.mapper") public class BigdataApplication {
public static void main(String[] args) { SpringApplication.run(BigdataApplication.class, args); }
}
|
注意
@MapperScan注解要找准扫描的类路径,和mapper/*.xml中定义的路径差不多。
8.使用mapper
在控制器中引入realTimeMapper,有些地方又多加了服务层,我这里比较简单,就直接把服务层去掉了,直接用控制层调用dao层。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| package com.bibichuan.bigdata.Ctrl;
import com.bibichuan.bigdata.mapper.RealTimeMapper; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
@RestController @RequestMapping("/api") public class ApiCtrl { @Resource private RealTimeMapper realTimeMapper;
@RequestMapping("/helloword") public String getData(){ System.out.println("helloworld"); System.out.println(realTimeMapper.searchAll()); return "helloworld"; } }
|
注意
这里还是要注意searchAll()方法要和RealTime.xml里面定义的方法一至,否则会报错:
总结
1.pom.xml中接入依赖包
2.application.yml编写配置信息
3.编写DruidConfig的配置类和JdbcParamConfig配置类
4.编写实体类,映射数据表
5.编写Mapper接口及接口方法,选择、插入、删除等
6.编写resource/mapper下的映射文件*.xml,方法对应Mapper接口,namespace对应Mapper接口包名
7.在启动类上添加Mapper接口扫描
8.编写控制层或者服务层调用,注入Mapper接口
问题
也不能说是问题吧,其实也不影响查询,执行查询的时候,出现:testWhileIdle is true, validationQuery not set。
(1) 编写配置文件,在click属性下添加如下两个属性
1 2
| validationQuery: SELECT 1 testWhileIdle: true
|
(2) 修改JdbcParamConfig类,添加上面两个属性
1 2 3
| private Boolean testWhileIdle; private String validationQuery;
|
(3) 将属性注入到dataSource的bean中
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| @Configuration public class DruidConfig { @Resource private JdbcParamConfig jdbcParamConfig; @Bean public DataSource dataSource() { DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(jdbcParamConfig.getUrl()); datasource.setDriverClassName(jdbcParamConfig.getDriverClassName()); datasource.setInitialSize(jdbcParamConfig.getInitialSize()); datasource.setMinIdle(jdbcParamConfig.getMinIdle()); datasource.setMaxActive(jdbcParamConfig.getMaxActive()); datasource.setMaxWait(jdbcParamConfig.getMaxWait());
datasource.setValidationQuery(jdbcParamConfig.getValidationQuery()); datasource.setTestWhileIdle(jdbcParamConfig.getTestWhileIdle());
return datasource; } }
|
上面三步,就完成了。