欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mybatis使用annotation在mysql,oracle上进行批量处理

程序员文章站 2022-06-05 22:38:54
...
mybatis 使用annotation进行批量插入和删除,mysql 和oracle不同,因为oracle不支持(xxx,xxx,xxx),(xxx,xxx,xxx)的写法。不多说了看代码。

/**
 * 文件名:User.java
 *
 * 版本信息:
 * 日期:2012-8-28
 * Copyright 足下 Corporation 2012 
 * 版权所有
 *
 */
package org.richiedryday.mybatis.batch.domain;

import java.io.Serializable;
import java.sql.Timestamp;


/**
 * 
 * 项目名称:mybatis-batch
 * 类名称:User
 * 类描述:用户领域模型
 * 创建人:richie144
 * 创建时间:2012-8-28 下午5:13:27
 * 修改人:richie144
 * 修改时间:2012-8-28 下午5:13:27
 * 修改备注:
 * @version 
 * 
 */
public class User implements Serializable {
	
	private static final long serialVersionUID = 1L;
	
	private Integer id;
	
	private String username;
	
	private String password;
	
	private int age;
	
	private Timestamp birthdate;
	
	private char gender;
	
	private String address;
	
	public User() {
		
	}

	public User(Integer id, String username, String password, int age,
			Timestamp birthdate, char gender, String address) {
		this.id = id;
		this.username = username;
		this.password = password;
		this.age = age;
		this.birthdate = birthdate;
		this.gender = gender;
		this.address = address;
	}
//下面省略getter()和setter()

下面是映射类
/**
 * 文件名:UserMapper.java
 *
 * 版本信息:
 * 日期:2012-8-28
 * Copyright 足下 Corporation 2012 
 * 版权所有
 *
 */
package org.richiedryday.mybatis.batch.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.richiedryday.mybatis.batch.domain.User;
import org.richiedryday.mybatis.batch.mapper.util.MapperProvider;

/**
 * 
 * 项目名称:mybatis-batch
 * 类名称:UserMapper
 * 类描述:用户对应mybatis映射的结果接口
 * 创建人:richie144
 * 创建时间:2012-8-28 下午5:42:56
 * 修改人:richie144
 * 修改时间:2012-8-28 下午5:42:56
 * 修改备注:
 * @version 
 * 
 */
public interface UserMapper {
	
	public static final String INSERT = "INSERT INTO richie144_user VALUES(NULL,#{username},#{password},#{age},#{birthdate},#{gender},#{address})";
	public static final String DELETE = " DELETE FROM richie144_user ";
	public static final String UPDATE = "UPDATE richie144_user SET mb_username = #{username},mb_password = #{password},mb_age = #{age},mb_birthdate = #{birthdate},mb_gender = #{gender},mb_address = #{address}";
	public static final String SELECTALL = " SELECT * FROM richie144_user ";
	public static final String WHERE_ID = " WHERE mb_id = #{id} ";
	@Insert(INSERT)
	@Options(useGeneratedKeys=true,keyColumn="mb_id",keyProperty="id")
	void insert(User user);
	
	@Delete(DELETE + WHERE_ID)
	void delete(int id);
	
	@Update(UPDATE)
	void update(User user);
	
	@Select(SELECTALL + WHERE_ID)
	@Results(value={
			@Result(column="mb_id",property="id"),
			@Result(column="mb_username",property="username"),
			@Result(column="mb_password",property="password"),
			@Result(column="mb_age",property="age"),
			@Result(column="mb_birthdate",property="birthdate"),
			@Result(column="mb_gender",property="gender"),
			@Result(column="mb_address",property="address")
	})
	User getSingle(Integer id);
	
	//上面是mysql 的,下面的是oracle的
	//@InsertProvider(type=MapperProvider.class,method="insertAll")
	@InsertProvider(type=MapperProvider.class,method="insertAll4Orcl")
	void insertAll(List<User> users);
	
	@DeleteProvider(type=MapperProvider.class,method="deleteAll")
	void deleteAll(List<User> users);
	
	@Select(SELECTALL)
	@Results(value={
			@Result(column="mb_id",property="id"),
			@Result(column="mb_username",property="username"),
			@Result(column="mb_password",property="password"),
			@Result(column="mb_age",property="age"),
			@Result(column="mb_birthdate",property="birthdate"),
			@Result(column="mb_gender",property="gender"),
			@Result(column="mb_address",property="address")
	})
	List<User> getAll();
}

下面是MapperProvider类

/**
 * 文件名:InsertProvider.java
 *
 * 版本信息:
 * 日期:2012-8-29
 * Copyright 足下 Corporation 2012 
 * 版权所有
 *
 */
package org.richiedryday.mybatis.batch.mapper.util;

import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

import org.richiedryday.mybatis.batch.domain.User;

/**
 * 
 * 项目名称:mybatis-batch
 * 类名称:InsertProvider
 * 类描述:批量插入辅助类
 * 创建人:richie144
 * 创建时间:2012-8-29 下午1:32:18
 * 修改人:richie144
 * 修改时间:2012-8-29 下午1:32:18
 * 修改备注:
 * @version 
 * 
 */
public class MapperProvider {
	
