Oracle 11~13일차 - 테이블 / 영화사이트 제작

2020.08.19(수)~08.21(금)

 

1. 테이블 제작

   = 컬럼명

   = 데이터형

   = 제약조건

   = 테이블 만드는 형식

      CREATE TABLE table_name

      (

         컬럼명 데이터형 [제약조건] => NOT NULL, DEFAULT

         컬럼명 데이터형 [제약조건],

         컬럼명 데이터형 [제약조건],

         [제약조건] => check, foreign key, primary key 여러개 있는 경우 동시에 처리

         CONSTRAINT emp_no_pk PRIMARY KEY(empno, deptno)

      )

 

-- PRIMARY KEY

CREATE TABLE myTable(
    no NUMBER CONSTRAINT m_no_pk PRIMARY KEY,
    cno NUMBER CONSTRAINT m_no_pk PRIMARY KEY
);

따로 설정하지 않고 모아서 밑과 같이 코딩해야 여러개 설정 가능

CREATE TABLE myTable(
    no NUMBER,
    cno NUMBER,
    CONSTRAINT m_cno_pk PRIMARY KEY(no,cno)
);

 

* 반드시 입력되어야 하는 것을 NOT NULL로 처리.

 

SQL

CREATE TABLE daum_movie(
    no NUMBER, -- 영화번호 같은것을 참조해서 댓글 가져와야함
    cateno NUMBER,
    title VARCHAR2(200) CONSTRAINT dm_title_nn NOT NULL,
    poster VARCHAR2(300) CONSTRAINT dm_poster_nn NOT NULL,
    regdate VARCHAR2(200), --공백허용 개봉일 없을 수도
    genre VARCHAR2(100) CONSTRAINT dm_genre_nn NOT NULL,
    grade VARCHAR2(100) CONSTRAINT dm_grade_nn NOT NULL,
    actor VARCHAR2(100), --애니메이션의 경우 배우 없을 수도
    score VARCHAR2(20),
    director VARCHAR2(100) CONSTRAINT dm_director_nn NOT NULL,
    story CLOB,
    key VARCHAR2(50),
    CONSTRAINT dm_no_pk PRIMARY KEY(no)
);

CREATE TABLE daum_reply(
    no NUMBER, --댓글번호
    mno NUMBER,
    id VARCHAR2(20) CONSTRAINT dr_id_nn NOT NULL,
    msg CLOB CONSTRAINT dr_msg_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE,
    CONSTRAINT dr_no_pk PRIMARY KEY(no),
    CONSTRAINT dr_mno_fk FOREIGN KEY(mno)
    REFERENCES daum_movie(no)
);

/*
private String title;
    private String poster;
    private String link;
    private String content;
    private String author;
*/

CREATE TABLE daum_news(
    title VARCHAR2(1000) CONSTRAINT dn_title_nn NOT NULL,
    poster VARCHAR2(1000) CONSTRAINT dn_poster_nn NOT NULL,
    link VARCHAR2(1000) CONSTRAINT dn_link_nn NOT NULL,
    content CLOB CONSTRAINT dn_content_nn NOT NULL,
    author VARCHAR2(1000) CONSTRAINT dn_author_nn NOT NULL
);

SELECT * FROM daum_news;
DELETE FROM daum_news;
commit;

CREATE TABLE member(
    id VARCHAR2(20),
    pwd VARCHAR2(10) CONSTRAINT member_pwd_nn NOT NULL,
    CONSTRAINT member_id_pk PRIMARY KEY(id)
);

INSERT INTO member VALUES('hong', '1234');
INSERT INTO member VALUES('shim', '1234');
INSERT INTO member VALUES('kang', '1234');
COMMIT;

SELECT * FROM member;

DESC daum_reply;

INSERT INTO daum_reply VALUES(1,1,'hong','댓글 올리기',SYSDATE);
INSERT INTO daum_reply VALUES(2,1,'shim','댓글 올리기',SYSDATE);
COMMIT;

/*
private String poster;
    private String chef;
    private String mem_cont1;
    private String mem_cont3;
    private String mem_cont7;
    private String mem_cont2;
    
    = 경로 (260)
    = 이름 (34)

*/

CREATE TABLE chef(
    poster VARCHAR2(260) CONSTRAINT chef_poster_nn NOT NULL,
    chef VARCHAR2(100) CONSTRAINT chef_chef_nn NOT NULL,
    mem_cont1 VARCHAR2(20),
    mem_cont3 VARCHAR2(20),
    mem_cont7 VARCHAR2(20),
    mem_cont2 VARCHAR2(20)
);

SELECT COUNT(*) FROM chef;
DROP TABLE chef;
TRUNCATE TABLE chef;

 

JAVA

 

NewsVO

