SQLServer 2008 MyBatis+联合主键+批量插入+忽略重复数据+联合外键

需求：批量添加设备，每一个设备，都从属于各自网络节点下的某个控制器。设备有多条定时信息。

分析：1.批量插入设备数据，在设备数量较多时可以提升效率。

           2.该设备只能通过网络节点id，控制器id和自身id确认唯一性。因此需要使用联合主键。

           3.可能有重复数据存在，如节点1_控制器1_设备1多次尝试添加自己。需要忽略重复数据。

           4.定时信息表需要外键映射设备表，设备表示联合主键，因此定时信息表需要添加联合外键。本例中，定时表外键关联只关心删除功能，即，删除某个设备后，会自动将外键关联的定时信息一起删除。

该问题可以在建表时解决。

           1.设置联合主键确定一个设备。

           2.MyBatis批量插入的xml写法，必须所有元素均不得为空

           3.使用IGNORE_DUP_KEY=ON忽略重复数据。避免批量插入发生重复时抛出异常，数据回滚。

           4.给定时信息表添加外键，关联删除

逐个解决：

1.建表

设备表：建表和设置联合主键，联合主键的组成元素都要设置不得为null。如下建表，设置了“IGNORE_DUP_KEY = ON”，即可忽略重复数据

if exists (select * from sysobjects where id = OBJECT_ID('[SysDevice]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [SysDevice]


CREATE TABLE [SysDevice] (
[Id] [int]  IDENTITY (1, 1)  NOT NULL,
[Station_Address] [varchar]  (100) NOT NULL,
[Controller_Addr] [varchar]  (100) NOT NULL,
[Device_Addr] [varchar]  (100)NOT NULL,
)


alter table [SysDevice] add constraint PK_Id primary key(Station_Addressasc,Controller_Addr asc,Device_Addr asc)WITH (IGNORE_DUP_KEY = ON);
           

定时表：建表和添加外键关联设备表

if exists (select * from sysobjects where id = OBJECT_ID('[Timing]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) 
DROP TABLE [Timing]

CREATE TABLE [Timing] (
[Id] [int]  IDENTITY (1, 1)  NOT NULL,
[Start_Time] [datetime]  NULL,
[End_Time] [datetime]  NULL,
[Create_Time] [datetime]  NULL,
[Station_Address] [varchar]  (100) NOT NULL,
[Controller_Addr] [varchar]  (100) NOT NULL,
[Device_Addr] [varchar]  (100)NOT NULL,
CONSTRAINT Timing_fk FOREIGN KEY ([Station_Address], [Controller_Addr],[Device_Addr]) REFERENCES SysDevice ([Station_Address], [Controller_Addr],[Device_Addr]) on delete cascade
)

ALTER TABLE [Timing] WITH NOCHECK ADD  CONSTRAINT [PK_Timing] PRIMARY KEY  NONCLUSTERED ( [Id] )
           

MyBatis批量插入写法。

mapper.java

int insertBatch(@Param("list") List<Device> list);
           

mapper.xml

<insert id="insertBatch" parameterType="java.util.List">
    insert into SysDevice
    (Station_Address,Controller_Addr,Device_Addr,Device_Type)
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (#{item.stationAddress},#{item.controllerAddr},#{item.deviceAddr},#{item.deviceType})
    </foreach>
  </insert>
           

除了id自增长,其余属性都不得为空