当前位置 博文首页 > iloki的博客:NamedParameterJdbcTemplate
private static final ThreadLocal<SimpleDateFormat> formatThreadLocal= ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
@Resource
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
/**
* 插入同步时间
* @param subSystemVo
*/
public boolean insertSyncTime(SubSystemVo subSystemVo) {
SubSyncDataBean bean=new SubSyncDataBean();
bean.setSyncId(UUID.randomUUID().toString());
bean.setSubsystemId(subSystemVo.getKey());
bean.setSubsystemCode(subSystemVo.getCode());
bean.setAddress(subSystemVo.getAddress());
bean.setRtaSyncTime(new Date());
bean.setVisitSyncTime(new Date());
namedParameterJdbcTemplate.update("INSERT INTO BSP_SUB_SYNC_DATA(SYNC_ID, SUBSYSTEM_ID, SUBSYSTEM_CODE, ADDRESS, RTA_SYNC_TIME, VISIT_SYNC_TIME) VALUES (:syncId,:subsystemId,:subsystemCode,:address,:rtaSyncTime,:visitSyncTime)",
new BeanPropertySqlParameterSource(bean));
return true;
}
//批量插入数据
public boolean batchInsertData(List<ParamRTAInfo> paramRTAInfoList, String key) {
List<ParamRTAInfoBean> list = new ArrayList<>();
for(ParamRTAInfo paramRTAInfo : paramRTAInfoList){
ParamRTAInfoBean bean = new ParamRTAInfoBean();
bean.setId(UUID.randomUUID().toString());
bean.setTargetType("SUBSYSTEMALIVE");
bean.setTargetKey(key);
bean.setTargetTitle("子系统存活状态");
bean.setItem("ALIVE");
bean.setItemCount(1);
try {
bean.setAtrdate(formatThreadLocal.get().parse(paramRTAInfo.getAtrdate()));
} catch (ParseException e) {
logger.error(paramRTAInfo.getAtrdate() + "日期转换错误",e);
continue;
}
list.add(bean);
}
//批量转数组
SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list);
String sql = "INSERT INTO NR_RTA_PARAM(RTA_ID,RTA_TARGETTYPE,RTA_TARGETKEY,RTA_TARGETTITLE,RTA_ITEM,RTA_ITEMCOUNT,RTA_DATE) VALUES (:id,:targetType,:targetKey,:targetTitle,:item,:itemCount,:atrdate)";
namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
return true;
}
/**
* 查询
* @param key
* @return
*/
public Map<String, Object> getInfoBySubSystemAddress(String key) {
logger.info("根据子服务key获取之前的更新记录:"+key);
Map<String, Object> paramSource = new HashMap<>(16);
StringBuffer sql = new StringBuffer();
sql.append(" SELECT SYNC_ID, SUBSYSTEM_ID, SUBSYSTEM_CODE, ADDRESS, RTA_SYNC_TIME, VISIT_SYNC_TIME");
sql.append(" FROM ").append(SubsystemDataSyncConstants.BSP_SUB_SYNC_DATA);
sql.append(" WHERE 1=1 ");
String address = "address";
sql.append(" AND " + SubsystemDataSyncConstants.ADDRESS + " = :")
.append(address);
paramSource.put(address, key);
List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql.toString(), paramSource);
if(CollectionUtils.isEmpty(list)) return null;
return list.get(0);
}
/**
*
* 更新
* @param subSystemVo
*/
public boolean updateSyncTime(SubSystemVo subSystemVo) {
SubSyncDataBean bean=new SubSyncDataBean();
bean.setSubsystemId(subSystemVo.getKey());
bean.setSubsystemCode(subSystemVo.getCode());
bean.setAddress(subSystemVo.getAddress());
bean.setRtaSyncTime(new Date());
bean.setVisitSyncTime(new Date());
namedParameterJdbcTemplate.update("UPDATE BSP_SUB_SYNC_DATA SET RTA_SYNC_TIME =:rtaSyncTime , VISIT_SYNC_TIME =:visitSyncTime WHERE SUBSYSTEM_ID =:subsystemId",
new BeanPropertySqlParameterSource(bean));
return true;
}
/**
*
* 删除
* @param id
*/
public void deleteNrSyncStatusExtend(String id) {
String sql = String.format("delete from NR_SYNC_STATUS_EXTEND where id= ?");
namedParameterJdbcTemplate.update(sql, id);
}
cs