net.wicp.tams.common.binlog.alone.DuckulaAssit Maven / Gradle / Ivy
The newest version!
/*
* **********************************************************************
* Copyright (c) 2022 .
* All rights reserved.
* 项目名称：common
* 项目描述：公共的工具集
* 版权说明：本软件属andy.zhou([email protected])所有。
* ***********************************************************************
*/
package net.wicp.tams.common.binlog.alone;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.JsonNodeFactory;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.google.protobuf.InvalidProtocolBufferException;
import net.wicp.tams.common.Conf;
import net.wicp.tams.common.apiext.StringUtil;
import net.wicp.tams.common.apiext.jdbc.JdbcAssit;
import net.wicp.tams.common.apiext.json.JSONUtil;
import net.wicp.tams.common.binlog.alone.ListenerConf.ColumnType;
import net.wicp.tams.common.binlog.alone.ListenerConf.DuckulaEvent;
import net.wicp.tams.common.binlog.alone.ListenerConf.DuckulaEvent.Builder;
import net.wicp.tams.common.binlog.alone.ListenerConf.DuckulaEventItem;
import net.wicp.tams.common.binlog.alone.ListenerConf.OptType;
import net.wicp.tams.common.binlog.alone.ListenerConf.Position;
import net.wicp.tams.common.constant.DateFormatCase;
import net.wicp.tams.common.constant.FieldFormart;
import net.wicp.tams.common.constant.dic.YesOrNo;
import net.wicp.tams.common.constant.ods.AddColName;
import net.wicp.tams.common.constant.ods.AddColNameType;
public abstract class DuckulaAssit {
public static SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static DuckulaEvent parse(byte[] data) throws InvalidProtocolBufferException {
DuckulaEvent retobj = DuckulaEvent.parseFrom(data);
return retobj;
}
/***
* 逻辑删除变为物理删除。 指定的列有修改表示删除,适合于源表为逻辑删除情况,多条拆分为
*
* @param oriData 原始数据
* @param changeColName 指定逻辑删除字段名
* @param delValue 指定逻辑删除表示为删除的值
* @param logic yes:物理删除变为逻辑删除，no:逻辑删除变为物理删除,null:不做改变
* @return
*/
public static List parseForUpsetKafka(DuckulaEvent oriData, String changeColName, String delValue,
YesOrNo logic) {
List ret = new ArrayList();
if (oriData == null) {
return ret;
}
int itemsCount = oriData.getItemsCount();
Builder builder = null;
if (itemsCount > 1) {
builder = oriData.toBuilder().clone();
builder.clearItems();
}
for (int i = 0; i < itemsCount; i++) {
boolean needLogic = false;
boolean needPhysics = false;
if (StringUtil.isNull(changeColName) || StringUtil.isNull(delValue) || logic == null) {// 不合法的参数，不支持对冲
needLogic = false;
needPhysics = false;
} else {
if (logic == YesOrNo.yes && oriData.getOptType() == OptType.delete) {// 需要逻辑删除
needLogic = true;
} else {
needLogic = false;
}
if (logic == YesOrNo.no && oriData.getOptType() != OptType.delete) {// 需要物理删除
String valueAfter = getValueStr(oriData, i, changeColName);
if (StringUtil.isNull(valueAfter)) {// 指定的changed列没有值，不支持对冲
needPhysics = false;
} else if (!delValue.equals(valueAfter)) {// 值不对，不需要做对冲
needPhysics = false;
} else {
needPhysics = true;
}
} else {
needPhysics = false;
}
}
DuckulaEvent retObj = null;
if (builder != null) {
Builder clone = builder.clone();
clone.addItems(oriData.getItems(i));
if (needPhysics) {
clone.setOptType(OptType.delete);
} else if (needLogic) {
clone.setOptType(OptType.update);
clone.getItemsBuilder(0).putAfter(changeColName, delValue);
}
retObj = clone.build();
} else {
if (needPhysics) {
Builder builder2 = oriData.toBuilder();
builder2.setOptType(OptType.delete);
retObj = builder2.build();
} else if (needLogic) {
Builder builder2 = oriData.toBuilder();
builder2.setOptType(OptType.update);
Map beforeData = builder2.getItemsBuilder(0).getBeforeMap();
builder2.getItemsBuilder(0).putAllAfter(beforeData);
builder2.getItemsBuilder(0).putAfter(changeColName, delValue);// 不能直接在beforeData添加，会报错。只能采用这种方式
retObj = builder2.build();
} else {
retObj = oriData;
}
}
ret.add(retObj);
}
return ret;
}
/***
* 得到对冲逻辑,item只有一条记录。
*
* @param data
* @param changeColName
* @param isLogic
* @return L:主键json R:数据
* @throws InvalidProtocolBufferException
*/
public static Pair parseHedge(byte[] data, String changeColName, boolean isLogic)
throws InvalidProtocolBufferException {
if (data == null || StringUtil.isNull(changeColName)) {// 不合法的参数，不支持对冲
return null;
}
DuckulaEvent oriData = DuckulaEvent.parseFrom(data);
if (oriData.getOptType() == OptType.delete) {// 删除逻辑不需要对冲
return null;
}
String valueAfter = getValueAfter(oriData, 0, changeColName);
if (StringUtil.isNull(valueAfter)) {// 指定的changed列没有值，不支持对冲
return null;
}
Builder oriDataBuilder = oriData.toBuilder();
ObjectMapper objmap=new ObjectMapper();
ObjectNode before = (ObjectNode)JSONUtil.parserStr(objmap, String.valueOf(valueAfter));
if (before == null || before.isEmpty()) {// 指定的changed列没有值，不支持对冲
return null;
}
ObjectNode keyUpdateJson = JsonNodeFactory.instance.objectNode();
ObjectNode keyTarget = DuckulaAssit.getKeyJson(oriDataBuilder, 0);
for (Iterator iterator = before.fieldNames(); iterator.hasNext();) {
String key = iterator.next();
int keyIndex = oriDataBuilder.getColsList().indexOf(key);
if (oriDataBuilder.getKeyindexsList().contains(keyIndex)) {// 包括主键
Serializable keyValue = getValue(oriDataBuilder.getColsType(keyIndex), before.get(key).asText());
keyUpdateJson.putPOJO(key, keyValue);
keyTarget.putPOJO(key, keyValue);
}
oriDataBuilder.getItemsBuilder(0).putAfter(key, before.get(key).asText());
}
if (keyUpdateJson.isEmpty()) {// 没有改变主键
return null;
}
// 如果有lastOpttype字段也需要修改。这样后续就可以做为源数据处理了。
int optIndex = oriData.getColsList().indexOf(AddColName.lastOpttype.getColNameTrue());
int isdeleteIndex = oriData.getColsList().indexOf(AddColName.isDelete.getColNameTrue());
if (isdeleteIndex >= 0) {// 逻辑删除需要设置为1
oriDataBuilder.getItemsBuilder(0).putAfter(AddColName.isDelete.getColNameTrue(), "1");
}
if (isLogic) {
oriDataBuilder.setOptType(OptType.update);
if (optIndex >= 0) {
oriDataBuilder.getItemsBuilder(0).putAfter(AddColName.lastOpttype.getColNameTrue(),
OptType.update.name());
}
} else {
oriDataBuilder.setOptType(OptType.delete);
if (optIndex >= 0) {
oriDataBuilder.getItemsBuilder(0).putAfter(AddColName.lastOpttype.getColNameTrue(),
OptType.delete.name());
}
}
return Pair.of(keyTarget, oriDataBuilder.build());
}
public static boolean isEmpty(CharSequence cs) {
return cs == null || cs.length() == 0;
}
public static Map getColNamesMap(DuckulaEvent duckulaEvent, FieldFormart fieldFormart) {
Map retmap = new HashMap();
for (int i = 0; i < duckulaEvent.getColsCount(); i++) {
retmap.put(duckulaEvent.getCols(i), fieldFormart.getColName(duckulaEvent.getCols(i)));
}
return retmap;
}
/***
* 转换字段名
*
* @param oriEvent
* @param fieldFormart
* @return
*/
public static DuckulaEvent convertEvent(DuckulaEvent oriEvent, FieldFormart fieldFormart) {
if (fieldFormart == FieldFormart.ori) {
return oriEvent;
}
Builder returBuilder = oriEvent.toBuilder();
if (fieldFormart != FieldFormart.ori) {
// 列处理
for (int i = 0; i < returBuilder.getColsCount(); i++) {
returBuilder.setCols(i, fieldFormart.getColName(returBuilder.getCols(i)));
}
// 数据处理
for (int i = 0; i < returBuilder.getItemsCount(); i++) {
DuckulaEventItem.Builder newBuilder = DuckulaEventItem.newBuilder();
DuckulaEventItem.Builder oldBuilder = returBuilder.getItemsBuilder(i);
newBuilder.setVersion(oldBuilder.getVersion());
Map afterMap = oldBuilder.getAfterMap();
if (MapUtils.isNotEmpty(afterMap)) {
for (String key : afterMap.keySet()) {
newBuilder.putAfter(fieldFormart.getColName(key), afterMap.get(key));
}
}
Map beforeMap = oldBuilder.getBeforeMap();
if (MapUtils.isNotEmpty(beforeMap)) {
for (String key : beforeMap.keySet()) {
newBuilder.putBefore(fieldFormart.getColName(key), beforeMap.get(key));
}
}
returBuilder.setItems(i, newBuilder);
}
}
return returBuilder.build();
}
// 返回数组有点困难
@SuppressWarnings("unchecked")
public static List> getKey(DuckulaEvent.Builder duckulaEventBuilder,
int index) {
Map datamap = !isAfter(duckulaEventBuilder.getOptType())
? duckulaEventBuilder.getItems(index).getBeforeMap()
: duckulaEventBuilder.getItems(index).getAfterMap();
Integer[] keysindex = duckulaEventBuilder.getKeyindexsCount() == 0 ? new Integer[] { 0 }
: duckulaEventBuilder.getKeyindexsList().toArray(new Integer[duckulaEventBuilder.getKeyindexsCount()]);
List> retlist = new ArrayList>();
for (int i = 0; i < keysindex.length; i++) {
String keyName = duckulaEventBuilder.getCols(keysindex[i]);
String value = datamap.get(keyName);
ColumnType columnType = duckulaEventBuilder.getColsType(keysindex[i]);
Serializable retobj = value;
switch (columnType) {
case LONGLONG:
retobj = Long.valueOf(value);
break;
case BIT:
case TINY:
case SHORT:
case INT24:
case LONG:
case ENUM:
case SET:
retobj = Integer.valueOf(value);
break;
default:
break;
}
retlist.add(Pair.of(keyName, (T) retobj));
}
return retlist;
}
/***
* 得到主键名
*
* @param duckulaEvent
* @return
*/
public static String[] getKeyColname(DuckulaEvent duckulaEvent) {
Integer[] keysindex = duckulaEvent.getKeyindexsCount() == 0 ? new Integer[] { 0 }
: duckulaEvent.getKeyindexsList().toArray(new Integer[duckulaEvent.getKeyindexsCount()]);
String[] retAry = new String[keysindex.length];
for (int i = 0; i < retAry.length; i++) {
retAry[i] = duckulaEvent.getCols(i);
}
return retAry;
}
public static String getKeyJoin(DuckulaEvent.Builder duckulaEventBuilder, int index, String splitChat) {
List> keyValues = getKey(duckulaEventBuilder, index);
String splitChatStr = StringUtils.isEmpty(splitChat) ? "," : splitChat;
StringBuffer buf = new StringBuffer();
for (Pair keyValue : keyValues) {
buf.append(splitChatStr);
buf.append(keyValue.getRight());
}
return buf.substring(1);
}
public static ObjectNode getKeyJson(DuckulaEvent.Builder duckulaEventBuilder, int index) {
List> keyValues = getKey(duckulaEventBuilder, index);
return PluginAssit.getKeySplit(keyValues);
}
/***
* 创建只有一个item的DuckulaEvent
*
* @param duckulaEventBuilder
* @param index
* @return
*/
public static DuckulaEvent.Builder buildSinglItemDuckulaEvent(DuckulaEvent.Builder duckulaEventBuilder, int index) {
net.wicp.tams.common.binlog.alone.ListenerConf.DuckulaEvent.Builder duckulaEventbuilder = null;
while (true) {
try {
duckulaEventbuilder = duckulaEventBuilder.clone();
break;
} catch (Throwable e) {
// 20230322 Exception in thread "pool-5-thread-1"
// java.lang.UnsupportedOperationException
System.out.println("有时发生clone异常?不明什么原因!已做处理，不会导致数据丢失。" + e.getMessage());
}
}
DuckulaEventItem curItems = duckulaEventbuilder.getItems(index);
duckulaEventbuilder.clearItems();
duckulaEventbuilder.addItems(curItems);
return duckulaEventbuilder;
}
/***
* 得到变化后数据的值
*
* @param duckulaEvent
* @param colName
* @return
*/
public static T getValueAfter(DuckulaEvent duckulaEvent, int index, String colName) {
return getValue(duckulaEvent, index, colName, true);
}
public static T getValueBefore(DuckulaEvent duckulaEvent, int index, String colName) {
return getValue(duckulaEvent, index, colName, false);
}
public static T getValue(DuckulaEvent duckulaEvent, int index, String colName) {
return getValue(duckulaEvent, index, colName, isAfter(duckulaEvent.getOptType()));
}
@SuppressWarnings("unchecked")
public static T getValue(DuckulaEvent duckulaEvent, int index, String colName,
boolean isAfter) {
if (duckulaEvent.getColsCount() != duckulaEvent.getColsList().size()) {
throw new RuntimeException("列名与值不一致，请联系相关人员。");
}
String value = getValueStr(duckulaEvent, index, colName, isAfter);
if (isEmpty(value)) {
return null;
}
int colindex = duckulaEvent.getColsList().indexOf(colName);
Serializable retobj = getValue(duckulaEvent.getColsType(colindex), value);
return (T) retobj;
}
public static Serializable getValue(ColumnType columnType, String value) {
Serializable retobj = null;
switch (columnType) {
case LONGLONG:
retobj = Long.valueOf(value);
break;
case BIT:
case TINY:
case SHORT:
case INT24:
case LONG:
case ENUM:
case SET:
retobj = Integer.valueOf(value);
break;
case FLOAT:
retobj = Float.valueOf(value);
break;
case DOUBLE:
retobj = Double.valueOf(value);
break;
case DECIMAL:
case NEWDECIMAL:
retobj = new BigDecimal(value);
break;
case GEOMETRY:
try {
retobj = Base64.decodeBase64(value);
} catch (Exception e) {
retobj = value;
}
break;
case YEAR:
try {
retobj = Integer.valueOf(value);
} catch (Exception e) {
retobj = value;
}
break;
case TIMESTAMP2:
case DATETIME2:
try {
retobj = formater.parse(value);
} catch (ParseException e) {
retobj = value;
}
break;
case BLOB:
default:
retobj = value;
break;
}
return retobj;
}
public static String getValueStr(DuckulaEvent duckulaEvent, int index, String colName) {
return getValueStr(duckulaEvent, index, colName, isAfter(duckulaEvent.getOptType()));
}
public static String getValueStr(DuckulaEvent duckulaEvent, int index, String colName, boolean isAfter) {
DuckulaEventItem item = duckulaEvent.getItems(index);
String value;
if (isAfter) {
value = item.getAfterMap().get(colName);
} else {
value = item.getBeforeMap().get(colName);
}
return value;
}
/**
* 选取的操作类型
*
* @param optType
* @return
*/
public static boolean isAfter(OptType optType) {
if (optType == OptType.delete || optType == OptType.updateBefore) {
return false;
} else {
return true;
}
}
public static Map getValueMap(DuckulaEvent duckulaEvent, int index) {
DuckulaEventItem item = duckulaEvent.getItems(index);
Map retMap;
if (isAfter(duckulaEvent.getOptType())) {
retMap = item.getAfterMap();
} else {
retMap = item.getBeforeMap();
}
return retMap;
}
public static byte[] getBytes(String filePath) {
byte[] buffer = null;
try {
File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);
ByteArrayOutputStream bos = new ByteArrayOutputStream(1000);
byte[] b = new byte[1000];
int n;
while ((n = fis.read(b)) != -1) {
bos.write(b, 0, n);
}
fis.close();
bos.close();
buffer = bos.toByteArray();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return buffer;
}
public static ObjectNode convertJson(DuckulaEvent duckulaEvent) {
ObjectNode retobj = JsonNodeFactory.instance.objectNode();
retobj.putPOJO("optType", duckulaEvent.getOptType());
ArrayNode items = JsonNodeFactory.instance.arrayNode();
for (DuckulaEventItem duckulaEventItem : duckulaEvent.getItemsList()) {
ObjectNode itemobj = JsonNodeFactory.instance.objectNode();
switch (duckulaEvent.getOptType()) {
case update:
itemobj.putPOJO("after", duckulaEventItem.getAfterMap());
itemobj.putPOJO("before", duckulaEventItem.getBeforeMap());
break;
case insert:
itemobj.putPOJO("after", duckulaEventItem.getAfterMap());
break;
case delete:
itemobj.putPOJO("before", duckulaEventItem.getBeforeMap());
break;
default:
break;
}
items.add(itemobj);
}
retobj.set("items", items);
return retobj;
}
/**
* 通过duckula数据反推得到附加字段,
*
* @param duckulaEvent
* @return
*/
public static Map getAddColValues(DuckulaEvent duckulaEvent,
AddColNameType addColNameType) {
Map addValues = null;
if (addColNameType != AddColNameType.no) {// 组装附加字段
addValues = new HashMap();
for (AddColName addColName : AddColName.values()) {
if ((addColNameType == AddColNameType.selective || addColNameType == AddColNameType.selective_ori)
&& !addColName.isSetValue()) {// 是敏感没有配置列值
boolean logicDel = Conf.getBoolean("common.binlog.alone.binlog.global.logicDel").booleanValue();
if (!logicDel || (addColName != AddColName.lastOpttype && addColName != AddColName.commitTime
&& addColName != AddColName.isDelete)) {// 不是逻辑删除或逻辑删除是不是lastOpttype和commitTime就跳过
continue;
}
}
addValues.put(addColName, getAddColValue(duckulaEvent, addColName));
}
}
return addValues;
}
public static String getAddColValue(DuckulaEvent duckulaEvent, AddColName addColName) {
String retvalue = "";
switch (addColName) {
case commitTime:
if (duckulaEvent.getCommitTime() > 0) {// 20220726 对于jdbc插件，如果是long型是没办法入库的
String tempValue = DateFormatCase.YYYY_MM_DD_hhmmss.getInstanc()
.format(new Date(duckulaEvent.getCommitTime()));
retvalue = tempValue;
}
break;
case dumpTime:
if (duckulaEvent.getDumpTime() > 0) {// 20220726 对于jdbc插件，如果是long型是没办法入库的
String tempValue = DateFormatCase.YYYY_MM_DD_hhmmss.getInstanc()
.format(new Date(duckulaEvent.getDumpTime()));
retvalue = tempValue;
}
break;
case isDelete:
retvalue = !isAfter(duckulaEvent.getOptType()) ? "1" : "0";
break;
case lastOpttype:
retvalue = duckulaEvent.getOptType().name();
break;
case oriDb:
retvalue = duckulaEvent.getDb();
break;
case oriTb:
retvalue = duckulaEvent.getTb();
break;
case oriInstid:
retvalue = String.valueOf(duckulaEvent.getDbInstanceId());
break;
default:
break;
}
return retvalue;
}
/***
* 得到update类型的before值，有些对冲逻辑会有需要。
*
* @param duckulaEvent
* @param index
* @return
*/
public static ObjectNode getChangedUpdateBefore(DuckulaEvent duckulaEvent, int index) {
if (duckulaEvent.getOptType() == OptType.update && duckulaEvent.getItemsCount() >= index + 1
&& MapUtils.isNotEmpty(duckulaEvent.getItems(index).getBeforeMap())) {
ObjectNode retjson = JsonNodeFactory.instance.objectNode();
Map beforeMap = duckulaEvent.getItems(index).getBeforeMap();
Map afterMap = duckulaEvent.getItems(index).getAfterMap();
for (String colName : duckulaEvent.getColsList()) {// 都是业务字段，没有附加字段
// 排除附加字段的
// AddColName addColName = AddColName.findByColName(colName);
// if(addColName!=null) {
// continue;
// }
if (!beforeMap.containsKey(colName) && !afterMap.containsKey(colName)) {
// 都为空表示相等，不处理
} else if (beforeMap.containsKey(colName) && !afterMap.containsKey(colName)) {
retjson.set(colName, DuckulaAssit.getValue(duckulaEvent, index, colName));
} else if (!beforeMap.containsKey(colName) && afterMap.containsKey(colName)) {// after新增，这块，本方法不处理。
retjson.set(colName, null);
} else {
String beforeValue = beforeMap.get(colName);
String afterValue = afterMap.get(colName);
if (!beforeValue.equals(afterValue)) {
retjson.set(colName, DuckulaAssit.getValueBefore(duckulaEvent, index, colName));// 20230213把before放到json
}
}
}
return retjson;
}
return null;
}
public static Map getAddColValuesStr(Map addValues,
FieldFormart fieldFormart) {
Map retmap = new HashMap();
for (AddColName addColName : addValues.keySet()) {
String value = null;
// 值已做过处理，所以这里不需要处理了。
// if (AddColName.commitTime == addColName || AddColName.dumpTime == addColName) {
// // 使用TyyyyMMddHHmmss导致大了8个小时的问题，改为一般时间。20220708
// value = DateFormatCase.YYYY_MM_DD_hhmmss.getInstanc().format(addValues.get(addColName));
// } else {
value = String.valueOf(addValues.get(addColName));
// }
retmap.put(addColName.getColNameTrue(fieldFormart), value);
}
return retmap;
}
public static Map getAddColValuesStr(DuckulaEvent duckulaEvent, AddColNameType addColNameType,
FieldFormart fieldFormart) {
return getAddColValuesStr(getAddColValues(duckulaEvent, addColNameType), fieldFormart);
}
/***
* 得到mysql的当前位点
*
* @param conn 数据库连接
* @return
*/
public static Position.Builder getMastStatus(Connection conn) {
ResultSet rs = JdbcAssit.querySql(conn, "show master status");
try {
if (rs.next()) {
String filename = rs.getString(1);
long pos = rs.getLong(2);
Position.Builder ret = Position.newBuilder();
ret.setFileName(filename);
ret.setPos(pos);
if (rs.getMetaData().getColumnCount() >= 5) {
String gtidStr = rs.getString(5);
ret.setGtids(gtidStr.replace("/n", ""));
}
rs = JdbcAssit.querySql(conn, "show variables like 'server_id'");
if (rs.next()) {
long masterServerId = rs.getLong(2);
ret.setMasterServerId(masterServerId);
}
rs = JdbcAssit.querySql(conn, "SELECT unix_timestamp(now())");// 得到时间戳，单位为秒
if (rs.next()) {
long time = rs.getLong(1) * 1000;
ret.setTime(time);
ret.setTimeStr(DateFormatCase.YYYY_MM_DD_hhmmss.getInstanc().format(time));
}
// 20200817 缺少serverip和clientid问题修复
// ret.setServerIp(connConfBuilder.getHost());
// ret.setClintId(connConfBuilder.getClientId());
return ret;
}
throw new RuntimeException("没有得到mastStatus,服务器不支持binlog");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}