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


    一、服务器安装clickhouse服务

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

    二.连接数据库

    成功



    三、新建库

    1 CREATE/ATTACH DATABASE zabbix ENGINE = Ordinary;
    View Code


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

    这里采用

    1 CREATE DATABASE mrliu ENGINE = Ordinary;
    View Code


    四、创建表

     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)
    View Code



    五、添加数据

    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');
    View Code

    六、创建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.0</modelVersion>
  5     <parent>
  6         <groupId>org.springframework.boot</groupId>
  7         <artifactId>spring-boot-starter-parent</artifactId>
  8         <version>2.2.5.RELEASE</version>
  9         <relativePath/> <!-- lookup parent from repository -->
 10     </parent>
 11     <groupId>com.mrliu</groupId>
 12     <artifactId>undertow</artifactId>
 13     <version>0.0.1-SNAPSHOT</version>
 14     <name>undertow</name>
 15     <description>Demo project for Spring Boot</description>
 16 
 17     <properties>
 18         <java.version>1.8</java.version>
 19         <spring-boot-admin.version>2.2.1</spring-boot-admin.version>
 20     </properties>
 21 
 22     <dependencies>
 23 
 24         <!--引入knife4j以来-->
 25         <dependency>
 26             <groupId>com.github.xiaoymin</groupId>
 27             <artifactId>knife4j-spring-boot-starter</artifactId>
 28             <version>1.9.6</version>
 29         </dependency>
 30 
 31         <dependency>
 32             <groupId>org.springframework.boot</groupId>
 33             <artifactId>spring-boot-starter-web</artifactId>
 34         </dependency>
 35 
 36         <dependency>
 37             <groupId>com.google.guava</groupId>
 38             <artifactId>guava</artifactId>
 39             <version>20.0</version>
 40         </dependency>
 41 
 42         <dependency>
 43             <groupId>com.google.code.gson</groupId>
 44             <artifactId>gson</artifactId>
 45             <version>2.8.6</version>
 46             <scope>test</scope>
 47         </dependency>
 48 
 49         <!-- tomcat支持 -->
 50         <dependency>
 51             <groupId>org.springframework.boot</groupId>
 52             <artifactId>spring-boot-starter-tomcat</artifactId>
 53             <scope>provided</scope>
 54         </dependency>
 55 
 56         <!-- 用于编译jsp-->
 57         <dependency>
 58             <groupId>org.apache.tomcat.embed</groupId>
 59             <artifactId>tomcat-embed-jasper</artifactId>
 60             <scope>provided</scope>
 61         </dependency>
 62         <!-- jsp标签库 -->
 63         <dependency>
 64             <groupId>javax.servlet</groupId>
 65             <artifactId>jstl</artifactId>
 66         </dependency>
 67 
 68         <dependency>
 69             <groupId>org.mybatis.spring.boot</groupId>
 70             <artifactId>mybatis-spring-boot-starter</artifactId>
 71             <version>1.3.2</version>
 72         </dependency>
 73 
 74         <dependency>
 75             <groupId>com.alibaba</groupId>
 76             <artifactId>druid-spring-boot-starter</artifactId>
 77             <version>1.1.13</version>
 78         </dependency>
 79 
 80         <dependency>
 81             <groupId>mysql</groupId>
 82             <artifactId>mysql-connector-java</artifactId>
 83             <version>5.1.38</version>
 84         </dependency>
 85 
 86         <dependency>
 87             <groupId>commons-lang</groupId>
 88             <artifactId>commons-lang</artifactId>
 89             <version>2.6</version>
 90         </dependency>
 91         <!-- clickHouse数据库 -->
 92         <dependency>
 93             <groupId>ru.yandex.clickhouse</groupId>
 94             <artifactId>clickhouse-jdbc</artifactId>
 95             <version>0.1.53</version>
 96             <exclusions>
 97                 <exclusion>
 98                     <artifactId>guava</artifactId>
 99                     <groupId>com.google.guava</groupId>
