JDBC 7 - CRUD를 담당하는 NoticeService 생성

NoticeService

여태까지 만든 getList, insert, update, delete 를 한 클래스로 합친다

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";
	
    // DB에서 레코드를 가져와 객체에 저장한 후 반환
	public List<Notice> getList() throws ClassNotFoundException, SQLException{
		String sql = "SELECT * 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>();
		
		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 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);
		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=?,"
				+ "    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, content);
		st.setInt(3, 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);
//		Statement st = con.createStatement();
//		미리 시퀄문을 준비해서 값을 준비하여 바로 실행만 할 수있도록 준비
		PreparedStatement st = con.prepareStatement(sql);
		st.setInt(1, id);
		
		int result = st.executeUpdate();
		
		System.out.println(result);
		st.close();
		con.close();
		return result;
	}
}

 

Notice 객체 생성

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) {
		super();
		this.id = id;
		this.title = title;
		this.writerId = writerId;
		this.regDate = regDate;
		this.content = content;
		this.hit = hit;
	}
	
	
}