75강 getNoticeList JDBC 코드 구현하기

1.PostListController (실제 입출력 제어 클래스) 가 실행되면

Post Service클래스 getNoticList메서드를 불러 온다.

 

 

@WebServlet("/PostListController")
public class PostListController extends HttpServlet {

private static List list;

private static final long serialVersionUID = 1L;

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

// 임시 변수
String writer_ = request.getParameter("writer");
String title_ = request.getParameter("title");
String search_ = request.getParameter("search");
String page_ = request.getParameter("p");

// 기본값 넣어주기
String writer = "writerid";
String title = "posttile";
String search = "";
String page = "1";
int count = 0;

PostService service = new PostService();

list = service.getPostList(); // 기본값 게시글 모두 출력
count = service.getPostCount(); //기본값 게시글 수 모두 출력

// null 체크
if (search_ != null)
search = search_;

if (page_ != null)
page = page_;

//제목 또는 작성자를 눌렀는지 확인
if (writer_ != null) {
writer = "writerid";

list = service.getPostList(writer, search, Integer.parseInt(page));
count = service.getPostCount(writer, search);
} else if (title_ != null) {
title = "posttile";

list = service.getPostList(title, search, Integer.parseInt(page));
count = service.getPostCount(title, search);
}

 

 

,

2.Post Service 클래스 getNoticeList 메서드(JDBC 구현)

 

public List getPostList(String field, String query, int page) {

// =============================게시글 5개씩
// 출력하기===================================//


List list = new ArrayList<>();

String sql = "SELECT * FROM(" + " SELECT ROWNUM NUM, N.* FROM (SELECT * FROM POST WHERE " + field
+ " LIKE ? ORDER BY FIRSTDATE DESC) N " + ") " + "WHERE NUM  BETWEEN ? AND ?"; // 원하는 질의문 (SQL)

String url = "jdbc:oracle:thin:@localhost:1521/xepdb1"; // 해당 DB(Oracle) 경로

try {

Class.forName("oracle.jdbc.driver.OracleDriver");// 오라클 드라이버 로드시키기
Connection con = DriverManager.getConnection(url, "SUNGMAN", "????"); // 해당 오라클 접속
PreparedStatement stmt = con.prepareStatement(sql);

stmt.setString(1, "%" + query + "%");
// 인덱스 1번째 부터 페이지 보이기
stmt.setInt(2, 1 + (page - 1) * 5);
stmt.setInt(3, page * 5);

ResultSet rs = stmt.executeQuery(); // 해당 SQL에명령어 대한 DB의 반환 값을 rs에 담아라.

while (rs.next()) {

String postid = rs.getString("POSTID");
String writerid = rs.getString("WRITERID");
String postcontent = rs.getString("POSTCONTENT");
String kateid = rs.getString("KATEID");
Date lastdate = rs.getDate("LASTDATE");
String posttile = rs.getString("POSTTILE");
Date firstdate = rs.getDate("FIRSTDATE");
int hit = rs.getInt("HIT");
String files = rs.getString("FILES");
String vip = rs.getString("VIP");

Post notice = new Post(postid, writerid, postcontent, kateid, lastdate, posttile, firstdate, hit, files,
vip);

// array리스트화
list.add(notice);

}

con.close();

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

return list;
}