Springboot+Mybatis+Clickhouse+jsp 搭建单体应用项目(一)

一、服务器安装clickhouse服务

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

二.连接数据库

成功

三、新建库

1 CREATE/ATTACH DATABASE zabbix ENGINE = Ordinary;

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

这里采用

1 CREATE DATABASE mrliu ENGINE = Ordinary;

四、创建表

 1 CREATE TABLE mrliu.userinformation (
 2 `id` UInt16,
 3 `user_name` String,
 4 `user_age` String,
 5 `user_sex` String,
 6 `user_id_card` String,
 7 `user_phone` String,
 8 `user_from` String,
 9 `user_minzu` String,
10 `user_address` String,
11 `user_zhiye` String,
12 `user_educate` String,
13 `iddeleted` Int8,
14 `update_date` Date,
15 `create_date` Date ) ENGINE = MergeTree(create_date,
16 id,
17 8192)

五、添加数据

1 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 (
2 1,'赵大','25','男','25600000000000001','135145895612','中国浙江','汉族','西湖区果哥子大街2020号','销售','本科',0,'2020-05-07','2020-07-25');

六、创建springboot项目

1.引入pom依赖

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4     <modelVersion>4.0.0modelVersion>
  5     <parent>
  6         <groupId>org.springframework.bootgroupId>
  7         <artifactId>spring-boot-starter-parentartifactId>
  8         <version>2.2.5.RELEASEversion>
  9         <relativePath/> 
 10     parent>
 11     <groupId>com.mrliugroupId>
 12     <artifactId>undertowartifactId>
 13     <version>0.0.1-SNAPSHOTversion>
 14     <name>undertowname>
 15     <description>Demo project for Spring Bootdescription>
 16 
 17     <properties>
 18         <java.version>1.8java.version>
 19         <spring-boot-admin.version>2.2.1spring-boot-admin.version>
 20     properties>
 21 
 22     <dependencies>
 23 
 24         
 25         <dependency>
 26             <groupId>com.github.xiaoymingroupId>
 27             <artifactId>knife4j-spring-boot-starterartifactId>
 28             <version>1.9.6version>
 29         dependency>
 30 
 31         <dependency>
 32             <groupId>org.springframework.bootgroupId>
 33             <artifactId>spring-boot-starter-webartifactId>
 34         dependency>
 35 
 36         <dependency>
 37             <groupId>com.google.guavagroupId>
 38             <artifactId>guavaartifactId>
 39             <version>20.0version>
 40         dependency>
 41 
 42         <dependency>
 43             <groupId>com.google.code.gsongroupId>
 44             <artifactId>gsonartifactId>
 45             <version>2.8.6version>
 46             <scope>testscope>
 47         dependency>
 48 
 49         
 50         <dependency>
 51             <groupId>org.springframework.bootgroupId>
 52             <artifactId>spring-boot-starter-tomcatartifactId>
 53             <scope>providedscope>
 54         dependency>
 55 
 56         
 57         <dependency>
 58             <groupId>org.apache.tomcat.embedgroupId>
 59             <artifactId>tomcat-embed-jasperartifactId>
 60             <scope>providedscope>
 61         dependency>
 62         
 63         <dependency>
 64             <groupId>javax.servletgroupId>
 65             <artifactId>jstlartifactId>
 66         dependency>
 67 
 68         <dependency>
 69             <groupId>org.mybatis.spring.bootgroupId>
 70             <artifactId>mybatis-spring-boot-starterartifactId>
 71             <version>1.3.2version>
 72         dependency>
 73 
 74         <dependency>
 75             <groupId>com.alibabagroupId>
 76             <artifactId>druid-spring-boot-starterartifactId>
 77             <version>1.1.13version>
 78         dependency>
 79 
 80         <dependency>
 81             <groupId>mysqlgroupId>
 82             <artifactId>mysql-connector-javaartifactId>
 83             <version>5.1.38version>
 84         dependency>
 85 
 86         <dependency>
 87             <groupId>commons-langgroupId>
 88             <artifactId>commons-langartifactId>
 89             <version>2.6version>
 90         dependency>
 91         
 92         <dependency>
 93             <groupId>ru.yandex.clickhousegroupId>
 94             <artifactId>clickhouse-jdbcartifactId>
 95             <version>0.1.53version>
 96             <exclusions>
 97                 <exclusion>
 98                     <artifactId>guavaartifactId>
 99                     <groupId>com.google.guavagroupId>
