자바기반의 웹&앱 응용 SW개발자 양성과정 50일차 -79

 

공개 설정 이용하기.

 

 

 

checked라는 속성을 조건 처리해 공개된 공지사항을 표시해주기

<c:forEach var="n" items="${list}">
<c:set var="open" value="" />
<c:if test="${n.pub}">
<c:set var="open" value="checked" />
</c:if>
<tr>
<td>${n.id}</td>
<td class="title indent text-align-left"><a
href="detail?id=${n.id}">${n.title}></a><span>${n.cmtCount}</span></td>
<td>${n.writerId}</td>
<td><fmt:formatDate pattern="yyyy-MM-dd hh:mm:ss"
value="${n.regdate}" /></td>
<td>${n.hit}</td>

<td><input type="checkbox" name="open-id" ${open}
checked value="${n.id}"></td>
<td><input type="checkbox" name="del-id" value="${n.id}"></td>
</tr>
</c:forEach>

 

사용자도 공개 설정된 공지사항만 볼 수 있도록 하기.

 

사용자 notice/list 쿼리문에 Pub 값이 =1 인 공지글만 보도록 쿼리문을 설정한다.

String sql = "SELECT * FROM(" + 
" SELCT ROWNUM NUM, N.* " 
+ "   FROM (SELECT * FROM NOTICE_VIEW WHERE "+field+" LIKE ? ORDER BY REGDATE DESC) N" + ")" +  
"WHERE PUB = 1 AND NUM BETWEEN ? AND ? ";

 

 

공지사항 일괄 공개하기

 

공개하기 위해 우리는 checked 된 id만 보냈다.

체크된 글이 다시 체크가 해지됬다면 다시 업데이트를 해야 합니다.

 

먼저 등록된 ids를 미리 받아 놓고  전과 후를 비교하면서 업데이트 서비스를 나누도록 합니다.

 

또한 업데이트는 하나의 Transaction처리로 구현하기 위해

하나의 함수로 두 개의 인자를 넘깁니다.

service.pubNoticeAll(opnIds, clsIds);

또한 Service에서 함수 오버 로딩을 통해 여러 가지 id를 받도록 해줍니다.

 

	public int pubNoticeAll(int[] oids, int[] cids) {

		return pubNoticeAll();
	}

	public int pubNoticeAll(List<String> oids, List<String> cids) {

		return pubNoticeAll();
	}
	

	public int pubNoticeAll(String oidsCSV, String cidsCSV) {

		return 0;
	}

CSV: , 가 존재하는 문자열

 

Service

package com.newlec.web.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import com.newlec.web.entity.Notice;
import com.newlec.web.entity.NoticeView;

public class NoticeService {
public int removeNoticeAll(int[] ids) { // 몇개가 삭제됬는지

return 0;
}

public int pubNoticeAll(int[] oids, int[] cids) {

List<String> oidsList = new ArrayList<String>();

for (int i = 0; i < oids.length; i++)
oidsList.add(String.valueOf(oids[i]));

List<String> cidsList = new ArrayList<String>();

for (int i = 0; i < cids.length; i++)
cidsList.add(String.valueOf(cids[i]));

return pubNoticeAll(oidsList, cidsList);
}

public int pubNoticeAll(List<String> oids, List<String> cids) {
String oidsCSV = String.join(",", oids);
String cidsCSV = String.join(",", cids);

return pubNoticeAll(oidsCSV, cidsCSV);
}

public int pubNoticeAll(String oidsCSV, String cidsCSV) {
int result = 0;

String sqlOpen = String.format("UPDATE NOTICE SET PUB = 1 WHERE ID IN(%s)", oidsCSV);
String sqlClose = String.format("UPDATE NOTICE SET PUB = 0 WHERE ID IN(%s)", cidsCSV);
String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
Statement stOpen = con.createStatement();
result += stOpen.executeUpdate(sqlOpen);

Statement stClose = con.createStatement();
result += stClose.executeUpdate(sqlClose);

stOpen.close();
stClose.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}

public int insertNotice(Notice notice) { // 추가됬으면 1 아니면 0을 반환

int result = 0;

String sql = "INSERT INTO NOTICE(TITIE, CONTENT, WRITER_ID, PUB,FILES) VALUES(?,?,?,?,?)";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);
result = st.executeUpdate();

st.setString(1, notice.getTitle());
st.setString(2, notice.getContent());
st.setString(3, notice.getWriterId());
st.setBoolean(4, notice.getPub());
st.setString(5, notice.getFiles());

st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}

