뉴렉처 JDBC 예제 코드

NoticeService

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

// CRUD를 담당하는 서비스 클래스 생성하기
public class NoticeService {
	
	private String url = "jdbc:oracle:thin:@localhost:1521/xe";
	private String uid = "사용자 이름";
	private String pwd = "비밀번호";
	private String driver = "oracle.jdbc.driver.OracleDriver";
	
	// 객체를 반환하기 위해 Notice으로 목록을 만듬
	// 그전에는 그냥 Notice가 달라고 했는데 이제는 page를 줌
	// 무엇을 기준으로 검색할건지(제목, 작성자 등)를 field에 저장
	// 실제로 검색할 검색어를 query에 저장
	public List<Notice> getList(int page, String field,String query) throws ClassNotFoundException, SQLException{
		
		// 10개 단위로 출력
		int start = 1+(page-1)*10;//a1+(n-1)*d;	//1, 11, 21 ...
		int end = 10*page;	//10 20 30 ...
		
		// 뷰를 생성했기 때문에 복잡하게 사용하지 않고 아래의 sql 사용
//		String sql = "SELECT * FROM ("
//				+ "    SELECT ROWNUM NUM, N.* FROM ("
//				+ "        SELECT * FROM NOTICE ORDER BY REGDATE DESC"
//				+ "    ) N "
//				+ ") "
//				+ "WHERE NUM BETWEEN ? AND ?";

		String sql = "SELECT * FROM NOTICE_VIEW WHERE "+field+" LIKE ? AND NUM BETWEEN ? AND ?";

		// 매번 실행할 때마다 따로 있어야 함
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
        
		// 값이 ?가 되면서 Statement에서 PreparedStatement가 됨
		// sql을 여기서 전달하므로 executeQuery의 sql은 지워줌
		PreparedStatement st = con.prepareStatement(sql);
        
		st.setString(1, "%"+query+"%");
		st.setInt(2, start);
		st.setInt(3, end);
        
		// ?로 라면 TITLE이 아닌 'TITLE' 형식으로 들어가기 때문에 에러 발생
//		st.setString(1, field);

		ResultSet rs = st.executeQuery();
		
		// 반환하기 위한 준비
		List<Notice> list = new ArrayList<Notice>();
		
		// next()가 값을 정상적으로 가져왔으면 true, 그렇지 않으면 false
		// 가져온게 없으면 false니까 {} 안에는 실행되지 않음
		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");
		String files = rs.getString("Files");
		
		// Notice 객체 생성
		Notice notice = new Notice(id, title, writerId, regDate, content, hit, files);
		
		// 리스트에 Notice를 추가
		list.add(notice);
		
	}
		
		rs.close();
		st.close();
		con.close();
		
		return list;
		
	}
	
	// 단일 값을 얻어오는 함수(Scalar)
	public int getCount() throws SQLException, ClassNotFoundException {
		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>();
		
		// 결과집합에서 가져온게 있으면 count에 넣고 없으면 기본값인 0이 반환
		if(rs.next())
		count =rs.getInt("COUNT");
		
		rs.close();
		st.close();
		con.close();
		
		return count;
	}
	
	public int insert(Notice notice) throws SQLException, ClassNotFoundException {
		
		String title = notice.getTitle();
		String writerId ="corine";
		String content= notice.getWriterId();
		String file=notice.getFiles();
		
		String url = "jdbc:oracle:thin:@localhost:1521/xe";
		String sql = "INSERT INTO notice ("
				+ "    title,"
				+ "    writer_id,"
				+ "    content,"
				+ "    files"
				+ ") VALUES (?,?,?,?)";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement st = con.prepareStatement(sql);
		st.setString(1, title);
		st.setString(2, writerId);
		st.setString(3, content);
		st.setString(4, file);

		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 file = notice.getFiles();
		int id = notice.getId();
		
		String url = "jdbc:oracle:thin:@localhost:1521/xe";
		String sql = "update NOTICE "
				+ "SET"
				+ "    TITLE=?,"
				+ "    CONTENT=?,"
				+ "    FILES=?"
				+ "WHERE ID=?";
		
		Class.forName(driver);
		Connection con = DriverManager.getConnection(url,uid,pwd);
		PreparedStatement st = con.prepareStatement(sql);
		st.setString(1, title);
		st.setString(2, content);
		st.setString(3, file);
		st.setInt(4, id);

		int result = st.executeUpdate();
		
		st.close();
		con.close();
		
		return result;
	}
	
	public int delete(int id) throws ClassNotFoundException, SQLException {
		
		String url = "jdbc:oracle:thin:@localhost:1521/xe";
		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();

		st.close();
		con.close();
		
		return result;
	}

}