100                 </exclusion>
101             </exclusions>
102         </dependency>
103 
104         <!--转化工具-->
105         <dependency>
106             <groupId>org.yaml</groupId>
107             <artifactId>snakeyaml</artifactId>
108             <version>1.26</version>
109         </dependency>
110     </dependencies>
111 
112     <build>
113         <resources>
114             <resource>
115                 <directory>src/main/java</directory>
116                 <includes>
117                     <include>**/*.xml</include>
118                 </includes>
119                 <filtering>false</filtering>
120             </resource>
121         </resources>
122         <plugins>
123             <plugin>
124                 <groupId>org.springframework.boot</groupId>
125                 <artifactId>spring-boot-maven-plugin</artifactId>
126             </plugin>
127         </plugins>
128     </build>
129 
130 </project>
    View Code

    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 }
    View Code

    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<UserInfo> selectList();
23 
24 
25 }
    View Code

    4.配置文件xml

     1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="com.click.house.mapper.UserInfoMapper">
 4     <resultMap id="BaseResultMap" type="com.click.house.entity.UserInfo">
 5         <id column="id" jdbcType="INTEGER" property="id" />
 6         <result column="user_name" jdbcType="VARCHAR" property="user_name" />
 7         <result column="user_age" jdbcType="VARCHAR" property="user_age" />
 8         <result column="user_sex" jdbcType="VARCHAR" property="user_sex" />
 9         <result column="user_id_card" jdbcType="VARCHAR" property="user_id_card" />
10         <result column="user_phone" jdbcType="VARCHAR" property="user_phone" />
11         <result column="user_from" jdbcType="VARCHAR" property="user_from" />
12         <result column="user_minzu" jdbcType="VARCHAR" property="user_minzu" />
13         <result column="user_address" jdbcType="VARCHAR" property="user_address" />
14         <result column="user_zhiye" jdbcType="VARCHAR" property="user_zhiye" />
15         <result column="user_educate" jdbcType="VARCHAR" property="user_educate" />
16         <result column="iddeleted" jdbcType="INTEGER" property="iddeleted" />
17         <result column="update_date" jdbcType="VARCHAR" property="update_date" />
18         <result column="create_date" jdbcType="VARCHAR" property="create_date" />
19     </resultMap>
20 
21     <sql id="Base_Column_List">
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     </sql>
24 
25     <insert id="saveData" parameterType="com.click.house.entity.UserInfo" >
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     </insert>
32 
33     <select id="selectById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
34         select
35         <include refid="Base_Column_List" />
36         from cs_user_info
37         where id = #{id,jdbcType=INTEGER}
38     </select>
39 
40     <select id="selectList" resultMap="BaseResultMap" >
41         select
42         <include refid="Base_Column_List" />
43         from userinformation
44     </select>
45 </mapper>
    View Code

    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<UserInfo> selectList();
17 }
    View Code

    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<UserInfo> selectList() {
25         return userInfoMapper.selectList();
26     }
27 }
    View Code

    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<UserInfo> 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<UserInfo> list = userInfoService.selectList();
 62 
 63         List<Map<String, Object>> 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<UserInfo> 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<Map<String, Object>> getMapsResult(List<UserInfo> list) {
112         List<Map<String, Object>> mapList = new ArrayList<>();
113 
114         for (UserInfo userInfo : list) {
115 
116             Map<String, Object> 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 }
    View Code

    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
    View Code

    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/















    参考
  • 相关阅读:
    关于各种好玩的神奇函数
    模板——AC自动机
    模板——造数据
    VIM常用操作
    springboot注解
    面试题
    Linux常用命令
    Zookeeper
    对cpu与load的理解及线上问题处理思路
    top
  • 原文地址：https://www.cnblogs.com/liuyangfirst/p/13404473.html
Copyright © 2020-2023  润新知