2020第二次分享
<h1>210205分享</h1>
<h5>1:签到-直播相关业务逻辑介绍</h5>
<h5>2: EasyCode模板改造，支持新增，修改非空非null判断，解决插入非空列跟值不匹配的问题</h5>
<ul>
<li>
<p>异常</p>
<pre><code>Cause: java.sql.SQLException: Column count doesn't match value count at row 1</code></pre>
</li>
<li>改造后模板：</li>
</ul>
<pre><code>##引入mybatis支持
$!mybatisSupport
##设置保存名称与保存位置
$!callback.setFileName($tool.append($!{tableInfo.name}, "Dao.xml"))
$!callback.setSavePath($tool.append($modulePath, "/src/main/resources/mapper"))
##拿到主键
#if(!$tableInfo.pkColumn.isEmpty())
#set($pk = $tableInfo.pkColumn.get(0))
#end
<?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="$!{tableInfo.savePackageName}.dao.$!{tableInfo.name}Dao">
<resultMap type="$!{tableInfo.savePackageName}.entity.$!{tableInfo.name}" id="$!{tableInfo.name}Map">
#foreach($column in $tableInfo.fullColumn)
<result property="$!column.name" column="$!column.obj.name" jdbcType="$!column.ext.jdbcType"/>
#end
</resultMap>
<!--查询单个-->
<select id="queryById" resultMap="$!{tableInfo.name}Map">
select * from $!tableInfo.obj.name
where $!pk.obj.name = #{$!pk.name}
</select>
<!--通过实体作为筛选条件查询-->
<select id="queryAll" resultMap="$!{tableInfo.name}Map">
select * from $!tableInfo.obj.name
<where>
#foreach($column in $tableInfo.fullColumn)
<if test="$!column.name != null#if($column.type.equals("java.lang.String")) and $!column.name != ''#end">
and $!column.obj.name = #{$!column.name}
</if>
#end
</where>
</select>
<!--新增不为null 判断-->
<insert id="insert" keyProperty="$!pk.name" useGeneratedKeys="true" parameterType="$!{tableInfo.savePackageName}.entity.$!{tableInfo.name}">
insert into $!{tableInfo.obj.name}
<trim prefix="(" suffix=")" suffixOverrides=",">
#foreach($column in $tableInfo.fullColumn)
<if test="$!column.name != null#if($column.type.equals("java.lang.String")) and $!column.name != ''#end">
$!column.obj.name,
</if>
#end
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
#foreach($column in $tableInfo.fullColumn)
<if test="$!column.name != null#if($column.type.equals("java.lang.String")) and $!column.name != ''#end">
#{$!column.name,jdbcType=$!column.ext.jdbcType},
</if>
#end
</trim>
</insert>
<!--通过主键修改数据-->
<update id="update">
update $!{tableInfo.obj.name}
<set>
#foreach($column in $tableInfo.otherColumn)
<if test="$!column.name != null#if($column.type.equals("java.lang.String")) and $!column.name != ''#end">
$!column.obj.name = #{$!column.name},
</if>
#end
</set>
where $!pk.obj.name = #{$!pk.name}
</update>
<!--通过主键删除-->
<delete id="deleteById">
delete from $!{tableInfo.obj.name} where $!pk.obj.name = #{$!pk.name}
</delete>
</mapper></code></pre>
<ul>
<li>改造前：</li>
</ul>
<pre><code><insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.syhd.user.eto.IntegralLog" useGeneratedKeys="true">
insert into integral_log
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="serialNumber != null">
serial_number,
</if>
<if test="xyAccount != null">
xy_account,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="serialNumber != null">
#{serialNumber,jdbcType=VARCHAR},
</if>
<if test="xyAccount != null">
#{xyAccount,jdbcType=VARCHAR},
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="serialNumber != null">
#{serialNumber,jdbcType=VARCHAR},
</if>
<if test="xyAccount != null">
#{xyAccount,jdbcType=VARCHAR},
</if>
</trim>
</insert></code></pre>
<ul>
<li>
<p>改造后：</p>
<pre><code><insert id="insert" keyProperty="id" useGeneratedKeys="true" parameterType="com.syhd.eto.LiveClass">
insert into live_class
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null and name != ''">
name,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null and name != ''">
#{name,jdbcType=VARCHAR},
</if>
</trim>
</insert></code></pre>
</li>
</ul>
<h5>3:DBImport_V38导出数据字典</h5>
<h5>4:特殊字符入库问题分析跟终极解决方案</h5>
<p>测试数据:大猫🦁</p>
<ul>
<li>
<p>设置表utf8mb4字符集
alter table integral_log character set utf8mb4;</p>
<ul>
<li>设置某个字段utf8mb4字符集
alter table <表名> change <字段名> <字段名> <类型> character set utf8mb4;</li>
</ul>
</li>
<li>
<p>上述只是修改了db本身支持特殊字符,用程序执行依然报错,还需修改数据源相关配置
SpringBoot2+ 默认HIKARI 数据库连接池</p>
<ul>
<li>解决数据源支持utf8mb4字符
spring.datasource.hikari.connection-init-sql=set names utf8mb4</li>
</ul>
</li>
<li>阿里巴巴Durid连接池
List<String> collection=new ArrayList();
String utf8mb4Sql="set names utf8mb4";
collection.add(utf8mb4Sql);
datasource.setConnectionInitSqls(collection);</li>
</ul>
<h5>5：雪花算法生成唯一id介绍&使用过程中遇到的问题及解决</h5>
<ul>
<li>雪花算法java实现</li>
</ul>
<pre><code>package com.example.demo.util;
/**
* 描述: Twitter的分布式自增ID雪花算法snowflake (Java版)
**/
public class SnowFlake {
/**
* 起始的时间戳
*/
private final static long START_STMP = 1480166465631L;
/**
* 每一部分占用的位数
*/
private final static long SEQUENCE_BIT = 12; //序列号占用的位数
private final static long MACHINE_BIT = 5; //机器标识占用的位数
private final static long DATACENTER_BIT = 5;//数据中心占用的位数
/**
* 每一部分的最大值
*/
private final static long MAX_DATACENTER_NUM = -1L ^ (-1L << DATACENTER_BIT);
private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT);
private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);
/**
* 每一部分向左的位移
*/
private final static long MACHINE_LEFT = SEQUENCE_BIT;
private final static long DATACENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT;
private final static long TIMESTMP_LEFT = DATACENTER_LEFT + DATACENTER_BIT;
private long datacenterId; //数据中心
private long machineId; //机器标识
private long sequence = 0L; //序列号
private long lastStmp = -1L;//上一次时间戳
public SnowFlake(long datacenterId, long machineId) {
if (datacenterId > MAX_DATACENTER_NUM || datacenterId < 0) {
throw new IllegalArgumentException("datacenterId can't be greater than MAX_DATACENTER_NUM or less than 0");
}
if (machineId > MAX_MACHINE_NUM || machineId < 0) {
throw new IllegalArgumentException("machineId can't be greater than MAX_MACHINE_NUM or less than 0");
}
this.datacenterId = datacenterId;
this.machineId = machineId;
}
/**
* 产生下一个ID
*
* @return
*/
public synchronized long nextId() {
long currStmp = getNewstmp();
if (currStmp < lastStmp) {
throw new RuntimeException("Clock moved backwards. Refusing to generate id");
}
/**
* 时间不连续出来全是偶数
*/
if (currStmp == lastStmp) {
//相同毫秒内，序列号自增
sequence = (sequence + 1) & MAX_SEQUENCE;
//同一毫秒的序列数已经达到最大
if (sequence == 0L) {
currStmp = getNextMill();
}
} else {
//不同毫秒内，序列号置为0
sequence = 0L;
}
lastStmp = currStmp;
return (currStmp - START_STMP) << TIMESTMP_LEFT //时间戳部分
| datacenterId << DATACENTER_LEFT //数据中心部分
| machineId << MACHINE_LEFT //机器标识部分
| sequence; //序列号部分
}
private long getNextMill() {
long mill = getNewstmp();
while (mill <= lastStmp) {
mill = getNewstmp();
}
return mill;
}
private long getNewstmp() {
return System.currentTimeMillis();
}
public static void main(String[] args) throws Exception {
/**
* 分布式数据中心id
* 机器id
*/
SnowFlake snowFlake = new SnowFlake(5, 6);
}
}</code></pre>
<ul>
<li>雪花算法工具类:</li>
</ul>
<pre><code>package com.example.demo.util;
import java.util.Random;
/**
* 雪花算法工具类
*
* @author jxd
* @version 1.0 *
* @date 2021/1/4 19:03
*/
public class SnowFlakeUtil {
/**
* 单例
*/
private static SnowFlake snowFlake = new SnowFlake(5, 6);
/**
* 随机数生成器
*/
private static Random random = new Random();
/**
* 通过循环一次或两次解决毫秒数不连续导致的全为偶数问题
* 时间停顿 第一次一定是偶数 第二次一定是基数 随机 实现奇数偶数混合
*
* @return
*/
public static Long getUid() {
Long uid = null;
for (int i = 0; i < random.nextInt(2) + 1; i++) {
uid = snowFlake.nextId();
}
return uid;
}
}
</code></pre>
<ul>
<li>测试多线程下唯一性</li>
</ul>
<pre><code>package com.example.demo.friday;
import com.example.demo.util.SnowFlakeUtil;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CountDownLatch;
/**
* 雪花算法测试
*
* @author jxd
* @version 1.0 *
* @date 2021/2/5 10:07
*/
public class SnowFlakeTest {
public static void main(String[] args) {
testCurrentRepeat(10);
}
/**
* 测试雪花算法在多线程运算10000000次下是否有重复数据 结果没有
*/
public static void testCurrentRepeat(int n) {
//10000000万次没有重复
Map<Long, Long> map = new ConcurrentHashMap<>();
CountDownLatch countDownLatch = new CountDownLatch(n * 1000000);
for (int j = 0; j < n; j++) {
new Thread(() -> {
for (int i = 0; i < 1000000; i++) {
Long ss = SnowFlakeUtil.getUid();
map.put(ss, 1L);
countDownLatch.countDown();
}
}).start();
}
try {
countDownLatch.await();
System.out.println(map.size());
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
</code></pre>