public int deleteNotice(int id) { // 삭제됬으면 1 아니면 0

return 0;
}

public int updateNotice(Notice notice) {

return 0;
}

public List<Notice> getNoticeNewestList() {

return null;

}

public List<NoticeView> getNoticeList() {

return getNoticeList("title", "", 1);
}

public List<NoticeView> getNoticeList(int page) {

return getNoticeList("title", "", page);
}

public List<NoticeView> getNoticeList(String field, String query, int page) {

List<NoticeView> list = new ArrayList<NoticeView>();

String sql = "SELECT * FROM(" + " SELCT ROWNUM NUM, N.* " + "   FROM (SELECT * FROM NOTICE_VIEW WHERE " + field
+ " LIKE ? ORDER BY REGDATE DESC) N" + ")" + "WHERE NUM BETWEEN ? AND ? ";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setString(1, "%" + query + "%"); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.
st.setInt(2, 1 + (page - 1) * 10);
st.setInt(3, page * 10);

ResultSet rs = st.executeQuery();

while (rs.next()) {
int id = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
// String content = rs.getString("CONTENT");
Date regdate = rs.getDate("REGDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
int cmtCount = rs.getInt("CMT_COUNT");
boolean pub = rs.getBoolean("PUB");

NoticeView notice = new NoticeView(id, title, writerId,
// content,
regdate, hit, files, cmtCount, pub);

list.add(notice);
}

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return list;
}

public List<NoticeView> getNoticePubList(String field, String query, int page) {
List<NoticeView> list = new ArrayList<NoticeView>();

String sql = "SELECT * FROM(" + " SELCT ROWNUM NUM, N.* " + "   FROM (SELECT * FROM NOTICE_VIEW WHERE " + field
+ " LIKE ? ORDER BY REGDATE DESC) N" + ")" + "WHERE PUB = 1 AND NUM BETWEEN ? AND ? ";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setString(1, "%" + query + "%"); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.
st.setInt(2, 1 + (page - 1) * 10);
st.setInt(3, page * 10);

ResultSet rs = st.executeQuery();

while (rs.next()) {
int id = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
// String content = rs.getString("CONTENT");
Date regdate = rs.getDate("REGDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
int cmtCount = rs.getInt("CMT_COUNT");
boolean pub = rs.getBoolean("PUB");

NoticeView notice = new NoticeView(id, title, writerId,
// content,
regdate, hit, files, cmtCount, pub);

list.add(notice);
}

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return list;
}

public int getNoticeCount() {

return getNoticeCount("title", "");
}

public int getNoticeCount(String field, String query) {

int count = 0;

String sql = "SELECT COUNT(ID) COUNT FROM(" + "   SELCT ROWNUM NUM, N.* "
+ "   FROM (SELECT * FROM NOTICE WHERE " + field + " LIKE ? ORDER BY REGDATE DESC) N" + ")";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setString(1, "%" + query + "%"); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.

ResultSet rs = st.executeQuery();

if (rs.next())
count = rs.getInt("count");

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}

public Notice getNotice(int id) {

Notice notice = null;
String sql = "SELECT * FROM NOTICE WHERE ID=?";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setInt(1, id); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.

ResultSet rs = st.executeQuery();

if (rs.next()) {
int nid = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
String content = rs.getString("CONTENT");
Date regdate = rs.getDate("REGDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
boolean pub = rs.getBoolean("PUB");

notice = new Notice(nid, title, writerId, content, regdate, hit, files, pub);
}

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return notice;
}

public Notice getNextNotice(int id) {
Notice notice = null;
String sql = "SELECT * FROM NOTICE "
+ " WHERE ID = (SELECT ID FROM NOTICE WHERE REGDATE > (SELECT REGDATE FROM NOTICE WHERE ID=?)"
+ "AND ROWNUM = 1 " + ")";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setInt(1, id); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.

ResultSet rs = st.executeQuery();

if (rs.next()) {
int nid = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
String content = rs.getString("CONTENT");
Date regdate = rs.getDate("REGDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
boolean pub = rs.getBoolean("PUB");

notice = new Notice(nid, title, writerId, content, regdate, hit, files, pub);
}

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return notice;
}

public Notice getPrevNotice(int id) {
Notice notice = null;
String sql = "SELECT ID FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC)"
+ " WHERE REGDATE < (SELECT REGDATE FROM NOTICE  WHERE ID=?)" + "AND ROWNUM =1";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
PreparedStatement st = con.prepareStatement(sql);

st.setInt(1, id); // String 은 '' 싱글퀘테이션으로 감싸서 들어가진다.

ResultSet rs = st.executeQuery();

if (rs.next()) {
int nid = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
String content = rs.getString("CONTENT");
Date regdate = rs.getDate("REGDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
boolean pub = rs.getBoolean("PUB");

notice = new Notice(nid, title, writerId, content, regdate, hit, files, pub);
}

rs.close();
st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return notice;
}

public int deleteNoticeAll(int[] ids) {

int result = 0;
String params = "";

for (int i = 0; i < ids.length; i++) {
params += ids[i];
if (i < ids.length - 1)
params += ",";
}
String sql = "DELETE NOTICE WHERE ID IN(" + params + ")";

String url = "jdbc:oracle:thin:@192.168.0.79:1521/xepdb1";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(url, "lec", "111");
Statement st = con.createStatement();
result = st.executeUpdate(sql);

st.close();
con.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}

}

 

 

ListController

package com.newlec.web.controller.admin.notice;


import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.newlec.web.entity.NoticeView;
import com.newlec.web.service.NoticeService;

@WebServlet("/admin/notice/list")
public class ListController extends HttpServlet {


@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String[] openIds = request.getParameterValues("open-id");
String[] delIds = request.getParameterValues("del-id");

String cmd = request.getParameter("cmd");
String ids_ = request.getParameter("ids");
String[] ids = ids_.trim().split(" ");

NoticeService service = new NoticeService();


switch(cmd) {
case "일괄공개":

List<String> oids = Arrays.asList(openIds);


List<String> cids = new ArrayList(Arrays.asList(ids));
cids.removeAll(oids);

service.pubNoticeAll(oids,cids);

break;
case "일괄삭제":

int []ids1 = new int[delIds.length];
for(int i =0; i<delIds.length; i++)
ids1[i]= Integer.parseInt(delIds[i]);

int result = service.deleteNoticeAll(ids1);
break;
}

response.sendRedirect("list");


}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {


String field_ = request.getParameter("f"); //검색목록
String query_ = request.getParameter("q"); //검색어
String page_ = request.getParameter("p"); //현재페이지번호

String field = "title";
if(field_ != null && !field_.equals("") )
field = field_;

String query ="";
if(query_ != null && !query_.equals(""))
query = query_;

int page = 1;
if(page_ != null && !page_.equals(""))
page = Integer.parseInt(page_);

NoticeService service = new NoticeService();
List<NoticeView> list = service.getNoticeList(field,query,page);
int count = service.getNoticeCount(field,query);

request.setAttribute("list", list);
request.setAttribute("count", count);

request.getRequestDispatcher("/WEB-INF/VIEW/admin/board/notice/list.jsp").forward(request, response);
}

}