一、服务器安装clickhouse服务

参阅 :https://www.cnblogs.com/liuyangfirst/p/13379064.html

二.连接数据库

成功

三、新建库

 CREATE/ATTACH DATABASE zabbix ENGINE = Ordinary;

ATTACH 也可以建库，但是metadata目录下不会生成.sql文件，一般用于metadata元数据sql文件被删除后，恢复库表结构使用。

这里采用

 CREATE DATABASE mrliu ENGINE = Ordinary;

四、创建表

 CREATE TABLE mrliu.userinformation (

 `id` UInt16,

 `user_name` String,

 `user_age` String,

 `user_sex` String,

 `user_id_card` String,

 `user_phone` String,

 `user_from` String,

 `user_minzu` String,

 `user_address` String,

 `user_zhiye` String,

 `user_educate` String,

 `iddeleted` Int8,

 `update_date` Date,

 `create_date` Date ) ENGINE = MergeTree(create_date,

 id,

 8192)

五、添加数据

 INSERT INTO mrliu.userinformation (id,user_name,user_age,user_sex,user_id_card,user_phone,user_from,user_minzu,user_address,user_zhiye,user_educate,iddeleted,update_date,create_date) VALUES (

 1,'赵大','','男','','','中国浙江','汉族','西湖区果哥子大街2020号','销售','本科',0,'2020-05-07','2020-07-25');

六、创建springboot项目

