专栏首页RabbitMQ实战一个需求的三种实现(sql)

一个需求的三种实现(sql)

需求1:系统有一张订单表 futao_order,该订单表的数据根据第三方进销存系统的数据生成,现需要同步进销存的订单,如果订单已经同步过,则skip,否则insert新订单

  • 表结构
-- auto-generated definition
create table futao_order
(
  id             varchar(32)                         not null
  comment '订单主键',
  userId         varchar(32)                         not null
  comment '用户id',
  erpOrderId     varchar(32)                         not null
  comment '进销存订单id',
  remark         varchar(300)                        null
  comment '备注',
  createTime     timestamp default CURRENT_TIMESTAMP not null
  comment '创建时间',
  lastModifyTime timestamp default CURRENT_TIMESTAMP not null
  comment '最后修改时间',
  constraint futao_order_id_uindex
  unique (id)
)
  comment '订单表';

alter table futao_order
  add primary key (id);

思路1(通过java代码实现): 根据erpOrderId查询订单表,如果数据不存在则insert新订单,如果存在则继续判断下一条erpOrderId

  • 代码实现:
  • dao层-根据erpOrderid查询订单信息
/**
     * 根据erpOrderid查询订单信息
     *
     * @param erpOrderId 进销存订单编号
     * @return 订单实体
     */
    @Select("select * from futao_order where erpOrderId=#{erpOrderId}")
    Order queryIfExistByErpOrderId(@Param("erpOrderId") String erpOrderId);
  • service层-根据erpOrderId查询订单是否存在
/**
     * 根据erpOrderId查询订单是否存在
     *
     * @param erpOrderId
     * @return
     */
    @Override
    public Boolean queryIfExistByErpOrderId(String erpOrderId) {
        Order order = orderDao.queryIfExistByErpOrderId(erpOrderId);
        return order != null;
    }
  • dao层-新增订单
/**
     * 新增订单
     *
     * @param id             订单id
     * @param userId         用户id
     * @param erpOrderId     进销存订单id
     * @param createTime     创建时间
     * @param lastModifyTime 最后修改时间
     * @return 插入的条数
     */
    @Insert("insert " +
            "into futao_order(id,userId," +
            "erpOrderId,createTime,lastModifyTime) " +
            "values(#{id},#{userId},#{erpOrderId}," +
            "#{createTime},#{lastModifyTime})")
    int add(@Param("id") String id, @Param("userId") String userId,
            @Param("erpOrderId") String erpOrderId,
            @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);
  • service层-新增订单
/**
     * 新增订单
     *
     * @param erpOrderId 进销存订单id
     * @return
     */
    @Override
    public int addErpOrder(String erpOrderId) {
        Timestamp currentTimeStamp = currentTimeStamp();
        return orderDao.add(UUIDService.get(), userService.currentUser().getId(), erpOrderId, currentTimeStamp, currentTimeStamp);
    }
  • 同步进销存订单方法
@Override
    public String sync(int times) {
        long startTime1 = System.currentTimeMillis();
        for (int i = 0; i < times; i++) {
            String uuid = UUIDService.get();
            if (!queryIfExistByErpOrderId(uuid)) {
                addErpOrder(uuid);
            }
        }
        long time1 = System.currentTimeMillis() - startTime1;
        return "先查询再新增耗时:" + time1
}
  • ok,这是第一种实现方式,通过java代码来判断订单是否存在,再决定是否将数据插入数据库。

思路2(通过sql实现): 通过一条sql,如果我们的条件(不存在指定的erpOrderId)成立,则新增,否则啥也不做

  • 先上mysql写法
insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT '3', '1', '1', current_timestamp, current_timestamp
from dual
where not exists(select id from futao_order where id = '0018ec3a7e0d4dbeb5b99e436f94ad57');

或者(可省略from dual)

insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT 主键, 用户id, 进销订单id, current_timestamp, current_timestamp
where not exists(select * from futao_order where id = '1');
  • oracle写法,不能省略from dual
insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
SELECT '3', '1', '1', current_timestamp, current_timestamp
from dual
where not exists(select id from futao_order where id = '0018ec3a7e0d4dbeb5b99e436f94ad57');
  • mybatis
