[Error] Sql 쿼리 INSERT문

2024/10/23

p1

댓글 입력

 

BoardCommentDao.java

public int insertComment(Connection conn, BoardComment bc) throws Exception {
    int row=0;
    String sql = """
            insert into board_comment(
                article_no articleNo
                , member_id memberId
                , comment
                , createdate
            ) values (?,?,?,now())
            """;
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setInt(1, bc.getArticelNo());
    stmt.setString(2, bc.getMemberId());
    stmt.setString(3, bc.getComment());
    System.out.println("댓글 입력 stmt = "+stmt);

    row=stmt.executeUpdate();
    return row;
}

 

InsertCommentServlet.java

@WebServlet("/board/insertComment")
public class InsertCommentServlet extends HttpServlet{
	// -> C: /board/boardOne
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		HttpSession session = request.getSession();
		String loginId = (String)session.getAttribute("loginId");
		if(loginId == null) { // 로그인이 안됐다면
			response.sendRedirect(request.getContextPath()+"login");
			return;
		}
		
		int articleNo = Integer.parseInt(request.getParameter("articleNo"));
		
		String comment = request.getParameter("comment");
		
		BoardComment bc = new BoardComment();
		bc.setArticelNo(articleNo);
		bc.setMemberId(loginId);
		bc.setComment(comment);
		
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false); // 트랙잭션 작업을 위해
			BoardCommentDao boardCommentDao = new BoardCommentDao();
			int row = boardCommentDao.insertComment(conn, bc);
			if(row == 1) {
				System.out.println("댓글 입력 성공");
				}
			conn.commit(); // 정상적으로 try절 이행하면 commit
			response.sendRedirect(request.getContextPath()+"/board/boardOne.jsp");
			
		} catch (Exception e) {
			try {
				System.out.println("댓글 입력 실패");
				conn.rollback(); // 위 try절에서 예외 발생 시 롤백
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
}

 

 

stmt는 잘 나오는데 댓글 입력 실패

 

콘솔창

java.sql.SQLSyntaxErrorException: 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 'articleNo
					, member_id memberId
					, comment
					, createdate
	) values (' at line 2
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:987)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1165)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1100)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1466)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1083)
	at dao.BoardCommentDao.insertComment(BoardCommentDao.java:28)
	at controller.InsertCommentServlet.doPost(InsertCommentServlet.java:43)

Sql 쿼리가 잘못됐다고 나옴

 

Sql INSERT문에는 컬럼 별칭을 쓸 수 없다!

 

public int insertComment(Connection conn, BoardComment bc) throws Exception {
		int row=0;
		String sql = """
					insert into board_comment(article_no, member_id, comment, createdate) 
					values (?,?,?,now())
				"""; // insert문에는 별칭 XX
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, bc.getArticelNo());
		stmt.setString(2, bc.getMemberId());
		stmt.setString(3, bc.getComment());
		System.out.println("댓글 입력 stmt = "+stmt);
		
		row=stmt.executeUpdate();
		return row;
	}

BoardCommentDao.java 수정 - 별칭을 지움

 

이제 다른 오류로..