[JDBC] CRUD

//CRUD

Create : 생성
Read : 읽기(조회)
Update : 수정
Delete : 삭제

- 효과적인 CRUD 작업을 위한 Service가 필요하다.

- 데이터베이스의 테이블 역할을 하는 클래스를 생성한다.

- 해당 테이블의 칼럼들은 클래스의 멤버 변수로 선언하고 생성자를 이용하여 초기화한다.
    ~ private으로 선언하고 Getter Setter 이용

- CRUD 각 기능에 맞는 메서드를 구현한다.

---

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() throws SQLException, ClassNotFoundException {
		
		String sql = "SELECT * FROM NOTICE";

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

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