package com.sist.manager;
/*
 * 	1. 데이터베이스 => 테이블 제작
 *  2. DML 연습 (SELECT, INSERT, UPDATE, DELETE)
 *  3. 자바에서 제어 (SQL) => 오라클 전송
 *  4. 화면에 출력 (HTML, CSS)
 *  5. JavaScript
 *  
 *  웹브라우저
 *  ==> 1. 오라클
 *  ==> 2. 자바
 *  ==============================> 70% 스프링 (자바기반, 데이터베이스)
 *  ==> 3. HTML
 *  ==> 4. CSS/JavaScript (부가적)
 */
// 사용자 정의 데이터형 
public class NewsVO {
    private String title;
    private String poster;
    private String link;
    private String content;
    private String author;
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getPoster() {
		return poster;
	}
	public void setPoster(String poster) {
		this.poster = poster;
	}
	public String getLink() {
		return link;
	}
	public void setLink(String link) {
		this.link = link;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
    
}

 

MovieVO

package com.sist.manager;

public class MovieVO {
   private int cateno;
   private int no;
   private String title;
   private String poster;
   private String regdate;
   private String genre;
   private String grade;
   private String actor;
   private String score;
   private String director;
   private String story;
   private String key;
public int getCateno() {
	return cateno;
}
public void setCateno(int cateno) {
	this.cateno = cateno;
}
public int getNo() {
	return no;
}
public void setNo(int no) {
	this.no = no;
}
public String getTitle() {
	return title;
}
public void setTitle(String title) {
	this.title = title;
}
public String getPoster() {
	return poster;
}
public void setPoster(String poster) {
	this.poster = poster;
}
public String getRegdate() {
	return regdate;
}
public void setRegdate(String regdate) {
	this.regdate = regdate;
}
public String getGenre() {
	return genre;
}
public void setGenre(String genre) {
	this.genre = genre;
}
public String getGrade() {
	return grade;
}
public void setGrade(String grade) {
	this.grade = grade;
}
public String getActor() {
	return actor;
}
public void setActor(String actor) {
	this.actor = actor;
}
public String getScore() {
	return score;
}
public void setScore(String score) {
	this.score = score;
}
public String getDirector() {
	return director;
}
public void setDirector(String director) {
	this.director = director;
}
public String getStory() {
	return story;
}
public void setStory(String story) {
	this.story = story;
}
public String getKey() {
	return key;
}
public void setKey(String key) {
	this.key = key;
}
   
   
}

 

DaumNewsMain

package com.sist.manager;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.sist.dao.MovieDAO;

/*
 *  <ul class="list_line #list">

							<li>
				<a href="http://v.movie.daum.net/v/20191129113631107" class="thumb_line bg_noimage2 @1">
																				<span class="thumb_img" style="background-image:url(//img1.daumcdn.net/thumb/S320x200/?fname=https://t1.daumcdn.net/news/201911/29/sportsdonga/20191129113632574euca.jpg);"></span>
				</a>
				<span class="cont_line">
					<strong class="tit_line"><a href="http://v.movie.daum.net/v/20191129113631107" class="link_txt @1">염정아, '세컨드 마더'로 'SKY캐슬' 감독과 재회하나..소속사 "검토 중"</a></strong>
					<a href="http://v.movie.daum.net/v/20191129113631107" class="desc_line @1">
						[동아닷컴]  배우 염정아가 드라마 SKY캐슬 조현탁 PD의 영화 데뷔작 출연을 검토 중이다. 염정아의 소속사 아티스트컴퍼니 관계자는 29일 동아닷컴에 “염정아가 조현탁 감독의 세컨드 마더 출연을 검토 중이다라며 하지만 지금은 영화 인생은 아름다워에 집중하고 있다라고 말했다. 세컨드 마더는 2015년 11월 개봉한 동명의 브라질 영화를
					</a>
					<span class="state_line">
						스포츠동아<span class="txt_dot"></span><span class="screen_out">발행일자</span>19.11.29
					</span>
				</span>
			</li>
 */
public class DaumNewsMain {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
        try
        {
        	MovieDAO dao=new MovieDAO();
        	for(int i=1;i<=7;i++)
        	{
	        	Document doc=Jsoup.connect("https://movie.daum.net/magazine/new?tab=nws&regdate=20200818&page="+i).get();
	        	Elements title=doc.select("ul.list_line span.cont_line a.link_txt");
	        	Elements poster=doc.select("ul.list_line a.thumb_line span.thumb_img");
	        	Elements link=doc.select("ul.list_line a.thumb_line");
	        	Elements content=doc.select("ul.list_line span.cont_line a.desc_line");
	        	Elements author=doc.select("ul.list_line span.cont_line span.state_line");
	        	
	        	for(int j=0;j<title.size();j++)
	        	{
	        		String p=poster.get(j).attr("style");
	        		p=p.substring(p.indexOf("(")+1,p.lastIndexOf(""));
	        		System.out.println(title.get(j).text());
	        		System.out.println(content.get(j).text());
	        		System.out.println(author.get(j).text());
	        		System.out.println(p);
	        		System.out.println(link.get(j).attr("href"));
	        		
	        		System.out.println("================================================================");
	        		
	        		NewsVO vo=new NewsVO();
	        		vo.setTitle(title.get(j).text());
	        		vo.setPoster(p);
	        		vo.setLink(link.get(j).attr("href"));
	        		vo.setContent(content.get(j).text());
	        		vo.setAuthor(author.get(j).text());
	        		Thread.sleep(100);
	        		dao.newsInsert(vo);
	        	}
        	}
        	System.out.println("End...");
        }catch(Exception ex){ex.printStackTrace();}
	}

}

 

