TodoList 연습 2 , DB 연동 ( html,css,js, ajax ( Servlet ) )

 

 

 

만든 TodoList DB 까지 연동해보자 .

 

 

 

 

-- Add 버튼 눌렀을때 .

 

 

-- DB 에 추가된거 확인 .

 

 

-- Delete 버튼 눌렀을때 .

 

 

-- DB 에서 삭제된거 확인 .

 

 

-- All Delete 눌렀을때 .

 

 

-- 전체삭제 확인.

 

 

 

 

 

--- Add 버튼 눌렀을때 이벤트로 ajax 실행   ( " /addList " )  로 Servlet 에게 패스 ! 

    -- Add 버튼 눌렀을때 createElement 하기때문에 . 

        -- Add버튼 눌렀을때  --> Delete버튼 눌렀을때  --> ajax 실행 (  "/deleteList" ) 로 Servlet 에게 패스 ! 

--- todojs.js 코드

addBtn.addEventListener("click",function(e){   // Add 버튼 눌렀을때 .
    if ( inputTodo.value=="" ) {
	
    	// 코드생략

    }else{

          ///////////// DB 에 TodoList 추가 . ( insert )
          $.ajax({
            url : "addList",                          // Servlet 으로 넘기고  " /addList "
            data : {"inputTodo" : inputTodo.value},   // input 값을 가져간다.
            type : "POST",
            success : function(result){
              console.log("addList 성공");
            },
            error : function(){
              console.log("addList 실패")
            }
          });

		// 코드생략

    const span = document.createElement("span");
    span.classList.add("liSpan");  // span 생성

		// 코드생략
        
    inputTodo.value="";    // input 값을 "" 하기전에 ajax 실행해야함 .!!

		// 코드생략
        
    button.addEventListener("click",function(){    // Delete 눌렀을때 삭제
       
        ///////////// DB 에서 삭제 추가 . ( delete )
        $.ajax({
          url : "deleteList",                       // Servlet 으로 전달     " /deleteList "
          data : {"spanInput" : span.innerText},    // 만든 span 태그의 innerText .
          type : "POST",
          success : function(result){
            console.log("deleteList 성공");
          },
          error : function(){
            console.log("deleteList 실패")
          }
    
        });
        this.parentElement.remove();                // remove()  전에 ajax 작성 !! 

		// 코드생략    

    });
    }   
});


  /////////////////////  전체 삭제
const allDelete = document.getElementById("allDelete");
const todoList = document.getElementById("todo-listId");
allDelete.addEventListener("click",function(){

    if(confirm("정말 삭제하시겠습니까?")==true){   //취소메시지가 true(ok)일때
        if(todoList.innerHTML==''){              //목록칸이 비어있다면
            alert("삭제할 목록이 없습니다");       //삭제할 목록이 없다는 경고창뜨기
        }else{                                   //삭제할 목록이 있다면
            todoList.innerHTML='';               //전체 삭제
            countSpanInput.innerText= "";        // 남은할일 : "" ;
            count = 0;
        }
    }else{                  //취소메시지가 false(no)일때
        return false;                   //삭제 취소
    }


      ///////////// DB 에 전체삭제 추가 . ( alldelete )     
      $.ajax({
        url : "AllListDelete",                       // " /AllListDelete" 서블릿 전달
        type : "POST",
        success : function(result){
          console.log("AllListDelete 성공");
        },
        error : function(){
          console.log("AllListDelete 실패")
        }
  
      });

});

 

 

 

 

 

 

--- DB 연결을 담당하는  JDBCTemplate.class   생성 

public class JDBCTemplate {
	private static Connection conn = null;

