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

기업형 애플리케이션 컨트롤러와 서비스 함수 분리하기.

 

1. 공지사항 게시글 컨트롤러 코드

package com.newlec.web.controller;

import java.io.IOException;
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.Notice;
import com.newlec.web.service.NoticeService;

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


@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
NoticeService service = new NoticeService();
List<Notice> list = service.getNoticeList();

request.setAttribute("list", list);

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

}

 

service 함수를 따로 만들어 컨트롤러에서는 객체를 이용해 model을 view에게 전달한다.

 

 

NoticeService()에서는 JDBC를 이용해 값을 가져와 세팅을 하여 넘겨준다.

 

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

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

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

 

기본적인 공지사항 게시판을 먼저 작성을 할 것인데

 

1번째 조건은 기본적으로 10개의 글을 노출시키며

 

2번째 조건은 검색의 기본값으로 title을 검색하도록한다

 

preparedStatement를 사용해? 에 값을 넣어줄 것이다.

만약 값이 String값이라면 자동적으로 싱클 퀘테이션이 붙으므로 변수인 field를?로 넣게 되면 오류가 발생한다.

따라서 +연산자를 통해 쿼리문을 작성해주며

 

st.setString(1, "%"+query+"%") 검색한 title에 포함되어있는 검색어이다. 

st.setInt(2,1+(page-1)*10) 1,11,21의 등차수열 일반식이다.

st.setInt(3, page*10); 마지막 10 ,20, 30인 숫자이다.

 

전체 코드.

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.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.newlec.web.entity.Notice;

public class NoticeService {

public List<Notice> getNoticeList() {

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

public List<Notice> getNoticeList(int page) {

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

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

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

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


//1,11,21  -> 1 + (n-1)10
//10,20 page*10
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");

Notice notice = new Notice(id, title, writerId, content, regdate, hit, files);
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) {
String sql = "SELECT * FROM(" + "   SELCT ROWNUM NUM, N.* "
+ "   FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) N" + ")" + "WHERE NUM BETWEEN 1 AND 5 ";
return 0;
}

public Notice getNotice(int id) {
String sql = "SELECT * FROM NOTICE WHERE ID=?";
return null;
}

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

return null;
}

public Notice getPrevNotice(int id) {

String sql = "SELECT ID FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC)"
+ " WHERE REGDATE < (SELECT REGDATE FROM NOTICE  WHERE ID=?)" + "AND ROWNUM =1";
return null;
}
}

 

나머지 service함수 채우기 

쿼리식만 다르고 JDBC를 이용하는 것은 비슷합니다.

따라서 전체 코드를 게시하겠습니다.

 

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.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.newlec.web.entity.Notice;

public class NoticeService {

public List<Notice> getNoticeList() {

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

public List<Notice> getNoticeList(int page) {

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

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

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

String sql = "SELECT * FROM(" +
" SELCT ROWNUM NUM, N.* "
+ "   FROM (SELECT * FROM NOTICE 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");

Notice notice = new Notice(id, title, writerId, content, regdate, hit, files);
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();

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");

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

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");

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

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");

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

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;
}
}