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

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 users);

@DeleteProvider(type=MapperProvider.class,method="deleteAll")

void deleteAll(List 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 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> map) {

List 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

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> map) {

List 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

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> map){

List 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

//注意空格

sb.append("SELECT ");

sb.append(messageFormat.format(new Object[]{i}));

//注意空格

sb.append(" FROM DUAL ");

if(i

sb.append(" UNION ALL ");

}

}

System.out.println(sb.toString());

return sb.toString();

}

}

配置文件放在类路径下(mybatis-config.xml)

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

下面开始测试

/**

* 文件名：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 users = new ArrayList();

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 users = getAll();

userMapper.deleteAll(users);

session.commit();

session.close();

}

/**

*

* getAll(给批量删除提供数据)

* @param @return 设定文件

* @return String DOM对象

* @Exception 异常对象

* @since CodingExample　Ver(编码范例查看) 1.1

*/

privateList getAll() {

SqlSession session = getSession(sessionFactory);

UserMapper userMapper = session.getMapper(UserMapper.class);

List users = userMapper.getAll();

return users;

}

@Test

public void testInsertAll4Orcl() {

SqlSession session = getSession(sessionFactory);

UserMapper userMapper = session.getMapper(UserMapper.class);

List users = new ArrayList();

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搞定奉上源码。。详见附件

分享到：

2012-08-29 21:11

浏览 8384

评论

2 楼

zouzhuoqi

2013-12-20

1 楼

yonguo

2013-08-05

附件无法下载