서버08_MVC2, JDBC

charl hi·2021년 12월 16일

서버

목록 보기

8/15

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

DB에 연결-> 쿼리문 생성

회원가입

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>