	//批量插入
	public String insertAll(Map<String,List<User>> map) {	
		List<User> users = map.get("list");
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO richie144_user VALUES");
		MessageFormat messageFormat = new MessageFormat("(null,#'{'list[{0}].username},#'{'list[{0}].password},#'{'list[{0}].age},#'{'list[{0}].birthdate},#'{'list[{0}].gender},#'{'list[{0}].address})");
		for(int i = 0 ;i<users.size();i++) {
			sb.append(messageFormat.format(new Object[]{i}));
			if (i < users.size() - 1) {  
			    sb.append(",");   
            }
		}
		System.out.println(sb.toString());
		return sb.toString();
	}
	//批量删除
	public String deleteAll(Map<String,List<User>> map) {
		List<User> users =map.get("list");
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM richie144_user WHERE mb_id in (");
		MessageFormat messageFormat = new MessageFormat("#'{'list[{0}].id}");
		for(int i = 0 ;i<users.size();i++) {
			sb.append(messageFormat.format(new Integer[]{i}));
			if (i < users.size() - 1) {  
			    sb.append(",");   
            }
		}
		sb.append(")");
		System.out.println(sb.toString());
		return sb.toString();
	}
	//批量更新就简单了一个普通的更新方法就可以搞定。
	
	//下面是oracle 批量插入的insertProvider,因为oracle
	public String insertAll4Orcl(Map<String,List<User>> map){
		List<User> users =map.get("list");
		StringBuilder sb = new StringBuilder();
		MessageFormat messageFormat = new MessageFormat("#'{'list[{0}].username},#'{'list[{0}].password},#'{'list[{0}].age},#'{'list[{0}].birthdate},#'{'list[{0}].gender},#'{'list[{0}].address}");
		sb.append(" INSERT INTO richie144_user(mb_username,mb_password,mb_age,mb_birthdate,mb_gender,mb_address) ");
		for(int i = 0 ;i<users.size();i++) {
			//注意空格
			sb.append("SELECT ");
			sb.append(messageFormat.format(new Object[]{i}));
			//注意空格
			sb.append(" FROM DUAL ");
			if(i<users.size()-1) {
				sb.append(" UNION ALL ");
			}
		}
		System.out.println(sb.toString());
		return sb.toString();
	}
}

配置文件放在类路径下(mybatis-config.xml)
<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration  
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<!-- <property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis_batch"/>
				<property name="username" value="root" />
				<property name="password" value="admin" /> -->
				
				<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
				<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
				<property name="username" value="scott" />
				<property name="password" value="tiger" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper class="org.richiedryday.mybatis.batch.mapper.UserMapper"/>
	</mappers>
</configuration>


下面开始测试
/**
 * 文件名:CRUDTest.java
 *
 * 版本信息:
 * 日期:2012-8-29
 * Copyright 足下 Corporation 2012 
 * 版权所有
 *
 */
package org.richiedryday.mybatis.batch.test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.richiedryday.mybatis.batch.domain.User;
import org.richiedryday.mybatis.batch.mapper.UserMapper;


/**
 * 
 * 项目名称:mybatis-batch
 * 类名称:CRUDTest
 * 类描述:各种CRUD 包括批处理测试类
 * 创建人:richie144
 * 创建时间:2012-8-29 上午9:38:11
 * 修改人:richie144
 * 修改时间:2012-8-29 上午9:38:11
 * 修改备注:
 * @version 
 * 
 */
public class CRUDTest {
	