/**
     * @param id
     * @param userId
     * @param erpOrderId
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("insert " +
            "into futao_order(id,userId,erpOrderId," +
            "createTime,lastModifyTime)  " +
            "select #{id},#{userId},#{erpOrderId}," +
            "#{createTime},#{lastModifyTime} " +
            " where not exists (" +
            "select id from futao_order where erpOrderId=#{erpOrderId})")
    int addOrder(@Param("id") String id, @Param("userId") String userId,
                 @Param("erpOrderId") String erpOrderId,
                 @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);

还有思路3:根据需求可以知道,erpOrderId在订单表中是唯一的,所以可以在数据库中给erpOrderId字段添加唯一约束UNIQUE。随后java代码就可以将所有数据直接执行insert狂怼入DB,如果存在之前已经插入过的erpOrderId,数据库必定会报异常,插入失败,我们只需要catch住异常,不影响程序的执行,并继续往后执行即可,新erpOrderId数据因为没有发生异常,会正常插入数据库。

需求2:在需求1的基础上,如果数据库中已经存在指定的erpOrderId,则更新这条数据,否则进行新增(这类需求非常常见,存在即更新、不存在则插入)

思路1:类似需求1的思路1,先通过一条sql查询数据库中是否已经存在满足条件的数据,如果不存在再执行另外一条insert sql。思路2:一条sql搞定。通过sql判断有没有满足我们条件的数据,如果存在则执行update操作,否则执行insert操作,都在一条sql中。

  • oracle的实现
merge into futao_order T1
merge into futao_order T1
using(select erpOrderId as a from dual) T2
on (T1.erpOrderId=T2.a)
when matched then
  update set T1.remark='备注'
when not matched then
  insert (id, userId, erpOrderId, createTime, lastModifyTime)
  values('3', '1', '1', current_timestamp, current_timestamp)
  • mysql实现(需要设置erpOrderId为唯一索引约束)
-- 如果唯一索引命中,则执行insert操作,否则执行update操作,update哪些字段由update后面的语句决定
insert into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
values ('1', '11111', '11111', current_timestamp, current_timestamp)
on duplicate key update erpOrderId = '1111';

-- 如果唯一约束命中则删除之前的数据,在重新插入修改之后的数据
replace into futao_order (id, userId, erpOrderId, createTime, lastModifyTime)
values ('99', '11111', '99', current_timestamp, current_timestamp)

mysql没有oracle那么灵活,有一个比较坑的地方是,如果一张表中有很多唯一索引,必须所有的唯一索引约束都未命中,才会执行insert,否则命中一个唯一索引约束就会执行update

  • 结合项目,使用mysql+mybatis实现需求
/**
     * ByDuplicateKey
     *
     * @param id
     * @param userId
     * @param erpOrderId
     * @param remark
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("insert into futao_order (id, userId, erpOrderId,remark, createTime, lastModifyTime)" +
            "values (#{id}, #{userId}, #{erpOrderId}, #{remark},#{createTime}, #{lastModifyTime})" +
            "on duplicate key update erpOrderId = #{erpOrderId},remark=#{remark}")
    int addOrUpdateByDuplicateKey(@Param("id") String id, @Param("userId") String userId,
                                  @Param("erpOrderId") String erpOrderId, @Param("remark") String remark,
                                  @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);


    /**
     * ByReplace
     *
     * @param id
     * @param userId
     * @param erpOrderId
     * @param remark
     * @param createTime
     * @param lastModifyTime
     * @return
     */
    @Insert("replace into futao_order (id, userId, erpOrderId,remark, createTime, lastModifyTime)" +
            "values (#{id}, #{userId}, #{erpOrderId}, #{remark},#{createTime}, #{lastModifyTime})")
    int addOrUpdateByReplace(@Param("id") String id, @Param("userId") String userId,
                             @Param("erpOrderId") String erpOrderId, @Param("remark") String remark,
                             @Param("createTime") Timestamp createTime, @Param("lastModifyTime") Timestamp lastModifyTime);
  • 测试
  • byDuplicateKey测试 新增

修改

再来一条

  • byReplace测试 新增

修改

再来一条

需要注意,byReplace方法会删除之前的数据再新增,byDuplicateKey是在原来的基础上update,请关注测试结果的时间戳

本文分享自微信公众号 - 喜欢天文(AllUnderControl),作者:UnderControl

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-11-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SpringBoot如何做到自动帮我们创建RabbitMQ的Queue和Exchange

    解释:在连接工厂connectionFactory中注册了一个监听器,在监听器中执行了initialize()方法。

    喜欢天文的pony站长
  • Java8 Stream 基本类型特化流

    喜欢天文的pony站长
  • AMQP-RabbitMQ/3/发布订阅模式

    RabbitMQ中消息传递模型的核心思想是生产者永远不会将任何消息直接发送到队列。实际上,生产者通常甚至不知道消息是否会被传递到任何队列。 相反,生产者只能向E...

    喜欢天文的pony站长
  • CRM User Status profile中Business Transaction字段的用途

    有朋友问到User Status profile中Business Transaction字段的用途,如下图INPR, FINI所示。

    Jerry Wang
  • 今日零时正式启动 | 2020腾讯AI Lab犀牛鸟专项研究及访问学者计划

    ? 2020腾讯AI Lab犀牛鸟专项研究及访问学者计划,已于今日零时正式开放申请,欢迎各位老师通过PC端访问以下对应链接申请: 专项研究计划: https...

    腾讯高校合作
  • 替换一下同义词,AI就把句子意思弄反了|华人研究者揭示NLP模型脆弱性

    麻省理工和香港大学的研究生们开发了一个算法,让AI在文本分类和推理问题上的正确率从80%下降到10%。

    JAVA葵花宝典
  • 排障集锦:九九八十一难之第十难!mysq备份恢复,Could not read entry at offset *: Error in log format or read error.

    经查阅资料发现 --start-position=‘655’ 是个存储过程的中间位置,无法直接读取,所以会报错。

    不吃小白菜
  • 替换一下同义词,AI就把句子意思弄反了|华人研究者揭示NLP模型脆弱性

    麻省理工和香港大学的研究生们开发了一个算法,让AI在文本分类和推理问题上的正确率从80%下降到10%。

    量子位
  • 程序员这个职业会是另一个即将破灭的泡沫吗?

    一位朋友最近向我提了个问题,这个问题我曾经在不同的论坛上听到过,虽然问题的形式不太一样: “你认为 IT 和一些低级别的编程工作最终会消失吗?这个工作看起来有点...

    企鹅号小编
  • MYSQL回顾(表关系相关)

    比如有两个表,分别是书籍表和出版社表。书籍和出版社是典型的多对一关系,即一本书只能由一个出版社出版,一个出版社可以出版多本书。则书籍表应该有一个外键press_...

    VV木公子

扫码关注云+社区

领取腾讯云代金券