ibatis 实现saveorupdate 结果发现 mysql saveorupdate 支持

写ibatis要实现saveorupdate两种方式

一、都是在业务上进行控制,

二、mysql 的replace into 语句支持类似的功.

原理是根据唯一索引去判断新插入数据合法性,合法(不存在)则插入,存在则删除.

这在自增主键情况下会使autoincrement +2, 并且原来的数据全部清空存在数据不安全的隐患.

三、mysql 的ON DUPLICATE KEY 

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

是根据唯一索引去判断,存在冲突则执行update

有这两个mysql的语法,实现saveorupdate可以更方便安全一些

配置如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="vcenterviewmodel">
	<typeAlias alias="VCenterViewModel"
		type="com.upyoo.vmware.viewandmodel.VCenterViewModel" />



	<statement id="insertvcenterviewmodel">
		insert into
		vctener(id, description_name, cluster_count,host_count,
		vm_count,
		cpu_core_count, vcpu_core_count, cpu_used_scale,
		cpu_portion_scale,
		memory_cap, vmemory_cap,
		memory_used_cap, memory_used_scale,
		memory_portion_scale, store_cap, vstroe_cap,
		store_sas_cap,
		store_sas_used_cap, store_sas_used_scale,
		store_sata_cap,
		store_sata_used_cap,
		store_sata_used_scale, vstore_cap,
		store_portion_scale


		) values(#id#,
		#description_name#,#cluster_count#,#host_count#,
		#vm_count#,
		#cpu_core_count#, #vcpu_core_count#, #cpu_used_scale#,
		#cpu_portion_scale#, #memory_cap#, #vmemory_cap#,
		#memory_used_cap#,
		#memory_used_scale#,
		#memory_portion_scale#, #store_cap#, #vstroe_cap#,
		#store_sas_cap#, #store_sas_used_cap#, #store_sas_used_scale#,
		#store_sata_cap#, #store_sata_used_cap#,
		#store_sata_used_scale#,
		#vstore_cap#, #store_portion_scale#

		)
	</statement>

	<statement id="insertvcenterviewmodel1">
		insert into
		vctener(id, description_name, cluster_count

		) values(#id#,
		#description_name#,#cluster_count#
		)
		ON DUPLICATE KEY UPDATE
		cluster_count=#cluster_count#,description_name=#description_name#
		
	</statement>

	<statement id="checkFromVCenterViewModel" resultClass="VCenterViewModel">
		select * from vctener where 1=1
	</statement>


</sqlMap>

代码如下：

package com.upyoo.test;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import com.upyoo.util.Generate;
import com.upyoo.vmware.viewandmodel.ClusterViewModel;
import com.upyoo.vmware.viewandmodel.VCenterViewModel;
import com.vmware.vim25.GeneralEvent;
public class IBatisDemo {
	public static void main(String[] args) throws IOException{
		
		String config = "sqlMapConfig.xml";
		
		Reader reader = Resources.getResourceAsReader(config);
		SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
//		ClusterViewModel cViewModel = new ClusterViewModel();
//		cViewModel.setId(Generate.getRpid());
//		sqlMap.insert("insertClusterViewModel", cViewModel);
		
		VCenterViewModel center = new VCenterViewModel();
		center.setId("fortest1");
		center.setCluster_count(18);
		center.setDescription_name("tes1t");
		try {
			sqlMap.insert("insertvcenterviewmodel1", center);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
		}
		List<VCenterViewModel> list;
		try {
			list = sqlMap.queryForList("checkFromVCenterViewModel");
			for (VCenterViewModel vCenterViewModel : list) {
				System.out.println(vCenterViewModel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
}

运行如下：

VCenterViewModel [id=fortest, description_name=null, cluster_count=null, host_count=null, vm_count=null, cpu_core_count=null, vcpu_core_count=null, cpu_usage=null, cpu_portion_scale=null, memory_cap=null, vmemory_cap=null, memory_used_cap=null, memory_used_scale=null, memory_portion_scale=null, store_cap=null, vstroe_cap=null, store_sas_cap=null, store_sas_used_cap=null, store_sas_used_scale=null, store_sata_cap=null, store_sata_used_cap=null, store_sata_used_scale=null, vstore_cap=null, store_portion_scale=null]
VCenterViewModel [id=fortest1, description_name=tes1t, cluster_count=18, host_count=null, vm_count=null, cpu_core_count=null, vcpu_core_count=null, cpu_usage=null, cpu_portion_scale=null, memory_cap=null, vmemory_cap=null, memory_used_cap=null, memory_used_scale=null, memory_portion_scale=null, store_cap=null, vstroe_cap=null, store_sas_cap=null, store_sas_used_cap=null, store_sas_used_scale=null, store_sata_cap=null, store_sata_used_cap=null, store_sata_used_scale=null, vstore_cap=null, store_portion_scale=null]
VCenterViewModel [id=SHANGHAICLOUD, description_name=VMware vCenter Server 5.1.0 build-947673, cluster_count=null, host_count=90, vm_count=7561, cpu_core_count=2624, vcpu_core_count=12042, cpu_usage=null, cpu_portion_scale=null, memory_cap=null, vmemory_cap=null, memory_used_cap=null, memory_used_scale=null, memory_portion_scale=null, store_cap=null, vstroe_cap=null, store_sas_cap=null, store_sas_used_cap=null, store_sas_used_scale=null, store_sata_cap=null, store_sata_used_cap=null, store_sata_used_scale=null, vstore_cap=null, store_portion_scale=null]

主要观察

center.setId("fortest1");
		center.setCluster_count(18);
		center.setDescription_name("tes1t");
		try {
			sqlMap.insert("insertvcenterviewmodel1", center);

在配置中对应的是：

insertvcenterviewmodel1

<statement id="insertvcenterviewmodel1">
		insert into
		vctener(id, description_name, cluster_count

		) values(#id#,
		#description_name#,#cluster_count#
		)
		ON DUPLICATE KEY UPDATE
		cluster_count=#cluster_count#,description_name=#description_name#
		
	</statement>

参考：

mysql 忽略主键冲突、避免重复插入的几种方式 - leejun_2005的个人页面 - 开源中国社区http://my.oschina.net/leejun2005/blog/150510