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

/**
 * 对列进行操作，然后更新到数据库
 */
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.Driver
jdbcUrl = jdbc\:mysql\://****\:3306/*****?characterEncoding\=UTF8&zeroDateTimeBehavior\=convertToNull
filters = stat,wall
user = ***
password = ****


db.type=mysql

db.initialSize=10
db.minIdle=20
db.maxActive=500
db.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.加密解密要对中文适应，注意字符集