1.引入pom依赖

 <?xml version="1.0" encoding="UTF-8"?>

 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

     <modelVersion>4.0.0</modelVersion>

     <parent>

         <groupId>org.springframework.boot</groupId>

         <artifactId>spring-boot-starter-parent</artifactId>

         <version>2.2.5.RELEASE</version>

         <relativePath/> <!-- lookup parent from repository -->

     </parent>

     <groupId>com.mrliu</groupId>

     <artifactId>undertow</artifactId>

     <version>0.0.1-SNAPSHOT</version>

     <name>undertow</name>

     <description>Demo project for Spring Boot</description>

     <properties>

         <java.version>1.8</java.version>

         <spring-boot-admin.version>2.2.1</spring-boot-admin.version>

     </properties>

     <dependencies>

         <!--引入knife4j以来-->

         <dependency>

             <groupId>com.github.xiaoymin</groupId>

             <artifactId>knife4j-spring-boot-starter</artifactId>

             <version>1.9.6</version>

         </dependency>

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-web</artifactId>

         </dependency>

         <dependency>

             <groupId>com.google.guava</groupId>

             <artifactId>guava</artifactId>

             <version>20.0</version>

         </dependency>

         <dependency>

             <groupId>com.google.code.gson</groupId>

             <artifactId>gson</artifactId>

             <version>2.8.6</version>

             <scope>test</scope>

         </dependency>

         <!-- tomcat支持 -->

         <dependency>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-tomcat</artifactId>

             <scope>provided</scope>

         </dependency>

         <!-- 用于编译jsp-->

         <dependency>

             <groupId>org.apache.tomcat.embed</groupId>

             <artifactId>tomcat-embed-jasper</artifactId>

             <scope>provided</scope>

         </dependency>

         <!-- jsp标签库 -->

         <dependency>

             <groupId>javax.servlet</groupId>

             <artifactId>jstl</artifactId>

         </dependency>

         <dependency>

             <groupId>org.mybatis.spring.boot</groupId>

             <artifactId>mybatis-spring-boot-starter</artifactId>

             <version>1.3.2</version>

         </dependency>

         <dependency>

             <groupId>com.alibaba</groupId>

             <artifactId>druid-spring-boot-starter</artifactId>

             <version>1.1.13</version>

         </dependency>

         <dependency>

             <groupId>mysql</groupId>

             <artifactId>mysql-connector-java</artifactId>

             <version>5.1.38</version>

         </dependency>

         <dependency>

             <groupId>commons-lang</groupId>

             <artifactId>commons-lang</artifactId>

             <version>2.6</version>

         </dependency>

         <!-- clickHouse数据库 -->

         <dependency>

             <groupId>ru.yandex.clickhouse</groupId>

             <artifactId>clickhouse-jdbc</artifactId>

             <version>0.1.53</version>

             <exclusions>

                 <exclusion>

                     <artifactId>guava</artifactId>

                     <groupId>com.google.guava</groupId>

                 </exclusion>

             </exclusions>

         </dependency>

         <!--转化工具-->

         <dependency>

             <groupId>org.yaml</groupId>

             <artifactId>snakeyaml</artifactId>

             <version>1.26</version>

         </dependency>

     </dependencies>

     <build>

         <resources>

             <resource>

                 <directory>src/main/java</directory>

                 <includes>

                     <include>**/*.xml</include>

                 </includes>

                 <filtering>false</filtering>

             </resource>

         </resources>

         <plugins>

             <plugin>

                 <groupId>org.springframework.boot</groupId>

                 <artifactId>spring-boot-maven-plugin</artifactId>

             </plugin>

         </plugins>

     </build>

 </project>

2.实体类建立

 package com.mrliu.undertow.pojo;

 import io.swagger.annotations.ApiModel;

 import io.swagger.annotations.ApiModelProperty;

 /**

  * 用户信息

  *

  * @author liuyangos8888

  */

 @ApiModel("用户信息")

 public class UserInfo {

     @ApiModelProperty(required = true, notes = "用户ID", example = "001")

     private Integer id;

     @ApiModelProperty(required = true, notes = "用户姓名", example = "龙五")

     private String userName;

     @ApiModelProperty(required = true, notes = "用户年龄", example = "28")

     private String userAge;

     @ApiModelProperty(required = true, notes = "用户性别", example = "男")

     private String userSex;

     @ApiModelProperty(required = true, notes = "用户身份证", example = "24511000012234512")

     private String userIdCard;

     @ApiModelProperty(required = true, notes = "用户号码", example = "13745124512")

     private String userPhone;

     @ApiModelProperty(required = true, notes = "用户产地", example = "安徽")

     private String userFrom;

     @ApiModelProperty(required = true, notes = "用户民族", example = "汉族")

     private String userMinZu;

     @ApiModelProperty(required = true, notes = "用户住址", example = "某某大街110号")

     private String userAddress;

     @ApiModelProperty(required = true, notes = "用户职业", example = "大佬")

     private String userZhiYe;

     @ApiModelProperty(required = true, notes = "用户学历", example = "小学")

     private String userEducate;

     @ApiModelProperty(required = true, notes = "用户是否存在", example = "否")

     private Integer idDeleted;

     @ApiModelProperty(required = true, notes = "用户信息更新时间", example = "2020-07-18 22:22:22")

     private String updateDate;

     @ApiModelProperty(required = true, notes = "用户创建", example = "2020-07-18 22:22:22")

     private String createDate;

     public Integer getId() {

         return id;

     }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getUserName() {

         return userName;

     }

     public void setUserName(String userName) {

         this.userName = userName;

     }

     public String getUserAge() {

         return userAge;

     }

     public void setUserAge(String userAge) {

         this.userAge = userAge;

     }

     public String getUserSex() {

         return userSex;

     }

     public void setUserSex(String userSex) {

         this.userSex = userSex;

     }

     public String getUserIdCard() {

         return userIdCard;

     }

     public void setUserIdCard(String userIdCard) {

         this.userIdCard = userIdCard;

     }

     public String getUserPhone() {

         return userPhone;

     }

     public void setUserPhone(String userPhone) {

         this.userPhone = userPhone;

     }

     public String getUserFrom() {

         return userFrom;

     }

     public void setUserFrom(String userFrom) {

         this.userFrom = userFrom;

     }

     public String getUserMinZu() {

         return userMinZu;

     }

     public void setUserMinZu(String userMinZu) {

         this.userMinZu = userMinZu;

     }

     public String getUserAddress() {

         return userAddress;

     }

     public void setUserAddress(String userAddress) {

         this.userAddress = userAddress;

     }

     public String getUserZhiYe() {

         return userZhiYe;

     }

     public void setUserZhiYe(String userZhiYe) {

         this.userZhiYe = userZhiYe;

     }

     public String getUserEducate() {

         return userEducate;

     }

     public void setUserEducate(String userEducate) {

         this.userEducate = userEducate;

     }

     public Integer getIdDeleted() {

         return idDeleted;

     }

     public void setIdDeleted(Integer idDeleted) {

         this.idDeleted = idDeleted;

     }

     public String getUpdateDate() {

         return updateDate;

     }

     public void setUpdateDate(String updateDate) {

         this.updateDate = updateDate;

     }

     public String getCreateDate() {

         return createDate;

     }

     public void setCreateDate(String createDate) {

         this.createDate = createDate;

     }

 }