DaumMovieMain

package com.sist.manager;

import java.io.FileWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.sist.dao.MovieDAO;

public class DaumMovieMain {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try
		   {
			     
			MovieDAO dao=new MovieDAO();
			int k=1;
			//for(int i=1;i<=7;i++)
			{
			     Document doc=Jsoup.connect("https://movie.daum.net/boxoffice/yearly").get();
			     Elements link=doc.select("span.wrap_link a.link_desc[href*=/moviedb/]");
			     
			     
			     for(int j=0;j<link.size();j++)
			     {
			    	try
			    	{
			    	
			    	 String dLink="https://movie.daum.net"+link.get(j).attr("href");
			    	 Document doc2=Jsoup.connect(dLink).get();
			    	 System.out.println(dLink);
			    	
			    	 Element title=doc2.selectFirst("div.mobile_subject strong.tit_movie");
			    	 Element poster=doc2.selectFirst("span.thumb_summary img.img_summary");
			    	 Element regdate=doc2.select("dl.list_movie dd.txt_main").get(1);
			    	 Element grade=doc2.select("dl.list_movie dd").get(3);
			    	 Element genre=doc2.select("dl.list_movie dd.txt_main").get(0);
			    	 Element score=doc2.selectFirst("div.info_origin a");
			    	 Element actor=doc2.select("dd.type_ellipsis a").get(1);
			    	 Element director=doc2.select("dd.type_ellipsis a").get(0);
			    	 Element story=doc2.selectFirst("div.desc_movie");
			    	 
			    	 System.out.println(title.text());
			    	 System.out.println(poster.attr("src"));
			    	 System.out.println(regdate.text());
			    	 System.out.println(genre.text());
			    	 System.out.println(grade.text());
			    	 System.out.println(score.text());
			    	 System.out.println(actor.text());
			    	 System.out.println(director.text());
			    	 System.out.println(story.text());
			    	 //System.out.println(youtubeGetKey(title.text()));
			    	 System.out.println("================================================");
			    	
			    	 MovieVO vo=new MovieVO();
			    	 vo.setCateno(5);
			    	 //vo.setNo(k);
			    	 vo.setTitle(title.text());
			    	 vo.setPoster(poster.attr("src"));
			    	 vo.setRegdate(regdate.text());
			    	 vo.setGenre(genre.text());
			    	 vo.setGrade(grade.text());
			    	 vo.setScore(score.text());
			    	 vo.setActor(actor.text());
			    	 vo.setDirector(director.text());
			    	 vo.setStory(story.text());
			    	 vo.setKey(youtubeGetKey(title.text()));
			    	 
			    	 dao.movieInsert(vo);
			    	 k++;
			    	}catch(Exception ex){ex.printStackTrace();}
			     }
			    
			 }
			
		   }catch(Exception ex)
		   {
			   ex.printStackTrace();
		   }
	}
    public static String youtubeGetKey(String title){
    	String key="";
    	try
    	{
    		Document doc=Jsoup.connect("https://www.youtube.com/results?search_query="+title).get();
    		Pattern p=Pattern.compile("/watch\\?v=[^가-힣]+");
    		Matcher m=p.matcher(doc.toString());
    		while(m.find())
    		{
    			String s=m.group();
    			key=s.substring(s.indexOf("=")+1,s.indexOf("\""));
    			break;
    		}
    		
    	}catch(Exception ex){}
    	return key;
    }
}

 

MovieDAO (movieInsert, newsInsert)

package com.sist.dao;
import java.sql.*;
import java.util.*;

