mybatis使用annotation在mysql,oracle上进行批量处理
来源:互联网 发布:钢琴可以自学吗 知乎 编辑:程序博客网 时间:2024/05/18 03:46
- /**
- * 文件名: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搞定奉上源码。。详见附件
阅读全文
0 0
- mybatis使用annotation在mysql,oracle上进行批量处理
- 使用mybatis在oracle进行批量插入的insert语句
- oracle和mysql数据库的批量update在mybatis
- MyBatis批量插入Oracle、MySQL
- mybatis 使用in批量处理
- Mybatis 对oracle进行批量操作
- MyBatis中进行批量更新(MySQL数据库)
- annotation在android上进行数据操作
- Mysql对检索结果进行处理后返回以及在列上使用函数
- 使用Jdbc进行批量处理
- MyBatis批量处理Oracle数据库数据
- Mybatis操作Oracle、mysql批量插入
- mybatis 批量插入oracle与mysql
- MyBatis中的批量插入、删除(MySql、Oracle)
- mybatis批量增加(oracle,mysql)
- oracle和mysql mybatis批量更新
- mybatis之mysql&oracle 批量操作
- mybatis下oracle,mysql 批量更新 写法
- DC-2 : scanf的用法
- Codeforces #521B: Cubes 题解
- github可以下载msm和MTK代码
- css引入方式和选择器
- OAuth2.0
- mybatis使用annotation在mysql,oracle上进行批量处理
- MongoDB常用命令汇总之索引。
- 【GDOI2018模拟7.10】B
- 求N!的十进制表示中末尾0的个数 (python实现)
- (转)Rust:Vec、String 内存布局
- 百度无人车ApolloAuto使用入门
- bzoj/hysbz-2440-完全平方数
- Android官方BottomNavigationView添加Badge(角标)
- Android系统广播大全