3.dao建立

 package com.mrliu.undertow.mapper;

 import com.mrliu.undertow.pojo.UserInfo;

 import org.apache.ibatis.annotations.Mapper;

 import java.util.List;

 /**

  * @author Administrator

  */

 @Mapper

 public interface UserInfoMapper {

     /**

      * 查询全部

      *

      * @return

      */

     List<UserInfo> selectList();

 }

4.配置文件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.click.house.mapper.UserInfoMapper">

     <resultMap id="BaseResultMap" type="com.click.house.entity.UserInfo">

         <id column="id" jdbcType="INTEGER" property="id" />

         <result column="user_name" jdbcType="VARCHAR" property="user_name" />

         <result column="user_age" jdbcType="VARCHAR" property="user_age" />

         <result column="user_sex" jdbcType="VARCHAR" property="user_sex" />

         <result column="user_id_card" jdbcType="VARCHAR" property="user_id_card" />

         <result column="user_phone" jdbcType="VARCHAR" property="user_phone" />

         <result column="user_from" jdbcType="VARCHAR" property="user_from" />

         <result column="user_minzu" jdbcType="VARCHAR" property="user_minzu" />

         <result column="user_address" jdbcType="VARCHAR" property="user_address" />

         <result column="user_zhiye" jdbcType="VARCHAR" property="user_zhiye" />

         <result column="user_educate" jdbcType="VARCHAR" property="user_educate" />

         <result column="iddeleted" jdbcType="INTEGER" property="iddeleted" />

         <result column="update_date" jdbcType="VARCHAR" property="update_date" />

         <result column="create_date" jdbcType="VARCHAR" property="create_date" />

     </resultMap>

     <sql id="Base_Column_List">

         id, user_name, user_age, user_sex, user_id_card, user_phone, user_from, user_minzu, user_address, user_zhiye, user_educate, iddeleted, update_date, create_date

     </sql>

     <insert id="saveData" parameterType="com.click.house.entity.UserInfo" >

         INSERT INTO cs_user_info

         (id,user_name,pass_word,phone,email,create_day)

         VALUES

         (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},

         #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})

     </insert>

     <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">

         select

         <include refid="Base_Column_List" />

         from cs_user_info

         where id = #{id,jdbcType=INTEGER}

     </select>

     <select id="selectList" resultMap="BaseResultMap" >

         select

         <include refid="Base_Column_List" />

         from userinformation

     </select>

 </mapper>

5.service层建立

service

 package com.mrliu.undertow.service;

 import com.mrliu.undertow.pojo.UserInfo;

 import java.util.List;

 public interface UserInfoService {

     /**

      * 查询全部

      *

      * @return

      */

     List<UserInfo> selectList();

 }

serviceImpl

 package com.mrliu.undertow.service.impl;

 import com.mrliu.undertow.mapper.UserInfoMapper;

 import com.mrliu.undertow.pojo.UserInfo;

 import com.mrliu.undertow.service.UserInfoService;

 import org.springframework.stereotype.Service;

 import javax.annotation.Resource;

 import java.util.List;

 /**

  * @author Administrator

  */

 @Service

 public class UserInfoServiceImpl implements UserInfoService {

     @Resource

     private UserInfoMapper userInfoMapper;

     @Override

     public List<UserInfo> selectList() {

         return userInfoMapper.selectList();

     }

 }

