最详细的mybatis结合mysql和oracle批量增删改

╰+攻爆jí腚メ 2022-05-15 11:39 484阅读 0赞

最近总结了一下mybatis框架结合mysql数据库和oracle数据库的批量增删改语句在sql映射文件中的写法，批量操作的重要性就不必强调了，这里主要以实战为主，废话不多说，直接上代码！

# 1、准备工作 #

## 1、实体类 ##

public class Book implements Serializable{
    	private int id;
    	private String bookName;
    	private double bookPrice;
    	private int bookPage;
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getBookName() {
    		return bookName;
    	}
    	public void setBookName(String bookName) {
    		this.bookName = bookName;
    	}
    	public double getBookPrice() {
    		return bookPrice;
    	}
    	public void setBookPrice(double bookPrice) {
    		this.bookPrice = bookPrice;
    	}
    	public int getBookPage() {
    		return bookPage;
    	}
    	public void setBookPage(int bookPage) {
    		this.bookPage = bookPage;
    	}
    }

## 2、Dao接口 ##

public interface BookDao {
    	//针对mysql的批量插入
    	int saveSomeBooksForMysql(List<Map<String,Object>> list);
    	//针对mysql的批量更新
    	int updateSomeBookSomeColumnForMysql(List<Map<String,Object>> list);
    	//批量删除，mysql和oracle都适用
    	int deleteSomeBook(List<Integer> list);
    	//针对oracle的批量插入
    	int saveSomeBooksForOracle(List<Map<String,Object>> list);
    	//针对oracle的批量插入，可批量生成主键
    	int saveSomeBooksManyPKForOracle(List<Map<String,Object>> list);
    	//针对oracle的批量更新
    	int updateSomeBooksForOracle(List<Map<String,Object>> list);
    }

# 2、mybatis+mysql #

## 1、批量插入 ##

Book.xml中的写法：

<insert id="saveSomeBooksForMysql" parameterType="list">
       		insert into book(book_name,book_price,book_page) values
       		<foreach collection="list" item="item" separator=",">
       			
       			(#{item.bookName},#{item.bookPrice},#{item.bookPage})
       		</foreach>
        </insert>

测试代码：

//获取BookDao的代理类
    BookDao mapper = mysqlSession.getMapper(BookDao.class);
    //将要插入数据库的第一本书
    Map<String, Object> book1 = new HashMap<String,Object>();
    book1.put("bookName", "java");
    book1.put("bookPrice", "50");
    book1.put("bookPage", "300");
    //将要插入数据库的第二本书
    Map<String, Object> book2 = new HashMap<String,Object>();
    book2.put("bookName", "javaweb");
    book2.put("bookPrice", "70");
    book2.put("bookPage", "350");
    //new一个list作为参数
    List<Map<String, Object>> list= new ArrayList<Map<String, Object>>();
    list.add(book1);
    list.add(book2);
    //调用代理类的批量插入方法
    mapper.saveSomeBooksForMysql(list);

## 2、批量更新 ##

1、Book.xml中的写法

<update id="updateSomeBookSomeColumnForMysql" parameterType="list">
        	
        	<foreach collection="list" item="item" close=";" separator=";">
        		update book
    	   		<trim prefix="set" suffixOverrides=",">
    	   			<if test="item.bookName!=null">
    	   				book_name=#{item.bookName},
    	   			</if>
    	   			<if test="item.bookPrice!=null">
    	   				book_price=#{item.bookPrice},
    	   			</if>
    	   			<if test="item.bookPage!=null">
    	   				book_page=#{item.bookPage},
    	   			</if>
    	   		</trim>
    	   		where id=#{item.id}
        	</foreach>
        </update>

2、测试代码

//获取BookDao的代理类
    BookDao mapper = mysqlSession.getMapper(BookDao.class);
    //将要更新的第一本书
    Map<String, Object> book1 = new HashMap<String,Object>();
    book1.put("id", 129);
    //不更新的列设其值为null
    book1.put("bookName", null);
    book1.put("bookPrice", "60");
    book1.put("bookPage", "380");
    //将要更新的第二本书
    Map<String, Object> book2 = new HashMap<String,Object>();
    book2.put("id", 130);
    book2.put("bookName", null);
    book2.put("bookPrice", null);
    book2.put("bookPage", "270");
    //new一个list作为参数
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    list.add(book1);
    list.add(book2);
    //调用代理类的批量插入方法
    mapper.updateSomeBookSomeColumnForMysql(list);

## 3、批量删除 ##

1、Book.xml中的写法

<delete id="deleteSomeBook" parameterType="list">
        	delete from book where id in
        	<foreach collection="list" open="(" close=")" separator="," item="item">
        		#{item}
        	</foreach>
        </delete>

这个mysql和oracle都适用，因为比较简单，就不写测试代码了。

# 3、mybatis+oracle #

## 1、批量插入 ##

1、Book.xml中的写法

<insert id="saveSomeBooksForOracle" parameterType="list" useGeneratedKeys="false">
     		INSERT ALL
    		<foreach collection="list" item="item">
    			into book(id, book_name, book_price, book_page)
    			values(#{item.id}, #{item.bookName}, #{item.bookPrice}, #{item.bookPage})
    		</foreach>
    		SELECT 1 FROM DUAL
     	</insert>
     	
     	
     	<insert id="saveSomeBooksManyPKForOracle" parameterType="list" useGeneratedKeys="false">
     		<selectKey resultType="int" keyProperty="id" order="BEFORE"> 
               SELECT book_seq.nextval FROM dual
             </selectKey> 
             INSERT INTO book(id,book_name,book_price,book_page)
             SELECT book_seq.nextval, t.* from(
              <foreach collection="list" item="item" separator="union all">
                 select
                 
                 #{item.bookName,jdbcType=VARCHAR},
                 #{item.bookPrice,jdbcType=DOUBLE},
                 #{item.bookPage,jdbcType=INTEGER}
               	 from dual
             </foreach>
             )t
     	</insert>

注意：这两有两个insert标签，都可以实现批量插入，两者的区别在注释中写的比较清楚了，测试代码就套上面mybatis+mysql批量插入的测试代码

## 2、批量更新 ##

1、Book.xml中的写法

<update id="updateSomeBooksForOracle" parameterType="list">
     		<foreach collection="list" item="item" open="begin" close=";end;" separator=";">
    	        update book b set
    		        b.book_name= #{item.bookName},
    		        b.book_price= #{item.bookPrice},
    		        b.book_page= #{item.bookPage}
    	        where b.id = #{item.id}
        	</foreach>
     	</update>

测试代码就套上面mybatis+mysql批量更新的测试代码

最后，如果大家在使用过程中有不明白的地方，或者有其他意见，欢迎给我留言！