Mysql数据库AES加密工具

chenjiahe

2022-01-28 b1097dce55d857ae1ae63798911c1e7027c00de4

Mysql数据库AES加密工具

 src/main/java/com/hx/common/annotations/MysqlHexAes.java

@@ -12,6 +12,8 @@
public @interface MysqlHexAes {
    /**秘钥-没有就是配置文件设置*/
    String aesKey() default "";
    /**数据库数据初始化版本*/
    String initVersion() default "";
    /**xml生成查询解密*/
    boolean selectDec() default false;
    /**xml更新加密*/

 src/main/java/com/hx/common/dao/CommonDao.java

@@ -110,5 +110,11 @@
     */
    <T extends Serializable> int updateSentence( SqlSentence sqlSentence);

    /**查询条数语句（全语句）
     * @param sqlSentence 查询参数类
     * @return 返回条数
     */
    <T extends Serializable> int selectCountSql( SqlSentence sqlSentence);


}

 src/main/java/com/hx/common/dao/CommonMapper.java

@@ -14,4 +14,7 @@
    /**查询列表，返回Map的List*/
    List<Map<String,Object>> selectListMap(SqlSentence sqlSentence);

    /**查询，返回条数数量*/
    int selectCountSql(SqlSentence sqlSentence);

}

 src/main/java/com/hx/common/service/CommonService.java

@@ -111,5 +111,10 @@
     */
    <T extends Serializable> int updateSentence(SqlSentence sqlSentence);

    /**查询条数语句（全语句）
     * @param sqlSentence 查询参数类
     * @return 返回条数
     */
    <T extends Serializable> int selectCountSql(SqlSentence sqlSentence);

}

 src/main/java/com/hx/common/service/impl/CommonDaoImpl.java

@@ -109,5 +109,10 @@
        return sqlSessionFactory.openSession().delete(getStatement(CommonMapper.class,"updateSentence"),sqlSentence);
    }

    /**查询条数（全语句）*/
    @Override
    public <T extends Serializable> int selectCountSql(SqlSentence sqlSentence) {
        return sqlSessionFactory.openSession().delete(getStatement(CommonMapper.class,"selectCountSql"),sqlSentence);
    }

}

 src/main/java/com/hx/common/service/impl/CommonServiceImpl.java

@@ -104,5 +104,11 @@
        return commonDao.updateSentence(sqlSentence);
    }

    /**更新sql语句（全语句）*/
    @Override
    public <T extends Serializable> int selectCountSql(SqlSentence sqlSentence) {
        return commonDao.selectCountSql(sqlSentence);
    }


}

 src/main/java/com/hx/common/xml/CommonMapper.xml

@@ -11,4 +11,9 @@
    <select id="selectListMap" resultType="java.util.Map" parameterType="com.hx.mybatisTool.SqlSentence" >
        ${sqlSentence}
    </select>

    <select id="selectCountSql" resultType="int" parameterType="com.hx.mybatisTool.SqlSentence" >
        ${sqlSentence}
    </select>

</mapper>

 src/main/java/com/hx/mybatis/aes/springbean/ConstantBean.java

@@ -16,6 +16,9 @@
    /**固定AES的秘钥*/
    @Value("${mysql.hxe.aes.fixd.key:null}")
    private String fixedAesKey;
    /**数据库初始化加密字段版本号*/
    @Value("${mysql.hxe.aes.init.version:null}")
    private String initVersion;

    public String getPackPath() {
        return packPath;
@@ -32,4 +35,12 @@
    public void setFixedAesKey(String fixedAesKey) {
        this.fixedAesKey = fixedAesKey;
    }

    public String getInitVersion() {
        return initVersion;
    }

    public void setInitVersion(String initVersion) {
        this.initVersion = initVersion;
    }
}

 src/main/java/com/hx/mybatis/aes/springbean/FieldData.java

New file
@@ -0,0 +1,40 @@
package com.hx.mybatis.aes.springbean;

import java.util.Set;

public class FieldData {

    //主键
    private String id;
    //数据库表明
    private String tableName;
    //需要加密得表字段
    private Set<String> encrypFields;

