car-wash-java/car-wash-jdbc/src/main/java/com/kym/DbUtil.java

package com.kym;

import com.kym.jdbc.annotations.DBF;
import com.kym.jdbc.annotations.Entity;
import com.kym.jdbc.annotations.Many;
import com.kym.jdbc.annotations.One;
import com.kym.jdbc.annotations.QE;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Array;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 描述:基础工具类 refelct、empty check
 * create at 2020/5/7 23:01
 *
 * @author yaop
 */
public class DbUtil {
    private static final Logger logger = LoggerFactory.getLogger(DbUtil.class);

    private DbUtil() {
    }


    public static void asserts(boolean express, String errorMsg) {
        if (!express) {
            throw new RuntimeException(errorMsg);
        }
    }

    public static int null2Int(Object val) {
        if (null == val) {
            return 0;
        }
        try {
            return Integer.parseInt(val.toString());
        } catch (Exception e) {
            // do nothings
        }
        return 0;
    }

    public static boolean isGenericType(Type type) {
        Class<?> t =  (Class) type;
        return  t.isAssignableFrom(String.class)
                ||t.isAssignableFrom(byte.class)
                ||t.isAssignableFrom(Byte.class)
                ||t.isAssignableFrom(boolean.class)
                ||t.isAssignableFrom(Boolean.class)
                ||t.isAssignableFrom(int.class)
                ||t.isAssignableFrom(Integer.class)
                ||t.isAssignableFrom(short.class)
                ||t.isAssignableFrom(Short.class)
                ||t.isAssignableFrom(long.class)
                ||t.isAssignableFrom(Long.class)
                ||t.isAssignableFrom(float.class)
                ||t.isAssignableFrom(Float.class)
                ||t.isAssignableFrom(double.class)
                ||t.isAssignableFrom(Double.class)
                ||t.isAssignableFrom(char.class)
                ||t.isAssignableFrom(Character.class);
//        return type == String.class || type == Integer.TYPE || type == Long.TYPE || type == Short.TYPE || type == Byte.TYPE
//                || type == Boolean.TYPE || type == Character.TYPE || type == Float.TYPE || type == Double.TYPE;
    }

    public static boolean isGenericType(Object obj) {
        if (obj instanceof Integer) {
            return true;
        } else if (obj instanceof Short) {
            return true;
        } else if (obj instanceof Float) {
            return true;
        } else if (obj instanceof Double) {
            return true;
        } else if (obj instanceof Byte) {
            return true;
        } else if (obj instanceof Boolean) {
            return true;
        } else if (obj instanceof Long) {
            return true;
        } else if (obj instanceof String) {
            return true;
        } else {
            return obj instanceof Character;
        }
    }

    /**
     * 数据库持久化的字段
     */
    public static boolean isUsageField(Field f) {
        if (f.isAnnotationPresent(DBF.class)) {
            return true;
        }
        if (f.isAnnotationPresent(One.class) && !f.getAnnotation(One.class).noQuery()) {
            return true;
        }
        return f.isAnnotationPresent(Many.class) && !f.getAnnotation(Many.class).noQuery();
    }

    public static boolean isStaticFinalField(Field f) {
        int modifiers = f.getModifiers();
        return Modifier.isFinal(modifiers) || Modifier.isStatic(modifiers);
    }

    public static String firstToLowerCase(String str) {
        return str.substring(0, 1).toLowerCase() + str.substring(1);
    }

    public static String getColumnName(String field) {
        StringBuilder sbr = new StringBuilder();
        char[] chars = field.toCharArray();
        for (char ch : chars) {
            if (Character.isUpperCase(ch)) {
                sbr.append("_").append(Character.toLowerCase(ch));
            } else {
                sbr.append(ch);
            }
        }
        return sbr.toString();
    }

    public static Object getFieldValue(Object o, Field field) {

        field.setAccessible(true);
        Object val = null;
        try {
            val = field.get(o);
        } catch (IllegalAccessException e) {
            logger.error("SQLHelper ERR# Object getFieldValue,fileName:{}", field.getName());
        }
        field.setAccessible(false);
        return val;
    }

