zxc123
182
2018-01-03 22:32:45 작성 2018-01-04 09:27:36 수정됨
14
8267

spring mybatis 연동중 질문있습니다


select는 동작하는데 insert시 에러가 발생합니다

값을 받아오는것까지는 보이는데  sql에러의 원인을 모르겠습니다..

에러에 찍힌 sql문을 db에 직접 작성시 값이 insert됩니다

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''board_title', 'board_content') VALUES('13', '1313')
    ' at line 1
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO board('board_title', 'board_content') VALUES(?, ?)     
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''board_title', 'board_content') VALUES('13', '1313')
    ' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''board_title', 'board_content') VALUES('13', '1313')
    ' at line 1
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

mapper.xml

<mapper namespace="com.my.begin.dao.board.BoardDAOMapper">

    <resultMap type="com.my.begin.model.Board" id="MemberResultMap">
        <result property="boardIndex" column="board_index" /><!--property가 클래스고 뒤에 컬럼이 db이다-->
        <result property="boardTitle" column="board_title" />
        <result property="boardContent" column="board_content" />
        <result property="boardDate" column="board_date" />
    </resultMap>

    <select id="findAll" resultMap="MemberResultMap">
        SELECT * FROM board
     </select>

    <insert id="insertByBoardIndex">
        INSERT INTO board('board_title', 'board_content') VALUES(#{boardTitle}, #{boardContent})
    </insert>

</mapper>


daoimpl.java


package com.my.begin.dao.board.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.my.begin.dao.board.BoardDAOMapper;
import com.my.begin.model.Board;


@Repository
public class BoardDAOMapperImpl implements BoardDAOMapper{

    @Autowired
    private SqlSession sqlSession;

    @Override
    public List<Board> findAll() {
    	List<Board> boardList = new ArrayList<>();
    	//sqlSession을 통하여 매핑한다.
    	BoardDAOMapper boardDAOMapper = sqlSession.getMapper(BoardDAOMapper.class);
    	boardList = boardDAOMapper.findAll();
    	
    	return boardList;
    }
    
    @Override
    public void insertByBoardIndex(String boardTitle, String boardContent) {
        BoardDAOMapper boardDAOMapper = sqlSession.getMapper(BoardDAOMapper.class);
        boardDAOMapper.insertByBoardIndex(boardTitle,boardContent);
    }

}


dao.java


package com.my.begin.dao.board;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.my.begin.model.Board;

public interface BoardDAOMapper {
	List<Board> findAll();

	void insertByBoardIndex(@Param("boardTitle") String boardTitle, @Param("boardContent") String boardContent);
	
}


model


package com.my.begin.model;
public class Board {
    private int boardIndex;
    private String boardTitle;
    private String boardContent;
    private String boardDate;

    public Board() {
    }
    public Board(int boardIndex, String boardTitle, String boardContent, String boardDate) {
		super();
		this.boardIndex = boardIndex;
		this.boardTitle = boardTitle;
		this.boardContent = boardContent;
		this.boardDate = boardDate;
	}

    public int getBoardIndex() {
		return boardIndex;
	}

	public void setBoardIndex(int boardIndex) {
		this.boardIndex = boardIndex;
	}

	public String getBoardTitle() {
		return boardTitle;
	}

	public void setBoardTitle(String boardTitle) {
		this.boardTitle = boardTitle;
	}

	public String getBoardContent() {
		return boardContent;
	}

	public void setBoardContent(String boardContent) {
		this.boardContent = boardContent;
	}

	public String getBoardDate() {
		return boardDate;
	}

	public void setBoardDate(String boardDate) {
		this.boardDate = boardDate;
	}

	@Override
    public String toString() {
        return "Board{" +
                "boardIndex=" + boardIndex +
                ", boardTitle='" + boardTitle + '\'' +
                ", boardContent=" + boardContent +
                ", boardDate=" + boardDate +
                '}';
    }
}



controller


@RequestMapping(value = "/board/save", method = RequestMethod.GET)
	public String boardWrite(HttpServletRequest request, ModelMap modelMap) {
		logger.info("/save in");
		
		String boardTitle = request.getParameter("input_title");
		String boardContent = request.getParameter("input_content");
		
		System.out.println("inputTitle: " + boardTitle);
	    System.out.println("inputContent: " + boardContent);

	    boardDAO.insertByBoardIndex(boardTitle, boardContent);
	    List<Board> boardList = boardDAO.findAll();

	    modelMap.put("boardList", boardList);
		
		return "board/board_list";
	}


0
  • 답변 14

  • 공부하는취준생
    803
    2018-01-04 00:09:14 작성 2018-01-04 00:14:54 수정됨

    위에러는 insert 구문이 잘못되었다는 에러입니다..

    error의 키워드만 긁어서 구글링해도 바로 나오는데..

    화이팅

    INSERT INTO board('board_title', 'board_content') >>> INSERT INTO board(board_title, board_content)
  • zxc123
    182
    2018-01-04 09:27:01

    공부하는취준생 

    네 제가 위에 본문에 쓰는것을 빼먹은거같아요... 저도 insert 구문이 잘못됬다는건 알고있어서 저 구문 그대로 db에 직접 insert시 입력이되는데 이클립스에서 실행시 에러가 발생하여서 질문드렸습니다

  • 내가개발자라니..
    924
    2018-01-04 09:40:04 작성 2018-01-04 09:40:24 수정됨
     INSERT INTO board('board_title', 'board_content') VALUES(#{boardTitle}, #{boardContent})

    이걸 아래로 변경

    INSERT INTO board(board_title, board_content) VALUES (#{boardTitle}, #{boardContent});

  • zxc123
    182
    2018-01-04 10:39:41

    내가개발자라니.. 

    공부하는취준생 

    주신코드로 변경 하였는데도 같은 에러가 발생합니다...


    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
    ### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 2
    ### The error may involve defaultParameterMap
    ### The error occurred while setting parameters
    ### SQL: INSERT INTO board(board_title, board_content) VALUES (?, ?)     
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 2
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 2
    



  • 시드비셔스
    199
    2018-01-04 13:15:56 작성 2018-01-04 13:20:53 수정됨

    insert 태그를 쓰셨는데 입력하는 파라미터가 아예 없네요

    defaultParameterMap 에러는 <insert id="삽입매퍼이름" parameterClass="DTO나 java빈이름"> 이런 식으로 쓰셔야합니다. 참고로 insert, update, delete는 resultMap이 필요없는게 result값은 어차피 숫자거든요.

  • zxc123
    182
    2018-01-04 13:52:38

    시드비셔스 

    파라미터 값이 없는것 같아서 hashmap으로 변경후 parameterType에 hashmap 줬는데도 같은 에러가 발생합니다 제가 잘못적은것이 있는건가요...?

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
    ### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    ### The error may involve com.my.begin.dao.board.BoardDAOMapper.insertByBoardIndex-Inline
    ### The error occurred while setting parameters
    ### SQL: INSERT INTO board (board_title, board_content) VALUES(?, ?)              
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

    mapper.xml

    <insert id="insertByBoardIndex" parameterType="hashmap">
            INSERT INTO board (board_title, board_content) VALUES(#{boardMap.boardTitle}, #{boardMap.boardContent})
            <!-- INSERT INTO board('board_title', 'board_content') VALUES(#{boardTitle}, #{boardContent}) -->
        </insert>

    dao

    @Override
        public void insertByBoardIndex(HashMap<String, String> boardSaveMap){
            BoardDAOMapper boardDAOMapper = sqlSession.getMapper(BoardDAOMapper.class);
            boardDAOMapper.insertByBoardIndex(boardSaveMap);
        }


  • 내가개발자라니..
    924
    2018-01-04 14:25:20

    boardSaveMap 에 값 확인해 보셨나요?

  • 시드비셔스
    199
    2018-01-04 14:50:38

    @zxc123

    모델(Board.java)에 setter와 getter가 있는데 활용을 안하시는것 같아요.

    컨트롤러에서도 Board.java로 파라미터 설정해서 받으셔야 해시맵을 받을 수 있을 것 같습니다.


  • zxc123
    182
    2018-01-05 09:34:41

    시드비셔스

    내가개발자라니.. 

    계속 태그해서 죄송합니다ㅜㅜ

    dao부분에서 boardSaveMap 에 값이 넘어오는것까지는 확인했습니다

    boardDAOMapper.insertByBoardIndex(boardSaveMap);

    이부분 실행후 mapper.xml로 넘어가면서 에러가 발생하는것 같습니다

    또 mapper에서 

    INSERT INTO board (board_title, board_content) VALUES(#{boardMap.boardTitle}, #{boardMap.boardContent})

    이부분에서 컬럼명을 '' 로 감싸주면 맨위 본문에서 발생하는 에러가 발생하고

    컬럼명을 안 감싸고 사용할경우 바로위 같은 에러가 발생합니다

  • 내가개발자라니..
    924
    2018-01-05 09:55:07 작성 2018-01-05 10:15:00 수정됨

    궁금한게있는데요

    HashMap 쓰실때 String, String 을

    String, Object 로 한번 바꿔보실래요?


    음.. 상관없으려나..

  • 시드비셔스
    199
    2018-01-05 14:56:26

    parameterType="java.util.HashMap"으로 풀 패키지명을 써보세요.

    mybatis-config 파일에서 따로 typealias를 정하지 않으면 저렇게 줄여쓸 수 없습니다.

  • zxc123
    182
    2018-01-06 01:18:00

    시드비셔스 

    내가개발자라니.. 

    말씀해주신거 다 해봤는데 계속 같은 오류가 나옵니다.

    insertByBoardIndex를 호출한곳에서 hashmap에 값이 들어오는것도 확인해봤구요

    어떤것이 문제일까요....

    type Exception report

    message Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:

    description The server encountered an internal error that prevented it from fulfilling this request.

    exception

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
    ### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    ### The error may involve com.my.begin.dao.board.BoardDAOMapper.insertByBoardIndex-Inline
    ### The error occurred while setting parameters
    ### SQL: INSERT INTO board (board_title, board_content) VALUES(?, ?)              
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    ' at line 3
    	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
    	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
    	org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    	org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
  • 시드비셔스
    199
    2018-01-08 11:32:00

    @zxc123


    일단 대소문자 확인하시고... DB 각 컬럼을 다시 확인하셔서 혹시 타입에 맞지 않는건 아닌지(제목이나 내용 부분에 숫자만 들어가게 해놨다거나, 컬럼 생성시 데이터타입을 밀려썼다거나...) 확인해보심이 어떨까요

  • zxc123
    182
    2018-01-11 16:27:05

    시드비셔스 

    감사합니다 확인했는데 뒤에 '      ' 이런 공백이 들어있는걸  쿼리 만들떄 인식하는거였습니다

  • 로그인을 하시면 답변 을 등록할 수 있습니다.