100                 exclusion>
101             exclusions>
102         dependency>
103 
104         
105         <dependency>
106             <groupId>org.yamlgroupId>
107             <artifactId>snakeyamlartifactId>
108             <version>1.26version>
109         dependency>
110     dependencies>
111 
112     <build>
113         <resources>
114             <resource>
115                 <directory>src/main/javadirectory>
116                 <includes>
117                     <include>**/*.xmlinclude>
118                 includes>
119                 <filtering>falsefiltering>
120             resource>
121         resources>
122         <plugins>
123             <plugin>
124                 <groupId>org.springframework.bootgroupId>
125                 <artifactId>spring-boot-maven-pluginartifactId>
126             plugin>
127         plugins>
128     build>
129 
130 project>

2.实体类建立

  1 package com.mrliu.undertow.pojo;
  2 
  3 import io.swagger.annotations.ApiModel;
  4 import io.swagger.annotations.ApiModelProperty;
  5 
  6 /**
  7  * 用户信息
  8  *
  9  * @author liuyangos8888
 10  */
 11 @ApiModel("用户信息")
 12 public class UserInfo {
 13 
 14 
 15     @ApiModelProperty(required = true, notes = "用户ID", example = "001")
 16     private Integer id;
 17 
 18     @ApiModelProperty(required = true, notes = "用户姓名", example = "龙五")
 19     private String userName;
 20 
 21     @ApiModelProperty(required = true, notes = "用户年龄", example = "28")
 22     private String userAge;
 23 
 24     @ApiModelProperty(required = true, notes = "用户性别", example = "男")
 25     private String userSex;
 26 
 27     @ApiModelProperty(required = true, notes = "用户身份证", example = "24511000012234512")
 28     private String userIdCard;
 29 
 30     @ApiModelProperty(required = true, notes = "用户号码", example = "13745124512")
 31     private String userPhone;
 32 
 33     @ApiModelProperty(required = true, notes = "用户产地", example = "安徽")
 34     private String userFrom;
 35 
 36     @ApiModelProperty(required = true, notes = "用户民族", example = "汉族")
 37     private String userMinZu;
 38 
 39     @ApiModelProperty(required = true, notes = "用户住址", example = "某某大街110号")
 40     private String userAddress;
 41 
 42     @ApiModelProperty(required = true, notes = "用户职业", example = "大佬")
 43     private String userZhiYe;
 44 
 45     @ApiModelProperty(required = true, notes = "用户学历", example = "小学")
 46     private String userEducate;
 47 
 48     @ApiModelProperty(required = true, notes = "用户是否存在", example = "否")
 49     private Integer idDeleted;
 50 
 51     @ApiModelProperty(required = true, notes = "用户信息更新时间", example = "2020-07-18 22:22:22")
 52     private String updateDate;
 53 
 54     @ApiModelProperty(required = true, notes = "用户创建", example = "2020-07-18 22:22:22")
 55     private String createDate;
 56 
 57     public Integer getId() {
 58         return id;
 59     }
 60 
 61     public void setId(Integer id) {
 62         this.id = id;
 63     }
 64 
 65     public String getUserName() {
 66         return userName;
 67     }
 68 
 69     public void setUserName(String userName) {
 70         this.userName = userName;
 71     }
 72 
 73     public String getUserAge() {
 74         return userAge;
 75     }
 76 
 77     public void setUserAge(String userAge) {
 78         this.userAge = userAge;
 79     }
 80 
 81     public String getUserSex() {
 82         return userSex;
 83     }
 84 
 85     public void setUserSex(String userSex) {
 86         this.userSex = userSex;
 87     }
 88 
 89     public String getUserIdCard() {
 90         return userIdCard;
 91     }
 92 
 93     public void setUserIdCard(String userIdCard) {
 94         this.userIdCard = userIdCard;
 95     }
 96 
 97     public String getUserPhone() {
 98         return userPhone;
 99     }
100 
101     public void setUserPhone(String userPhone) {
102         this.userPhone = userPhone;
103     }
104 
105     public String getUserFrom() {
106         return userFrom;
107     }
108 
109     public void setUserFrom(String userFrom) {
110         this.userFrom = userFrom;
111     }
112 
113     public String getUserMinZu() {
114         return userMinZu;
115     }
116 
117     public void setUserMinZu(String userMinZu) {
118         this.userMinZu = userMinZu;
119     }
120 
121     public String getUserAddress() {
122         return userAddress;
123     }
124 
125     public void setUserAddress(String userAddress) {
126         this.userAddress = userAddress;
127     }
128 
129     public String getUserZhiYe() {
130         return userZhiYe;
131     }
132 
133     public void setUserZhiYe(String userZhiYe) {
134         this.userZhiYe = userZhiYe;
135     }
136 
137     public String getUserEducate() {
138         return userEducate;
139     }
140 
141     public void setUserEducate(String userEducate) {
142         this.userEducate = userEducate;
143     }
144 
145     public Integer getIdDeleted() {
146         return idDeleted;
147     }
148 
149     public void setIdDeleted(Integer idDeleted) {
150         this.idDeleted = idDeleted;
151     }
152 
153     public String getUpdateDate() {
154         return updateDate;
155     }
156 
157     public void setUpdateDate(String updateDate) {
158         this.updateDate = updateDate;
159     }
160 
161     public String getCreateDate() {
162         return createDate;
163     }
164 
165     public void setCreateDate(String createDate) {
166         this.createDate = createDate;
167     }
168 }