	private static final Logger log = Logger.getLogger(CRUDTest.class);
	private static final String resource = "mybatis-config.xml";
	private static SqlSessionFactory sessionFactory = null;
	User u1 = new User(null, "aaa", "aaaa", 10, Timestamp.valueOf("1986-11-10 23:23:56"), '男', "武昌关山");
	User u2 = new User(null, "bbb", "bbbb", 11, Timestamp.valueOf("1987-11-10 23:23:56"), '男', "武昌关山");
	User u3 = new User(null, "ccc", "cccc", 12, Timestamp.valueOf("1985-11-10 23:23:56"), '男', "武昌关山");
	User u4 = new User(null, "ddd", "dddd", 13, Timestamp.valueOf("1989-11-10 23:23:56"), '男', "武昌关山");
	User u5 = new User(null, "eee", "eeee", 14, Timestamp.valueOf("1983-11-10 23:23:56"), '男', "武昌关山");
	User u6 = new User(null, "fff", "ffff", 15, Timestamp.valueOf("1988-11-10 23:23:56"), '男', "武昌关山");
	User u7 = new User(null, "ggg", "gggg", 16, Timestamp.valueOf("1980-11-10 23:23:56"), '男', "武昌关山");
	User u8 = new User(null, "hhh", "hhhh", 17, Timestamp.valueOf("1982-11-10 23:23:56"), '男', "武昌关山");
	User u9 = new User(null, "iii", "iiii", 18, Timestamp.valueOf("1985-11-10 23:23:56"), '男', "武昌关山");
	User u10 = new User(null, "jjj", "jjjj", 19, Timestamp.valueOf("1984-11-10 23:23:56"), '男', "武昌关山");
	User u11 = new User(null, "kkk", "kkkk", 20, Timestamp.valueOf("1985-11-10 23:23:56"), '男', "武昌关山");
	User u12 = new User(null, "lll", "llll", 21, Timestamp.valueOf("1981-11-10 23:23:56"), '男', "武昌关山");
	User u13 = new User(null, "mmm", "mmmm", 22, Timestamp.valueOf("1985-11-10 23:23:56"), '男', "武昌关山");
	User u14 = new User(null, "nnn", "nnnn", 23, Timestamp.valueOf("1990-11-10 23:23:56"), '男', "武昌关山");
	User u15 = new User(null, "ooo", "oooo", 24, Timestamp.valueOf("1992-11-10 23:23:56"), '男', "武昌关山");
	private static final User u16 = new User(null, "dryday", "000000", 23,Timestamp.valueOf("1989-11-10 23:23:56") , '男', "湖北省武汉市武昌华城新都");
	private static final User u17 = new User(null, "renhuan", "000000", 24,Timestamp.valueOf("1989-11-10 23:23:56") , '男', "湖北省武汉市武昌关山大道曙光村");
	@BeforeClass
	public static void setUp() {
		InputStream is = null;
		try {
			is = Resources.getResourceAsStream(resource);
			sessionFactory = new SqlSessionFactoryBuilder().build(is);
		} catch (IOException e) {
			log.debug("未找到资源文件"+resource);
			e.printStackTrace();
		} finally {
			try {
				if(is != null) {
					is.close();
					is = null;
				}
			} catch (IOException e) {
				log.debug("回收资源"+ is + "失败 !");
				e.printStackTrace();
			}
		}
	}
	
	@AfterClass
	public static void shutDown() {
		System.err.println("test over");
	}
	
	public static SqlSession getSession(SqlSessionFactory sessionFactory) {
		return sessionFactory.openSession();
	}
	
	//下面是最基本的增删改查
	@Test
	public void testInsert() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		userMapper.insert(u1);
		userMapper.insert(u2);
		session.commit(true);
		session.close();
	}
	
	@Test
	public void testDelete() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		userMapper.delete(2);
		session.commit();
		session.close();
	}
	@Test
	public void testUpdate() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = new User(null, "richie144", "admin", 24, Timestamp.valueOf("1989-11-10 23:23:56") , '男', "湖北省武汉市武昌关山大道曙光村");
		userMapper.update(user);
		session.commit();
		session.close();
	}
	
	@Test
	public void testGetSingle() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User u = userMapper.getSingle(1);
		System.out.println(u);
		session.commit();
		session.close();
	}
	
	//下面进行批量处理
	
	@Test
	public void testInsertAll() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		List<User> users = new ArrayList<User>();
		users.add(u1);
		users.add(u2);
		users.add(u3);
		users.add(u4);
		users.add(u5);
		users.add(u6);
		users.add(u7);
		users.add(u8);
		users.add(u9);
		users.add(u10);
		users.add(u11);
		users.add(u12);
		users.add(u13);
		users.add(u14);
		users.add(u15);
		users.add(u16);
		users.add(u17);
		userMapper.insertAll(users);
		session.commit();
		session.close();
	}
	
	@Test
	public void testDeleteAll() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		List<User> users = getAll();
		userMapper.deleteAll(users);
		session.commit();
		session.close();
	}
	
	/**
	 * 
	 * getAll(给批量删除提供数据)
	 * @param  @return    设定文件
	 * @return String    DOM对象
	 * @Exception 异常对象
	 * @since  CodingExample Ver(编码范例查看) 1.1
	 */
	private	List<User> getAll() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		List<User> users = userMapper.getAll();
		return users;
	}
	@Test
	public void testInsertAll4Orcl() {
		SqlSession session = getSession(sessionFactory);
		UserMapper userMapper = session.getMapper(UserMapper.class);
		List<User> users = new ArrayList<User>();
		users.add(u1);
		users.add(u2);
		users.add(u3);
		users.add(u4);
		users.add(u5);
		users.add(u6);
		users.add(u7);
		users.add(u8);
		users.add(u9);
		users.add(u10);
		users.add(u11);
		users.add(u12);
		users.add(u13);
		users.add(u14);
		users.add(u15);
		users.add(u16);
		users.add(u17);
		userMapper.insertAll(users);
		session.commit();
		session.close();
	}
}

测试成功,OK搞定奉上源码。。详见附件