ROWNUM이란 순번은 매기고 싶거나 특정 개수만큼 원하는 데이터를 출력허고 싶을 때 사용하는 것을 말한다.

// 잘못된 코드
SELECT * FROM (SELECT ROWNUM NUM, NOTICE.* FROM NOTICE ORDER BY REGDATE DESC)
WHERE NUM BETWEEN 1 AND 10;

이때 ORDER BY와 ROWNUM을 같이 사용할 때 주의할 점이 있다.

위의 코드는 ROWNUM을 구한 상태에서 정렬이 이루어지고 있기 때문에 잘못된 코드이다.

정렬된 상태에서 ROWNUM을 뽑아야하기 때문에 한 번 더 서브 쿼리(INNER)를 아래와 같이 사용해줘야 한다.

// 올바른 코드(NUM은 별칭)
// 5번 줄에서 NOTICE의 모든 컬럼을 보여달라고 했는데
// 6번 줄이 서브 쿼리이기 때문에 더 이상 NOTICE가 아님 -> 따라서 N이라고 별칭
SELECT * FROM (
    SELECT ROWNUM NUM, N.* FROM (
        SELECT * FROM NOTICE ORDER BY REGDATE DESC
    ) N
)
WHERE NUM BETWEEN 1 AND 10;

그리고 VIEW를 사용한다면 코드가 간결해진다. 

CREATE VIEW NOTICE_VIEW
AS
SELECT * FROM (
    SELECT ROWNUM NUM, N.* FROM (
        SELECT * FROM NOTICE ORDER BY REGDATE DESC
    ) N
)
WHERE NUM BETWEEN 1 AND 10;

위와 같이 VIEW를 생성함으로써 VIEW가 첫 번째 SELECT부터 WHRE 전까지의 괄호를 대신한다.

 

※ VIEW 생성시 다음과 같은 에러 발생 시 참고 ※

에러)

ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

 

해결 방법) cmd 창에 다음을 입력

▶ ▷ sqlplus

▶ ▷ Enter user-name: sys as sysdba 후 비밀번호 입력

▶ ▷ SQL> GRANT CREATE ANY TABLE TO 사용자 이름;
▶ ▷ SQL> GRANT CREATE VIEW TO 사용자 이름;

 

Notice

package com.jdbc.app.entity;

import java.util.Date;

// 값을 담을 수 있는 클래스
public class Notice {
	
	// 구조가 노출되지 않도록 private 이용
	private int id;
	private String title;
	private String writerId;
	private Date regDate;
	private String content;
	private int hit;
	private String files;
	
	// 객체 생성과 동시에 값을 바로 전달하기 위한 생성자
	public Notice() {
		
	}
	
	// 필드
	public Notice(int id, String title, String writerId, Date regDate, String content, int hit, String files) {
		this.id = id;
		this.title = title;
		this.writerId = writerId;
		this.regDate = regDate;
		this.content = content;
		this.hit = hit;
		this.files = files;
	}

	// 값을 세팅하기 위한 getter, setter
	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 String getFiles() {
		return files;
	}
	public void setFiles(String files) {
		this.files = files;
	}
	
}

 

NoticeConsole

package com.jdbc.app.console;

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

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

public class NoticeConsole {
	
	// 데이터 서비스를 위한 멤버변수
	private NoticeService service;
	// 상태 변수(현재 페이지)
	private int page;
	private String searchField;
	private String searchWord;
		
