import lombok.Data;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;
/**
* clickhouse工具类
*
*/
public final class ClickHouseUtil {
/**
* 私有化工具类构造方法
*/
private ClickHouseUtil() {
}
/**
* 将String类型的集合转换为clickhouse数组，最终格式为：['A', 'B']
*
* @param list 集合
* @return clickhouse字符串数组
*/
public static String toArrayString(List<?> list) {
if (CollectionUtils.isEmpty(list)) {
return "[]";
}
if (list.get(0) instanceof String) {
// return list.stream().map(s -> "'" + StringEscapeUtils.escapeJava((String) s) + "'").collect(Collectors.joining(",", "[", "]"));
return list.stream().map(s -> "'" + s + "'").collect(Collectors.joining(",", "[", "]"));
} else {
return list.stream().map(String::valueOf).collect(Collectors.joining(",", "[", "]"));
}
}
/**
* 解析tuple(key,value)类型数据(['1','2','3','4'],[107179,111364,106635,111979])
* 只支持一维数组，且key和value的数组长度必须相同
*
* @param tupleStr tuple字符串
* @return Tuple对象
*/
public static <K, V> MapList<K, V> parseTuple(String tupleStr, Class<K> kClass, Class<V> vClass) {
if (StringUtils.isBlank(tupleStr)) {
MapList<K, V> mapList = new MapList<>();
mapList.setKeyList(Collections.emptyList());
mapList.setValueList(Collections.emptyList());
return mapList;
}
// 去掉([和])符号，最终样式为'1','2','3','4'],[107179,111364,106635,111979
String replacedStr = StringUtils.replaceEach(tupleStr, new String[]{"([", "])"}, new String[]{"", ""});
// 根据],[拆分，最终样式为'1','2','3','4'和107179,111364,106635,111979两个数组
String[] splitArray = replacedStr.split("],\\[");
List<String> keyList = new ArrayList<>();
List<String> valueList = new ArrayList<>();
if (splitArray.length == 2) {
String[] keys = splitArray[0].split(",");
if (keys.length > 0) {
for (String key : keys) {
// 如果key包含‘，说明是字符串
if (key.contains("'")) {
key = key.replace("'", "");
}
keyList.add(key);
}
}
String[] values = splitArray[1].split(",");
for (String value : values) {
if (value.contains("'")) {
value = value.replace("'", "");
}
valueList.add(value);
}
}
MapList<K, V> mapList = new MapList<>();
mapList.setKeyList(stringOrNumberList(keyList, kClass));
mapList.setValueList(stringOrNumberList(valueList, vClass));
return mapList;
}
/**
* 解析array类型数据['1','2','3','4']或[107179,111364,106635,111979]
* 数组里的类型只能是字符串或数字
*
* @param arrayStr array字符串
* @return 集合
*/
public static <T> List<T> parseArray(String arrayStr, Class<T> tClass) {
// 去掉[]符号，最终样式为'1','2','3','4'或107179,111364,106635,111979
String replacedStr = StringUtils.replaceEach(arrayStr, new String[]{"[", "]"}, new String[]{"", ""});
if (StringUtils.isBlank(replacedStr)) {
return Collections.emptyList();
}
// 根据逗号拆分
String[] splitArray = replacedStr.split(",");
List<String> list = new ArrayList<>();
for (String s : splitArray) {
if (s.contains("'")) {
s = s.replace("'", "");
}
list.add(s);
}
return stringOrNumberList(list, tClass);
}
/**
* String的List转Number类型
*
* @param list 原始String集合
* @param tClass 泛型class
* @param <T> 泛型
* @return 转换后的集合
*/
@SuppressWarnings("unchecked")
public static <T> List<T> stringOrNumberList(List<String> list, Class<T> tClass) {
switch (tClass.getSimpleName()) {
case "Byte":
return (List<T>) list.stream().map(Byte::parseByte).collect(Collectors.toList());
case "Short":
return (List<T>) list.stream().map(Short::parseShort).collect(Collectors.toList());
case "Integer":
return (List<T>) list.stream().map(Integer::parseInt).collect(Collectors.toList());
case "Long":
return (List<T>) list.stream().map(Long::parseLong).collect(Collectors.toList());
case "BigInteger":
return (List<T>) list.stream().map(BigInteger::new).collect(Collectors.toList());
case "BigDecimal":
return (List<T>) list.stream().map(BigDecimal::new).collect(Collectors.toList());
case "Double":
return (List<T>) list.stream().map(Double::parseDouble).collect(Collectors.toList());
case "Float":
return (List<T>) list.stream().map(Float::parseFloat).collect(Collectors.toList());
default:
return (List<T>) list;
}
}
/**
* K-V集合，keyList里面的每一个元素与valueList中的元素对应
* keyList和valueList长度必须相同
*
* @param <K>只能是字符串或数字类型
* @param <V>只能是字符串或数字类型
*/
@Data
public static class MapList<K, V> {
private List<K> keyList;
private List<V> valueList;
/**
* 将2个list的key-value转换为k-v对象集合
*
* @return List<KeyValue < K, V>>
*/
public List<KeyValue<K, V>> toList() {
if (keyList == null || valueList.isEmpty()) {
return Collections.emptyList();
}
if (keyList.size() != valueList.size()) {
throw new IllegalArgumentException("keyList与valueList集合数量不同，无法转换");
}
List<KeyValue<K, V>> list = new ArrayList<>();
for (int i = 0; i < keyList.size(); i++) {
KeyValue<K, V> keyValue = new KeyValue<>();
keyValue.setKey(keyList.get(i));
keyValue.setValue(valueList.get(i));
list.add(keyValue);
}
return list;
}
/**
* 转换为Map
*
* @return Map
*/
public Map<K, V> toMap() {
if (keyList == null || valueList == null) {
return Collections.emptyMap();
}
if (keyList.size() != valueList.size()) {
throw new IllegalArgumentException("keyList与valueList集合数量不同，无法转换");
}
Map<K, V> map = new HashMap<>(keyList.size() * 2);
for (int i = 0; i < keyList.size(); i++) {
map.put(keyList.get(i), valueList.get(i));
}
return map;
}
}
/**
* k-v对象
*
* @param <K>
* @param <V>
*/
@Data
public static class KeyValue<K, V> {
private K key;
private V value;
}
public static void main(String[] args) {
// clickhouse 数组中'会导致插入失败
String aa = "aa'n";
System.out.println(aa.replace("'","\\'"));
}
}
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
public class ClickhouseArrayTypeHandler extends BaseTypeHandler<List<?>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<?> parameter, JdbcType jdbcType) throws SQLException {
ps.setObject(i, ClickHouseUtil.toArrayString(parameter));
}
@Override
public List<?> getNullableResult(ResultSet rs, String columnName) throws SQLException {
return getList(rs.getArray(columnName));
}
@Override
public List<?> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return getList(rs.getArray(columnIndex));
}
@Override
public List<?> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return getList(cs.getArray(columnIndex));
}
private List<?> getList(Array array) {
if (array == null) {
return new ArrayList<>();
}
return Arrays.stream(getArray(array)).collect(Collectors.toList());
}
/*@SuppressWarnings("unchecked")
private <T> T[] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (T[]) array.getArray();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}*/
private Object [] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (Object[]) array.getArray();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