    public FieldData() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public Set<String> getEncrypFields() {
        return encrypFields;
    }

    public void setEncrypFields(Set<String> encrypFields) {
        this.encrypFields = encrypFields;
    }
}

 src/main/java/com/hx/mybatis/aes/springbean/InitMysqlData.java

@@ -12,10 +12,7 @@

import javax.annotation.PostConstruct;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.*;

public class InitMysqlData {

@@ -28,72 +25,49 @@
        //项目启动的时候填入
        if(!StringUtils.isEmpty(packPath)){
            Set<Class<?>> classes = VariableAesKey.classData(packPath);
            Map<String,String> aesKeysFild = new HashMap<>();
            boolean isAes = false;
            String tableName = null;
            String fildName = null;
            String fildValue = null;

            SqlSentence sqlSentence = new SqlSentence();
            Map<String,Object> values = new HashMap<>();

            for(Class<?> cl:classes){
                //表名称
                boolean hasAnnotation = cl.isAnnotationPresent(Table.class);
                if(!hasAnnotation){
                    continue;
            //解析表数据
            List<FieldData> fieldDatas = entityhandle(classes);

            StringBuilder selectField;
            StringBuilder setField;
            for(FieldData fieldData:fieldDatas){
                //获取条数
                sqlSentence.sqlSentence("SELECT COUNT(0) FROM "+fieldData.getTableName(),values);
                int total = commonService.selectCountSql(sqlSentence);

                //查询数据
                selectField = new StringBuilder();
                selectField.append(fieldData.getId());
                for(String fieldName:fieldData.getEncrypFields()){
                    selectField.append(","+fieldName);
                }
                Table table = cl.getAnnotation(Table.class);
                tableName = table.name();
                sqlSentence.sqlSentence("SELECT "+selectField.toString()+" FROM "+fieldData.getTableName(),values);

                aesKeysFild = new HashMap<>();
                isAes = false;

                // 取得本类的全部属性
                Field[] fields = cl.getDeclaredFields();
                fields = VariableAesKey.getPatentFields(fields,cl);
                for (Field field:fields) {
                    fildName = null;
                    // 判断方法中是否有指定注解类型的注解
                    hasAnnotation = field.isAnnotationPresent(MysqlHexAes.class);
                    if (hasAnnotation) {
                        // 根据注解类型返回方法的指定类型注解
                        MysqlHexAes mysqlHexAes = field.getAnnotation(MysqlHexAes.class);

                        //String aesKeyField = mysqlHexAes.aesKeyField();
                        String aesKey = mysqlHexAes.aesKey();

                        if(StringUtils.isEmpty(aesKey)){
                            aesKey = VariableAesKey.AES_KEY;
                            if(StringUtils.isEmpty(aesKey)){
                                throw new RuntimeException("mysql的AES秘钥不能为空:"+field.getName());
                            }
                        }

                        hasAnnotation = field.isAnnotationPresent(Column.class);
                        if(hasAnnotation){
                            Column column = field.getAnnotation(Column.class);
                            fildName = column.name();
                        }
                        if(StringUtils.isEmpty(fildName)){
                            fildName = field.getName();
                        }

                        sqlSentence.sqlSentence("SELECT id,"+fildName+" FROM "+tableName,values);
                //更新数据
                        List<Map<String,Object>> list = commonService.selectListMap(CommonMapper.class,sqlSentence);
                        for(Map<String,Object> map:list){
                            fildValue = (String)map.get(fildName);
                            System.out.println("fildValue:"+fildValue);
                            if(StringUtils.isEmpty(fildValue)){
                    setField = new StringBuilder();
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String mapKey = entry.getKey();
                        String mapValue = (String) entry.getValue();
                        if(StringUtils.isEmpty(mapValue)){
                                continue;
                            }
                            if(fildValue.length()%32==0 && MysqlHexAesTool.isHexStrValid(fildValue)){
                        if(mapValue.length()%32==0 && MysqlHexAesTool.isHexStrValid(mapValue)){
                                continue;
                            }
                            values.clear();
                            values.put("id",map.get("id"));
                            values.put("filedData",fildValue);
                            sqlSentence.sqlSentence("UPDATE "+tableName+" SET "+fildName+" = #{m.filedData} WHERE id = #{m.id}",values);
                        if(setField.length()>0){
                            setField.append(",");
                        }
                        setField.append(mapKey+" = #{m."+mapKey+"}");
                    }

                    values = map;
                    sqlSentence.sqlSentence("UPDATE "+fieldData.getTableName()+" SET "+setField.toString()+" WHERE id = #{m.id}",values);
                            if(commonService.updateSentence(sqlSentence)!=1){
                                throw new ServiceException("更新超过1条，更新失败！");
                            }
@@ -101,7 +75,83 @@
                    }
                }
            }

    /**获取到表的数据*/
    public static List<FieldData> entityhandle(Set<Class<?>> classes){

        List<FieldData> fildDatas = new ArrayList<>();
        //存储单表字段信息
        FieldData fildData;
        //存储需要加密的字段
        Set<String> encrypFields;

        String fildName;

        for(Class<?> cl:classes){
            fildData = new FieldData();
            encrypFields = new HashSet<>();

            //表名称
            if(!cl.isAnnotationPresent(Table.class)){
                continue;
        }
            Table table = cl.getAnnotation(Table.class);
            fildData.setTableName(table.name());

            // 取得本类的全部属性
            Field[] fields = cl.getDeclaredFields();
            fields = VariableAesKey.getPatentFields(fields,cl);
            for (Field field:fields) {

                fildName = null;
                if(field.isAnnotationPresent(Column.class)){
                    Column column = field.getAnnotation(Column.class);
                    fildName = column.name();
                    if(StringUtils.isEmpty(fildName)){
                        fildName = field.getName();
                    }
                    if(column.isKey()){
                        fildData.setId(fildName);
                    }
                }else{
                    fildName = field.getName();
                }

                // 判断方法中是否有指定注解类型的注解
                if (!field.isAnnotationPresent(MysqlHexAes.class)) {
                    continue;
                }
                // 根据注解类型返回方法的指定类型注解
                MysqlHexAes mysqlHexAes = field.getAnnotation(MysqlHexAes.class);
                //判断版本号是不是一样的
                if(!StringUtils.isEmpty(VariableAesKey.INIT_VERSION)){
                    if(!VariableAesKey.INIT_VERSION.equals(mysqlHexAes.initVersion())){
                        continue;
                    }
                }else{
                    if(!StringUtils.isEmpty(mysqlHexAes.initVersion())){
                        continue;
                    }
                }

                String aesKey = mysqlHexAes.aesKey();
                if(StringUtils.isEmpty(aesKey)){
                    aesKey = VariableAesKey.AES_KEY;
                    if(StringUtils.isEmpty(aesKey)){
                        throw new RuntimeException("mysql的AES秘钥不能为空:"+field.getName());
                    }
                }

                encrypFields.add(fildName);
            }
            //是否有需要加密得字段
            if(encrypFields.size()<=0){
                continue;
            }
            fildData.setEncrypFields(encrypFields);
            fildDatas.add(fildData);
        }
        return fildDatas;
    }



 src/main/java/com/hx/mybatis/aes/springbean/VariableAesKey.java

@@ -42,6 +42,10 @@

    /**固定的aes秘钥*/
    public static String AES_KEY = null;
    /**数据库加密字段初始化版本号*/
    public static String INIT_VERSION = null;



    /**存储AES秘钥*/
    public static void setAesKey(String aesKeyFild,String aesKey){
@@ -69,6 +73,7 @@
        //项目启动的时候填入
        logger.info("扫描获取AES的包:" + constantBean.getPackPath());
        AES_KEY = constantBean.getFixedAesKey();
        INIT_VERSION = constantBean.getInitVersion();
        if(!StringUtils.isEmpty(constantBean.getPackPath())){
            Set<Class<?>> classes = classData(constantBean.getPackPath());
            logger.info("扫描获取AES的包classes:" + classes.size());

 src/main/java/com/hx/platform/tool/PlatformSign.java