[MyBats/iBatis] insertList 정리

기본적으로 오라클에서 INSERT ALL 을 제공해준다.

- INSERT ALL 예제 (출처: http://302.pe.kr/349 )

Insert All

-  Oracle 9i 이상에서 지원.

- Multitable insert는 한개의 테이블에 여러 row를 넣거나, 여러개의 테이블에 데이터를 한 insert 명령으로 넣을 수 있습니다.  이전 버전에서는 이런 기능은 PL/SQL로 구현을 해야 했으나 9i 이후로는 한 명령으로 수행할 수 있습니다.

-- Unconditional insert into ALL tables                                 
INSERT ALL                                                              
  INTO sal_history VALUES(empid,hiredate,sal)                           
  INTO mgr_history VALUES(empid,mgr,sysdate)                            
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200;                               
                                                                        
-- Pivoting insert to split non-relational data                         
INSERT ALL                                                              
  INTO Sales_info VALUES (employee_id,week_id,sales_MON)                
  INTO Sales_info VALUES (employee_id,week_id,sales_TUE)                
  INTO Sales_info VALUES (employee_id,week_id,sales_WED)                
  INTO Sales_info VALUES (employee_id,week_id,sales_THUR)               
  INTO Sales_info VALUES (employee_id,week_id, sales_FRI)               
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,                      
    sales_WED, sales_THUR,sales_FRI                                     

FROM Sales_source_data;                                   

이 INSERT ALL 을 iBatis에서 적용해 볼 수 있다.

(출처: http://baeksupervisor.tistory.com/23)

단일 insert 할 때는 dao 단에서 map 을 통해 혹은 자바빈을 통해 인자를 넘겼다면
다중 insert 를 ibatis 에서 한번에 하려면. map 혹은 자바빈을 list 에 담아서 보내주면 된다.

당연히 list 의 각 row 에 해당하는 빈의 속성은 모든 row 가 동일해야 한다.

INSERT ALL
		
			
				INTO FILE_ATTACH (
					BOARD_ID
					,BOARD_DETAIL_ID
					,FILE_SEQ_NUM
					,FILE_NAME_ORG
					,FILE_NAME_REAL
					,FILE_SIZE
					,FILE_DATE
					,FILE_PATH
				) VALUES (
					#[].board_id#
					,#[].board_detail_id#
					,#[].file_seq_num#
					,#[].file_name_org#
					,#[].file_name_real#
					,#[].file_size#
					,TO_CHAR(SYSDATE, 'YYYY-MM-DD')
					,#[].file_path#
				)
			
		
		SELECT * FROM DUAL
	

그렇다면..
dao 단에서 트랜잭션을 통해 query 를 row 만큼 호출하는 것이 효율적일까..
아니면 iterator 를 통해 query 는 한번만 호출하는 것이 효율적일까...

- iBatis List 형태 파라미터 insert List<Object>

출처: http://hdm6337.tistory.com/entry/iBatis-List-%ED%98%95%ED%83%9C-%ED%8C%8C%EB%9D%BC%EB%AF%B8%ED%84%B0-insert-ListObject

오라클에는 여러개의 값을 한번에 insert하기 위해서 쓰는 multi insert구문이 있습니다.

바로 insert all인데요.

[쿼리 구문] 

CREATE TABLE t (
pid   NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(25));

INSERT ALL
INTO t (pid, fname, lname)
VALUES (1, 'Dan', 'Morgan')
INTO t (pid, fname, lname)
VALUES (2, 'Jeremiah', 'Wilton')
INTO t (pid, fname, lname)
VALUES (3, 'Helen', 'Lofstrom')
SELECT * FROM dual;

SELECT * FROM t;

위와 같이 쓰는겁니다.

자 그럼 위와 같은 커리를 iBatis로 편하게 만드려면 어케하는지 봅시다.

일단 자바 클래스부터 살펴볼까요?

[모델 클래스]

public class Table{

private String pid;

private String fname;

private int lname;

public String getPid() { return pid; } public void setPid(String pid) { this.pid = pid; } public String getFname() { return fname; } public void setFname(String fname) { this.fname = fname; } public int getLname() { return lname; } public void setLname(int lname) { this.lname = lname; }

}

[DAO 메소드]

public Object tableMultiInsert(List<Table> param ) {

return getSqlMapClientTemplate().insert("Table.tableMultiInsert", param);

}

[XXX.SqlMap.xml]

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"

    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Table">

<insert id="tableMultiInsert" parameterClass="java.util.List">

INSERT ALL

        <dynamic>

<iterate conjunction=" "  >

INTO t (pid, fname, lname)

VALUES (#[].pid#, #[].fname#, #[].lname#)

</iterate>

</dynamic>

SELECT * FROM dual

</insert>

</sqlMap>

- 또다른 예제 MyBatis

출처: http://stackoverflow.com/questions/17563463/mybatis-insert-list-values

Mapper.xml (Mapper xml file) <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="TestDAO"> <insert id="insertEmployeeList" parameterType="java.util.List"> INSERT INTO EMPLOYEE (id, name) VALUES <foreach collection="list" item="element" index="index" open="(" separator="," close=")"> #{element.id}, #{element.name} </foreach> </insert> </mapper> Employee.java public class Employee { private List<Emp> list = new ArrayList<Emp>(); public List<Emp> getList() { return list; } public void setList(List<Emp> list) { this.list = list; } } Emp.java public class Emp { public Emp(int id, String name) { this.id = id; this.name = name; } private int id; private String name; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } TestDAO.java public interface TestDAO { public Integer insertEmployeeList(List<Emp> empList) throws SQLException; } Main.java public class Main { public static void main (String args[]) { TestDAO tm = session.getMapper(TestDAO.class); Employee e = new Employee(); Emp e11 = new Emp(123,"abc"); Emp e12 = new Emp(456,"def"); e.getList().add(e11); e.getList().add(e12); tm.insertEmployeeList(e.getList()); } } Exception I'm getting is: Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values The error may involve com.XXXX.sample.test.dao.TestDAO.insertEmployeeList-Inline The error occurred while setting parameters Cause: java.sql.SQLSyntaxErrorException: ORA-00913: too many values java oracle mybatis share|improve this question edited Jul 10 at 7:25 Angelo Neuschitzer 4,121428 asked Jul 10 at 6:02 user358099 96113

class Main { public static void main (String args[]) { TestDAO tm = session.getMapper(TestDAO.class); Employee e = new Employee(); Emp e11 = new Emp(123,"abc"); Emp e12 = new Emp(456,"def"); e.getList().add(e11); e.getList().add(e12); tm.insertEmployeeList(e.getList()); } } – user358099 Jul 10 at 6:07 could you edit your last comment into your question, please? It makes it a lot easier to read. – Angelo Neuschitzer Jul 10 at 6:32 also please add the TestDAO as well. – Angelo Neuschitzer Jul 10 at 6:34 Is that fine? Kindly revert in case of any clarifications – user358099 Jul 10 at 6:40

2 Answers

activeoldestvotes

up vote1down vote

Configure your logging system to print out the generated Queries, then try to execute them to your DB directly. In your case, the expected query SHOULD be INSERT INTO EMPLOYEE (id, name) VALUES (123, "abc")(456, "def") as far as I can remember that is not valid. INSERT INTO only takes one set of VALUES. I looked up thedocumentation and it looks like this there as well. See this answer on how to INSERT multiple rows in oracle. share|improve this answer answered Jul 10 at 7:36 Angelo Neuschitzer 4,121428

Logging the query helped me to fix the issue. Thanks a lot. – user358099 Jul 10 at 17:23

up vote1down voteaccepted

Mapper.xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.xxxx.sample.test.dao.TestDAO"> <insert id="insertEmployeeList" parameterType="java.util.List"> INSERT ALL <foreach collection="list" item="element" index="index" > INTO EMPLOYEE (id,name) values (#{element.id},#{element.name}) </foreach> SELECT * FROM dual </insert> </mapper> This is how the query should be there in Mapper xml