import com.sist.manager.MovieVO;
import com.sist.manager.NewsVO;
import com.sist.recipe.ChefVO;
import com.sist.recipe.RecipeVO;
public class MovieDAO {
		private Connection conn;
		private PreparedStatement ps;
		private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
		public MovieDAO() {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}
		}
		public void getConnection() {
			try {
				conn=DriverManager.getConnection(URL,"hr","happy");
			}catch(Exception ex) {}
		}
		public void disConnection() {
			try {
				if(ps!=null) ps.close();
				if(conn!=null) conn.close();
			}catch(Exception ex) {}
		}
		
		// 기능
		// 1. 저장 => INSERT, UPDATE, DELETE => 결과값이 없다 (void)
		//public void movieInsert(int no, int cno, String title, String actor, .......)
		public void movieInsert(MovieVO vo) {
			try {
				getConnection();
				String sql = "INSERT INTO daum_movie VALUES("
						+"(SELECT NVL(MAX(no)+1,1) FROM daum_movie),?,?,?,"
						+"?,?,?,?,?,?,?,?)";
				ps=conn.prepareStatement(sql);
				// ?에 값을 채운다
				ps.setInt(1, vo.getCateno());
				ps.setString(2, vo.getTitle());
				ps.setString(3, vo.getPoster());
				ps.setString(4, vo.getRegdate());
				ps.setString(5, vo.getGenre());
				ps.setString(6, vo.getGrade());
				ps.setString(7, vo.getActor());
				ps.setString(8, vo.getScore());
				ps.setString(9, vo.getDirector());
				ps.setString(10, vo.getStory());
				ps.setString(11, vo.getKey());
				// 실행 명령
				ps.executeUpdate();
				
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}
			//finally { disConnection(); }
		}
		
		public void newsInsert(NewsVO vo) {
			try {
				getConnection();
				String sql = "INSERT INTO daum_news VALUES(?,?,?,?,?)";
				ps=conn.prepareStatement(sql);
				// ?에 값을 채운다
				ps.setString(1, vo.getTitle());
				ps.setString(2, vo.getPoster());
				ps.setString(3, vo.getLink());
				ps.setString(4, vo.getContent());
				ps.setString(5, vo.getAuthor());
				// 실행 명령
				ps.executeUpdate();
				
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}
			finally {
				disConnection();
			}
		}
		
		public ArrayList<MovieVO> movieListData(int cno){
			ArrayList<MovieVO> list = new ArrayList<MovieVO>();
			try {
				// 연결
				getConnection();
				// SQL문장
				String sql="SELECT poster, title, no FROM daum_movie "
						+"WHERE cateno=? ORDER BY no";
				ps=conn.prepareStatement(sql);
				ps.setInt(1, cno);
				// 전송
				
				// 시행후에 데이터를 받는다
				ResultSet rs=ps.executeQuery();
				while(rs.next()) {
					MovieVO vo = new MovieVO();
					vo.setPoster(rs.getString(1));
					vo.setTitle(rs.getString(2));
					vo.setNo(rs.getInt(3));
					list.add(vo);
				}
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}return list;
		}
		
		
		public ArrayList<NewsVO> newsListData(){
			ArrayList<NewsVO> list = new ArrayList<NewsVO>();
			try {
				// 연결
				getConnection();
				// SQL문장
				String sql="SELECT title, link, author, poster, content FROM daum_news";
				ps=conn.prepareStatement(sql);
				ResultSet rs=ps.executeQuery();
				while(rs.next()) {
					NewsVO vo = new NewsVO();
					vo.setTitle(rs.getString(1));
					vo.setLink(rs.getString(2));
					vo.setAuthor(rs.getString(3));
					vo.setPoster(rs.getString(4));
					vo.setContent(rs.getString(5));
					list.add(vo);
				}
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}return list;
		}
		// 영화 상세보기 =>VO(영화 한개에 대한 모든 정보) / 목록보기 => ArrayList
		public MovieVO movieDetailData(int no) {
			MovieVO vo = new MovieVO();
			try {
				// 연결
				getConnection();
				// SQL 전송
				String sql="SELECT * FROM daum_movie "
						+"WHERE no=?";
				ps=conn.prepareStatement(sql);
				// 실행요청하기 전에 ?에 값을 채운다
				ps.setInt(1, no);
				// 결과값을 받는다
				ResultSet rs=ps.executeQuery(); // 실행
				rs.next(); // 커서이동 (데이터가 출력된 위치)
				/*
				 * NO       NOT NULL NUMBER        
				CATENO            NUMBER        
				TITLE    NOT NULL VARCHAR2(200) 
				POSTER   NOT NULL VARCHAR2(300) 
				REGDATE           VARCHAR2(200) 
				GENRE    NOT NULL VARCHAR2(100) 
				GRADE    NOT NULL VARCHAR2(100) 
				ACTOR             VARCHAR2(100) 
				SCORE             VARCHAR2(20)  
				DIRECTOR NOT NULL VARCHAR2(100) 
				STORY             CLOB          
				KEY               VARCHAR2(50)  
				 * 
				 */
				vo.setNo(rs.getInt(1));
				vo.setCateno(rs.getInt(2));
				vo.setTitle(rs.getString(3));
				vo.setPoster(rs.getString(4));
				vo.setRegdate(rs.getString(5));
				vo.setGenre(rs.getString(6));
				vo.setGrade(rs.getString(7));
				vo.setActor(rs.getString(8));
				vo.setScore(rs.getString(9));
				vo.setDirector(rs.getString(10));
				vo.setStory(rs.getString(11));
				vo.setKey(rs.getString(12));
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return vo;
		}
		
		// 댓글 관련 => INSERT, UPDATE, DELETE
		public ArrayList<ReplyVO> movieReplyData(int mno){ // mno 영화번호
			ArrayList<ReplyVO> list = new ArrayList<ReplyVO>();
			try {
				getConnection();
				String sql="SELECT no, mno, id, msg, TO_CHAR(regdate, 'YYYY-MM-DD HH24:MI:SS') "
						+"FROM daum_reply "
						+"WHERE mno=? "
						+"ORDER BY no DESC"; //최신순으로 출력
				ps=conn.prepareStatement(sql);
				ps.setInt(1, mno);
				ResultSet rs=ps.executeQuery();
				while(rs.next()) {
					ReplyVO vo = new ReplyVO();
					vo.setNo(rs.getInt(1));
					vo.setMno(rs.getInt(2));
					vo.setId(rs.getString(3));
					vo.setMsg(rs.getString(4));
					vo.setDbday(rs.getString(5));
					list.add(vo);
				}
				rs.close();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return list;
		}
		public void movieReplyInsert(ReplyVO vo) {
			try {
				getConnection();
				String sql="INSERT INTO daum_reply VALUES("
						+"(SELECT NVL(MAX(no)+1,1) FROM daum_reply),"
						+"?,?,?,SYSDATE)";
				ps=conn.prepareStatement(sql);
				// ?에 값을 채운다
				ps.setInt(1, vo.getMno());
				ps.setString(2, vo.getId());
				ps.setString(3, vo.getMsg());
				
				// 실행 요청
				ps.executeUpdate();
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
		}
		
		// 레시피 저장
		public void recipeInsert(RecipeVO vo) {
			try {
				getConnection();
				String sql = "INSERT INTO recipe VALUES((SELECT NVL(MAX(no)+1,1) FROM recipe),?,?,?,?)";
				ps=conn.prepareStatement(sql);
				// ?에 값을 채운다
				ps.setString(1, vo.getTitle());
				ps.setString(2, vo.getPoster());
				ps.setString(3, vo.getChef());
				ps.setString(4, vo.getLink());
				// 실행 명령
				ps.executeUpdate();
				
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}
			finally {
				disConnection();
			}
		}
		
		public void chefInsert(ChefVO vo) {
			try {
				//연결
				getConnection();
				//SQL
				String sql="INSERT INTO chef VALUES(?,?,?,?,?,?)";
				//전송
				ps=conn.prepareStatement(sql);
				//?에 값을 채운다
				ps.setString(1, vo.getPoster());
				ps.setString(2, vo.getChef());
				ps.setString(3, vo.getMem_cont1());
				ps.setString(4, vo.getMem_cont3());
				ps.setString(5, vo.getMem_cont7());
				ps.setString(6, vo.getMem_cont2());
				//실행
				ps.executeUpdate();
			}catch(Exception ex) {
				ex.printStackTrace();
			}finally {
				disConnection();
			}
		}
		
		// 로그인
		/*
		 * 	목록 ==> ArrayList
		 *  상세보기 ==> ~VO
		 *  경우의 수
		 *    2개 ======> boolean
		 *        ID중복  => boolean
		 *    3개 이상
		 *       String, int
		 *       
		 *       ID가 없는 경우
		 *       PWD가 틀리다
		 *       로그인
		 */
		public String isLogin(String id, String pwd) {
			String result="";
			try {
				getConnection();
				String sql="SELECT COUNT(*) FROM member "
						+"WHERE id=?"; // ID가 존재?
				ps=conn.prepareStatement(sql);
				ps.setString(1, id);
				ResultSet rs = ps.executeQuery();
				rs.next();
				int count = rs.getInt(1);
				rs.close();
				
				if(count == 0) { // ID가 없는 상태
					result="NOID";
				}
				else {// ID가 존재하는 상태
					sql = "SELECT pwd FROM member "
						+"WHERE id=?";
					ps=conn.prepareStatement(sql);
					ps.setString(1, id);
					rs=ps.executeQuery();
					rs.next();
					String db_pwd=rs.getString(1);
					rs.close();
					
					if(db_pwd.equals(pwd)) { //Login
						result="OK";
					}
					else { //비밀번호가 틀린상태
						result="NOPWD";
					}
				}
			}catch(Exception ex) {
				System.out.println(ex.getMessage());
			}finally {
				disConnection();
			}
			return result;
		}
		
		public void replyDelete(int no) {
			try {
				getConnection();
				String sql="DELETE FROM daum_reply "
						+"WHERE no=?";
				//전송
				ps=conn.prepareStatement(sql);
				ps.setInt(1, no);
				
				ps.executeUpdate();
			}catch(Exception ex) {
				ex.printStackTrace();
			}finally {
				disConnection();
			}
		}
}

 

MovieMain

package com.sist.view;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*; // ArrayList => 배열 (가변형배열)
import com.sist.dao.*; // MovieDAO
import com.sist.manager.*; // MovieVO
 
@WebServlet("/MovieMain")
public class MovieMain extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		/*
		 * HttpServletRequest request
		 *  => 사용자의 요청값
		 *  => 브라우저 정보 (사용자의 IP)
		 * HttpServletResponse response
		 *  => 응답정보 (서버에서 클라이언트 전송)
		 */
		// 브라우저가 준비 (HTML, XML)
		response.setContentType("text/html;charset=EUC-KR"); // <%%>
		// charset=ISO-8859 => ASC(1byte)
		PrintWriter out = response.getWriter();
		//out.println("<?xml version=\"1.0\" encoding=\"EUC-KR\"?>");
		out.println("<html>");
		out.println("<head>");
		out.println("<link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");
		out.println("</head>");
		out.println("<body>");
		out.println("<div class=container>");
		out.println("<h1 class=text-center>영화목록</h1>");
		out.println("<div class=row>");
		out.println("<center>");
		out.println("<a href=MovieMain?no=1 class=\"btn btn-sm btn-primary\">현재상영영화</a>");
		out.println("<a href=MovieMain?no=2 class=\"btn btn-sm btn-danger\">개봉예정영화</a>");
		out.println("<a href=MovieMain?no=3 class=\"btn btn-sm btn-info\">박스오피스(주간)</a>");
		out.println("<a href=MovieMain?no=4 class=\"btn btn-sm btn-warning\">박스오피스(월간)</a>");
		out.println("<a href=MovieMain?no=5 class=\"btn btn-sm btn-success\">박스오피스(연간)</a>");
		out.println("<a href=NewsMain class=\"btn btn-sm btn-active\">뉴스</a>");
		out.println("</center>");
		out.println("</div>");
		out.println("<div class=row>");
		/*	 * <div class="row">
  <div class="col-md-4">
    <div class="thumbnail">
      <a href="/w3images/lights.jpg">
        <img src="/w3images/lights.jpg" alt="Lights" style="width:100%">
        <div class="caption">
          <p>Lorem ipsum...</p>
        </div>
      </a>
    </div>
  </div>
		 */
		// 전송받은 값 받기
		String no = request.getParameter("no");
		if(no==null) //값이 없으면 1페이지 띠워라
			no="1";
		MovieDAO dao = new MovieDAO();
		ArrayList<MovieVO> list = dao.movieListData(Integer.parseInt(no));
		for(MovieVO vo :list) {
			out.println("<div class=\"col-md-3\">");
			out.println("<div class=\"thumbnail\">");
			out.println("<a href=MovieDetail?no="+vo.getNo()+">");
			out.println("<img src="+vo.getPoster()+" alt=\"Lights\" style=\"width:100%\">");
			out.println("<div class=\"caption\">");
			String str=vo.getTitle();
			if(str.length()>18) {
				str=str.substring(0,18)+"...";
			}
			out.println("<p>"+str+"</p>");
			out.println("</div>");
			out.println("</a>");
			out.println("</div>");
			out.println("</div>");
		}

		out.println("</div>");
		out.println("</div>");
		out.println("</body>");
		out.println("</html>");	
	}
}

 

