본문 바로가기
Java/Java 활용 실습

[Java+Oracle] 로그인해서 홈으로 넘어가기

by ProSeraphina 2020. 8. 11.

emp.sql
0.00MB

 

 

1. class MYDAO;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
package com.sist.dao;
import java.sql.*;
import java.util.*;
public class MyDAO {
    //연결
    private Connection conn;
    //오라클에 SQL 전송
    private PreparedStatement ps;
    //URL
    private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
    //드라이버 등록
    public MyDAO() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(Exception ex) {
            System.out.println(ex.getMessage());
        }
    }
    
    //연결(conn hr/happy)
    public void getConnection() {
        try{
            conn=DriverManager.getConnection(URL,"hr","happy");
        }catch(Exception ex){}
    }
    
    //해제(exit)
    public void disConnection(){
        try{
            if(ps!=null) ps.close();
            if(conn!=null) conn.close();
        }catch(Exception ex){}    
    }
    
    //JDBC(원시소스) >> DBCP >> ORM(MyBatis, Hibernate, JPA)
    //기능
    public String isLogin(String ename,int empno) {
        String result="";
        try {
            getConnection();
            //SQL 문장전송
            String sql="SELECT COUNT(*) FROM emp WHERE ename=?";
            ps=conn.prepareStatement(sql);
            //?에 값채우기
            ps.setString(1, ename);
            ResultSet rs=ps.executeQuery();
            rs.next();
            int count=rs.getInt(1);//0:아이디를 입력하세요 or 1:비밀번호 확인
            rs.close();
            if(count==0) {
                result="NONAME";
            }else {
                sql="SELECT empno FROM emp WHERE ename=?";
                ps=conn.prepareStatement(sql);
                ps.setString(1, ename);
                rs=ps.executeQuery();
                rs.next();//데이터가 있는 위치에 커서를 갖다놔라
                int db_empno=rs.getInt(1);
                rs.close();
                
                if(empno==db_empno) {//로그인
                    result=ename;
                }else { //사번이 틀린상태
                    result="NOSABUN";
                }
            }
        }catch(Exception ex) {
            System.out.println(ex.getMessage());
        }finally {
            disConnection();
        }
        return result;
    }
}
 cs

 

2. servlet LoginServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
package com.sist.dao;
 
import java.io.*;
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;
 
@WebServlet("/LoginServlet")
public class LoginServlet 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");
        //HTML 제작
        PrintWriter out=response.getWriter();
        //response:응답(브라우저 전송), request:사용자가 보낸 데이터를 받을 때
        out.println("<html>");
        out.println("<body>");
        out.println("<center>");
        out.println("<h1>Login</h1>");
        out.println("<form method=post action=LoginServlet>");//호출: form
        //호출방식: get(default), post
        out.println("<table width=250>");
        
        out.println("<tr>");//다음줄에 출력
        out.println("<td width=15% align=right>이름</td>");//라벨
        out.println("<td width=85%>");
        out.println("<input type=text name=ename size=17>");//입력창
        out.println("</td>");
        out.println("</tr>");
        
        out.println("<tr>");
        out.println("<td width=15% align=right>사번</td>");//라벨
        out.println("<td width=85%>");
        out.println("<input type=password name=empno size=17>");//입력창
        out.println("</td>");
        out.println("</tr>");
        
        out.println("<tr>");
        out.println("<td align=center colspan=2>");//colspan:td 2개를 합쳐줌
        out.println("<input type=submit value=로그인>");//버튼, submit:전송
        out.println("</td>");
        out.println("</tr>");
        
        out.println("</table>");
        out.println("</form>");
        out.println("</center>");
        out.println("</body>");
        out.println("</html>");    
    }
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //요청처리
        response.setContentType("text/html;charset=EUC-KR");
        PrintWriter out=response.getWriter();
        String ename=request.getParameter("ename");
        String empno=request.getParameter("empno");
        MyDAO dao=new MyDAO();
        String result=dao.isLogin(ename.toUpperCase(), Integer.parseInt(empno));
         if (result.equals("NONAME")) {
             out.println("<script>");
             out.println("alert(\"이름이 존재하지 않습니다.\");"); 
             out.println("history.back();");//back버튼(원상복귀)
             out.println("</script>");
          } else if (result.equals("NOSABUN")) {
             out.println("<script>");
             out.println("alert(\"사번이 틀립니다.\");"); 
             out.println("history.back();");
             out.println("</script>");
          } else {
             response.sendRedirect("MusicServlet"); //로그인시 화면을 이동
          }
       }
    }
 
 cs

 

