서블릿과 DB 연동

프로그래밍/자바 웹 프로그래밍 2019. 12. 19. 01:35

회원정보 테이블 만들기.


회원테이블 스크립트

CREATE TABLE t_member (
    id varchar2(10) PRIMARY KEY,
    pwd varchar2(10),
    name varchar2(50),
    email varchar2(50),
    joinDate DATE DEFAULT sysdate
);

SELECT*
FROM t_member
;

INSERT INTO t_member values('hong', '1212', '홍길동', 'hong@gmail.com', sysdate);
INSERT INTO t_member values('lee', '1212', '이순신', 'lee@test.com', sysdate);
INSERT INTO t_member values('kim', '1212', '김유신', 'kim@jweb.com', sysdate);

COMMIT; -- 커밋을 해야함.

SELECT* FROM t_member;

ojdbc8라이브러리 추가


MemberVO

package sec01.ex01;

import java.sql.Date;

public class MemberVO {
    private String id;
    private String pwd;
    private String name;
    private String email;
    private Date joinDate;


    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public void setJoinDate(Date joinDate) {
        this.joinDate = joinDate;
    }
    public Date getJoinDate() {
        return joinDate;
    }        
}

MemberDAO

package sec01.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {

    private static final String driver = "oracle.jdbc.driver.OracleDriver";
    private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
    private static final String user = "scott";
    private static final String pwd = "tiger";
    private Connection con;
    private Statement stmt;


    public List<MemberVO> listMembers() {
        List<MemberVO> list = new ArrayList<MemberVO>();

        try {
            connDB();
            String query = "select* from t_member";
            System.out.println(query);
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()) {
                String id = rs.getString("id");
                String pwd = rs.getString("pwd");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date joinDate = rs.getDate("joinDate");
                MemberVO  vo = new MemberVO();
                vo.setId(id);
                vo.setPwd(pwd);
                vo.setName(name);
                vo.setEmail(email);
                vo.setJoinDate(joinDate);
                list.add(vo);
            }
            rs.close();
            stmt.close();
            con.close();            
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    private void connDB() {
        try {
            Class.forName(driver);
            System.out.println("Oracle 드라이버 로딩 성공");
            con = DriverManager.getConnection(url, user, pwd);
            System.out.println("Connection 생성 성공");
            stmt = con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

MemberServlet

package sec01.ex01;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;

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("/member")
public class MemberServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        MemberDAO dao = new MemberDAO();
        List<MemberVO> list = dao.listMembers();

        out.print("<html><body>");
        out.print("<table  border=1><tr align='center' bgcolor='lightgreen'>");
        out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td></tr>");

        for (int i=0; i<list.size(); i++) {
            MemberVO memberVO = (MemberVO)list.get(i);
            String id = memberVO.getId();
            String name = memberVO.getName();
            String pwd = memberVO.getPwd();
            String email = memberVO.getEmail();
            Date joinDate = memberVO.getJoinDate();

            out.print("<tr><td>"+id+"</td><td>"+
                    pwd+"</td><td>"+
                    name+"</td><td>"+
                    email+"</td><td>"+
                    joinDate+"</td></tr>");                    
        }
        out.print("</table></body></html>");

    }

}

  • 데이터베이스에 연결하여 t_member 테이블의 데이터를 조회하였다.
  • 이렇게하면 회원을 조회할때마다 계속 새로운 객체를 생성하게 된다.

PrepareStatement

일반 Statement보다 sql 구문을 미리 컴파일해서 실행하므로, 속도가 조금더 빠르다.


MemberDAO

package sec01.ex02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MemberDAO {

    private static final String driver = "oracle.jdbc.driver.OracleDriver";
    private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
    private static final String user = "scott";
    private static final String pwd = "tiger";
    private Connection con;
    private PreparedStatement pstmt;


    public List<MemberVO> listMembers() {
        List<MemberVO> list = new ArrayList<MemberVO>();

        try {
            connDB();
            String query = "select* from t_member";
            System.out.println(query);
            pstmt = con.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery(query);

            while(rs.next()) {
                String id = rs.getString("id");
                String pwd = rs.getString("pwd");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date joinDate = rs.getDate("joinDate");
                MemberVO  vo = new MemberVO();
                vo.setId(id);
                vo.setPwd(pwd);
                vo.setName(name);
                vo.setEmail(email);
                vo.setJoinDate(joinDate);
                list.add(vo);
            }
            rs.close();
            pstmt.close();
            con.close();            
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    private void connDB() {
        try {
            Class.forName(driver);
            System.out.println("Oracle 드라이버 로딩 성공");
            con = DriverManager.getConnection(url, user, pwd);
            System.out.println("Connection 생성 성공");
            pstmt = (PreparedStatement) con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }        
}

커넥션풀

클라이언트에서 요청이 들어올 때마다 새롭게 커넥션을 생성해서 정보를 조회해오는 방식은 바람직하지 않다. 그 이유는 요청이 올때마다 계속해서 커넥션 객체를 생성하게 되, 자원적인 낭비가 심하기 때문이다.

그렇기 때문에 어플리케이션이 실행 될 때 미리 DB연결을 해놓고, 요청이 올 때마다 해당 객체에 접근해서 정보를 조회하는 것이 바람직하다.

커넥션풀 기능은 톰켓컨테이너에서 지원한다.


톰켓컨테이너 - 커넥션풀 사용하기.


context.xml 파일 수정


MemberDAO 수정

package sec02.ex01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class MemberDAO {

//    private static final String driver = "oracle.jdbc.driver.OracleDriver";
//    private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
//    private static final String user = "scott";
//    private static final String pwd = "tiger";

    private Connection con;
    private PreparedStatement pstmt;
    private DataSource dataFactory;

    public MemberDAO() {
        try {
            Context ctx = new InitialContext();
            Context envContext = (Context) ctx.lookup("java:/comp/env");
            dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public List<MemberVO> listMembers() {
        List<MemberVO> list = new ArrayList<MemberVO>();

        try {
//            connDB();
            con = dataFactory.getConnection(); // 커넥션 풀
            String query = "select* from t_member";
            System.out.println(query);
            pstmt = con.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery(query);

            while(rs.next()) {
                String id = rs.getString("id");
                String pwd = rs.getString("pwd");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date joinDate = rs.getDate("joinDate");
                MemberVO  vo = new MemberVO();
                vo.setId(id);
                vo.setPwd(pwd);
                vo.setName(name);
                vo.setEmail(email);
                vo.setJoinDate(joinDate);
                list.add(vo);
            }
            rs.close();
            pstmt.close();
            con.close();            
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /*
    private void connDB() {
        try {
            Class.forName(driver);
            System.out.println("Oracle 드라이버 로딩 성공");
            con = DriverManager.getConnection(url, user, pwd);
            System.out.println("Connection 생성 성공");
            pstmt = (PreparedStatement) con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    */


}
  • 톰켓 컨테이너에서 지정한 커넥션 리소스를 가지고 DAO 객체 생성 시 연결정보를 가져온다.

회원 가입하기


회원가입 폼

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 가입창</title>
<script type="text/javascript">
    function fn_sendMember() {
        var frmMember = document.frmMember;
        var id = frmMember.id.value;
        var pwd = frmMember.pwd.value;
        var name = frmMember.name.value;
        var email = frmMember.email.value;

        if (id.length == 0 || id == "") {
            alert("아이디는 필수입니다.");
        } else if (pwd.length == 0 || pwd == "") {
            alert("비밀번호는 필수입니다.");
        } else if (name.length == 0 || name == "") {
            alert("이름은 필수입니다.");
        } else if (email.length == 0 || email == "") {
            alert("이메일은 필수입니다.");
        } else {
            frmMember.method = "post";
            frmMember.action = "member3";
            frmMember.submit();
        }
    }
</script>
</head>
<body>
    <form name="frmMember">
        <table>
            <th>회원 가입창</th>
            <tr>
                <td>아이디</td>
                <td><input type="text" name="id"></td>
            </tr>
            <tr>
                <td>비밀번호</td>
                <td><input type="password" name="pwd"></td>
            </tr>
            <tr>
                <td>이름</td>
                <td><input type="text" name="name"></td>
            </tr>
            <tr>
                <td>이메일</td>
                <td><input type="text" name="email"></td>
            </tr>
        </table>
        <input type="button" value="가입하기" onclick="fn_sendMember()"> <input
            type="reset" value="다시입력"> <input type="hidden"
            name="command" value="addMember" />
    </form>
</body>
</html>


가입 요청 처리 (Servlet)

package sec02.ex02;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;

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("/member3")
public class MemberServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doHandle(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doHandle(request, response);
    }

    private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        MemberDAO dao = new MemberDAO();
        PrintWriter out = response.getWriter();
        String command = request.getParameter("command");


        if(command != null && command.equals("addMember")) { // 회원 입력
            String _id = request.getParameter("id");
            String _pwd = request.getParameter("pwd");
            String _name = request.getParameter("name");
            String _email = request.getParameter("email");

            MemberVO vo = new MemberVO();
            vo.setId(_id);
            vo.setPwd(_pwd);
            vo.setName(_name);
            vo.setEmail(_email);

            dao.addMember(vo);            
        }

       List list = dao.listMembers();

       out.print("<html><body>");
       out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
       out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td><td >삭제</td></tr>");

        for (int i=0; i<list.size(); i++) {
            MemberVO memberVO = (MemberVO)list.get(i);
            String id = memberVO.getId();
            String name = memberVO.getName();
            String pwd = memberVO.getPwd();
            String email = memberVO.getEmail();
            Date joinDate = memberVO.getJoinDate();

             out.print("<tr><td>"+id+"</td><td>"
                        +pwd+"</td><td>"
                        +name+"</td><td>"
                        +email+"</td><td>"
                        +joinDate+"</td><td>"
                        +"<a href='/member3?command=delMember&id="+id+"'>삭제 </a></td></tr>");            
        }        
         out.print("</table></body></html>");
        out.print("<a href='/memberForm.html'>새 회원 등록하기</a");

    }

}

MemberDAO

package sec02.ex02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class MemberDAO {

//    private static final String driver = "oracle.jdbc.driver.OracleDriver";
//    private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
//    private static final String user = "scott";
//    private static final String pwd = "tiger";

    private Connection con;
    private PreparedStatement pstmt;
    private DataSource dataFactory;

    public MemberDAO() {
        try {
            Context ctx = new InitialContext();
            Context envContext = (Context) ctx.lookup("java:/comp/env");
            dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public List<MemberVO> listMembers() {
        List<MemberVO> list = new ArrayList<MemberVO>();

        try {
//            connDB();
            con = dataFactory.getConnection(); // 커넥션 풀
            String query = "select* from t_member";
            System.out.println(query);
            pstmt = con.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery(query);

            while(rs.next()) {
                String id = rs.getString("id");
                String pwd = rs.getString("pwd");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date joinDate = rs.getDate("joinDate");
                MemberVO  vo = new MemberVO();
                vo.setId(id);
                vo.setPwd(pwd);
                vo.setName(name);
                vo.setEmail(email);
                vo.setJoinDate(joinDate);
                list.add(vo);
            }
            rs.close();
            pstmt.close();
            con.close();            
        }catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    public void addMember(MemberVO memberVO) {
        try {
            Connection con = dataFactory.getConnection();
            String id = memberVO.getId();
            String pwd = memberVO.getPwd();
            String name = memberVO.getName();
            String email = memberVO.getEmail();
            String query = "insert into t_member";

            query += "(id, pwd, name, email)";
            query += " values(?, ?, ?, ?)";
            System.out.println("query=" + query);

            pstmt = con.prepareStatement(query);
            pstmt.setString(1, id);
            pstmt.setString(2, pwd);
            pstmt.setString(3, name);
            pstmt.setString(4, email);
            pstmt.executeUpdate();
            pstmt.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /*
    private void connDB() {
        try {
            Class.forName(driver);
            System.out.println("Oracle 드라이버 로딩 성공");
            con = DriverManager.getConnection(url, user, pwd);
            System.out.println("Connection 생성 성공");
            pstmt = (PreparedStatement) con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    */        
}
  • addMember 메서드 추가.


회원정보 삭제


브라우저 삭제처리 요청.


MemberServlet

        if(command != null && command.equals("addMember")) { // 회원 입력
            String _id = request.getParameter("id");
            String _pwd = request.getParameter("pwd");
            String _name = request.getParameter("name");
            String _email = request.getParameter("email");

            MemberVO vo = new MemberVO();
            vo.setId(_id);
            vo.setPwd(_pwd);
            vo.setName(_name);
            vo.setEmail(_email);

            dao.addMember(vo);            
        }else if(command != null && command.equals("delMember")) {
            String id = request.getParameter("id");
            dao.delMember(id);
        }
  • 넘어온 파라미터요청에 따라 db에서 삭제처리를 수정한다.

MemberDAO

    public void delMember(String id) {
        try {
            con = dataFactory.getConnection();            
            String query = "delete from t_member" + " where id=?"; // 이렇게 나누는 이유는 ?
            System.out.println("prepareStatement=" + query);
            pstmt = con.prepareStatement(query);
            pstmt.setString(1, id);
            pstmt.executeUpdate();
            pstmt.close();

        } catch (Exception e) {
            e.printStackTrace();
        }        
    }


이클립스 디버깅

이클립스 디버깅 기능을 활용하여, 소스 디버깅하기.


소스 중단점 찍기


디버그모드로 서버실행


새로운 회원 등록


perspective 전환


이클립스 메뉴에서 디버깅모드 전환

출처: 자바 웹을 다루는 기술

'프로그래밍 > 자바 웹 프로그래밍' 카테고리의 다른 글

브라우저에서의 사용자정보관리(쿠키, 세션)  (0) 2020.01.16
서블릿 리다이렉트 와 바인딩  (0) 2019.12.26
서블릿 Response객체  (0) 2019.12.15
서블릿 기초 실습  (0) 2019.12.14
서블릿  (0) 2019.12.14
블로그 이미지

파니동

,

공지사항

글 보관함

달력

«   2025/05   »
일 월 화 수 목 금 토
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

티스토리툴바