19.05.02 JSP와 데이터베이스 연동-2 (동영상 26강~

Back-End/JSP 2019. 5. 2. 13:32
728x90
반응형

자바클래스를 만들어 jsp가 자바클래스를 호출해서 회원정보를 받아서 데이터베이스에 입력되도록 만들어보기



회원가입 (MemberJoin.jsp)


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
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 
<center>
    <h2>회원 가입</h2>
    <body>
        <form action="MemberJoinProc.jsp" method="post">
            <table width="500" border="1">
 
                <tr height="50">
                    <td width="150" align="center">아이디</td>
                    <td width="350" align="center"><input type="text" name="id"
                        size="40" placeholder="id를 넣으세요."></td>
                <tr height="50">
                    <td width="150" align="center">패스워드</td>
                    <td width="350" align="center"><input type="password"
                        name="pass1" size="40" placeholder="비밀번호는 영문과 숫자만 넣어주세요."></td>
                <tr height="50">
                    <td width="150" align="center">패스워드 확인</td>
                    <td width="350" align="center"><input type="password"
                        name="pass2" size="40"></td>
                <tr height="50">
                    <td width="150" align="center">이메일</td>
                    <td width="350" align="center"><input type="email"
                        name="email" size="40" placeholer></td>
                <tr height="50">
                    <td width="150" align="center">전화번호</td>
                    <td width="350" align="center"><input type="tel" name="tel"
                        size="40"></td>
                <tr heigth="50">
                    <td width="150" align="center">당신의 관심분야</td>
                    <td width="350" align="center"><input type="checkbox"
                        name="hobby" value="캠핑">캠핑 &nbsp;&nbsp; <input
                        type="checkbox" name="hobby" value="등산">등산 &nbsp;&nbsp; <input
                        type="checkbox" name="hobby" value="영화">영화 &nbsp;&nbsp; <input
                        type="checkbox" name="hobby" value="독서">독서 &nbsp;&nbsp;</td>
                </tr>
                <tr heigth="50">
                    <td width="150" align="center">당신의 직업은</td>
                    <td width="350" align="center"><select name="job">
                            <option value="교사">교사</option>
                            <option value="변호사">변호사</option>
                            <option value="의사">의사</option>
                            <option value="기술사">기술사</option>
                    </select></td>
                </tr>
                <tr height="50">
                    <td width="150" align="center">당신의 연령은</td>
                    <td width="350" align="center"><input type="radio" name="age"
                        value="10">10대 &nbsp;&nbsp; <input type="radio" name="age"
                        value="20">20대&nbsp;&nbsp; <input type="radio" name="age"
                        value="30">30대&nbsp;&nbsp; <input type="radio" name="age"
                        value="40">40대&nbsp;&nbsp;</td>
                </tr>
 
                <tr height="50">
                    <td width="150" align="center">하고싶은말</td>
                    <td width="350" align="center"><textarea rows="5" cols="40"
                            name="info"></textarea></td>
                </tr>
 
                <tr height="50">
                    <td align="center" colspan="2"><input type="submit"
                        value="회원 가입"> <input type="reset" value="취소">
            </table>
        </form>
</center>
</body>
</html>
 
 
cs



자바빈 클래스 (입력한 회원정보를 저장) (MemberBean.java)


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 model;
 
public class MemberBean { //자바빈 클래스
    
    private String id;
    private String pass1;
    private String pass2;
    private String email;
    private String tel;
    private String hobby;
    private String job;
    private String age;
    private String info;
    
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPass1() {
        return pass1;
    }
    public void setPass1(String pass1) {
        this.pass1 = pass1;
    }
    public String getPass2() {
        return pass2;
    }
    public void setPass2(String pass2) {
        this.pass2 = pass2;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    public String getHobby() {
        return hobby;
    }
    public void setHobby(String hobby) {
        this.hobby = hobby;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public String getAge() {
        return age;
    }
    public void setAge(String age) {
        this.age = age;
    }
    public String getInfo() {
        return info;
    }
    public void setInfo(String info) {
        this.info = info;
    }
    
    
    
}
 
cs



데이터베이스에 접속하고 INSERT, DELETE등 명령을 수행하는 클래스 (MemberDAO.java)


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
package model;
 
import java.sql.*; //SQL 패키지인지 반드시 확인
                    //*을 쓰면 sql에 있는 패키지를 모두 가져오기 때문에 따로 안써도 된다.
 
 
//오라클 데이터 베이스에 연결하고 SELECT, INSERT, UPDATE, DELETE 작업을 실행해주는 클래스입니다.
public class MemberDAO {
    
     //아이디,비밀번호 사용해서 연결하는 부분은 반복적으로 계속 사용하므로 메소드로 만드는 것이 좋다.
    
    //오라클에 접속하는 소스를 작성
    String id = "system";
    String pass = "123456";
    String url = "jdbc:oracle:thin:@localhost:1521:XE"; //접속 URL
    
    Connection con; //데이터베이스에 접근할수 있도록 설정
    PreparedStatement pstmt; //데이터베이스에서 쿼리를 실행시켜주는 객체
    ResultSet rs; //데이터베이스의 테이블의 결과(SELECT, INSERT, UPDATE, DELETE 등)
                    //데이터베이스의 테이블의 결과를 리턴받아 자바에 저장해주는 객체
    
    
    
    //데이터베이스에 접근할 수 있도록 도와주는 메소드
    
    public void getCon() 
    {
        
        try 
        {
            //1.해당 데이터베이스를 사용한다고 선언 (클래스를 등록 = 오라클을 사용)
            Class.forName("oracle.jdbc.driver.OracleDriver");
            //2. 해당 데이터 베이스에 접속
            con = DriverManager.getConnection(url,id,pass);
            //con 앞에 Connection을 쓰면 메소드의 지역변수에 저장이 되기때문에 쓰지 않는다
        }
        catch(Exception e) 
        {
            
            
        }
    }
    
    //데이터베이스에 한사람의 회원 정보를 저장해주는 메소드
    public void insertMember(MemberBean mbean) 
    {
        try
        {
            getCon();
            //3. 접속후 쿼리준비하여 쿼리를 사용하도록 설정
            String sql = "insert into member values(?,?,?,?,?,?,?,?)"; //8개의 값이 들어간다.
            //쿼리를 사용하도록 설정
            PreparedStatement pstmt = con.prepareStatement(sql); //jsp 에서 쿼리를 사용하도록 설정
            // ?에 맞게 데이터를 맵핑
            pstmt.setString(1, mbean.getId()); //SQL의 insert to values에 넣을 값들.
            pstmt.setString(2, mbean.getPass1());
            pstmt.setString(3, mbean.getEmail());
            pstmt.setString(4, mbean.getTel());
            pstmt.setString(5, mbean.getHobby());
            pstmt.setString(6, mbean.getJob());
            pstmt.setString(7, mbean.getAge());
            pstmt.setString(8, mbean.getInfo());
            //4. 오라클에서 쿼리를 실행 하시오.
            pstmt.executeUpdate(); //insert, update, delete 시 사용하는 메소드
            
            //5. 자원 반납
            con.close();
            
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
    
    
}
 
cs



자바빈 클래스와 DAO클래스를 취합하는 JSP파일 (MemberJoinProc.jsp)


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
<%@page import="model.MemberDAO"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
 
    <% 
    request.setCharacterEncoding("EUC-KR"); //한글 처리
    
    //취미 부분은 별도로 읽어들여 다시 빈클래스에 저장
    
    String [] hobby = request.getParameterValues("hobby");
    //배열에 있는 내용을 하나의 스트림으로 저장
    String texthobby = "";
    
    for(int i =0; i< hobby.length; i++)
    {
        texthobby += hobby[i]+" "; //취미를 배열을 사용해 하나씩 출력해서 texthobby에 넣는다.
    }
%>
 
    <!-- useBean을 이용하여 한꺼번에 데이터를 받아옴 -->
    <!-- 맵핑 시키시오 -->
    <jsp:useBean id="mbean" class="model.MemberBean">
        <jsp:setProperty name="mbean" property="*" />
    </jsp:useBean>
 
    <% 
    mbean.setHobby(texthobby); //기존 취미는 주소번지가 저장되기에 위에 배열의 내용을 하나의 스트림으로 저장한 변수를 다시 입력
                                                        
    
    MemberDAO mdao = new MemberDAO(); //insert 메소드를 사용하기 위해 MemberDAO 객체 생성
    mdao.insertMember(mbean); //insert메소드를 호출해 mbean에 저장된 값을 넘겨주면 그 값이 데이터베이스에 저장된다.
    
                                                        
%>
 
    오라클에 접속 완료 ~
 
</body>
</html>
cs





데이터베이스에 접속하고 INSERT, DELETE등 명령을 수행하는 클래스(위에 코드에서 SELECT문 사용 검색해서 모든 회원 검색) (MemberDAO.java)



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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
package model;
 
import java.sql.*; //SQL 패키지인지 반드시 확인
import java.util.Vector;
//*을 쓰면 sql에 있는 패키지를 모두 가져오기 때문에 따로 안써도 된다.
 
//오라클 데이터 베이스에 연결하고 SELECT, INSERT, UPDATE, DELETE 작업을 실행해주는 클래스입니다.
public class MemberDAO {
 
    // 아이디,비밀번호 사용해서 연결하는 부분은 반복적으로 계속 사용하므로 메소드로 만드는 것이 좋다.
 
    // 오라클에 접속하는 소스를 작성
    String id = "system";
    String pass = "123456";
    String url = "jdbc:oracle:thin:@localhost:1521:XE"; // 접속 URL
 
    Connection con; // 데이터베이스에 접근할수 있도록 설정
    PreparedStatement pstmt; // 데이터베이스에서 쿼리를 실행시켜주는 객체
    ResultSet rs; // 데이터베이스의 테이블의 결과(SELECT, INSERT, UPDATE, DELETE 등)
                    // 데이터베이스의 테이블의 결과를 리턴받아 자바에 저장해주는 객체
 
    // 데이터베이스에 접근할 수 있도록 도와주는 메소드
 
    public void getCon() {
 
        try {
            // 1.해당 데이터베이스를 사용한다고 선언 (클래스를 등록 = 오라클을 사용)
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2. 해당 데이터 베이스에 접속
            con = DriverManager.getConnection(url, id, pass);
            // con 앞에 Connection을 쓰면 메소드의 지역변수에 저장이 되기때문에 쓰지 않는다
        } catch (Exception e) {
 
        }
    }
 
    // 데이터베이스에 한사람의 회원 정보를 저장해주는 메소드
    public void insertMember(MemberBean mbean) {
        try {
            getCon();
            // 3. 접속후 쿼리준비하여 쿼리를 사용하도록 설정
            String sql = "insert into member values(?,?,?,?,?,?,?,?)"; // 8개의 값이 들어간다.
            // 쿼리를 사용하도록 설정
            PreparedStatement pstmt = con.prepareStatement(sql); // jsp 에서 쿼리를 사용하도록 설정
            // ?에 맞게 데이터를 맵핑
            pstmt.setString(1, mbean.getId()); // SQL의 insert to values에 넣을 값들.
            pstmt.setString(2, mbean.getPass1());
            pstmt.setString(3, mbean.getEmail());
            pstmt.setString(4, mbean.getTel());
            pstmt.setString(5, mbean.getHobby());
            pstmt.setString(6, mbean.getJob());
            pstmt.setString(7, mbean.getAge());
            pstmt.setString(8, mbean.getInfo());
            // 4. 오라클에서 쿼리를 실행 하시오.
            pstmt.executeUpdate(); // insert, update, delete 시 사용하는 메소드
 
            // 5. 자원 반납
            con.close();
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    // 모든 회원의 정보를 리턴해주는 메소드 호출
    // 아까 벡터로 받았기 때문에 리턴타입을 Vector<MemberBean>로 사용한다.
 
    // 회원가입을 했을 때 자료는 bean클래스에 일단 저장하고 다시 벡터에 저장 (반복한다)
    public Vector<MemberBean> allSelectMember() {
        // 가변길이로 데이터를 저장
        Vector<MemberBean> v = new Vector<>();
 
        // 무조건 데이터 베이스는 예외처리를 해야된다.
        // 무조건 예외처리를 해야될때 (데이터베이스, 네트워크, 스레드, 입출력)
        try {
            // 커넥션 연결 (getCon() 메소드를 호출해서 데이터베이스와 연결)
            getCon();
            // 쿼리 준비
            String sql = "select * from member";
            // 쿼리를 실행시켜주는 객체 선언
            pstmt = con.prepareStatement(sql);
            // 쿼리를 실행 시킨 결과를 리턴해서(오라클 테이블의 검색된 결과를 자바객체에 저장)
            rs = pstmt.executeQuery(); // 데이터베이스의 쿼리를 실행한 데이터가 rs에 저장된다.
            // 반복문을 사용해서 rs에 저장된 데이터를 추출해놓아야 한다.
            while (rs.next()) // 저장된 데이터만큼 반복문 실행한다는 뜻
            {
                MemberBean bean = new MemberBean(); // 데이터베이스에서 멤버빈클래스로 데이터를 받아야 하기 때문에 객체 생성
                bean.setId(rs.getString(1)); // 컬럼으로 나뉘어진 데이터를 빈클래스에 저장, 계속 데이터를 넣어야 하기때문에 새로운 객체를 계속 만들어야함
                bean.setPass1(rs.getString(2));
                bean.setEmail(rs.getString(3));
                bean.setTel(rs.getString(4));
                bean.setHobby(rs.getString(5));
                bean.setJob(rs.getString(6));
                bean.setAge(rs.getString(7));
                bean.setInfo(rs.getString(8));
                // 패키징된 memberbean 클래스를 벡터에 저장
                v.add(bean); // 벡터에 0번지부터 순서대로 데이터가 저장
            }
            // 자원 반납
            con.close();
 
        } catch (Exception e) {
 
        }
 
        // 다 저장된 벡터를 리턴
        return v;
    }
 
}
 
cs



회원정보를 가져오고 출력하는 페이지 (MemberList.jsp)


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
<%@page import="java.util.Vector"%>
<%@page import="model.MemberDAO"%>
<%@page import="model.MemberBean"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<body>
 
    <!-- 1.데이터 베이스에서 모든 회원의 정보를 가져옴  2. table태그를 이용하여 화면에 회원들의 정보를 출력-->
 
    <%
        //회원들의 정보가 얼마나 저장되어있는지 모르기에 (배열은 길이가 고정되어 있기 때문에 List나 벡터를 쓴다.) (List,벡터는 가변길이를 지원)
        MemberDAO mdao = new MemberDAO();
        Vector<MemberBean> vec = mdao.allSelectMember();
        //MemberBean클래스를 안넣어주면 기본적으로 벡터는 오브젝트 클래스이기때문에 다시 캐스팅을 해주어야한다.
    %>
 
    <center>
        <h2>모든 회원 보기</h2>
        <table width="800" border="1">
            <tr height="50">
                <td align="center" width="150">아이디</td>
                <td align="center" width="250">이메일</td>
                <td align="center" width="200">전화번호</td>
                <td align="center" width="200">취미</td>
            </tr>
            <%
                for (int i = 0; i < vec.size(); i++) {
                    MemberBean bean = vec.get(i); //벡터에 담긴 빈클래스를 하나씩 추출
            %>
            <tr height="50">
                <td align="center" width="150"><a
                    href="MemberInfo.jsp?id=<%=bean.getId()%>"> <!-- MemberInfo.jsp로 얻은 Id를 넘겨준다. -->
                        <%=bean.getId()%></a></td>
                <td align="center" width="250"><%=bean.getEmail()%></td>
                <td align="center" width="200"><%=bean.getTel()%></td>
                <td align="center" width="200"><%=bean.getHobby()%></td>
            </tr>
 
            <%
                }
            %>
        </table>
    </center>
</body>
</html>
cs









728x90
반응형
저작자표시

'Back-End > JSP' 카테고리의 다른 글

JSP 웹소켓 채팅  (0) 2019.05.03
MySQL JDBC Driver 다운로드  (0) 2019.05.03
19.05.01 기본 SQL문  (0) 2019.05.01
19.05.01 JSP와 자바빈즈  (0) 2019.05.01
19.05.01 JSP와 데이터베이스 연동 (22강~25강)  (0) 2019.05.01
:

티스토리툴바