본문 바로가기
DB/Oracle

JSP와 데이터베이스 연동-1

by avvin 2019. 5. 2.

JSP와 데이터베이스 연동


1. 회원가입 jsp 페이지

2. MemberBean.java

3. Oracle에서 Member Table 생성

4. 처리 jsp에서 DB 연결 후 저장 ( jsp에서 MemberBean에 데이터 저장하고 usebean을 데이터 처리 )

------------------------------------------------------------------------------------------------------------------

5. 회원 전체보기 페이지(Response.sendRedirect("")를 사용)

6. 수정, 삭제 가능하도록



1. 회원가입 jsp 페이지

2. MemberBean.java

1, 2 단계는 미리 만들어둔 jsp페이지와 자바파일로 대체


3.Member Table 생성


  • Toad for Oracle 로그인 
  • Database > Create > Table
  • Table Name : Member  //대소문자 구분 X
  • >Add Column > Data Type : VARCHAR2 / Size : 20 / PK(Primary Key) 



자바는 오라클을 지원하지 않기때문에 오라클을 사용하게 해줄 별도의 클래스파일을 오라클사에서 제공한 것이 Ojdbc.jar


Ojdbc14.jar 파일 : jsp와 Oracle을 접속해주는 클래스의 묶음 파일 //*Ojdbc6.jar : 커넥션 풀을 이용할때


 << oracle.jdbc.driver.OracleDriver 클래스 있는지 확인 해보기