3. class MusicVO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.sist.dao;
/*
 MNO                                                NUMBER(3)
 TITLE                                              VARCHAR2(300)
 SINGER                                             VARCHAR2(100)
 ALBUM                                              VARCHAR2(200)
 POSTER                                             VARCHAR2(1000)
 STATE                                              CHAR(6)
 IDCREMENT                                          NUMBER(3)
 KEY                                                VARCHAR2(50)
 */
public class MusicVO {
    private int mno;
    private String title;
    private String singer;
    private String album;
    private String poster;
    private String state;
    private int idcrement;
    private String key;
    
    public String getAlbum() {
        return album;
    }
    public void setAlbum(String album) {
        this.album = album;
    }
    public int getMno() {
        return mno;
    }
    public void setMno(int mno) {
        this.mno = mno;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getSinger() {
        return singer;
    }
    public void setSinger(String singer) {
        this.singer = singer;
    }
    public String getPoster() {
        return poster;
    }
    public void setPoster(String poster) {
        this.poster = poster;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    public int getIdcrement() {
        return idcrement;
    }
    public void setIdcrement(int idcrement) {
        this.idcrement = idcrement;
    }
    public String getKey() {
        return key;
    }
    public void setKey(String key) {
        this.key = key;
    }
    
}
 cs

 

4. class MusicDAO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
package com.sist.dao;
import java.util.*;
import java.sql.*;
public class MusicDAO {
    
    //오라클 연결
    private Connection conn;
    //SQL문장 오라클로 전송
    private PreparedStatement ps;
    //오라클 주소
    private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
    
    //드라이버 설치
    public MusicDAO(){
        //생성자=> 멤버변수 초기화, 네트워크 서버 연결
        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) {}
    }
    
    //SQL 문장전송 =>200개 데이터 요청
    public ArrayList<MusicVO> musicAllData(){
        ArrayList<MusicVO> list=new ArrayList<MusicVO>();
        try {
            //오라클 연결
            getConnection();
            //SQL 문장전송
            String sql="SELECT mno,poster,title,singer,album FROM genie_music ORDER BY mno ASC";
            ps=conn.prepareStatement(sql); //실행:executeQuery()
            //결과값 받기
            ResultSet rs=ps.executeQuery();
            while(rs.next()) {
                MusicVO vo=new MusicVO();
                vo.setMno(rs.getInt(1));
                vo.setPoster(rs.getString(2));
                vo.setTitle(rs.getString(3));
                vo.setSinger(rs.getString(4));
                vo.setAlbum(rs.getString(5));
                //200개 데이터 모아서 브라우저로 전송
                list.add(vo);
            }
            rs.close();
            //ArrayList에 값 채우기
            
        }catch(Exception ex) {
            System.out.println(ex.getMessage()); //에러종류 확인
        }finally {
            disConnection(); //서버 종료
        }
        return list;
    }
    //상세보기
    public MusicVO musicDetailData(int mno) { 
        MusicVO vo=new MusicVO();
        try{
            getConnection();
            String sql="SELECT mno,title,singer,album,poster,key FROM genie_music "
                    + "WHERE mno="+mno;
            ps=conn.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            rs.next();
            //값을 채운다
            vo.setMno(rs.getInt(1));
            vo.setTitle(rs.getString(2));
            vo.setSinger(rs.getString(3));
            vo.setAlbum(rs.getString(4));
            vo.setPoster(rs.getString(5));
            vo.setKey(rs.getString(6));
            rs.close();
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }finally {
            disConnection();
        }
        return vo;
    }
}
 
 
 cs

 

5. servlet MusicServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package com.sist.dao;
 
//servlet: 가벼운 서버 프로그램=>브라우저 실행
import java.io.*;
import java.util.ArrayList;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
@WebServlet("/MusicServlet")
public class MusicServlet 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");
        // html을 브라우저에 보낼 때, charset: 디코딩/인코딩, 한글 깨지지 않게 2byte씩 읽음
        PrintWriter out = response.getWriter();
        // 데이터 읽기
        MusicDAO dao = new MusicDAO();
        ArrayList<MusicVO> list = dao.musicAllData();
        // out=s.getOutputStream()
        // 브라우저에서 메모리에 출력된 HTML을 읽음
        out.println("<html>"); // jsp에서는 out.println 생략
        out.println("<body>");
        out.println("<center>");
        out.println("<h1>지니뮤직 Top200</h1>");
        out.println("<table width=1200 border=1 bordercolor=black>");
        out.println("<tr>");
        out.println("<th>순위</th>");
        out.println("<th></th>");
        out.println("<th>곡명</th>");
        out.println("<th>가수</th>");
        out.println("<th>앨범</th>");
        out.println("</tr>");
        for (MusicVO vo : list) {
            out.println("<tr>");
            out.println("<td>" + vo.getMno() + "</td>");
            out.println("<td><img src="+vo.getPoster()+" width=30 height=30></td>");
            out.println("<td><a href=MusicDetail?mno="+vo.getMno()+">"+vo.getTitle() + "</td>");
            //MusicDetail로 mno값을 넘긴다: url 주소 뒤에 출력
            out.println("<td>" + vo.getSinger() + "</td>");
            out.println("<td>" + vo.getAlbum() + "</td>");
            out.println("</tr>");
        }
        out.println("</table>");
        out.println("</center>");
        out.println("</body>");
        out.println("</html>");
    }
}
 cs

 