3.dao建立

 1 package com.mrliu.undertow.mapper;
 2 
 3 
 4 
 5 import com.mrliu.undertow.pojo.UserInfo;
 6 import org.apache.ibatis.annotations.Mapper;
 7 
 8 import java.util.List;
 9 
10 /**
11  * @author Administrator
12  */
13 
14 @Mapper
15 public interface UserInfoMapper {
16 
17     /**
18      * 查询全部
19      *
20      * @return
21      */
22     List selectList();
23 
24 
25 }

4.配置文件xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 
 3 
 4     
 5         
 6         
 7         
 8         
 9         
10         
11         
12         
13         
14         
15         
16         
17         
18         
19     
20 
21     
22         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
23     
24 
25     
26         INSERT INTO cs_user_info
27         (id,user_name,pass_word,phone,email,create_day)
28         VALUES
29         (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},
30         #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{createDay,jdbcType=VARCHAR})
31     
32 
33     
34         select
35         
36         from cs_user_info
37         where id = #{id,jdbcType=INTEGER}
38     
39 
40     
41         select
42         
43         from userinformation
44     
45 

5.service层建立

service

 1 package com.mrliu.undertow.service;
 2 
 3 
 4 import com.mrliu.undertow.pojo.UserInfo;
 5 
 6 import java.util.List;
 7 
 8 public interface UserInfoService {
 9 
10 
11     /**
12      * 查询全部
13      *
14      * @return
15      */
16     List selectList();
17 }

serviceImpl

 1 package com.mrliu.undertow.service.impl;
 2 
 3 
 4 import com.mrliu.undertow.mapper.UserInfoMapper;
 5 import com.mrliu.undertow.pojo.UserInfo;
 6 import com.mrliu.undertow.service.UserInfoService;
 7 import org.springframework.stereotype.Service;
 8 
 9 import javax.annotation.Resource;
10 import java.util.List;
11 
12 
13 /**
14  * @author Administrator
15  */
16 @Service
17 public class UserInfoServiceImpl implements UserInfoService {
18 
19     @Resource
20     private UserInfoMapper userInfoMapper;
21 
22 
23     @Override
24     public List selectList() {
25         return userInfoMapper.selectList();
26     }
27 }

