JDBC 10 - 최종 결과물

실행 MAIN클래스

package ex1;
import java.sql.SQLException;
import com.newlecture.app.console.NoticeConsole;

public class Program5 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		NoticeConsole console= new NoticeConsole();
		//int page;
		EXIT:
			while(true) {
				console.printNoticeList();
				int menu = console.inputNoticeMenu();
				
				switch(menu) {
				case 1:	// 상세조회
					break;
				case 2:	// 이전
					//page--;
					console.movePrevList();
					break;
				case 3:	// 다음
					//page++
					console.moveNextList();
					break;
				case 4: // 글쓰기
					break;
				case 5: // 검색
					console.inputSearchWord();
					break;
				case 6:
					System.out.print("bye");
					break EXIT;
				default:
					System.out.println("잘못된 번호");
					break;
				}
			}
	}
}

Notice 클래스

package com.newlecture.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 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;
	}
	public Notice() {
		
	}
	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;
	}
}

 

NoticeService 클래스 ( DB조작 관련 메소드 )

package com.newlecture.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.newlecture.app.entity.Notice;

public class NoticeService {
	private String url = "jdbc:oracle:thin:@localhost:1521/xepdb1";
	private String uid = "test";
	private String pwd = "1111";
	private String driver = "oracle.jdbc.driver.OracleDriver";
	
	public List<Notice> getList(int page, String field, String query) throws ClassNotFoundException, SQLException{
		int start = 1+(page-1)*10;		// 1, 11, 21, 31, ..
		int end =10*page;   // 10, 20, 30, 40...	
		String sql = "SELECT * FROM NOTICE_VIEW WHERE "+field+" LIKE ? AND NUM BETWEEN ? AND ?";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, uid, pwd);
		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("FILES");
			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;
	}
	public int getCount() throws ClassNotFoundException, SQLException {
		int count = 0;
		String sql = "SELECT COUNT(ID) COUNT FROM NOTICE";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, uid, pwd);
		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"
				+ ") VALUES (?,?,?)";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, uid, pwd);
//		Statement st = con.createStatement();
//		미리 시퀄문을 준비해서 값을 준비하여 바로 실행만 할 수있도록 준비
		PreparedStatement st = con.prepareStatement(sql);
		st.setString(1, title);
		st.setString(2, writerId);
		st.setString(3, content);
		
		int result = st.executeUpdate();
		
		System.out.println(result);
		st.close();
		con.close();
		return result;
	}
	public int update(Notice notice) throws ClassNotFoundException, SQLException {
		String title = notice.getTitle();
		String writerId = notice.getWriterId();
		String content = notice.getContent();
		int id = 255;

		String sql = "UPDATE NOTICE "
				+ "SET"
				+ "    TITLE=?,"
				+ "    WRITER_ID=?"
				+ "    CONTENT=?"
				+ "WHERE ID=?";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, uid, pwd);
//		Statement st = con.createStatement();
//		미리 시퀄문을 준비해서 값을 준비하여 바로 실행만 할 수있도록 준비
		PreparedStatement st = con.prepareStatement(sql);
		st.setString(1, title);
		st.setString(2, writerId);
		st.setString(3, content);
		st.setInt(4, id);
		
		int result = st.executeUpdate();
		
		System.out.println(result);
		st.close();
		con.close();
		
		return result;
	}
	public int delete(int id) throws ClassNotFoundException, SQLException {

		String sql = "DELETE NOTICE WHERE ID=?";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url, uid, pwd);
		PreparedStatement st = con.prepareStatement(sql);
		st.setInt(1, id);
		
		int result = st.executeUpdate();
		
		System.out.println(result);
		st.close();
		con.close();
		return result;
	}
}

 

NoticeConsole 클래스 (VIEW 영역)

package com.newlecture.app.console;

import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

import com.newlecture.app.entity.Notice;
import com.newlecture.app.service.NoticeService;

public class NoticeConsole {

	private NoticeService service;
	private int page;
	private String searchWord;
	private String searchField;
	
	public NoticeConsole() {
		service = new NoticeService();
		page = 1;
		searchWord = "";
		searchField = "TITLE";

	}
	public void printNoticeList() throws ClassNotFoundException, SQLException {
		List<Notice> list = service.getList(page, searchField, searchWord);
		//멤버 변수로 쓰는건 바람직하지않다
		//매번 값이 달라 저야 하기 때문에 지역 변수로 설정
		int count = service.getCount();
		int lastPage = count/10;
		lastPage = count%10 > 0 ? lastPage+1 : lastPage;
		System.out.println("----------------------------------");
		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.printf("           %d/%d pages \n", page, lastPage);
	}
	
	public int inputNoticeMenu() {
		Scanner sc = new Scanner(System.in);
		System.out.print("1. 상세조회/ 2.이전/ 3.다음/ 4.글쓰기/ 5.검색/ 6.종료 >");
		String menu_ =  sc.nextLine();
		int menu = Integer.parseInt(menu_);
		
		return menu;
	}
	public void movePrevList() {
		if(page == 1) {
			System.out.println("---------------------");
			System.out.println("이전 페이지가 없습니다.");
			System.out.println("---------------------");
			return;
		}
		page--;
	}
	public void moveNextList() throws ClassNotFoundException, SQLException {
		int count = service.getCount();
		int lastPage = count/10;
		lastPage = count%10 > 0 ? lastPage+1 : lastPage;
		if(page == lastPage) {
			System.out.println("---------------------");
			System.out.println("다음 페이지가 없습니다.");
			System.out.println("---------------------");
			return;
		}
		page++;
		
	}
	public void inputSearchWord() {
		Scanner sc = new Scanner(System.in);
		System.out.println("검색 범주(title/content/writerId)중에 하나를 입력하세요.");
		System.out.print(">");
		//검색하기 위한 필드어(title, id ,....)
		searchField = sc.nextLine();
		
		System.out.print("검색어> ");
		//다른메소드(printNoticeList)가 쓸내용이기 떄문에 멤버변수로 설정
		//검색어
		searchWord = sc.nextLine();
		
	}
}