mybatis+mysql (有则更新，无则插入)

男娘i 2022-04-23 05:10 541阅读 0赞

1.  创建表

在这里插入DROP TABLE
    IF EXISTS `virtualMachines`;
    CREATE TABLE `virtualMachines` (
    
       name VARCHAR (100) NOT NULL COMMENT '虚拟机名称',
    
       status VARCHAR (100) DEFAULT NULL COMMENT '虚拟机状态',
    
       state VARCHAR (100) DEFAULT NULL COMMENT '虚拟机开机状态',
    
       hostCPU BIGINT (20) DEFAULT NULL COMMENT 'CPU',
    
       hostMemory BIGINT (20) DEFAULT NULL COMMENT '内存',
    
       provisionedSpace BIGINT (20) DEFAULT NULL COMMENT '提供的空间',
    
       usedSpace BIGINT (20) DEFAULT NULL COMMENT '已用空间',
    
       ip BIGINT (20) DEFAULT NULL COMMENT 'ip地址',
    
       isFtpInstalled tinyint(4) DEFAULT '0' COMMENT '是否安装FTP  0：未安装  1：已安装',
    
    	PRIMARY KEY (name)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8  COMMENT='虚拟机表';代码片

1.  mapper文件 （单条记录的update or insert）

<insert id="save"
    		parameterType="com.bootdo.vcenter.dto.VirtualMachineDTO">
    		insert into virtualMachines
    		(
    		`name`,`status`,`state`,`hostCPU`,`hostMemory`,`provisionedSpace`,`usedSpace`,`ip`,`isFtpInstalled`
    		)
    		values
    		(
    		#{name},
    		#{status},
    		#{state},
    		#{hostCPU},
    		#{hostMemory},
    		#{provisionedSpace},
    		#{usedSpace},
    		#{ip},
    		#{isFtpInstalled}
    		)
    		ON DUPLICATE KEY UPDATE
    		    status =VALUES(status),
    		    state = VALUES(state),
    		    hostCPU = VALUES(hostCPU),
    		    hostMemory = VALUES(hostMemory),
    		    provisionedSpace = VALUES(provisionedSpace),
    		    usedSpace =	VALUES(usedSpace),
    		    ip = VALUES(ip),
    		    isFtpInstalled = VALUES(isFtpInstalled)
    	</insert>

1.  mapper文件 （多条记录的update or insert）

<insert id="batchUpdate" parameterType="java.util.List">
    		insert into virtualMachines
    		(
    		`name`,`status`,`state`,`hostCPU`,`hostMemory`,`provisionedSpace`,`usedSpace`,`ip`,`isFtpInstalled`
    		)
    		values
    		<foreach collection="list" item="item" index="index"
    			separator=",">
    			(
    			#{item.name,jdbcType=VARCHAR},
    			#{item.status,jdbcType=VARCHAR},
    			#{item.state,jdbcType=VARCHAR},
    			#{item.hostCPU,jdbcType=INTEGER},
    			#{item.hostMemory,jdbcType=INTEGER},
    			#{item.provisionedSpace,jdbcType=INTEGER},
    			#{item.usedSpace,jdbcType=INTEGER},
    			#{item.ip,jdbcType=VARCHAR},
    			#{item.isFtpInstalled,jdbcType=TINYINT}
    			)
    		</foreach>
    		ON DUPLICATE KEY UPDATE
    		    status =VALUES(status),
    		    state = VALUES(state),
    		    hostCPU = VALUES(hostCPU),
    		    hostMemory = VALUES(hostMemory),
    		    provisionedSpace = VALUES(provisionedSpace),
    		    usedSpace =	VALUES(usedSpace),
    		    ip = VALUES(ip),
    		    isFtpInstalled = VALUES(isFtpInstalled)
    	</insert>