账户余额项目总结

发表于 更新于 本文字数： 1.1k 阅读时长 ≈ 4 分钟

使用p6spy展示sql

pom.xml中引入p6spy

1
2
3
4
5
<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>${p6spy.version}</version>
</dependency>

application.yml中数据源设置p6spy

1
2
3
4
spring:
  datasource:
    dynamic:
      p6spy: true

spy.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
module.log=com.p6spy.engine.logging.P6LogFactory
# 单行日志
logMessageFormat=com.monezhao.config.CustomSqlFormat
#logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
# 使用Slf4J记录sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准，单位秒
outagedetectioninterval=2
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 过滤Quartz和Flowable的SQL
filter=true
exclude=QRTZ_*,ACT_*,FLW_*
excludecategories=info,debug,result,commit,resultset

自定义输出sql格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Slf4j
public class CustomSqlFormat implements MessageFormattingStrategy {
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category,
                                String prepared, String sql, String url) {
        if (CommonUtil.isEmptyStr(sql)) {
            return "";
        } else {
            if (elapsed > 200) {
                log.info("用时超过200ms!!!");
            }
            return "\n# " + now + " | 执行sql用时 " + elapsed + "ms" + "\n==>    SQl开始:\n" + sql + "\n<==    SQL结束";
        }
    }
}

数据乐观锁

数据库新增version字段

1
ALTER TABLE ${tableName} ADD COLUMN `version` int(6) NOT NULL DEFAULT 1 COMMENT '乐观锁'

表对应table entity新增字段

1
2
3
4
@Version
@ExcelIgnore
@ApiModelProperty(hidden = true)
private int version;

MybatisPlusConfig中设置乐观锁

1
2
3
4
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor() {
    return new OptimisticLockerInterceptor();
}

MybatisPlus会自动处理乐观锁的冲突, 因此按照之前的方式save, update即可

Nginx前后端分离部署

后端开启CROS

WebMvcConfigurer中配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Configuration
public class WebMvcConfig implements WebMvcConfigurer {
    @Bean
    public CorsFilter corsFilter() {
      final UrlBasedCorsConfigurationSource urlBasedCorsConfigurationSource = new UrlBasedCorsConfigurationSource();
      final CorsConfiguration corsConfiguration = new CorsConfiguration();
      /* 是否允许请求带有验证信息 */
      corsConfiguration.setAllowCredentials(true);
      /* 允许访问的客户端域名 */
      corsConfiguration.addAllowedOrigin("*");
      /* 允许服务端访问的客户端请求头 */
      corsConfiguration.addAllowedHeader("*");
      /* 允许访问的方法名,GET POST等 */
      corsConfiguration.addAllowedMethod("*");
      urlBasedCorsConfigurationSource.registerCorsConfiguration("/**", corsConfiguration);
      return new CorsFilter(urlBasedCorsConfigurationSource);
    }
}

前端vue配置

vue.config.js项目配置中publicPath设置为后端的context-path, 例如/bar/

.env文件里面VUE_APP_BASE_API后缀加上/api, 方便Nginx区分前端资源还是后台API

Nginx配置

