サーバ08 MVC 2,JDBC

60864 ワード

MVC2
  • MyServlet
  • package com.kh.controller;
    
    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.kh.service.MyService;
    import com.kh.vo.Member;
    
    @WebServlet("/my")
    public class MyServlet extends HttpServlet {
    
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		System.out.println("Controller > doGet called...");
    		
    		String id = request.getParameter("id");
    		String pwd = request.getParameter("pwd");
    		Member member = new Member(id, pwd);
    		
    		//컨트롤러(my)가 서비스 호출, 서비스에게 비즈니스 로직이나 데이터 넘김
    		MyService service = new MyService();
    		service.sss(member);
    	}
    
    }
    
  • MyService
  • package com.kh.service;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    
    import com.kh.dao.MyDao;
    import com.kh.vo.Member;
    
    public class MyService {
    
    	public void sss(Member m) {
    		System.out.println("service > sss called...");
    		
    		//비즈니스 로직 수행
    		//service에서 dao(data access object) 호출
    		MyDao dao = new MyDao();
    		dao.ddd(m);
    		
    //		String url = "jdbc:oracle:thin:@127.0.0.1:1521/xe";
    //		String id = "kh";
    //		String pwd = "kh123";
    //		
    //		String sql = "select 1 fron dual";
    //		
    //		try {
    //			Class.forName("oracle.jdbc.driver.OracleDriver");
    //			Connection conn = DriverManager.getConnection(url, id, pwd);
    //			Statement stmt = conn.createStatement();
    //			
    //			ResultSet rs = stmt.executeQuery(sql);
    //		} catch (ClassNotFoundException e) {
    //			// TODO Auto-generated catch block
    //			e.printStackTrace();
    //		}
    	}
    }
    
  • MyDao
  • package com.kh.dao;
    import com.kh.vo.Member;
    public class MyDao {
    public void ddd(Member m) {
    	System.out.println("DAO > ddd called...");
    	System.out.println(m);
    	
    	//JDBC 사용해서 DB에 접근해서 데이터 처리
    	//CRUD(Create Read Update Delete)
    	//insert
    	//select
    	//update
    	//delete
    }
    }
  • Member
  • package com.kh.vo;
    
    public class Member {
    	private String id;
    	private String pwd;
    	
    	public Member(String id, String pwd) {
    		this.id = id;
    		this.pwd = pwd;
    	}
    	
    	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;
    	}
    
    	@Override
    	public String toString() {
    		return "Member [id=" + id + ", pwd=" + pwd + "]";
    	}
    	
    	
    }
    
    127.0.0.1:8888/mvc2/my?id=abc&pwd=123
    DBへの接続
    package com.kh.dao;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.kh.vo.Member;
    
    public class MyDao {
    
    	public void ddd(Member m) {
    		System.out.println("DAO > ddd called...");
    		System.out.println(m);
    		
    		//JDBC 사용해서 DB에 접근해서 데이터 처리
    		//CRUD(Create Read Update Delete)
    		//insert
    		//select
    		//update
    		//delete
    		
    		//thin : java
    		String url = "jdbc:oracle:thin:@127.0.0.1:1521/xe";
    		String id = "SYSTEM";
    		String pwd = "qlsjtm1122";
    		String sql = "SELECT 1 AS DATA FROM DUAL";
    		
    		try {
    			Class.forName("oracle.jdbc.driver.OracleDriver");
    			Connection conn = DriverManager.getConnection(url, id, pwd);
    			
    			Statement stmt = conn.createStatement();
    			ResultSet rs = stmt.executeQuery(sql);	
    			//로우 0을 가리키는 커서? 컬럼명들이 있는 로우를 가리킨다??
    			//커서를 한 줄 내려야 DATA컬럼의 데이터 1인 인덱스 0을 가져올 수 있다!!
    			//rs.next() 할수록 로우가 한줄씩 내려간다.
    			
    			while(rs.next()) {
    				int data = rs.getInt("DATA");
    				System.out.println("DB에서 가져온 데이터 : " + data);				
    			}
    			
    			rs.next();
    			//컬럼이름이 DATA인 값 가져오기?
    			
    			
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    }
    JDBC
    データベースの接続->クエリーの生成
    会員収入
  • index.jsp
  • <%@ 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>
    	<h1>index page</h1>
    	
    	<a href="member">회원가입</a>
    </body>
    </html>
  • MemberController
  • 
    package com.kh.controller;
    
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    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 MemberController extends HttpServlet {
        @Override
        protected void doGet(HttpServletRequest requset, HttpServletResponse response) throws ServletException, IOException {
            requset.getRequestDispatcher("/WEB-INF/views/memberJoin.jsp").forward(requset, response);
        }
        @Override
        protected void doPost(HttpServletRequest requset, HttpServletResponse response) throws ServletException, IOException {
            //이 메소드의 최종 목표 : 전달받은 데이터 디비에 넣기
            String userId = requset.getParameter("userId");
            String userPwd = requset.getParameter("userPwd");
            String userName = requset.getParameter("userName");
            
            //JDBC
            String url = "jdbc:oracle:thin:@127.0.0.1:1521/xe";
            String id = "kh";
            String pwd = "kh";
            
    //        String query = "INSERT INTO MEMBER(ID, PWD, NAME, ENROLL_DATE)" 
    //        + " VALUES ('" +userId+ "','"+userPwd+"', '"+userName+"', SYSDATE)";
            String sql = "INSERT INTO MEMBER(ID, PWD, NAME, ENROLL_DATE)"
            		+ " VALUES (?, ?, ?, SYSDATE)";
            
            //1. driver를 lib에 등록
            //2. 클래스 등록
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                //3. 커넥션 가져옴(getConnection)
                Connection conn = DriverManager.getConnection(url, id, pwd);
                
    //            Statement stmt = conn.createStatement();
    //            stmt.executeQuery(query);
                
                //아직 완성이 안된 쿼리
                PreparedStatement pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, userId);
                pstmt.setString(2, userPwd);
                pstmt.setString(3, userName);
                
                pstmt.execute();
                
            } catch (ClassNotFoundException e) {
                System.out.println("class exception");
            } catch (SQLException e) {
                System.out.println("sql exception");
                e.printStackTrace();
            }
        }
    }
  • memberJoin.jsp
  • <%@ 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>
    	<h1>회원가입</h1>
    	<form action="/jdbc/member" method="post">
    	아이디 : <input type="text" name="userId"><br>
    	비번 : <input type="password" name="userPwd"><br>
    	이름 : <input type="text" name="userName"><br>
    	<input type="reset" value="취소">
    	<input type="submit" value="가입">
    	</form>
    </body>
    </html>




    prepareStatement()
    String sql = "INSERT INTO MEMBER(ID, PWD, NAME, ENROLL_DATE)"
            		+ " VALUES (?, ?, ?, SYSDATE)";
    
    //아직 완성이 안된 쿼리
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, userId);
    pstmt.setString(2, userPwd);
    pstmt.setString(3, userName);

    会員照会
  • MemberCheck.java
  • package com.kh.controller;
    
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Timestamp;
    
    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("/memberCheck")
    public class MemberCheck extends HttpServlet {
    
    	//회원 조회 페이지 보여주기
    	@Override
    	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    		req.getRequestDispatcher("/WEB-INF/views/memberCheck.jsp").forward(req, resp);
    	}
    	
    	//회원 조회 해오기
    	@Override
    	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    		String userId = req.getParameter("userId");
    		
    		String url = "jdbc:oracle:thin:@127.0.0.1:1521/xe";
    		String id = "kh";
    		String pwd = "kh";
    		
    		String sql = "select * from MEMBER where id = ?";
    		
    		try {
    			Class.forName("oracle.jdbc.driver.OracleDriver");
    			Connection conn = DriverManager.getConnection(url, id, pwd);
    			
    			PreparedStatement pstmt = conn.prepareStatement(sql);
    			pstmt.setString(1, userId);
    			
    			ResultSet rs = pstmt.executeQuery();
    //			rs.next();
    			
    			if(rs.next()) {
    				
    			String memberId = rs.getString("ID");
    			String memberPwd = rs.getString("PWD");
    			String memberName = rs.getString("NAME");
    			Timestamp enrollDate = rs.getTimestamp("ENROLL_DATE");
    			
    			System.out.println(memberId);
    			System.out.println(memberPwd);
    			System.out.println(memberName);
    			System.out.println(enrollDate);
    			}
    		} catch (Exception e) {
    			System.out.println("errrrrror~~!");
    		}
    	}
    }
  • memberCheck.jsp
  • <%@ 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>
    	<h1>회원 조회</h1>
    	
    	<form action="memberCheck" method="post">
    		조회할 회원 아이디 : <input type="text" name="userId"><br>
    		<input type="submit" value="조회">
    	</form>
    </body>
    </html>