6. servlet MusicDetail

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.sist.dao;
 
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;
 
@WebServlet("/MusicDetail")//url 끝에 이게 나와야함
public class MusicDetail 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");
        //값을 받는다
        String mno=request.getParameter("mno");
        MusicDAO dao=new MusicDAO();
        MusicVO vo=dao.musicDetailData(Integer.parseInt(mno));
        
        PrintWriter out=response.getWriter();
        out.println("<html>");
        out.println("<body>");
        out.println("<center>");
        out.println("<h1>&lt;"+vo.getTitle()+"&gt; 상세보기</h1>");
        out.println("<table width=700>");
        out.println("<tr>");
        out.println("<td>");
        //동영상 <iframe>: youtube
        //     <video>: 컴퓨터 안에 저장된 동영상
        out.println("<iframe src=http://youtube.com/embed/"+vo.getKey()+" width=700 height=400></iframe>");
        out.println("</td>");
        out.println("</tr>");
        out.println("</table>");
        
        out.println("<table width=700>");
        out.println("<tr>");
        out.println("<td width=45% rowspan=4>");//네칸 합치는것
        out.println("<img src="+vo.getPoster()+" width=100%>");
        out.println("</td>");
 
        out.println("<tr>");
        out.println("<td>");
        out.println(vo.getTitle());
        out.println("</td>");
        out.println("</tr>");
        
        out.println("<tr>");
        out.println("<td>");
        out.println(vo.getSinger());
        out.println("</td>");
        out.println("</tr>");
        
        out.println("<tr>");
        out.println("<td>");
        out.println(vo.getAlbum());
        out.println("</td>");
        out.println("</tr>");
        
        out.println("<tr>");
        out.println("<td>");
        out.println("<a href=MusicServlet>목록</a>");
        out.println("</td>");
        out.println("</tr>");
        
        out.println("</table>");
        out.println("</center>");
        out.println("<body>");
        out.println("</html>");        
    }
}
 cs

 

출력화면

SCOTT / 7788 입력시
다음과 같은 페이지로 넘어감
음악 목록 클릭시 상세페이지

 

'Java > Java 활용 실습' 카테고리의 다른 글

[Java] 페이지 소스에서 원하는 부분 추출하기(Jsoup)  (1) 2020.07.21
[Java] 퍼즐 만들기  (2) 2020.07.15
[Java] 윈도우에서 로그인창 만들기  (0) 2020.07.13
[Java] Swing Container/Component  (0) 2020.07.13
[Java] 인터넷 익스플로러 페이지 바로 연결하기  (0) 2020.07.10

관련글

댓글

티스토리툴바