NamedParameterJdbcTemplate

最新推荐文章于 2024-05-30 14:32:14 发布

相思子~

最新推荐文章于 2024-05-30 14:32:14 发布

阅读量618

点赞数

分类专栏： Demo 文章标签： java

本文链接：https://blog.csdn.net/iloki/article/details/118960024

版权

Demo 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

本文介绍了如何使用Spring框架的namedParameterJdbcTemplate进行数据库操作，包括插入同步时间、批量插入数据以及查询、更新和删除功能，重点展示了线程安全的时间格式化和参数化查询的使用。

摘要由CSDN通过智能技术生成

    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);
    }