    public static Object getFieldValue(Object bean, String fieldName) {
        Object defValu = "";
        try {
            fieldName = getCamelName(fieldName);
            Field field = null;
            try {
                field = bean.getClass().getDeclaredField(fieldName);
            } catch (Exception e) {
                try {
                    field = bean.getClass().getField(fieldName);
                } catch (Exception e1) {
                    try {
                        field = bean.getClass().getSuperclass().getDeclaredField(fieldName);
                    } catch (Exception e2) {
                        field = bean.getClass().getSuperclass().getField(fieldName);
                    }
                }
            }

            field.setAccessible(true);
            defValu = field.get(bean);
            if (null == defValu) {
                Class<?> type = field.getType();
                if (type == String.class) {
                    defValu = "";
                } else if (type == int.class || type == Integer.class) {
                    defValu = 0;
                } else if (type == double.class || type == Double.class) {
                    defValu = 0d;
                } else if (type == long.class || type == Long.class) {
                    defValu = 0L;
                } else if (type == float.class || type == Float.class) {
                    defValu = 0F;
                } else if (type == short.class || type == Short.class) {
                    defValu = 0;
                } else if (type == byte.class || type == Byte.class) {
                    defValu = 0;
                } else if (type == boolean.class || type == Boolean.class) {
                    defValu = 0;
                } else if (type == char.class || type == Character.class) {
                    defValu = '0';
                } else if (field.getType().isArray()) {
                    defValu = new Object[]{};
                }
            }
            field.setAccessible(false);
        } catch (Exception e) {
            logger.warn("SQLHandler ERR# getFieldValue ,field:{}, bean:{} ,msg:{}", fieldName, bean.getClass().getSimpleName(), e.getMessage());
        }
        return defValu;
    }

    public static String getCamelName(String fieldName) {
        StringBuilder sbr = new StringBuilder();
        char[] chars = fieldName.toCharArray();
        boolean preUpper = false;
        for (char ch : chars) {
            if ('_' == ch) {
                preUpper = true;
            } else {
                if (preUpper) {
                    sbr.append(Character.toUpperCase(ch));
                    preUpper = false;
                } else {
                    sbr.append(ch);
                }
            }
        }
        return sbr.toString();
    }

    public static void setFieldValue(Object o, Field field, Object val) {
        try {
            field.setAccessible(true);
            field.set(o, val);
            field.setAccessible(false);
        } catch (IllegalAccessException e) {
            logger.error("SQLHelper ERR# Object setFieldValue ,fileName:{}", field.getName());
        }
    }


    /**
     * 判断集合是否为空
     */
    public static boolean isEmptyOrNull(Collection<?> collection) {
        return null == collection || collection.isEmpty();
    }


    /**
     * 判断数组是否为空
     */
    public static boolean isEmptyOrNull(Object[] array) {
        return null == array || array.length == 0 || null == array[0];
    }

    /**
     * 判断字符串是否为空
     */
    public static boolean isEmptyOrNull(String value) {

        return null == value || value.isEmpty();
    }

    public static boolean isEmptyOrNull(Object value) {
        if (null == value) {
            return true;
        }
        if (value.getClass().isArray()) {
            return Array.getLength(value) == 0;
        } else if (value instanceof Collection) {
            return ((Collection<?>) value).isEmpty();
        } else if (value instanceof Map) {
            return ((Map<?, ?>) value).isEmpty();
        } else {
            return isEmptyOrNull(value.toString());
        }
    }

    public final static String regex = "(create\\s+(database|table|index)|truncate|drop\\s+(table|index|database)|alter\\s+(table|database)|update[\\s\\S]*set|insert[\\s\\S]*into|delete\\s+from|net user|xp_cmdshell|#)+\\s+";
/*
    public final static String regex = "'|and|exec|execute|insert|select|delete|update|count|drop|\\*|%|chr|mid|master|truncate|" +
            "char|declare|sitename|net user|xp_cmdshell|;|or|-|\\+|,|like'|and|exec|execute|insert|create|drop|" +
            "table|from|grant|use|group_concat|column_name|" +
            "information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|\\*|" +
            "chr|mid|master|truncate|char|declare|or|;|-|--|\\+|,|like|//|/|%|#";
*/

    /**
     * 把SQL关键字追加$字符串
     */
    public static String injectDefend(String param) {
        if (param == null) {
            return null;
        }
        // (?i)不区分大小写替换
        return param.replaceAll("(?i)" + regex, "$0.");
    }

    /**
     * 返回经过防注入处理的字符串
     */
    public static String fitlerSql(String sql) {
        return injectDefend(sql);
    }

