上一篇文章,实现了基本的数据库表字段加密,但是存在巨大的隐患,一旦某个记录更新失败(比如,字段长度不够,加密后长度超了),那么已经更新的就被更新了,后面的却不能更新,这就造成数据不一致,破坏了数据。所以在加密的时候必须实现事务控制。本文利用Db.tx实现了事务控制。另外优化了ColumnHandler,实现输入参数和输出参数可以不一样。直接贴代码:
package cn.esstx.cq.server.util;
import java.sql.SQLException;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.druid.DruidPlugin;
/**
* 对列进行操作,然后更新到数据库
*/
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 boolean 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);
return Db.update(tableName, record);
}
/**
* 一个列本来是什么类型,输出也是什么类型(T)
*/
public interface ColumnHandler<T> {
T handleColumn(T src);
}
/**
* 从S类型转换为T,可以一样
*/
public interface ConvertHandler<S, T> {
T convert(S 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;
}
/**
* @Title: getHandler
* @Description:获取一个Handler
* @return ColumnHandler<String>
*/
private static ConvertHandler<String, String> getColumnHandler(){
ConvertHandler<String, String> handler = new ConvertHandler<String, String>(){
@Override
public String convert(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){
Db.tx(new IAtom(){// 加入事务控制
@Override
public boolean run() throws SQLException{
try{
return doUpdate(tableName, columns);
}
catch(Exception e){
e.printStackTrace();
return false;// 或者直接抛出去也可以
}
}
});
}
/**
* @Title: doUpdate
* @Description:更新数据库,如果某一个记录更新失败,就返回false
* @param tableName
* @param columns
*/
private static boolean doUpdate(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();
boolean success = true;
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) + " 条数据");
success = updateRecord(tableName, records.get(j), columns, handler);
if(!success){
return false;
};
}
records.clear();
records = null;
}
return success;
}
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",
"id_in_payplat", "protector_name", "protector_phone" };
ColumnUtil.encryptoColumns2Db("order_info_encrypto", columnsOrder);
// try{
// String string = DESCrypto.encrypt4Column("叶小庆");
// System.out.println("加密后 : " + string);
// string =
// DESCrypto.decrypt4Column("440ce0a2207e4dd0d129c31aa40394c3527a8ddd865551739f834f5c9ea3f17c");
// 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();
// }
}
}