	// 생성자
	public NoticeConsole() {
		// 13번 줄에서 데이터 타입 설정
		service = new NoticeService();
		// 15번 줄에서 데이터 타입 설정
		page = 1;
		searchField="TITLE";
		searchWord="";
	}

	public void printNoticeList() throws ClassNotFoundException, SQLException {
		// page 하기 전에는 모든 목록이 나왔는데 이제 10개씩 최신순으로 끊어서 출력
//		List<Notice> list = service.getList(2);

		// 직접 숫자를 넣는게 아니라 이전, 다음을 사용하여 페이지 이동
		List<Notice> list = service.getList(page, searchField, searchWord);
        
		// count 값 구하기(현재 db의 테이블에서 Notice가 총 몇 개를 들고있는가)
		// count는 list를 구할때마다 count를 매번 다시 구해야함
		// list를 구할때마다 count가 달라지기 때문에 멤버 변수로 적합x(공유x) -> 지역 변수
		// 게시글 전체 개수(Notice의 개수)
		int count = service.getCount();
        
		// 전체 페이지 
		int lastPage = count/10;	// 100/10 -> 10, 93/10 -> 9
        
		lastPage = count%10 == 0?lastPage:lastPage+1;
//		lastPage = count%10 > 0?lastPage+1:lastPage; 45번 줄과 같은 의미

		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.println("─────────────────────────────────────");
		System.out.printf("              %d/%d pages\n", page, lastPage);
	}

	public int inputNoticeMenu() {
		Scanner scan = new Scanner(System.in);
		System.out.println("1.상세조회/ 2.이전/ 3.다음/ 4.글쓰기/ 5.검색/ 6.종료>");
        
		// 임시 변수
		String menu_ = scan.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:lastPage+1;
        
		// lastPage가 지역 변수인데 여기서 쓰려면 전역 변수(멤버 변수)가 되어야 하는거 아닌가?
		// -> 아니다 printNoticeList가 호출될 때 만들어지는 값과
		// moveNextList가 호출될 때 사용하는 값이 다를 수 있음
		// 따라서 서비스를 다시 호출해야한다(이전 출력할 때의 정보를 다시 여기서 쓰려고 하면 안됨)
		if(page==lastPage) {
			System.out.println("=====================================");
			System.out.println("다음 페이지가 없습니다.");
			System.out.println("=====================================");
			return;
            
		}
		page++;
		
	}

	public void inputSearchWord() {
		// 꼭 공유해야하는 것만 멤버 변수로 두는 것이 좋음
		// 개별적으로 마련하는 것이 좋은 방법이라면 지역 변수로 두는게 좋음
		Scanner scan = new Scanner(System.in);
		System.out.println("검색 범부(title/content/writerId) 중에 하나를 입력하세요.");
		System.out.println(" > ");
		
		// 제목으로 검색할건지 작성자로 검색할건지 검색하기 위한 필드
		searchField = scan.nextLine();
		
		System.out.println("검색어 >");
		
		// 목록을 조회할 때 printNoticeList가 쓸 내용이기 때문에 공유해야하므로 멤버 변수로 둬야함
		searchWord = scan.nextLine();
		
	}

}

 

 

Program

package ex1;

import java.sql.SQLException;

import com.jdbc.app.console.NoticeConsole;

public class Program {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		NoticeConsole console = new NoticeConsole();
		
		// 이전, 다음 메뉴를 구현하기 위해 현재 페이지가 이전 페이지인지 다음 페이지인지 알아야함
		// 따라서 페이지를 기억하기위한 상태 변수 구현(page--,page++과 같이 사용)
//		int page;
		
		// 종료 메뉴를 선택하기 전까지 반복
		EXIT:
		while(true) {
			// 목록을 출력하기 
//			console.printNoticeList(page);

			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.println("프로그램 종료이 종료되었습니다.");
				// switch문만 벗어나는 것이 아니라 EXIT라는 라벨을 이용해 while문을 벗어남
				break EXIT;
			default:
				System.out.println("메뉴는 1~4까지만 입력할 수 있습니다.");
			}
		}
	}

}

 

-> 상세 조회, 글쓰기, 검색 일부분 미완성 상태