11. DataBase 작업

JDBC를 이용한 연결
DBCP를 이용한 연결

11.1 JDBC(Java Database Connectivity)

자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API
데이터베이스에서 자료를 쿼리하거나 업데이트하는 방법을 제공

11.1.1 설치

각 DB의 JDBC 드라이버 파일을 WEB-INF/lib/ 폴더로 복사
H2 DataBase DownLoad
h2\bin\h2-1.4.200.jar 복사
h2\bin\ht.bat 으로 실행

11.1.2 JDBC 실행 순서

데이터베이스 연동을 위한 4가지 정보를 문자열에 저장 (driver, url, user, password)
드라이버 로딩
Connection 맺기
Statement 생성
SQL문 작성
SQL 문 전송 및 결과값 얻기

11.1.3 USERS DB CRUD 개발

java 파일 없이 개발 해보기

USERS.sql

CREATE TABLE `users` (
  `ID` varchar2(8) PRIMARY KEY,
  `PASSWORD` varchar2(8),
  `NAME` varchar2(20),
  `ROLE` varchar2(5)
);

INSERT INTO USERS VALUES('admin', '1111','admin','Admin');
INSERT INTO USERS VALUES('hong', '1234','홍길동','User');

JDBCTest.jsp

<%@page import="java.util.ArrayList"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@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>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";
ArrayList<String> userId = new ArrayList<String>();
try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "select * from users";
    pstmt = conn.prepareStatement(sql);
    rs = pstmt.executeQuery();

    while(rs.next()){
        userId.add(rs.getString("id"));
    }
}catch (Exception e){
    e.printStackTrace();
}finally{
    rs.close();
    pstmt.close();
    conn.close();
}
%>
<h1>User ID</h1>
<ul>
<% for(String id: userId) {%>
<li><%=id %></li>
<%} %>
</ul>

</body>
</html>

index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>Index</h1>
<h2>Menu</h2>
<ul>
<li><a href="join.html">Join</a></li>
<li><a href="login.html">Login</a></li>
<li><a href="modify.jsp">Modify</a></li>
</ul>
</body>
</html>

USERS 가입

C : insert

join.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>JOIN - 가입</title>
<style>
    th {width:100px; background-color: rgb(200, 150, 200); align:center;}
</style>
</head>
<body>
<center>
<h1>JOIN - 가입</h1>
<hr>
<form action="joinProc.jsp" method="post">
<table border="1">
    <tr><th>ID</td><td><input type="text" name="id"></td></tr>
    <tr><th>PW</td><td><input type="password" name="password"></td></tr>
    <tr><th>NAME</td><td><input type="text" name="name"></td></tr>
    <tr><th>ROLE</td>
    <td>
    User : <input type="radio" name="role" value="User" checked="checked">   
    Admin : <input type="radio" name="role" value="Admin">  
    </td></tr>

    <tr><td colspan="2" align="center">
    <input type="submit" value="Join">
    <input type="button" value="Cancel" onclick="history.back();">

    </td></tr>
</table>
</form>
<hr>
</center>
</body>
</html>

joinProc.jsp

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");
String name = request.getParameter("name");
String role = request.getParameter("role");
%>

<%
// DB - Insert 작업
// JDBC 처리
Connection conn = null;
PreparedStatement pstmt = null;
int rs = 0;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";
try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "insert into users(id,password,name,role) values(?,?,?,?)";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, id);
    pstmt.setString(2, pw);
    pstmt.setString(3, name);
    pstmt.setString(4, role);
    rs = pstmt.executeUpdate();

}catch (Exception e){
    e.printStackTrace();
}finally{
    pstmt.close();
    conn.close();
}
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Login Fail!!");
history.back();
</script>
<%
}else{
%>
<script>
alert("Join OK!!");
document.location.href="login.html"
</script>
<%} %>

USERS 로그인/로그아웃

R : select
session 객체 활용

session 객체 활용