NewsMain

package com.sist.view;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*; // ArrayList => 배열 (가변형배열)
import com.sist.dao.*; // MovieDAO
import com.sist.manager.*; // MovieVO

@WebServlet("/NewsMain")
public class NewsMain extends HttpServlet {
	private static final long serialVersionUID = 1L;


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=EUC-KR"); // <%%>
		// charset=ISO-8859 => ASC(1byte)
		PrintWriter out = response.getWriter();
		//out.println("<?xml version=\"1.0\" encoding=\"EUC-KR\"?>");
		out.println("<html>");
		out.println("<head>");
		out.println("<link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");
		out.println("</head>");
		out.println("<body>");
		out.println("<div class=container>");
		out.println("<h1 class=text-center>영화뉴스</h1>");
		out.println("<div class=row>");
		MovieDAO dao = new MovieDAO();
		ArrayList<NewsVO> list = dao.newsListData();
		for(NewsVO vo:list) {
			out.println("<table class=\"table table-hover\">");
			out.println("<tr>");
			out.println("<td rowspan=3 width=40%>");
			String poster=vo.getPoster();
			poster=poster.substring(0,poster.lastIndexOf(")"));
			out.println("<a href="+vo.getLink()+">");
			out.println("<img src="+poster+" width=100%></a>");
			out.println("</td>");
			out.println("<td width=60%><b><font color=orange>"+vo.getTitle()+"</font></b></td>");
			out.println("</tr>");
			out.println("<tr>");
			out.println("<td width=60%>"+vo.getContent()+"</td>");
			out.println("</tr>");
			out.println("<tr>");
			out.println("<td width=60% class=text-right>"+vo.getAuthor()+"</td>");
			out.println("</tr>");
			out.println("</table>");
		}
		
