[AJAX + SPA게시판(API 인터페이스 구축) -> 글쓰기, 수정하기, 삭제하기]
1. write.ajax
** com.command.write > AjaxResultCommand.java
package com.command.write;
import java.io.IOException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.lec.beans.AjaxWriteResult;
public class AjaxResultCommand implements Command {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
AjaxWriteResult result = new AjaxWriteResult();
result.setStatus((String)request.getAttribute("status"));
result.setMessage((String)request.getAttribute("message"));
result.setCount((Integer)request.getAttribute("result"));
ObjectMapper mapper = new ObjectMapper();
try {
String jsonString = mapper.writerWithDefaultPrettyPrinter()
.writeValueAsString(result);
response.setContentType("application/json; charset=utf-8");
response.getWriter().write(jsonString);
} catch(JsonProcessingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} // end execute()
} // end Command
** com.command.write > WriteCommand.java
package com.command.write;
import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.lec.beans.WriteDAO;
import com.sun.org.apache.xerces.internal.impl.xpath.regex.REUtil;
public class WriteCommand implements Command {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
int cnt = 0;
WriteDAO dao = new WriteDAO();
// ajax response 에 필요한 값들
StringBuffer message = new StringBuffer();
String status = "FAIL"; // 기본 FAIL
// 매개변수 받아오기
String name = request.getParameter("name");
String subject = request.getParameter("subject");
String content = request.getParameter("content");
// 유효성 체크
if(name == null || name.trim().length() == 0) {
message.append("[유효하지 않은 parameter : 작성자 필수]");
} else if (subject == null || subject.trim().length() == 0) {
message.append("[유효하지 않은 parameter : 글제목 필수]");
} else {
try {
cnt = dao.insert(subject, content, name);
if(cnt == 0) {
message.append("[트랙잰셕 실패: 0 insert");
} else {
status = "OK";
}
} catch(SQLException e) {
//e.printStackTrace();
message.append("[트랜잭션 에러:" + e.getMessage() + "]");
}
} // end if
request.setAttribute("result", cnt);
request.setAttribute("status", status);
request.setAttribute("message", message.toString());
} // end execute()
} // end Command
2. update.ajax
** com.command.write > UpdateCommand.java
package com.command.write;
import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.lec.beans.WriteDAO;
public class UpdateCommand implements Command {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
int cnt = 0;
WriteDAO dao = new WriteDAO();
// ajax response 에 필요한 값들
StringBuffer message = new StringBuffer();
String status = "FAIL"; // 기본 FAIL
//입력한 값을 받아오기
String param = request.getParameter("uid");
String subject = request.getParameter("subject");
String content = request.getParameter("content");
// 유효성 체크
if(param == null) {
message.append("[유효하지 않은 parameter 0 or null]");
} else if (subject == null || subject.trim().length() == 0) {
message.append("[유효하지 않은 parameter : 글제목 필수]");
} else {
try {
int uid = Integer.parseInt(param);
cnt = dao.update(uid, subject, content);
status = "OK";
if(cnt == 0) {
message.append("[0 update]");
}
} catch (SQLException e) {
//e.printStackTrace();
message.append("[트랜잭션 에러:" + e.getMessage() + "]");
} catch (Exception e) {
message.append("[유효하지 않은 parameter] " + param);
}
} // end if
request.setAttribute("result", cnt);
request.setAttribute("status", status);
request.setAttribute("message", message.toString());
} // end execute()
} // end Command
3. delete.ajax
: 여러 개의 게시글 지우는 트랜잭션 추가
** com.lec.beans > WriteDAO.java
package com.lec.beans;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import common.D;
// DAO : Data Access Object
// DB 에 접속하여 트랜잭션을 수행하는 객체
// 다루는 데이터 소스의 종류에 따라 DAO는 여러개 정의 사용 가능
public class WriteDAO {
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs = null; // SELECT 결과, executeQuery()
// DAO 객체가 생성될때 Connection 도 생성된다.
public WriteDAO() {
try {
Class.forName(D.DRIVER);
conn = DriverManager.getConnection(D.URL, D.USERID, D.USERPW);
System.out.println("WriteDAO 생성, 데이터 베이스 연결!");
} catch(Exception e) {
e.printStackTrace();
// throw e;
}
} // 생성자
// DB 자원 반납 메소드,
public void close() throws SQLException {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} // end close()
// 새글 작성 <-- DTO
public int insert(WriteDTO dto) throws SQLException {
String subject = dto.getSubject();
String content = dto.getContent();
String name = dto.getName();
int cnt = this.insert(subject, content, name);
return cnt;
}
// 새글 작성 <-- 제목, 내용, 작성자
public int insert(String subject, String content, String name) throws SQLException {
int cnt = 0;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_INSERT);
pstmt.setString(1, subject);
pstmt.setString(2, content);
pstmt.setString(3, name);
cnt = pstmt.executeUpdate();
} finally {
close();
}
return cnt;
}
// ResultSet --> DTO 배열로 리턴
public WriteDTO [] createArray(ResultSet rs) throws SQLException {
WriteDTO [] arr = null; // DTO 배열
ArrayList<WriteDTO> list = new ArrayList<WriteDTO>();
while(rs.next()) {
int uid = rs.getInt("wr_uid");
String subject = rs.getString("wr_subject");
String name = rs.getString("wr_name");
String content = rs.getString("wr_content");
int viewCnt = rs.getInt("wr_viewcnt");
Date d = rs.getDate("wr_regdate");
Time t = rs.getTime("wr_regdate");
String regDate = "";
if(d != null){
regDate = new SimpleDateFormat("yyyy-MM-dd").format(d) + " "
+ new SimpleDateFormat("hh:mm:ss").format(t);
}
WriteDTO dto = new WriteDTO(uid, subject, content, name, viewCnt);
dto.setRegDate(regDate);
list.add(dto);
} // end while
int size = list.size();
if(size == 0) return null;
arr = new WriteDTO[size];
list.toArray(arr); // List -> 배열
return arr;
}
// 전체 SELECT
public WriteDTO [] select() throws SQLException {
WriteDTO [] arr = null;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_SELECT);
rs = pstmt.executeQuery();
arr = createArray(rs);
} finally {
close();
}
return arr;
} // end select()
// 페이징 관련
// 몇번째 from 부터 몇개 rows 를 SELECT
public WriteDTO [] selectFromRow(int from, int rows) throws SQLException {
WriteDTO [] arr = null;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_SELECT_FROM_ROW);
pstmt.setInt(1, from);
pstmt.setInt(2, from + rows);
rs = pstmt.executeQuery();
arr = createArray(rs);
} finally {
close();
} // end try
return arr;
} // end selectFromRow()
// 전체 글의 개수
public int countAll() throws SQLException {
int cnt = 0;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_COUNT_ALL);
rs = pstmt.executeQuery();
rs.next();
cnt = rs.getInt(1);
} finally {
close();
} // end try
return cnt;
} // countAll()
// 특정 uid 의 글 내용 읽기, 조회수 증가
// viewCnt 도 1 증가 해야 하고, 글 읽어와야 한다 --> 트랜잭션 처리
public WriteDTO [] readByUid(int uid) throws SQLException{
int cnt = 0;
WriteDTO [] arr = null;
try {
// 트랜잭션 처리
// Auto-commit 비활성화
conn.setAutoCommit(false);
// 쿼리들 수행
pstmt = conn.prepareStatement(D.SQL_WRITE_INC_VIEWCNT);
pstmt.setInt(1, uid);
cnt = pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement(D.SQL_WRITE_SELECT_BY_UID);
pstmt.setInt(1, uid);
rs = pstmt.executeQuery();
arr = createArray(rs);
conn.commit();
} catch(SQLException e) {
conn.rollback();
throw e;
} finally {
close();
}
return arr;
} // end readByUid()
// 특정 uid 의 글 만 SELECT (조회수 증가 없슴!)
public WriteDTO [] selectByUid(int uid) throws SQLException {
WriteDTO [] arr = null;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_SELECT_BY_UID);
pstmt.setInt(1, uid);
rs = pstmt.executeQuery();
arr = createArray(rs);
} finally {
close();
}
return arr;
}
// 특정 uid 의 글 수정 (제목, 내용)
public int update(int uid, String subject, String content) throws SQLException {
int cnt = 0;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_UPDATE);
pstmt.setString(1, subject);
pstmt.setString(2, content);
pstmt.setInt(3, uid);
cnt = pstmt.executeUpdate();
} finally {
close();
}
return cnt;
} // end update()
// 특정 uid 글 삭제하기
public int deleteByUid(int uid) throws SQLException {
int cnt = 0;
try {
pstmt = conn.prepareStatement(D.SQL_WRITE_DELETE_BY_UID);
pstmt.setInt(1, uid);
cnt = pstmt.executeUpdate();
} finally {
close();
}
return cnt;
} // end deleteByUid()
// 특정 uid 글(들)을 삭제하기
public int deleteByUid(int[] uids) throws SQLException {
if(uids == null || uids.length == 0) {return 0;}
int cnt = 0;
try {
StringBuffer sql = new StringBuffer("DELETE FROM test_write WHERE wr_uid IN (");
for(int uid : uids) {
sql.append(uid + ","); // 콤마는 좌우의 공백 빼줘야 함
}
sql.deleteCharAt(sql.lastIndexOf(",")); // 맨 끈의 콤마 삭제
sql.append(")");
stmt = conn.createStatement();
cnt = stmt.executeUpdate(sql.toString());
} finally {
close();
} // end try
return cnt;
} // end deleteByUid()
} // end DAO
** com.command.write > DeleteCommand
package com.command.write;
import java.sql.SQLException;
import java.util.Arrays;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.lec.beans.WriteDAO;
public class DeleteCommand implements Command {
@Override
public void execute(HttpServletRequest request, HttpServletResponse response) {
int cnt = 0;
WriteDAO dao = new WriteDAO();
// ajax response 에 필요한 값들
StringBuffer message = new StringBuffer();
String status = "FAIL"; // 기본 FAIL
// 유효성 검증
String[] params = request.getParameterValues("uid");
int[] uids = null;
if(params == null || params.length == 0) {
message.append("[유효하지 않은 parameter 0 or null]");
} else {
uids = new int[params.length];
try {
for(int i = 0; i < params.length; i++) {
uids[i] = Integer.parseInt(params[i]);
}
cnt = dao.deleteByUid(uids);
status = "OK";
} catch (SQLException e) {
//e.printStackTrace();
message.append("[트랜잭션 에러 : " + e.getMessage() + "]");
} catch (Exception e) {
message.append("[유효하지 않은 parameter]" + Arrays.toString(params));
}
} // end if
request.setAttribute("result", cnt);
request.setAttribute("status", status);
request.setAttribute("message", message.toString());
} // end execute()
} // end Command'웹_프론트_백엔드 > JAVA프레임윅기반_풀스택' 카테고리의 다른 글
| 2020.06.10 (0) | 2020.06.10 |
|---|---|
| 2020.06.09 (0) | 2020.06.09 |
| 2020.06.05 (0) | 2020.06.05 |
| 2020.06.03 (0) | 2020.06.03 |
| 2020.06.02 (0) | 2020.06.02 |