세션은 클라이언트와 웹 서버 간의 상태를 지속적으로 유지하는 사용
웹 브라우저 마다 하나씩 존재
서버에 저장되어 보안에 유리
세션 지속 유효 시간이 있음(IIS 기본 20분, Tomcat 기본 30분)
값 할당 : session.setAtrribute("name","value")
값 사용 : session.getAtrribute("name")
세션 삭제 : session.invalidate();

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Login - 로그인</title>
<style>
    th {width:100px; background-color: rgb(200, 150, 200); align:center;}
</style>
</head>
<body>
<center>
<h1>Login - 로그인</h1>
<hr>
<form action="loginProc.jsp" method="post">
<table border="1">
    <tr><th>ID</td><td><input type="text" name="id"></td></tr>
    <tr><th>PW</td><td><input type="password" name="password"></td></tr>
    <tr><td colspan="2" align="center"><input type="submit" value="LogIn"></td></tr>
</table>
</form>
<hr>
</center>
</body>
</html>

loginProc.jsp

<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");
%>

<%
// DB - Insert 작업
// JDBC 처리
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";
boolean isLogin = false;
String name = "";
try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "select * from users where id=? and password=?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, id);
    pstmt.setString(2, pw);
    rs = pstmt.executeQuery();

    if(rs.next()){
         isLogin = true;
         name = rs.getString("name");
    }

}catch (Exception e){
    e.printStackTrace();
}finally{
    pstmt.close();
    conn.close();
}
%>

<%
// 성공 여부 체크
if(!isLogin){
%>
<script>
alert("Join Fail!!");
history.back();
</script>
<%
}else{
    // 로그인 성공
    session.setAttribute("id", id);
    session.setAttribute("name", name);

    response.sendRedirect("main.jsp");
} %>

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Main - 메인</title>
</head>
<body>
<h1>Main - 메인</h1>
<hr>
${sessionScope.name }님 환영!! | <a href="logout.jsp">logout</a>
| <a href="modify.jsp">modify</a> | <a href="deleteProc.jsp?id=${sessionScope.id }">delete</a>
<hr>
</body>
</html>

logout.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    session.invalidate();
%>

<script>
alert("Logout OK!!");
location.href="index.html"
</script>

USERS 정보 수정

U : update

modify.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.Connection"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<style>
    th {width:100px; background-color: rgb(200, 150, 200); align:center;}
</style>
</head>
<body>
<%
// 로그인 및 세션 확인
String id = null;
if(session.getAttribute("id") != null){
    id = (String)session.getAttribute("id");
}
%>
<%if(id == null) {%>
<script>
alert("Login State Fail!!");
location.href="login.html";
</script>
<%}%>

<%
// DB - Insert 작업
// JDBC 처리
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";

// user info
String pwd = "";
String name = "";
String role = "";

try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "select * from users where id=?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, id);
    rs = pstmt.executeQuery();

    if(rs.next()){
        pwd = rs.getString("password");
        name = rs.getString("name");
        role = rs.getString("role");
    }

}catch (Exception e){
    e.printStackTrace();
}finally{
    pstmt.close();
    conn.close();
}
%>
<center>
<h1>Modify - 수정</h1>
<hr>
<form action="modifyProc.jsp" method="post">
<table border="1">
    <tr><th>ID</td><td><input type="text" name="id" value="<%=id %>" readonly></td></tr>
    <tr><th>PW</td><td><input type="text" name="password" value="<%=pwd%>"></td></tr>
    <tr><th>NAME</td><td><input type="text" name="name"  value="<%=name%>"></td></tr>
    <tr><th>ROLE</td>
    <td>
    User <input type="radio" name="role" value="User" <%if(role.equals("User")){ %>checked="checked" <%} %>>   
    Admin <input type="radio" name="role" value="Admin" <%if(role.equals("Admin")){ %>checked="checked" <%} %>>   
    </td></tr>

    <tr><td colspan="2" align="center">
    <input type="submit" value="Modify">
    <input type="button" value="Cancel" onclick="history.back();">

    </td></tr>
</table>
</form>
<hr>
</center>
</body>
</html>

modifyProc.jsp

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");
String name = request.getParameter("name");
String role = request.getParameter("role");
%>

