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

喜欢天文的pony站长

发布于 2020-06-29 12:03:29

5370

发布于 2020-06-29 12:03:29

需求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);