mybatis使用annotation在mysql,oracle上进行批量处理
程序员文章站
2022-06-05 22:38:54
...
mybatis 使用annotation进行批量插入和删除,mysql 和oracle不同,因为oracle不支持(xxx,xxx,xxx),(xxx,xxx,xxx)的写法。不多说了看代码。
下面是映射类
配置文件放在类路径下(mybatis-config.xml)
下面开始测试
测试成功,OK搞定奉上源码。。详见附件
/** * 文件名: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搞定奉上源码。。详见附件