<%
// DB 작업
// JDBC 처리
Connection conn = null;
PreparedStatement pstmt = null;
int rs = 0;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";
try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "update users set password=?, name=?, role=? where id=?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, pw);
    pstmt.setString(2, name);
    pstmt.setString(3, role);
    pstmt.setString(4, id);
    rs = pstmt.executeUpdate();

}catch (Exception e){
    e.printStackTrace();
}finally{
    pstmt.close();
    conn.close();
}
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Modify Fail!!");
history.back();
</script>
<%
}else{
%>
<script>
alert("Modify OK!!");
document.location.href="main.jsp"
</script>
<%} %>

USERS 탈퇴

D : delete

deleteProc.jsp

<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.PreparedStatement"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
%>
<%if(id == null) {%>
<script>
alert("Login State Fail!!");
location.href="login.html";
</script>
<%}%>

<%
// DB 작업
// JDBC 처리
Connection conn = null;
PreparedStatement pstmt = null;
int rs = 0;

String driver = "org.h2.Driver";
String url = "jdbc:h2:tcp://localhost/~/test";
String user = "sa";
String password = "";
try{

    Class.forName(driver);
    conn = DriverManager.getConnection(url, user, password);
    String sql = "delete from users where id=?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, id);
    rs = pstmt.executeUpdate();

}catch (Exception e){
    e.printStackTrace();
}finally{
    pstmt.close();
    conn.close();
}
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Delete Fail!!");
history.back();
</script>
<%
}else{
    session.invalidate();
%>
<script>
alert("Delete OK!!");
document.location.href="index.html"
</script>
<%} %>

11.1.3 USERS DB CRUD 개발

VO, DAO, Service java 파일 추가 개발하기
User 정보 저장
공통적인 DB 작업 분리
서비스 추가 DB 관련 변경되어도 소스 수정 최소화

VO(Value Object) or DTO(Data Transfer Object) 작업

UserVO.java

package user.jdbc.vo;

public class UserVO {
    private String id;
    private String password;
    private String name;
    private String role;

    public UserVO() {
    }

    public UserVO(String id, String password, String name, String role) {
        super();
        this.id = id;
        this.password = password;
        this.name = name;
        this.role = role;
    }

    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 getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return "UserVO [id=" + id + ", password=" + password + ", name=" + name + ", role="+ role +"]";
    }
}

공통 작업

JDBCUtil.java

package user.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCUtil {
    public static Connection getConnection() {
        try {
            String driver = "org.h2.Driver";
            String url = "jdbc:h2:tcp://localhost/~/test";
            String user = "sa";
            String password = "";
            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    public static void close(PreparedStatement stmt, Connection conn) {

            try {
                if(stmt != null) stmt.close();
                if(conn != null) conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                stmt = null;
                conn = null;
            }
    }

    public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {

        try {
            if(rs != null) rs.close();
            if(stmt != null) stmt.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            rs = null;
            stmt = null;
            conn = null;
        }
    }

}

DAO(Data Access Object) 작업

UserDAO.java

package users.dao;

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

import user.common.JDBCUtil;
import users.vo.UserVO;

public class UserDAO {
    // JDBC
    private Connection conn = null;
    private PreparedStatement stmt = null;
    private ResultSet rs = null;

    // SQL
    private final String USER_INSERT = "insert into users(id,password,name,role) values(?,?,?,?)";
    private final String USER_UPDATE = "update users set password=?, name=?, role=? where id=?";
    private final String USER_DELETE = "delete from users where id=?";
    private final String USER_GET_ID = "select * from users where id=?";
    private final String USER_GET_ID_PWD = "select * from users where id=? and password=?";
    private final String USER_LIST = "select * from users order";

    // Method
    // USER_INSERT
    public int insertUSER(UserVO vo) {
        System.out.println("insertUSER()");
        int rs = 0;
        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.prepareStatement(USER_INSERT);
            stmt.setString(1, vo.getId());
            stmt.setString(2, vo.getPassword());
            stmt.setString(3, vo.getName());
            stmt.setString(4, vo.getRole());
            rs = stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(stmt, conn);
        }

        return rs;
    }

    // USER_UPDATE
    public int updateUSER(UserVO vo) {
        System.out.println("updateUSER()");
        int rs = 0;

        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.prepareStatement(USER_UPDATE);
            stmt.setString(1, vo.getPassword());
            stmt.setString(2, vo.getName());
            stmt.setString(3, vo.getRole());
            stmt.setString(4, vo.getId());
            rs = stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(stmt, conn);
        }

        return rs;
    }

    // USER_DELETE
    public int deleteUSER(UserVO vo) {
        System.out.println("delete");
        int rs = 0;

        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.prepareStatement(USER_DELETE);
            stmt.setString(1, vo.getId());
            rs = stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(stmt, conn);
        }

        return rs;
    }

    // USER_GET
    public UserVO getUSER(UserVO vo) {
        System.out.println("getUSER()");
        UserVO user = null;
        try {
            conn = JDBCUtil.getConnection();
            if(vo.getPassword().equals("")) {
                stmt = conn.prepareStatement(USER_GET_ID);
                stmt.setString(1, vo.getId());
            }else {
                stmt = conn.prepareStatement(USER_GET_ID_PWD);
                stmt.setString(1, vo.getId());
                stmt.setString(2, vo.getPassword());
            }
            rs = stmt.executeQuery();
            if(rs.next()) {
                String id = rs.getString("id");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String role = rs.getString("role");
                user = new UserVO(id, password, name, role);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, stmt, conn);
        }
        return user;
    }

    // USER_LIST
    public List<UserVO> getUserList() {
        System.out.println("getUserList()");
        List<UserVO> userList = new ArrayList<UserVO>();
        UserVO user = null;
        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.prepareStatement(USER_LIST);
            rs = stmt.executeQuery();
            while(rs.next()) {
                String id = rs.getString("id");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String role = rs.getString("role");
                user = new UserVO(id, password, name, role);
                userList.add(user);

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs, stmt, conn);
        }

        return userList;
    }
}