6.controller层建立

 package com.mrliu.undertow.controller;

 import com.mrliu.undertow.pojo.UserInfo;

 import com.mrliu.undertow.service.UserInfoService;

 import io.swagger.annotations.*;

 import org.slf4j.Logger;

 import org.slf4j.LoggerFactory;

 import org.springframework.web.bind.annotation.GetMapping;

 import org.springframework.web.bind.annotation.RequestMapping;

 import org.springframework.web.bind.annotation.RequestMethod;

 import org.springframework.web.bind.annotation.RestController;

 import org.springframework.web.servlet.ModelAndView;

 import javax.annotation.Resource;

 import javax.servlet.ServletException;

 import javax.servlet.http.HttpServletRequest;

 import javax.servlet.http.HttpServletResponse;

 import java.io.IOException;

 import java.util.ArrayList;

 import java.util.LinkedHashMap;

 import java.util.List;

 import java.util.Map;

 /**

  * @author liuyangos8888

  */

 @Api(tags = "用户操作功能接口")

 @RestController

 @RequestMapping("/user")

 public class UserInfoController {

     private static Logger log = LoggerFactory.getLogger(UserInfoController.class);

     @Resource

     private UserInfoService userInfoService;

     /**

      * 查询所有数据

      *

      * @return 所有数据

      */

     @ApiResponses(value = {

             @ApiResponse(code = 200, message = "接口返回成功状态"),

             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")

     })

     @ApiOperation(value = "用户全查接口", notes = "访问此接口，返回hello语句，测试接口")

     @GetMapping("/selectList")

     public List<UserInfo> selectList() {

         return userInfoService.selectList();

     }

     @ApiResponses(value = {

             @ApiResponse(code = 200, message = "接口返回成功状态"),

             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")

     })

     @ApiOperation(value = "JSP全查接口", notes = "访问此接口，返回hello语句，测试接口")

     @RequestMapping(value = "/selectList2", produces = "application/json;charset=UTF-8", method = {RequestMethod.GET})

     public void selectList2(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

         List<UserInfo> list = userInfoService.selectList();

         List<Map<String, Object>> mapList = getMapsResult(list);

         log.info("进入了selectList2方法！");

         ModelAndView mav = new ModelAndView("jspIndex.jsp");

         mav.addObject("list", mapList);

         request.setAttribute("list", mapList);

         request.getRequestDispatcher("/WEB-INF/jsp/jspIndex.jsp").forward(request, response);

     }

     @ApiResponses(value = {

             @ApiResponse(code = 200, message = "接口返回成功状态"),

             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")

     })

     @ApiOperation(value = "自定义JSP全查接口", notes = "访问此接口，返回hello语句，测试接口")

     @RequestMapping(value = "/selectList3", produces = "application/json;charset=UTF-8", method = {RequestMethod.GET})

     public void selectList3(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

         List<UserInfo> list = new ArrayList<>();

         UserInfo userInfo = new UserInfo();

         userInfo.setId(2);

         userInfo.setUserName("李四");

         userInfo.setUserAge("112");

         userInfo.setUserSex("男");

         userInfo.setUserIdCard("10001199104131278");

         userInfo.setUserPhone("1371452312");

         userInfo.setUserFrom("浙江");

         userInfo.setUserMinZu("汉族");

         userInfo.setUserAddress("某某大陆某某大街2220号");

         userInfo.setUserZhiYe("教师");

         userInfo.setUserEducate("博士");

         userInfo.setCreateDate("2020-05-17 22:22:22");

         userInfo.setUpdateDate("2020-05-17 22:22:22");

         userInfo.setIdDeleted(0);

         list.add(userInfo);

         log.info("进入了selectList3方法！");

         ModelAndView mav = new ModelAndView("jspIndex.jsp");

         mav.addObject("list", list);

         request.setAttribute("list", list);

         request.getRequestDispatcher("/WEB-INF/jsp/jspIndex.jsp").forward(request, response);

     }

     private List<Map<String, Object>> getMapsResult(List<UserInfo> list) {

         List<Map<String, Object>> mapList = new ArrayList<>();

         for (UserInfo userInfo : list) {

             Map<String, Object> map = new LinkedHashMap<>();

             map.put("id", userInfo.getId());

             map.put("userName", userInfo.getUserName());

             map.put("userAge", userInfo.getUserAge());

             map.put("userSex", userInfo.getUserSex());

             map.put("userIdCard", userInfo.getUserIdCard());

             map.put("userPhone", userInfo.getUserPhone());

             map.put("userFrom", userInfo.getUserFrom());

             map.put("userMinZu", userInfo.getUserMinZu());

             map.put("userAddress", userInfo.getUserAddress());

             map.put("userEducate", userInfo.getUserEducate());

             map.put("userZhiYe", userInfo.getUserZhiYe());

             map.put("updateDate", userInfo.getUpdateDate());

             map.put("createDate", userInfo.getCreateDate());

             map.put("idDeleted", userInfo.getIdDeleted());

             mapList.add(map);

         }

         return mapList;

     }

 }

