需求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,请关注测试结果的时间戳