		out.println("</div>");
		out.println("</div>");
		out.println("</body>");
		out.println("</html>");	
	}
}

 

MovieDetail

package com.sist.view;

import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.sist.dao.MovieDAO;
import com.sist.manager.MovieVO;
import java.util.*; // ArrayList => 배열 (가변형배열)
import com.sist.dao.*; // MovieDAO
import com.sist.manager.*; // MovieVO

//상세보기
@WebServlet("/MovieDetail")
//톰캣(JSP엔진) JSP=>Servlet
//톰캣 => 테스트용 웹서버(아파치) => 30
public class MovieDetail extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// HTML로 출력
		
		response.setContentType("text/html;charset=EUC-KR");
		PrintWriter out = response.getWriter(); //브라우저
		
		// HTML 전송
		// 값을 받는다 MovieMain => MovieDetail?no=1
		String no = request.getParameter("no");
		out.println("<html>");
		out.println("<head>");
		out.println("<link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");
//		out.println("<style type=text/css>");
//		out.println(".row{");
//		out.println("margin:0px auto;");
//		out.println("width:700px;");
//		out.println("}");
//		out.println("</style>");
		out.println("</head>");
		out.println("<body>");
		out.println("<div class=container>");
		out.println("<h1 class=text-center>영화상세</h1>");
		out.println("<div class=row>");
		out.println("<div class=col-sm-8>");
		MovieDAO dao=new MovieDAO();
		MovieVO vo = dao.movieDetailData(Integer.parseInt(no));
		out.println("<table class=table>");
		out.println("<tr>");
		out.println("<td>");
		out.println("<iframe src=http://youtube.com/embed/"+vo.getKey()+" width=700 height=350></iframe>");
		out.println("</td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<table class=table>");
		out.println("<tr>");
		out.println("<td width=30% class=text-center rowspan=7>");
		out.println("<img src="+vo.getPoster()+" width=210 height=300>");
		out.println("</td>");
		out.println("<td width=70%>"+vo.getTitle()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>감독:"+vo.getDirector()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>출연:"+vo.getActor()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>장르:"+vo.getGenre()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>등급:"+vo.getGrade()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>개봉:"+vo.getRegdate()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=70%>"+vo.getScore()+"</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td colspan=2 height=200 valign=top>"+vo.getStory()+"</td>");
		out.println("</tr>");
		
		out.println("</table>");
		//out.println("전송받은 영화번호:"+no);
		out.println("</div>");
		
		out.println("<div class=col-sm-4>");
		out.println("<table class=table>");
		out.println("<tr>");
		out.println("<td>");
		out.println("<form method=post action=MovieDetail>");
		out.println("<input type=hidden name=mno value="+no+">");
		out.println("<input type=text size=25 class=input-sm name=msg>");
		out.println("<input type=submit class=\"btn btn-sm btn-primary\" value=댓글쓰기>");
		out.println("</form>");
		out.println("</td>");
		out.println("</tr>");
		out.println("</table>");
		ArrayList<ReplyVO> rList=dao.movieReplyData(Integer.parseInt(no));
		out.println("<table class=table>");
		out.println("<tr>");
		out.println("<td>");
		
		HttpSession session=request.getSession();
		String id=(String)session.getAttribute("id");
		
		for(ReplyVO rvo:rList) {
			out.println("<table class=table>");
			out.println("<tr>");
			out.println("<td class=text-left>");
			out.println(rvo.getId()+"("+rvo.getDbday()+")");
			out.println("</td>");
			out.println("<td class=text-right>");
			if(id.equals(rvo.getId())) {
			out.println("<a href=# class=\"btn btn-xs btn-primary\">수정</a>");
			out.println("<a href=MovieDelete?no="+rvo.getNo()+"&mno="+vo.getNo()+" class=\"btn btn-xs btn-danger\">삭제</a>");
			}
			out.println("</td>");
			out.println("</tr>");
			out.println("<tr>");
			out.println("<td colspan=2 height=100 valign=top class=text-left>");
			out.println("<pre>"+rvo.getMsg()+"</pre>");
			out.println("</td>");
			out.println("</tr>");
			out.println("</table>");
		}
		out.println("</td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("</div>");
		out.println("</div>");
		out.println("</div>");
		out.println("</body>");
		out.println("</html>");
	}

	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
			request.setCharacterEncoding("EUC-KR");
			// 한글 깨짐 방지
		}catch(Exception ex) {}
		String mno=request.getParameter("mno");
		String msg=request.getParameter("msg");
		
		HttpSession session=request.getSession();
		String id=(String)session.getAttribute("id");
		
		ReplyVO vo = new ReplyVO();
		vo.setMno(Integer.parseInt(mno));
		vo.setMsg(msg);
		vo.setId(id); // Session 
		// DAO전송
		MovieDAO dao = new MovieDAO();
		dao.movieReplyInsert(vo);
		// 화면 이동
		response.sendRedirect("MovieDetail?no="+mno);
	}

}

 