MemberJoin.jsp (회원가입 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
74
75
76
77
78
79
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
     <div style="text-align:center;">
        <h2 align="center">회원가입</h2>
<form align="center" action="MemberJoinProc.jsp" method="post">
        <table align="center" width="500" border="1">
            <tr height="50">
                <td width="150" align="center">아이디</td>
                <td width="350" align="center" ><input type="text" name="id"
                    placeholder="id를 입력하세요" size="40"></td>
                    </tr>
            <tr height="50">
                <td width="150" align="center">패스워드</td>
                <td width="350" align="center"><input type="password"
                    name="password" placeholder="비밀번호는 영문과 숫자만 넣어주세요" size="40"></td>
                    </tr>
            <tr height="50">
                <td width="150" align="center">패스워드 확인</td>
                <td width="350" align="center"><input type="password"
                    name="password2" size="40"></td>
                    </tr>
            <tr height="50">
                <td width="150" align="center">이메일</td>
                <td width="350" align="center"><input type="email" name="email" size="40"></td>
                </tr>
            <tr height="50">
                <td width="150" align="center">전화번호</td>
                <td width="350" align="center"><input type="tel" name="tel" size="40"></td>
                </tr>
            <tr height="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 height="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="회원가입"> &nbsp; &nbsp; &nbsp; &nbsp; <input
                    type="reset" value="다시 입력"></td>
            </tr>
        </table>
    </form>    
    </div>
</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
package model;
 
public class MemberBean {
    
    private String id;
    private String password;
    private String email;
    private String tel;
    private String hobby; //다른 변수는 일대일로 Bean 클래스에 매핑되는데 
                    //배열은 회원가입 처리페이지에서 for문으로 하나의 String으로 만든 후
                    // setProperty 또는 setter메서드로 Bean클래스에 직접 넣어줘야한다.★
    private String job;
    private String age;
    private String info;
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    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




MemberJoinProc.jsp (처리 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
74
75
76
77
78
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
    <%
        request.setCharacterEncoding("UTF-8"); //한글처리. UTF-8은 굳이 할필요 X
    //hobby는 배열타입이므로 serProperty 자동저장이 안된다.
    String[] hobby = request.getParameterValues("hobby");//배열 받아올 때 사용하는 getValues
    //배열에 있는 내용을 하나의 스트링으로 저장해야 한다.
    String texthobby = "";
    
    for (String hob : hobby){
        
        texthobby += hob + " ";
    }
    %>
    
    
    <!-- useBean을 사용하여 한꺼번에 데이터 받아오기 -->
    <jsp:useBean id="mbean" class="model.MemberBean"><!--model은 패키지이름 -->
    <jsp:setProperty name="mbean" property ="*"/> <!-- 맵핑 -->
    </jsp:useBean>
    <!-- 자동 맵핑 과정에서 mbean의 hobby에 선택한 것 중 하나만 들어갔을 것 -->
    <!-- 위에서 만든  texthobby의 값으로 바꿔줘야한다 -->
    
    <% mbean.setHobby(texthobby);  
    
    //오라클에 접속하는 소스를 작성
    String id = "system"; // 접속 아이디
    String pass ="123456";
    String url="jdbc:oracle:thin:@localhost:1521:XE"; //접속 URL
    
    try{
        //오라클 사용 규칙! 외워야 할 부분
        //1. 해당 데이터베이스를 사용한다고 선언(클래스 등록 = 오라클용을 사용)
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2. 해당 데이터베이스에 접속
        Connection con = DriverManager.getConnection(url, id, pass);
        
        out.println(con);
        //3. 접속 후 쿼리를 준비하여 
        String sql="insert into member values(?,?,?,?,?,?,?,?)";
        //쿼리를 사용하도록 설정
        PreparedStatement pstmt = con.prepareStatement(sql);//jsp에서 쿼리를 사용하도록 설정
        //?에 맞게 데이터를 맵핑 // 쿼리에 데이터를 넣어줌
        pstmt.setString(1,mbean.getId());    //첫번째 물음표자리라는 뜻, 값
        pstmt.setString(2,mbean.getPassword());
        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();
    }
    %>
    
    오라클에 데이터 이동 완료
 
    
 
</body>
</html>
cs




*DAO (Data Access Object):


위 DAO부분은 모두 자바코드이기 때문에 따로 클래스로 만들고 jsp에서 호출해오는 식의 패턴 = 모델2 패턴


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
<%@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=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
    <%
        //request객체는 스크립트릿 안에 쓰기는 하지만 자바로 못넘어가기 때문에 jsp에서 쓸 수 밖에 없다
        request.setCharacterEncoding("UTF-8");
        String[] hobby = request.getParameterValues("hobby");
        String texthobby = "";
        for (String hob : hobby) {
 
            texthobby += hob + " ";
        }
    %>
 
    <!-- useBean을 사용하여 한꺼번에 데이터 받아오기 -->
    <jsp:useBean id="mbean" class="model.MemberBean">
        <!--model은 패키지이름 -->
        <jsp:setProperty name="mbean" property="*" />
        <!-- 맵핑 -->
    </jsp:useBean>
 
    <%
        mbean.setHobby(texthobby);
 
        //데이터베이스에 한 사람의 회원 정보를 저장해주는 메서드
        MemberDAO mdao = new MemberDAO();
 
        mdao.insertMember(mbean);
    %>
 
    오라클에 데이터 이동 완료
 
</body>
</html>
cs



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
package model;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
//오라클 데이터베이스에 연결하고 쿼리 사용 설정 후 
//select insert update delete 작업을 실행해주는 클래스
//모델2패턴, 반복적으로 사용하는 기능(중복해서 쓰는 소스는)은 메서드로 빼주는 것이 좋다
public class MemberDAO {
 
    // 오라클에 접속하는 소스를 작성
    String id = "system"; // 접속 아이디
    String pass = "123456";
    String url = "jdbc:oracle:thin:@localhost:1521:XE";
 
    Connection con; // 데이터베이스에 접근할 수 있도록 설정해주는 객체
    PreparedStatement pstmt; // 데이터베이스에서 쿼리를 실행시켜주는 객체
    ResultSet rs; // 데이터베이스 테이블의 결과를 리턴받아 자바에 저장해주는 객체
 
    // 데이터베이스에 접근할 수 있도록 도와주는 메서드
    public void getCon() {
 
        try {
            // 1. 해당 데이터베이스를 사용한다고 선언(클래스 등록 = 오라클용을 사용)
            Class.forName("oracle.jdbc.driver.OracleDriver");
            // 2. 해당 데이터베이스에 접속
            con = DriverManager.getConnection(url, id, pass);
 
        } catch (Exception e) {
        }
 
    }
 
    // 데이터베이스에 한 사람의 회원 정보를 저장해주는 메서드
    public void insertMember(MemberBean mbean) {
        try {
            getCon();
            String sql = "insert into member values(?,?,?,?,?,?,?,?)";
            // 쿼리를 사용하도록 설정
            PreparedStatement pstmt = con.prepareStatement(sql);
            // ?에 맞게 데이터를 맵핑
            pstmt.setString(1, mbean.getId()); // 첫번째 물음표자리라는 뜻, 값
            pstmt.setString(2, mbean.getPassword());
            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



저작자표시

'DB > Oracle' 카테고리의 다른 글

oracle 11g 트랜잭션 관리COMMIT ROLLBACKDML 인강  (0) 2019.05.31
Oracle sql developer tool : oracle developer the network adapter could not establish the connection  (0) 2019.05.28
커넥션풀(DBCP)  (0) 2019.05.07
JSP와 데이터베이스 연동-3  (0) 2019.05.07
JSP와 데이터베이스 연동-2  (0) 2019.05.06

관련글

티스토리툴바