package com.sorrel012.app.entity;
import java.util.Date;
public class Notice {
private int id;
private String title;
private String writerId;
private Date regDate;
private String content;
private int hit;
public Notice(int id, String title, String writerId, Date regDate, String content, int hit) {
this.id = id;
this.title = title;
this.writerId = writerId;
this.regDate = regDate;
this.content = content;
this.hit = hit;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getWriterId() {
return writerId;
}
public void setWriterId(String writerId) {
this.writerId = writerId;
}
public Date getRegDate() {
return regDate;
}
public void setRegDate(Date regDate) {
this.regDate = regDate;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
}
package com.sorrel012.app.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.Date;
import java.util.List;
import com.sorrel012.app.entity.Notice;
public class NoticeService {
private String url = "jdbc:oracle:thin:@localhost:1521/xe";
private String driver = "oracle.jdbc.driver.OracleDriver";
private String userId = "SORREL";
private String userPwd = "";
public List<Notice> getList(int page, String field, String query) throws SQLException, ClassNotFoundException {
int start = 1 + (page-1) * 3;
int end = 3 * page;
String sql = "SELECT * FROM NOTICE_VIEW WHERE " + field + " LIKE ? AND NUM BETWEEN ? AND ?";
Class.forName(this.driver);
Connection con = DriverManager.getConnection(this.url, this.userId, this.userPwd);
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "%"+query+"%");
st.setInt(2, start);
st.setInt(3, end);
ResultSet rs = st.executeQuery();
List<Notice> list = new ArrayList<Notice>();
while(rs.next()) {
int id = rs.getInt("ID");
String title = rs.getString("TITLE");
String writerId = rs.getString("WRITER_ID");
Date regDate = rs.getDate("REGDATE");
String content = rs.getString("CONTENT");
int hit = rs.getInt("hit");
Notice notice = new Notice(id, title, writerId, regDate, content, hit);
list.add(notice);
}
rs.close();
st.close();
con.close();
return list;
}
//Scalar
public int getCount() throws ClassNotFoundException, SQLException {
int count = 0;
String sql = "SELECT COUNT(ID) COUNT FROM NOTICE_VIEW";
Class.forName(this.driver);
Connection con = DriverManager.getConnection(this.url, this.userId, this.userPwd);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
List<Notice> list = new ArrayList<Notice>();
if(rs.next()) {
count = rs.getInt("COUNT");
}
rs.close();
st.close();
con.close();
return count;
}
public int insert(Notice notice) throws ClassNotFoundException, SQLException {
String title = notice.getTitle();
String writerId = notice.getWriterId();
String content = notice.getContent();
String sql = "INSERT INTO NOTICE (TITLE, WRITER_ID, CONTENT, FILES) "
+ "VALUES (?, ?, ?)";
Class.forName(this.driver);
Connection con = DriverManager.getConnection(this.url, this.userId, this.userPwd);
PreparedStatement st= con.prepareStatement(sql);
st.setString(1, title);
st.setString(2, writerId);
st.setString(3, content);
int result = st.executeUpdate();
st.close();
con.close();
return result;
}
public int update(Notice notice) throws SQLException, ClassNotFoundException {
String title = notice.getTitle();
String content = notice.getContent();
String sql = "UPDATE NOTICE SET TITLE = ?, CONTENT = ? WHERE WRITER_ID = 'sorrel012'";
Class.forName(this.driver);
Connection con = DriverManager.getConnection(this.url, this.userId, this.userPwd);
PreparedStatement st= con.prepareStatement(sql);
st.setString(1, title);
st.setString(2, content);
int result = st.executeUpdate();
st.close();
con.close();
return result;
}
public int delete(int id) throws SQLException, ClassNotFoundException {
String sql = "DELETE NOTICE WHERE ID = ?";
Class.forName(this.driver);
Connection con = DriverManager.getConnection(this.url, this.userId, this.userPwd);
PreparedStatement st= con.prepareStatement(sql);
st.setInt(1, id);
int result = st.executeUpdate();
System.out.println(result);
st.close();
con.close();
return result;
}
}
package ex1;
import java.sql.SQLException;
import com.sorrel012.app.console.NoticeConsole;
public class Program6 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
NoticeConsole console = new NoticeConsole();
EXIT:
while(true) {
console.printNoticeList(); //공지사항 목록 출력
int menu = console.inputNoticeMenu(); //메뉴 입력 받기
switch(menu) {
case 1: //이전
console.movePrevList();
break;
case 2: //다음
console.moveNextList();
break;
case 3: //검색
console.inputSearchWord();
break;
case 4: //종료
break EXIT;
default:
System.out.println("1~4 사이의 숫자를 입력해 주세요.\n");
}
}
System.out.println("프로그램을 종료합니다.");
} //main
}
package com.sorrel012.app.console;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;
import com.sorrel012.app.entity.Notice;
import com.sorrel012.app.service.NoticeService;
public class NoticeConsole {
private NoticeService service;
private int page;
private int lastPage;
private String searchWord;
private String searchField;
public NoticeConsole() {
this.service = new NoticeService();
this.page = 1;
this.searchWord = "";
this.searchField = "TITLE"; //에러 발생 방지
}
public void printNoticeList() throws ClassNotFoundException, SQLException {
List<Notice> list = service.getList(this.page, this.searchField, this.searchWord);
int count = service.getCount();
this.lastPage = (count % 3 == 0) ? count/3 : count/3 + 1;
System.out.println("\n────────────────────────────────────────────────────────");
System.out.printf("<공지사항> 총 %d개의 게시글\n", count);
System.out.println("────────────────────────────────────────────────────────");
for(Notice n : list) {
System.out.printf("%d. %s / %s / %s\n"
, n.getId()
, n.getTitle()
, n.getWriterId()
, n.getRegDate());
}
System.out.println("────────────────────────────────────────────────────────");
System.out.printf("%d/%d pages\n", this.page, lastPage);
System.out.println("────────────────────────────────────────────────────────\n");
}
public int inputNoticeMenu() {
Scanner scan = new Scanner(System.in);
System.out.print("1.이전 /2.다음 /3.검색 /4.종료 : " );
return Integer.parseInt(scan.nextLine());
}
private boolean checkMenu(String menu_) {
int menu = Integer.parseInt(menu_);
if(menu >= 1 && menu <= 4) {
return true;
} else {
return false;
}
}
public void movePrevList() {
if(this.page > 1) {
this.page--;
} else {
System.out.println("이전 페이지가 없습니다.");
}
}
public void moveNextList() {
if(this.page < this.lastPage) {
this.page++;
} else {
System.out.println("다음 페이지가 없습니다.");
}
}
public void inputSearchWord() {
Scanner scan = new Scanner(System.in);
System.out.print("\n검색 범주(title/content/writerid) 중 하나를 입력하세요. : ");
this.searchField = scan.nextLine();
System.out.print("검색어를 입력하세요. : ");
this.searchWord = scan.nextLine();
}
}
