ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • JDBC 7 - CRUD를 담당하는 NoticeService 생성
    servlet/JDBC 2021. 11. 30. 22:51
    반응형

    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;
    	}
    	
    	
    }
    반응형

    댓글

Designed by Tistory.