    /**
     * 设置参数
     *
     * @param ps
     * @param parameters
     */
    public static void setParameter(PreparedStatement ps, Object... parameters) throws SQLException {
        if (parameters == null || parameters.length == 0) {
            return;
        }
        int i = 1;
        for (Object o : parameters) {
            if (o == null) {
                ps.setString(i++, null);
                continue;
            }
            if (o instanceof String) {
                ps.setString(i++, ((String) o));
                continue;
            }
            if (o instanceof Date date) {
                ps.setTimestamp(i++, new Timestamp(date.getTime()));
                continue;
            }
            // Integer
            if (o instanceof Integer) {
                ps.setInt(i++, ((Integer) o));
                continue;
            }
            if (o instanceof Double) {
                ps.setDouble(i++, ((Double) o));
                continue;
            }
            if (o instanceof Float) {
                ps.setFloat(i++, ((Float) o));
                continue;
            }
            if (o instanceof BigDecimal) {
                ps.setBigDecimal(i++, ((BigDecimal) o));
                continue;
            }
            if (o instanceof Long) {
                ps.setLong(i++, ((Long) o));
                continue;
            }
            if (o instanceof Short) {
                ps.setShort(i++, ((Short) o));
                continue;
            }
            if (o instanceof Byte) {
                ps.setByte(i++, ((Byte) o));
                continue;
            }
            if (o instanceof byte[]) {
                ps.setBytes(i++, ((byte[]) o));
                continue;
            }
            if (o instanceof Boolean) {
                ps.setBoolean(i++, ((Boolean) o));
                continue;
            }

            if (o.getClass().isArray()) {
                ps.setString(i++, JacksonUtil.toJSONString(o));
                continue;
            }

            if (List.class.isAssignableFrom(o.getClass())) {
                ps.setString(i++, JacksonUtil.toJSONString(o));
                continue;
            }

            if (Set.class.isAssignableFrom(o.getClass())) {
                ps.setString(i++, JacksonUtil.toJSONString(o));
                continue;
            }

            if (Map.class.isAssignableFrom(o.getClass())) {
                ps.setString(i++, JacksonUtil.toJSONString(o));
                continue;
            }

            if (isGenericType(o.getClass())) {
                ps.setString(i++, JacksonUtil.toJSONString(o));
                continue;
            }

            logger.info("SetParameter PreparedStatement SQL  type:{},value:{}", o.getClass().getName(), JacksonUtil.toJSONString(o));
//            throw new IllegalArgumentException("unsupported type:" + o.getClass());
            ps.setString(i++,JacksonUtil.toJSONString(o));
        }
    }


    private static void recursiveFields(Class<?> clz, Set<Field> fieldSet) {
        if(null==clz){
            return;
        }
        Field[] fields = clz.getDeclaredFields();
        if (!isEmptyOrNull(fields)) {
            for (Field f : fields) {
                if (isUsageField(f)) {
                    fieldSet.add(f);
                }
            }
        }
        Entity annotation = clz.getDeclaredAnnotation(Entity.class);
        if (null == annotation) {
            recursiveFields(clz.getSuperclass(), fieldSet);
        }
    }

    public static Field getField(Object bean, String fieldName) throws NoSuchFieldException {
        Field field = null;
        int loop = 0;
        Class<?> clz = bean.getClass();
        while (loop < 10) {
            try {
                loop++;
                return clz.getDeclaredField(fieldName);
            } catch (Exception e) {
                clz = clz.getSuperclass();
            }
            if(null==clz){
                throw new NoSuchFieldException(fieldName);
            }
        }
        throw new NoSuchFieldException(fieldName);
    }

    public static Set<Field> getEntityFields(Class<?> clz) {
        Set<Field> fields = new HashSet<>();
        recursiveFields(clz, fields);
        return fields;
    }


    public static Set<Field> getQueryFields(Class<?> clz) {
        Set<Field> fields = new HashSet<>();
        if (clz.getName().equals("BasicQuery")) {
            return new HashSet<>(Arrays.asList(clz.getFields()));
        }
        QE annotation = clz.getDeclaredAnnotation(QE.class);
        if (null != annotation) {
            Field[] fields1 = clz.getFields();
            if (!isEmptyOrNull(fields1)) {
                for (Field f : fields1) {
                    if (!isStaticFinalField(f)) {
                        fields.add(f);
                    }
                }
            }
            Field[] fields2 = clz.getDeclaredFields();
            if (!isEmptyOrNull(fields2)) {
                for (Field f : fields2) {
                    if (!isStaticFinalField(f)) {
                        fields.add(f);
                    }
                }
            }
        }
        return fields;
    }

    public static void main(String[] args) {
        System.out.println(fitlerSql("droptable t_state"));
        System.out.println(fitlerSql("update t_state set "));
        System.out.println(fitlerSql("drop table t_state"));
        System.out.println(fitlerSql("DROP table t_state"));
        System.out.println(fitlerSql("Drop Table t_state"));
        System.out.println(fitlerSql("alter table t_state"));
        System.out.println(fitlerSql("delete from  table t_state"));
        System.out.println(fitlerSql(".lete from  -- table t_state"));
        System.out.println(fitlerSql("truncate table t_state -- '' delete from t"));
    }
}