서비스 작업

인터페이스 및 구현

UserService.java

package user.service;

import java.util.List;

import users.vo.UserVO;

public interface UserService {

    // insertUser
    int insertUSER(UserVO vo);

    // updateUser
    int updateUSER(UserVO vo);

    // deleteUser
    int deleteUSER(UserVO vo);

    // getUser
    UserVO getUSER(UserVO vo);

    // getUserList
    List<UserVO> getUserList();


}

UserServiceImpl.java

package user.serviceImpl;

import java.util.List;

import user.service.UserService;
import users.dao.UserDAO;
import users.vo.UserVO;

public class UserServiceImpl implements UserService{

    UserDAO dao = new UserDAO();

    @Override
    public int insertUSER(UserVO vo) {
        return dao.insertUSER(vo);
    }

    @Override
    public int updateUSER(UserVO vo) {
        return dao.updateUSER(vo);
    }

    @Override
    public int deleteUSER(UserVO vo) {
        return dao.deleteUSER(vo);
    }

    @Override
    public UserVO getUSER(UserVO vo) {

        vo = dao.getUSER(vo);

        return vo;
    }

    @Override
    public List<UserVO> getUserList() {

        List<UserVO> userList = dao.getUserList();

        return userList;
    }
}

Join DB 작업 수정

joinProc.jsp

<%@page import="users.vo.UserVO"%>
<%@page import="user.serviceImpl.UserServiceImpl"%>
<%@page import="user.service.UserService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");
String name = request.getParameter("name");
String role = request.getParameter("role");

// User 생성
UserVO vo = new UserVO(id, pw, name, role);

%>

<%
// insert Servie 진행
UserService service = new UserServiceImpl();
int rs = service.insertUSER(vo);
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Join Fail!!");
history.back();
</script>
<%
}else{
%>
<script>
alert("Join OK!!");
document.location.href="login.html"
</script>
<%} %>

loginProc.jsp

<%@page import="user.serviceImpl.UserServiceImpl"%>
<%@page import="user.service.UserService"%>
<%@page import="users.vo.UserVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");

// user  생성
UserVO vo = new UserVO(id, pw, "", "");
%>

<%
// insert Servie 진행
UserService service = new UserServiceImpl();
UserVO rs = service.getUSER(vo);
%>