nginx.conf

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
27
28
29
30
31
32
33
34
user  root;
worker_processes  1;
events {
    #设置Nginx网络连接序列化
    accept_mutex on;
    #设置Nginx的worker进程是否可以同时接收多个请求
    multi_accept on;
    #设置Nginx的worker进程最大的连接数
    worker_connections 1024;
}
http {
    include       mime.types;
    default_type  application/octet-stream;
    sendfile        on;
    keepalive_timeout  65;
    # 开启压缩
    gzip on;
    # 开启静态文件压缩
    gzip_static on;
    # 设置为on会在Header里增加 "Vary: Accept-Encoding"
    gzip_vary on;
    # 代理结果数据的压缩
    gzip_proxied any;
    # gzip压缩比（1~9），越小压缩效果越差，但是越大处理越慢，所以一般取中间值
    gzip_comp_level 5;
    # 获取多少内存用于缓存压缩结果,拿出 4 个 128K 用来缓存压缩的文件
    gzip_buffers 4 128k;
    # 识别http协议的版本
    gzip_http_version 1.1;
    # 设置允许压缩的页面最小字节数，超过10k的文件会被压缩
    gzip_min_length 10k;
    include /root/balance.conf;
    include /usr/local/etc/nginx/servers/*.conf;
}

balance.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
upstream backend {
    server localhost:8080 weight=2;
    #server localhost:18080 backup;
}
server {
    listen 8686;
    server_name localhost;
    add_header Cache-Control no-cache;
    location ^~ /balance/api/ {
        proxy_pass http://backend/balance/;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header REMOTE-HOST $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }
    location /balance {
        alias /Users/monezhao/Movies/balance/dist;
        try_files $uri $uri/  /balance/index.html;
        index index.html index.htm;
        expires 7d;
    }
}

spring MVC接口测试

1
2
3
4
5
6
7
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
@Transactional //测试结束后使用事务回滚,不影响现有数据库
@Ignore //maven打包时需要忽略与数据库操作相关的测试
public class XXControllerTest {
}

Druid Spring 监控

1
2
# properties配置
spring.datasource.druid.aop-patterns="com.monezhao.mapper.*"
1
2
3
4
5
# yml配置
spring:
  datasource:
    druid:
      aop-patterns: "com.monezhao.mapper.*"

Mybatis XML常用语法

验证字符串

1
2
3
4
5
6
-- 验证字符串不为空
<if test="entity.codeInfoId != null and entity.codeInfoId !=''">
</if>
-- 验证字符串为某个值
<if test='entity.countType != null and entity.countType == "0"'>
</if>

foreach并验证集合不为空

1
2
3
4
5
6
<if test="entity.orgList != null and entity.orgList.size > 0 ">
    and a.org_id  in
    <foreach collection="entity.orgList" item ="deptId" index="i" open="(" close=")" separator=",">
        #{deptId}
    </foreach>
</if>

查询结果默认值

1
select IFNULL(a.count_type, "1") AS countType from a;

where 标签

where 1=1 也会走索引，不影响查询效率, 但毕竟mysql优化器也是需要时间的，当数据量很大时，还是会有影响的

where标签的两个特性：

  1. 只有if标签有内容的情况下才会插入where子句;
  2. 若子句的开头为and或or, where标签会将它替换去除;

在动态构造sql时, 为防止注入或防止语句不当时会使用where 1=1

原本的SQL语句

1
2
3
4
5
6
7
WHERE 1 = 1
<if test="entity.codeInfoId != null and entity.codeInfoId !=''">
    <![CDATA[    AND b.code_info_id = #{entity.codeInfoId}    ]]>
</if>
<if test='entity.countType != null and entity.countType == "0"'>
    <![CDATA[    AND a.count_type = #{entity.countType}    ]]>
</if>

在使用where标签可以简化这条语句

注意，每条if语句都以and或or开头

1
2
3
4
5
6
7
8
<where>
    <if test="entity.codeInfoId != null and entity.codeInfoId !=''">
        <![CDATA[    AND b.code_info_id = #{entity.codeInfoId}    ]]>
    </if>
    <if test='entity.countType != null and entity.countType == "0"'>
        <![CDATA[    AND a.count_type = #{entity.countType}    ]]>
    </if>
</where>

ehcarts获取图表点击的列

1
2
3
4
5
6
7
8
9
10
this.chart.getZr().on('click', params => {
    const pointInPixel = [params.offsetX, params.offsetY]
    if (this.chart.containPixel('grid', pointInPixel)) {
        // 获得图表中点击的列
        const xIndex = this.chart.convertFromPixel({ seriesIndex: 0 }, [params.offsetX, params.offsetY])[0]
        const handleIndex = Number(xIndex)
        const xValue = this.chart.getOption().xAxis[0].data[handleIndex]
        const yValue = this.chart.getOption().series[0].data[handleIndex]
    }
})