	public static Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","workbook","1234");
			conn.setAutoCommit(false); // 자동커밋 비활성화
		} catch (Exception e) {
			System.out.println("[Connection 생성중 예외 발생]");
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * Connection 객체 자원 반환메서드
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		try {
			if (conn != null && !conn.isClosed())
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Statement 객체 자원 반환메서드 ( PreparedStatement(자식) 다형성/ 동적바인딩
	 * 
	 * @param stmt
	 */
	public static void close(Statement stmt) {
		try {
			if (stmt != null && !stmt.isClosed())
				stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * ResultSet 객체 자원 반환메서드
	 * 
	 * @param rs
	 */
	public static void close(ResultSet rs) {
		try {
			if (rs != null && !rs.isClosed())
				rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 트랜잭션 Commit 메서드
	 * 
	 * @param conn
	 */
	public static void commit(Connection conn) {
		try {
			if (conn != null && !conn.isClosed())
				conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 트랜잭션 Rollback 메서드
	 * 
	 * @param conn
	 */
	public static void rollback(Connection conn) {
		try {
			if (conn != null && !conn.isClosed())
				conn.rollback();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

 

 

 

 

 

 

---  ( " /addList " )  서블릿 코드 .  AddListServlet.class 

@WebServlet("/addList")
public class AddListServlet extends HttpServlet{
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

		TodoService service = new TodoService();
		try {
			String inputTodo = req.getParameter("inputTodo");
			int result = service.insertTodo(inputTodo);
			System.out.println("Servlet "+result);
			if ( result > 0 ) {
				System.out.println("인서트성공");
			}else {
				System.out.println("인서트실패");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

 

---  ( " /deleteList " )  서블릿 코드 .  DeleteListServlet.class 

@WebServlet("/deleteList")
public class DeleteListServlet extends HttpServlet{
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		try {
			TodoService service = new TodoService();
			String span = req.getParameter("spanInput");
			
			int result = service.deleteList(span);
			if ( result> 0 ) {
				System.out.println("딜리트성공");
			}else {
				System.out.println("딜리트실패");
			}
			resp.getWriter().print(result);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

---  ( " /AllListDelete" )  서블릿 코드 . AllListDeleteServlet.class 

@WebServlet("/AllListDelete")
public class AllListDeleteServlet extends HttpServlet{
	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		try {
			TodoService service = new TodoService();		
			int result = service.allListDelete();
			
			if ( result > 0 ) {
				System.out.println("전체삭제 성공");
			}else {
				System.out.println("전체삭제 실패");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

 

 

 

--- TodoService.class  추가

public class TodoService {
	private TodoDAO dao = new TodoDAO();
	
	/**     Todo db 추가 서비스
	 * @param inputTodo
	 * @return
	 * @throws Exception
	 */
	public int insertTodo(String inputTodo) throws Exception {

		Connection conn = getConnection();
		int result = dao.insertTodo(conn,inputTodo);
		if ( result>0 )  commit(conn);
		else 			 rollback ( conn) ;
		close (conn);
		
		return result;
	}

	/**   전체삭제 서비스
	 * @return
	 * @throws Exception
	 */
	public int allListDelete() throws Exception {
    
		Connection conn = getConnection();
		int result = dao.allListDelete(conn);
		if ( result > 0 ) commit(conn);
		else 			  rollback ( conn);
		close (conn);
		
		return result;
	}

	/**     한개삭제 서비스 .
	 * @param span
	 * @return
	 */
	public int deleteList(String span) throws Exception {
    
		Connection conn = getConnection();
		int result = dao.deleteList(conn,span);
		if ( result> 0 ) commit(conn);
		else 		     rollback(conn);
		close(conn);
		
		return result;
	}
}

 

 

--- TodoDAO.class  추가

public class TodoDAO {
	
	private Statement stmt;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private Properties prop ;
	
	public TodoDAO() {
		try {
			prop = new Properties();
			String filePath = TodoDAO.class.getResource("/todolist/sql/Todo-sql.xml").getPath();
			prop.loadFromXML( new FileInputStream(filePath));
			
		}catch(Exception e ) {
			e.printStackTrace();
		}
	}


	/**    TodoList 작성 데이터저장
	 * @param conn
	 * @param inputTodo
	 * @return
	 * @throws Exception
	 */
	public int insertTodo(Connection conn, String inputTodo) throws Exception{
		int result = 0;
		try {
			String sql = prop.getProperty("insertTodo");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, inputTodo);
			result = pstmt.executeUpdate();
		}finally {
			close(pstmt);
		}
		return result;
	}

	/**     전체삭제 DAO 
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public int allListDelete(Connection conn) throws Exception {
		int result = 0;
		try {
			String sql = prop.getProperty("allListDelete");
			pstmt = conn.prepareStatement(sql);
			result = pstmt.executeUpdate();
		}finally {
			close(pstmt);
		}
		return result;
	}
    

	// List 삭제 추가
	public int deleteList(Connection conn, String span) throws Exception{
		int result = 0;
		try {
			String sql = prop.getProperty("deleteList");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, span);
			result = pstmt.executeUpdate();
		}finally {
			close(pstmt);
		}
		return result;
	}

}

 

 

--- Todo-sql .xml   파일 추가

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
	<comment>Todo-sql.xml</comment>
	
	<entry key="insertTodo" >
	INSERT INTO TODOLIST 
	VALUES ( TODOLIST_NO_SEQ.NEXTVAL , ? , SYSDATE )
	</entry>
	
	<entry key="allListDelete">
	DELETE FROM TODOLIST
	</entry>
	
	<entry key="deleteList">
	DELETE FROM TODOLIST
	WHERE TESTADDTODO =?
	</entry>
</properties>