6.controller层建立

  1 package com.mrliu.undertow.controller;
  2 
  3 
  4 import com.mrliu.undertow.pojo.UserInfo;
  5 import com.mrliu.undertow.service.UserInfoService;
  6 import io.swagger.annotations.*;
  7 import org.slf4j.Logger;
  8 import org.slf4j.LoggerFactory;
  9 import org.springframework.web.bind.annotation.GetMapping;
 10 import org.springframework.web.bind.annotation.RequestMapping;
 11 import org.springframework.web.bind.annotation.RequestMethod;
 12 import org.springframework.web.bind.annotation.RestController;
 13 import org.springframework.web.servlet.ModelAndView;
 14 
 15 import javax.annotation.Resource;
 16 import javax.servlet.ServletException;
 17 import javax.servlet.http.HttpServletRequest;
 18 import javax.servlet.http.HttpServletResponse;
 19 import java.io.IOException;
 20 import java.util.ArrayList;
 21 import java.util.LinkedHashMap;
 22 import java.util.List;
 23 import java.util.Map;
 24 
 25 /**
 26  * @author liuyangos8888
 27  */
 28 @Api(tags = "用户操作功能接口")
 29 @RestController
 30 @RequestMapping("/user")
 31 public class UserInfoController {
 32 
 33     private static Logger log = LoggerFactory.getLogger(UserInfoController.class);
 34 
 35     @Resource
 36     private UserInfoService userInfoService;
 37 
 38     /**
 39      * 查询所有数据
 40      *
 41      * @return 所有数据
 42      */
 43     @ApiResponses(value = {
 44             @ApiResponse(code = 200, message = "接口返回成功状态"),
 45             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")
 46     })
 47     @ApiOperation(value = "用户全查接口", notes = "访问此接口，返回hello语句，测试接口")
 48     @GetMapping("/selectList")
 49     public List selectList() {
 50         return userInfoService.selectList();
 51     }
 52 
 53 
 54     @ApiResponses(value = {
 55             @ApiResponse(code = 200, message = "接口返回成功状态"),
 56             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")
 57     })
 58     @ApiOperation(value = "JSP全查接口", notes = "访问此接口，返回hello语句，测试接口")
 59     @RequestMapping(value = "/selectList2", produces = "application/json;charset=UTF-8", method = {RequestMethod.GET})
 60     public void selectList2(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 61         List list = userInfoService.selectList();
 62 
 63         List> mapList = getMapsResult(list);
 64 
 65         log.info("进入了selectList2方法！");
 66 
 67         ModelAndView mav = new ModelAndView("jspIndex.jsp");
 68         mav.addObject("list", mapList);
 69         request.setAttribute("list", mapList);
 70         request.getRequestDispatcher("/WEB-INF/jsp/jspIndex.jsp").forward(request, response);
 71     }
 72 
 73 
 74 
 75     @ApiResponses(value = {
 76             @ApiResponse(code = 200, message = "接口返回成功状态"),
 77             @ApiResponse(code = 500, message = "接口返回未知错误，请联系开发人员调试")
 78     })
 79     @ApiOperation(value = "自定义JSP全查接口", notes = "访问此接口，返回hello语句，测试接口")
 80     @RequestMapping(value = "/selectList3", produces = "application/json;charset=UTF-8", method = {RequestMethod.GET})
 81     public void selectList3(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 82         List list = new ArrayList<>();
 83 
 84         UserInfo userInfo = new UserInfo();
 85         userInfo.setId(2);
 86         userInfo.setUserName("李四");
 87         userInfo.setUserAge("112");
 88         userInfo.setUserSex("男");
 89         userInfo.setUserIdCard("10001199104131278");
 90         userInfo.setUserPhone("1371452312");
 91         userInfo.setUserFrom("浙江");
 92         userInfo.setUserMinZu("汉族");
 93         userInfo.setUserAddress("某某大陆某某大街2220号");
 94         userInfo.setUserZhiYe("教师");
 95         userInfo.setUserEducate("博士");
 96 
 97         userInfo.setCreateDate("2020-05-17 22:22:22");
 98         userInfo.setUpdateDate("2020-05-17 22:22:22");
 99         userInfo.setIdDeleted(0);
100         list.add(userInfo);
101 
102         log.info("进入了selectList3方法！");
103 
104         ModelAndView mav = new ModelAndView("jspIndex.jsp");
105         mav.addObject("list", list);
106         request.setAttribute("list", list);
107         request.getRequestDispatcher("/WEB-INF/jsp/jspIndex.jsp").forward(request, response);
108     }
109 
110 
111     private List> getMapsResult(List list) {
112         List> mapList = new ArrayList<>();
113 
114         for (UserInfo userInfo : list) {
115 
116             Map map = new LinkedHashMap<>();
117 
118             map.put("id", userInfo.getId());
119             map.put("userName", userInfo.getUserName());
120             map.put("userAge", userInfo.getUserAge());
121             map.put("userSex", userInfo.getUserSex());
122             map.put("userIdCard", userInfo.getUserIdCard());
123             map.put("userPhone", userInfo.getUserPhone());
124             map.put("userFrom", userInfo.getUserFrom());
125             map.put("userMinZu", userInfo.getUserMinZu());
126             map.put("userAddress", userInfo.getUserAddress());
127             map.put("userEducate", userInfo.getUserEducate());
128             map.put("userZhiYe", userInfo.getUserZhiYe());
129             map.put("updateDate", userInfo.getUpdateDate());
130             map.put("createDate", userInfo.getCreateDate());
131             map.put("idDeleted", userInfo.getIdDeleted());
132             mapList.add(map);
133         }
134         return mapList;
135     }
136 
137 }

7.yml配置

 1 server:
 2   port: 7788
 3   tomcat:
 4     uri-encoding: UTF-8
 5   servlet:
 6     encoding:
 7       charset: UTF-8
 8       force: true
 9       enabled: true
10     context-path: /
11 
12 #springmvc
13 spring:
14   mvc:
15     view:
16       prefix: /WEB-INF/jsp/
17       suffix: .jsp
18   datasource:
19     type: com.alibaba.druid.pool.DruidDataSource
20     click:
21       driverClassName: ru.yandex.clickhouse.ClickHouseDriver
22       url: jdbc:clickhouse://127.0.0.1:8123/mrliu
23       initialSize: 10
24       maxActive: 100
25       minIdle: 10
26       maxWait: 6000
27 
28 
29 
30   # mybatis 配置
31 mybatis:
32     type-aliases-package: com.mrliu.undertow.pojo
33     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/

参考