ReplyVO

package com.sist.dao;
import java.util.*;
public class ReplyVO {
	private int no;
	private int mno;
	private String id;
	private String msg;
	private Date regdate;
	private String dbday;
	
	public String getDbday() {
		return dbday;
	}
	public void setDbday(String dbday) {
		this.dbday = dbday;
	}
	public int getNo() {
		return no;
	}
	public void setNo(int no) {
		this.no = no;
	}
	public int getMno() {
		return mno;
	}
	public void setMno(int mno) {
		this.mno = mno;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getMsg() {
		return msg;
	}
	public void setMsg(String msg) {
		this.msg = msg;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	
}

 

Login

package com.sist.view;

import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.sist.dao.MovieDAO;

@WebServlet("/Login")
public class Login extends HttpServlet {
	private static final long serialVersionUID = 1L;

	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=EUC-KR");
		PrintWriter out = response.getWriter();
		out.println("<html>");
	    out.println("<head>");
	    out.println("<link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");
	    out.println("<style type=text/css>");
	    out.println(".row{");
	    out.println("margin:0px auto;");
	    out.println("width:300px;");
	    out.println("}");
	    out.println("</style>");
	    out.println("</head>");
	    out.println("<body>");
	    out.println("<div class=container>");
	    out.println("<h1 class=text-center>로그인</h1>");
	    out.println("<div class=row>");
	    
	    out.println("<form method=post action=Login>");
		out.println("<table class=table>");
		out.println("<tr>");
		out.println("<td width=20% class=text-right>ID</td>");
		out.println("<td width=75%>");
		out.println("<input type=text name=id size=15 class=input-sm>");
		out.println("</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td width=20% class=text-right>Password</td>");
		out.println("<td width=75%>");
		out.println("<input type=password name=pwd size=15 class=input-sm>");
		out.println("</td>");
		out.println("</tr>");
		
		out.println("<tr>");
		out.println("<td colspan=2 class=text-center>");
		out.println("<input type=submit value=로그인 class=\"btn btn-sm btn-info\">");
		out.println("<input type=button value=취소 class=\"btn btn-sm btn-danger\">");
		out.println("</td>");
		out.println("</tr>");
		
		out.println("</table>");
		out.println("</form>");
		out.println("</div>");
		out.println("</div>");
		out.println("</body>");
		out.println("</html>");
	}
	/*
	 *  <style>
	 *  	.aaa{ class는 .
	 *        =
	 *        =
	 *  	}
	 *      #aaa{ id는 #
	 *      }
	 *  </style>
	 * 	<input type=text class="aaa"><input type=text id="aaa"> id는 중복없음
	 * 	<input type=text class="aaa">
	 *  <input type=text class="aaa">
	 *  <input type=text class="aaa">
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String id = request.getParameter("id");
		String pwd = request.getParameter("pwd");
		//System.out.println("ID:"+id);
		//System.out.println("PWD:"+pwd);
		MovieDAO dao = new MovieDAO();
		String result=dao.isLogin(id, pwd);
		
		response.setContentType("text/html;charset=EUC-KR");
		PrintWriter out = response.getWriter();
		
		if(result.equals("NOID")) {
			out.println("<script>");
			out.println("alert(\"ID가 존재하지 않습니다\");");
			out.println("history.back();");
			out.println("</script>");
		}
		else if(result.equals("NOPWD")) {
			out.println("<script>");
			out.println("alert(\"비밀번호가 틀립니다\");");
			out.println("history.back();");
			out.println("</script>");
			
		}
		else { //로그인된 상태
			//id를 서버에 저장 => 프로그램 종료시까지 저장
			HttpSession session = request.getSession(); // 세션 생성
			session.setAttribute("id", id); // 서버에 저장
			// 기본 => 30분 
			response.sendRedirect("MovieMain");
		}
	}

}

 

MovieDelete

package com.sist.view;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sist.dao.MovieDAO;

@WebServlet("/MovieDelete")
public class MovieDelete extends HttpServlet {
	private static final long serialVersionUID = 1L;
	// MovieDelete?no="+vo.getNo()
	// <input type=text name=id>
	// http://localhost/20200819-MovieProject/MovieDetail?mno=3&msg=%B4%F1....
	/*
	 * 	GET => <a> response.sendRedirect()
	 *  POST => form
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String no = request.getParameter("no");
		String mno = request.getParameter("mno");
		// DAO전송
		MovieDAO dao = new MovieDAO();
		// 삭제처리
		dao.replyDelete(Integer.parseInt(no));
		// MovieDetail로 이동
		response.sendRedirect("MovieDetail?no="+mno);
	}

}