作者官方网站:http://www.wxl568.cn
--工作整理 Create table
create table PMP_AGNT_INCLUDE_CARDNO
(
include_cardno_id NUMBER,
table_name VARCHAR2(100),
orig_id_card VARCHAR2(300),
card_no_enc VARCHAR2(300),
update_by VARCHAR2(50),
update_time DATE,
remark VARCHAR2(300),
switch VARCHAR2(10)
);
-- Add comments to the table
comment on table PMP_AGNT_INCLUDE_CARDNO
is '区域代理包含有身份证的表';
-- Add comments to the columns
comment on column PMP_AGNT_INCLUDE_CARDNO.include_cardno_id
is '主键id';
comment on column PMP_AGNT_INCLUDE_CARDNO.table_name
is '含有身份证的表名';
comment on column PMP_AGNT_INCLUDE_CARDNO.orig_id_card
is '表本来的身份证字段';
comment on column PMP_AGNT_INCLUDE_CARDNO.card_no_enc
is '表的新的身份证加密字段';
comment on column PMP_AGNT_INCLUDE_CARDNO.update_by
is '更新人';
comment on column PMP_AGNT_INCLUDE_CARDNO.update_time
is '更新时间';
comment on column PMP_AGNT_INCLUDE_CARDNO.remark
is '备注';
comment on column PMP_AGNT_INCLUDE_CARDNO.switch
is '开关(0-关-这张表进行不加密转换;1-开-这张表进行加密转换)';
public void encreptToDecrept() {
log.info("************************开始执行加密字段更新********************************");
// 表名,旧的身份证字段,新的加密的身份证字段
List<Map<String, String>> listTable = examineCfgDao.getObjectList("getCardNoTable");// 包含身份证号码的表的数据
for (Map<String, String> map : listTable) { // 这个map里面有表名,旧的身份证字段,新的加密的身份证字段
int cnt = 0; // 只是用于测试,计算第几次连接数据库(仅在一张表里面)
boolean flag = true;
while (flag) {
cnt++;
// 根据表名查出相对应的这张表里面旧的身份证字段,map里面只有一个key(旧的身份证字段),value(旧的身份证字段所对应的值)
List<Map<String, String>> listData = examineCfgDao.getObjectList("getDetailedTable",map);
// 所处理过的数据存放在这里
List<DealEncreptDO> listPut = new ArrayList<DealEncreptDO>();
for (Map<String, String> mapData : listData) { // 对数据进行处理(加密)
try {
this.updateEncreptIdCard(map,mapData, listPut);
} catch (RuntimeException e) {
log.error("定时加密加密字段时第{}发生异常",e);
continue;
}
}
if (listPut.size() > 0) {
try {
log.info("定时加密开始******更新第{}次连接数据库",cnt);
examineCfgDao.update("updateToEncrept", listPut);
log.info("定时加密结束******更新第{}次连接数据库",cnt);
} catch (Exception e) {
log.error("定时加密库更新时第{}发生异常",e);
continue;
}
} else {
flag = false;
}
}
}
log.info("************************结束执行加密字段更新********************************");
}
/**
*
* @param map 里面包含表名,旧的身份证字段
* @param map03 旧的身份证字段所对应的值
* @param listPut 封装数据
*/
private void updateEncreptIdCard(Map<String, String> map, Map<String, String> map03, List<DealEncreptDO> listPut) {
DealEncreptDO dealEncreptDO = new DealEncreptDO();
String tableName = map.get("TABLE_NAME"); // 表名
String origIdCard = map.get("ORIG_ID_CARD");// 旧的身份证字段
String origIdCardValue = map03.get("ORIG_ID_CARD");// 旧的身份证字段所对应的值
String cardNoEnc = map.get("CARD_NO_ENC"); // 新的加密的身份证字段
String cardNoEncValue = this.encrypt(origIdCardValue);// 新的加密的身份证字段所对应的值
dealEncreptDO.setTableName(tableName);
dealEncreptDO.setOrigIdCard(origIdCard);
dealEncreptDO.setOrigIdCardValue("'" + origIdCardValue + "'");
dealEncreptDO.setCardNoEnc(cardNoEnc);
dealEncreptDO.setCardNoEncValue("'" + cardNoEncValue + "'");
listPut.add(dealEncreptDO);
}
/**
* 加密字符串
* @param tm 字符串业务类型 card|bank|addr 分别为身份证、银行卡、地址
* @param plaintext
* @return
*/
public String encrypt(String plaintext){
try {
String[] arr = this.getEncryptSetting("card");
return arr[3]+getUDE().dataEncrypt(Integer.parseInt(arr[2]), Integer.parseInt(arr[1].substring(2),16), plaintext);
} catch (Exception e) {
String msg = "调用加密机加密异常:plaintext="+plaintext;
throw new RuntimeException(msg,e);
}
}
/**
* 获取加密机实例
* @return
* @throws Exception
*/
private UserDataEncrypt getUDE() throws Exception{
if (ude == null) {
String cfgPath=System.getenv().get("CONF_PATH")+"/cfg.ini";
ude = UserDataEncrypt.getInstance(cfgPath);
}
return ude;
}
private String[] getEncryptSetting(String type){
Map<String,String[]> map = new HashMap();
String setting = getkeyWithCacheOrDic("DataEncryptKeySetting");
// String setting ="card,0x14,41,DE|bank,0x22,1,DE|addr,0x13,61,DE##";
if(!StringUtil.isEmpty(setting)){
String[] arr = setting.split("\\|");
String[] items;
String s;
for (int i = 0; i < arr.length; i++) {
s = arr[i];
items = s.split(",");
if(items.length!=4||!items[1].startsWith("0x")){
throw new RuntimeException("加密机密钥参数格式错误:"+setting);
}
map.put(items[0], items);
}
String[] result = map.get(type);
if(result==null){
throw new RuntimeException("密钥类型未在配置中找到:"+type+" ---- "+setting);
}
return result;
}else{
throw new RuntimeException("加密机密钥参数未配置");
}
}
/**
* 首先从缓存内获取key的value,如果没有,则再从数据字典中获取
* @param key
* @return
*/
public String getkeyWithCacheOrDic(String key){
MemoryCache memoryCache=new MemoryCache();
String value = (String)memoryCache.getByKey(key);
if(StringUtils.isEmpty(value)){
//如果值是空,则从数据字典获取
GlobalType globalType = globalTypeDao.getByDictNodeKeyNoCache(key);
value = globalType.getNodeCode();
memoryCache.add(key,value);
return value;
}else{
return value;
}
}
<!-- 表名,旧的身份证字段,新的加密身份证字段 -->
<select id="getCardNoTable" resultType="java.util.Map">
SELECT t.table_name,
t.orig_id_card,
t.card_no_enc
FROM PMP_AGNT_INCLUDE_CARDNO t
WHERE t.switch = '1'
</select>
<!-- 查出该table的所有身份证号码,不为DE开头的(所有的身份证明文),将所有潜在的有小写的身份证号变成大写,当密文不为空的时候,说明已经处理了,就不用再处理了 -->
<select id="getDetailedTable" parameterType="java.util.Map" resultType="java.util.Map" statementType="STATEMENT">
SELECT ${ORIG_ID_CARD} AS ORIG_ID_CARD
FROM ${TABLE_NAME}
WHERE SUBSTR(${ORIG_ID_CARD},0,2) != 'DE'
AND ${ORIG_ID_CARD} IS NOT NULL
AND ${CARD_NO_ENC} IS NULL
AND ROWNUM <![CDATA[ <= ]]> 1000
</select>
<update id="updateToEncrept" parameterType="java.util.List" statementType="STATEMENT">
begin
<foreach collection="list" item="item" separator=";" open="" close="">
UPDATE ${item.tableName}
<set>
${item.cardNoEnc} = ${item.cardNoEncValue}
</set>
<where>
${item.origIdCard} = ${item.origIdCardValue}
</where>
</foreach>
;commit;end;
</update>