<%
// 성공 여부 체크
if(rs == null){
%>
<script>
alert("Login Fail!!");
history.back();
</script>
<%
}else{
    // 로그인 성공
    session.setAttribute("id", rs.getId());
    session.setAttribute("name", rs.getName());

    response.sendRedirect("main.jsp");
} %>

Modify DB 작업 수정

modify.jsp

<%@page import="users.vo.UserVO"%>
<%@page import="user.serviceImpl.UserServiceImpl"%>
<%@page import="user.service.UserService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"  pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title></title>
<style>
    th {width:100px; background-color: rgb(200, 150, 200); align:center;}
</style>
</head>
<body>
<%
// 로그인 및 세션 확인
String id = null;
if(session.getAttribute("id") != null){
    id = (String)session.getAttribute("id");
}
%>
<%if(id == null) {%>
<script>
alert("Login State Fail!!");
location.href="login.html";
</script>
<%}%>

<%
// get user Servie 진행
UserVO vo = new UserVO(id,"","","");
UserService service = new UserServiceImpl();
UserVO rs = service.getUSER(vo);
String pwd = rs.getPassword();
String name = rs.getName();
String role = rs.getRole();
%>

<center>
<h1>Modify - 수정</h1>
<hr>
<form action="modifyProc.jsp" method="post">
<table border="1">
    <tr><th>ID</td><td><input type="text" name="id" value="<%=id %>" readonly="readonly"></td></tr>
    <tr><th>PW</td><td><input type="text" name="password" value="<%=pwd%>"></td></tr>
    <tr><th>NAME</td><td><input type="text" name="name"  value="<%=name%>"></td></tr>
    <tr><th>ROLE</td>
    <td>
    User <input type="radio" name="role" value="User" <%if(role.equals("User")){ %>checked="checked" <%} %>>   
    Admin <input type="radio" name="role" value="Admin" <%if(role.equals("Admin")){ %>checked="checked" <%} %>>   
    </td></tr>

    <tr><td colspan="2" align="center">
    <input type="submit" value="Modify">
    <input type="button" value="Cancel" onclick="history.back();">

    </td></tr>
</table>
</form>
<hr>
</center>
</body>
</html>

modifyProc.jsp

<%@page import="user.serviceImpl.UserServiceImpl"%>
<%@page import="user.service.UserService"%>
<%@page import="users.vo.UserVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
String pw = request.getParameter("password");
String name = request.getParameter("name");
String role = request.getParameter("role");

//User 생성
UserVO vo = new UserVO(id, pw, name, role);
%>

<%
// update Servie 진행
UserService service = new UserServiceImpl();
int rs = service.updateUSER(vo);
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Modify Fail!!");
history.back();
</script>
<%
}else{
%>
<script>
alert("Modify OK!!");
document.location.href="main.jsp"
</script>
<%} %>

Delete DB 작업 수정

deleteProc.jsp

<%@page import="user.serviceImpl.UserServiceImpl"%>
<%@page import="user.service.UserService"%>
<%@page import="users.vo.UserVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
// 1. 넘어온 값 확인
String id = request.getParameter("id");
%>
<%if(id == null) {%>
<script>
alert("Login State Fail!!");
location.href="login.html";
</script>
<%}%>

<%
//User 생성
UserVO vo = new UserVO(id, "", "", "");

// delete Servie 진행
UserService service = new UserServiceImpl();
int rs = service.updateUSER(vo);
%>

<%
// 성공 여부 체크
if(rs == 0){
%>
<script>
alert("Delete Fail!!");
history.back();
</script>
<%
}else{
    session.invalidate();
%>
<script>
alert("Delete OK!!");
document.location.href="index.html"
</script>
<%} %>

11. DataBase 작업

11.1 JDBC(Java Database Connectivity)

11.1.1 설치

11.1.2 JDBC 실행 순서

11.1.3 USERS DB CRUD 개발

USERS 가입

USERS 로그인/로그아웃

session 객체 활용

USERS 정보 수정

USERS 탈퇴

11.1.3 USERS DB CRUD 개발

VO(Value Object) or DTO(Data Transfer Object) 작업

공통 작업

DAO(Data Access Object) 작업

서비스 작업

Join DB 작업 수정

Login DB 작업 수정

Modify DB 작업 수정

Delete DB 작업 수정