스프링 에러 : SQLSyntaxErrorExceptio

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

### The error may exist in com/vam/mapper/MemberMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into BOOK_MEMBER values('admin23', 'admin', 'admin', 'admin', 'admin', 'admin', 'admin', 1, sysdate, 1000000, 1000000);
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다

1. 상황

- blog_project에서 회원가입기능을 구현하기위해 오라클 쿼리 작성 후 테스트 하였으나 위와같은 경고 문구가 뜸

- 구조 

   - MemberMapper.xml/MemberMapper.java

   - MmeberMapperTests.java

 

○MemberMapper.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.vam.mapper.MemberMapper">
  	<insert id="memberJoin">
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, 0, sysdate, 100000, 5000 ); -->
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, 0, sysdate, 100000, 5000); -->
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, #{adminCk}, sysdate, #{money}, #{point}); -->
  		insert into BOOK_MEMBER values('admin23', 'admin', 'admin', 'admin', 'admin', 'admin', 'admin', 1, sysdate, 1000000, 1000000);
  	</insert>
  </mapper>

 

○MemberMapper.java

package com.vam.mapper;

import com.vam.model.MemberVO;

public interface MemberMapper {

	//회원가입
	public void memberJoin(MemberVO member);
	
	
}

 

○MemberMapperTests.java

package com.vam.member;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.vam.mapper.MemberMapper;
import com.vam.model.MemberVO;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
public class MemberMapperTests {

	@Autowired
	private MemberMapper membermapper;
	
	@Test
	public void memberJoin() throws Exception{
		MemberVO member = new MemberVO();
		/*
		 * member.setMemberId("test"); member.setMemberPw("1234");
		 * member.setMemberName("test"); member.setMemberMail("test");
		 * member.setMemberAddr1("123"); member.setMemberAddr2("member");
		 * member.setMemberAddr3("member"); member.setAdminCk(0); member.setPoint(100);
		 * member.setMoney(100);
		 */
		membermapper.memberJoin(member);
	}
	
	
}

 

2. 시도해본 것들

- Oracle Develper에서 insert문 테스트후 그 쿼리문 그대로 MemberMapper.xml에 적용

=> 실패

- select문을 시도하였으나 동일하게 Oracle Developer 에선 실행되지만 스프링에선 여전히 실행되지 않음

 

3. 해결

쿼리문장에 세미콜론(;)이 들어있어서 실패

insert into BOOK_MEMBER values('admin23', 'admin', 'admin', 'admin', 'admin', 'admin', 'admin', 1, sysdate, 1000000, 1000000);

=>

insert into BOOK_MEMBER values('admin23', 'admin', 'admin', 'admin', 'admin', 'admin', 'admin', 1, sysdate, 1000000, 1000000)

○MemberMapper.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.vam.mapper.MemberMapper">
  	<insert id="memberJoin">
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, 0, sysdate, 100000, 5000 ); -->
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, 0, sysdate, 100000, 5000); -->
  		<!-- insert into book_member values(#{memberId}, #{memberPw}, #{memberName}, #{memberMail}, #{memberAddr1}, #{memberAddr2}, #{memberAddr3}, #{adminCk}, sysdate, #{money}, #{point}); -->
  		insert into BOOK_MEMBER values('admin23', 'admin', 'admin', 'admin', 'admin', 'admin', 'admin', 1, sysdate, 1000000, 1000000)
  	</insert>
  </mapper>

 

4. 느낀점

mysql에선 스프링에 있는 쿼리문에 세미콘이 들어있더라도 실행이 되었습니다. 오라클은 MySQL에비해 문법이나 구문에 조금더 신경을 써줘야 할 것같습니다.