/**
* 对列进行操作，然后更新到数据库
*/
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.加密解密要对中文适应，注意字符集
