基于JFinal建立的Db+Record实现的数据库表字段加密

来源：互联网发布：刘雨欣张檬事件知乎编辑：程序博客网时间：2024/05/22 06:40

/** * 对列进行操作，然后更新到数据库 */public class ColumnUtil{    private static final Logger logger = LoggerFactory.getLogger(ColumnUtil.class);    /**     *      * @Title: test     * @Description:初始化Db     */    public static void initDb(){        PropKit.use("jdbc.properties");        String url = PropKit.get("jdbcUrl");        String username = PropKit.get("user");        String password = PropKit.get("password");        String driverClass = PropKit.get("driverClass");        String filters = PropKit.get("filters");        int initialSize = PropKit.getInt("db.initialSize");        int minIdle = PropKit.getInt("db.minIdle");        int maxActive = PropKit.getInt("db.maxActive");        long maxWait = PropKit.getLong("db.maxWait");        // mysql 数据源        DruidPlugin dsMysql = new DruidPlugin(url, username, password, driverClass, filters);        dsMysql.set(initialSize, minIdle, maxActive);        dsMysql.setMaxWait(maxWait);        dsMysql.start();        ActiveRecordPlugin arpMysql = new ActiveRecordPlugin("mysql", dsMysql);        arpMysql.setShowSql(false);        arpMysql.start();    }    private static void updateRecord(String tableName,Record record, String[] columns, ColumnHandler<String> handler){        // 做一些更新【1.先注意备份，再操作，以免数据丢失2.确保varchar数据长度够】        // record.set("...","...");        System.out.println("------------原record : " + record);        for(String column : columns){            String str = record.getStr(column);            if((null != str) && !"".equals(str)){                record.set(column, handler.handleColumn(str));            }        }        System.out.println("------------新record : " + record);        Db.update(tableName, record);    }    /**     * 一个列本来是什么类型，输出也是什么类型(T)     */    public interface ColumnHandler<T> {        T handleColumn(T src);    }    /**     * @Title: getHandler     * @Description:获取一个Handler     * @return ColumnHandler<String>     */    private static ColumnHandler<String> getHandler(){        ColumnHandler<String> handler = new ColumnHandler<String>(){            @Override            public String handleColumn(String src){                try{                    return DESCrypto.encrypt4Column(src);// 进行加密的handler                }                catch(Exception e){                    e.printStackTrace();                    throw new RuntimeException(e.getMessage());                }            }        };        return handler;    }    public static void encryptoColumns2Db(String tableName, String[] columns){        long count = Db.findFirst("select count(*) as count from " + tableName).getLong("count");        System.out.println("总记录数 ： " + count);        int pageSize = 100;        int pageNum = (int)((count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1));        ColumnHandler<String> handler = getHandler();        for(int i = 1; i <= pageNum; i++){            List<Record> records = Db.paginate(i, pageSize, "select *", "from " + tableName).getList();            int size = records.size();            System.out.println("------开始更新第 " + i + " 页数据");            for(int j = 0; j < size; j++){                System.out.println("------------开始更新第 " + ((i - 1) * pageSize + j + 1) + " 条数据");                updateRecord(tableName,records.get(j), columns, handler);            }            records.clear();            records = null;        }    }    public static void main(String[] args){        initDb();        String[] columnsMember = new String[] { "name", "bank_name", "bank_card", "bank_information", "bank_opening",                "bank_address", "mobile", "qq", "weixin" };        ColumnUtil.encryptoColumns2Db("member_org_encrypto", columnsMember);        String[] columnsSys = new String[] { "real_name", "telephone", "email" };        ColumnUtil.encryptoColumns2Db("sys_user_encrypto", columnsSys);        String[] columnsOrder = new String[] { "payer_name", "payer_tel", "recpt_name", "recpt_tel", "recpt_province",                "recpt_province_code", "recpt_city", "recpt_city_code", "recpt_area", "recpt_addr", "recpt_zipcode",                "pay_channel", "id_in_payplat", "protected_name", "protector_phone" };        ColumnUtil.encryptoColumns2Db("order_info_encrypto", columnsOrder);        // try{        // String string = DESCrypto.encrypt4Column("叶小庆");        // System.out.println("加密后 ： " + string);        // string = DESCrypto.decrypt4Column(string);        // System.out.println("解密后 ： " + string);        //        // string =        // DESCrypto.encrypt4Column("liuyusssssssssssssssssssssssssssjia");        // System.out.println("加密后 ： " + string);        // string = DESCrypto.decrypt4Column(string);        // System.out.println("解密后 ： " + string);        //        // }        // catch(Exception e){        // // TODO Auto-generated catch block        // e.printStackTrace();        // }    }}

jdbc.properties

driverClass = com.mysql.jdbc.DriverjdbcUrl = jdbc\:mysql\://****\:3306/*****?characterEncoding\=UTF8&zeroDateTimeBehavior\=convertToNullfilters = stat,walluser = ***password = ****db.type=mysqldb.initialSize=10db.minIdle=20db.maxActive=500db.maxWait=60000

public class DESCrypto{    private static String encoding = "ASCII";    public static byte[] encrypt(byte[] message, String key) throws Exception{        Cipher cipher = Cipher.getInstance("DES/CBC/PKCS5Padding");        DESKeySpec desKeySpec = new DESKeySpec(key.getBytes(encoding));        SecretKeyFactory keyFactory = SecretKeyFactory.getInstance("DES");        SecretKey secretKey = keyFactory.generateSecret(desKeySpec);        IvParameterSpec iv = new IvParameterSpec(key.getBytes(encoding));        cipher.init(Cipher.ENCRYPT_MODE, secretKey, iv);        return cipher.doFinal(message);    }    public static byte[] convertHexString(String ss){        byte digest[] = new byte[ss.length() / 2];        for(int i = 0; i < digest.length; i++){            String byteString = ss.substring(2 * i, 2 * i + 2);            int byteValue = Integer.parseInt(byteString, 16);            digest[i] = (byte)byteValue;        }        return digest;    }    public static String toHexString(byte b[]){        StringBuffer hexString = new StringBuffer();        for(int i = 0; i < b.length; i++){            String plainText = Integer.toHexString(0xff & b[i]);            if(plainText.length() < 2)                plainText = "0" + plainText;            hexString.append(plainText);        }        return hexString.toString();    }    // 解密数据，for third party decrypt，与encrypt配对    public static String decrypt(byte[] bytesrc, String key) throws Exception{        Cipher cipher = Cipher.getInstance("DES/CBC/PKCS5Padding");        DESKeySpec desKeySpec = new DESKeySpec(key.getBytes(encoding));        SecretKeyFactory keyFactory = SecretKeyFactory.getInstance("DES");        SecretKey secretKey = keyFactory.generateSecret(desKeySpec);        IvParameterSpec iv = new IvParameterSpec(key.getBytes(encoding));        cipher.init(Cipher.DECRYPT_MODE, secretKey, iv);        byte[] retByte = cipher.doFinal(bytesrc);        return new String(retByte, "utf-8");    }    public static String encrypt4Column(String src) throws Exception{        String to = toHexString(DESCrypto.encrypt(src.getBytes("utf-8"), ClientApiConstant.CONSTANT_DES_KEY));        return to;    }    public static String decrypt4Column(String src) throws Exception{        byte[] bytesrc = convertHexString(src);        return DESCrypto.decrypt(bytesrc, ClientApiConstant.CONSTANT_DES_KEY);    }}

特别注意几点：

1.数据备份，否则出现错误悔之晚矣

2.确保varchar长度足够

3.应该能找到更好的批量修改的方法

4.加密解密要对中文适应，注意字符集

0 0