7.yml配置

 server:

   port: 7788

   tomcat:

     uri-encoding: UTF-8

   servlet:

     encoding:

       charset: UTF-8

       force: true

       enabled: true

     context-path: /

 #springmvc

 spring:

   mvc:

     view:

       prefix: /WEB-INF/jsp/

       suffix: .jsp

   datasource:

     type: com.alibaba.druid.pool.DruidDataSource

     click:

       driverClassName: ru.yandex.clickhouse.ClickHouseDriver

       url: jdbc:clickhouse://127.0.0.1:8123/mrliu

       initialSize: 10

       maxActive: 100

       minIdle: 10

       maxWait: 6000

   # mybatis 配置

 mybatis:

     type-aliases-package: com.mrliu.undertow.pojo

     mapper-locations: classpath:/mapper/*.xml

8.启动测试

访问：

http://localhost:7016/user//selectList

参考:

1. clickhouse 安装 
查看 https://www.cnblogs.com/liuyangfirst/p/13379064.html
2. Knife4J 使用   
查看 https://www.cnblogs.com/liuyangfirst/p/12900597.html
3. IDEA使用  
查看 https://www.cnblogs.com/liuyangfirst/tag/IntelliJ%20IDEA%E4%BD%BF%E7%94%A8/

参考

最新文章

  1. Ext treelist 动态切换TreeStore
  2. scala中的下划线_
  3. 惊魂web应用宕机记一次网站的紧急恢复
  4. Mtk Android编译命令
  5. c#中SqlHelper类的编写（三）
  6. stop() 是用于停止动画 :animated 用于判断动画是否在进行中
  7. Laravel nginx 伪静态规则
  8. [android] 百度地图开发 (两).所在地的城市定位和城市POI搜索
  9. [NOI 2007]货币兑换Cash
  10. 上传本地项目到Github
  11. Android 9.0适配遇到的问题1
  12. hadoop源码分析(2)：Map-Reduce的过程解析
  13. VirtualBox 使用物理硬盘
  14. A Model of Saliency-Based Visual Attention for Rapid Scene Analysis
  15. 使用 MD5 加密 去重对插入的影响
  16. RabbitMQ学习笔记（二）：基础概念
  17. Java一个汉字占几个字节（详解与原理）(转载)
  18. POJ 2773 Happy 2006------欧几里得 or 欧拉函数。
  19. centos7搭建.netcore运行环境
  20. Thread.sleep() 和 Thread.yield() 区别

热门文章

  1. css3渐进增强 VS 优雅降级
  2. H5+CSS复习笔记（全）
  3. 005.Nginx配置下载站点
  4. iOS刨根问底-深入理解GCD
  5. 【Nginx】如何获取客户端真实IP、域名、协议、端口？看这一篇就够了！
  6. VSCode下，项识别为 cmdlet、函数、脚本文件或可运行程序的名称。
  7. scrapy 基础组件专题（一）：scrapy框架中各组件的工作流程
  8. .NET Core CLI 的性能诊断工具介绍
  9. 微信浏览器内 h5 直接唤醒 app 之 微信开放标签 wx-open-launch-app
  10. SQL中的多表联查（SELECT DISTINCT 语句）