(22.12.12)MVC 방식 개발방법론2 - SQL연동하여 관리자 사이트 만들기

●●관리자 사이트 만들기●●

 

이용자가 문제를 푼 결과를 받아 채점을 하고 다음 학습을 할 수 있게 해준다.

이용자의 학습 히스토리를 받아서 보게 된다.

 

M : AdminVO.java , UserVO.java , HwStatusVO.java , AdminLmsDAO.java 

V :  adminList.jsp , adminDetail.jsp , AdminLoginForm.jsp , userDetail.jsp

C : AdminLmsServlet.java

Service Class : AdminLmsService.java

 

 

AdminLmsServlet.java

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
 
@WebServlet("/adminlms")
public class AdminLmsServlet extends HttpServlet {
    
    protected void service(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException 
    {
        HttpSession session = request.getSession();
        String uid = (String)session.getAttribute("adminid");
        if(uid==null) {
            session.setAttribute("url", request.getRequestURL().toString());
            response.sendRedirect("lmslogin?cmd=adminloginform");
            return;
        }
        String view = new AdminLmsService(request, response).exec();
        if(view!=null) {
            getServletContext().getRequestDispatcher(view).forward(request, response);
        }
    }
}
 

 

 

 

AdminVO.java

public class AdminVO 
{
    private String adminid;
    private int adminpw;
    private String adminemail;
    private int adminphone;
    
    public AdminVO() {}
    
    public AdminVO(String adminid) {
        this.adminid = adminid;
    }
    public AdminVO(String adminid, int adminpw, String adminemail, int adminphone) {
        this.adminid = adminid;
        this.adminpw = adminpw;
        this.adminemail = adminemail;
        this.adminphone = adminphone;
    }
 
    public String getAdminid() {
        return adminid;
    }
 
    public void setAdminid(String adminid) {
        this.adminid = adminid;
    }
 
    public int getAdminpw() {
        return adminpw;
    }
 
    public void setAdminpw(int adminpw) {
        this.adminpw = adminpw;
    }
 
    public String getAdminemail() {
        return adminemail;
    }
 
    public void setAdminemail(String adminemail) {
        this.adminemail = adminemail;
    }
 
    public int getAdminphone() {
        return adminphone;
    }
 
    public void setAdminphone(int adminphone) {
        this.adminphone = adminphone;
    }
}

 

UserVO.java

public class UserVO 
{
    private String userid;
    private int userpw;
    private String useremail;
    private String userphone;
    
    public UserVO() {}
    
    public UserVO(String userId) {
        this.userid = userId;
    }
    public UserVO(String userid, int userpw, String useremail, String phone) {
        this.userid = userid;
        this.userpw = userpw;
        this.useremail = useremail;
        this.userphone = phone;
    }
    public String getUserid() {
        return userid;
    }
 
    public void setUserid(String userid) {
        this.userid = userid;
    }
 
    public int getUserpw() {
        return userpw;
    }
 
    public void setUserpw(int userpw) {
        this.userpw = userpw;
    }
 
    public String getUseremail() {
        return useremail;
    }
 
    public void setUseremail(String useremail) {
        this.useremail = useremail;
    }
 
    public String getUserphone() {
        return userphone;
    }
 
    public void setUserphone(String userphone) {
        this.userphone = userphone;
    }
}

 

HwStatusVO.java

import java.sql.Timestamp;
 
public class HwStatusVO 
{
    private int num;
    private int lvl_code;
    private String userid;
    private java.sql.Timestamp regdate;
    private int answer;
    private int pass;
    private String edustatus;
    
    public HwStatusVO() {}
    
    public HwStatusVO(int num) {
        this.num = num;
    }
    public HwStatusVO(int num, int lvl_code, String userid, Timestamp regdate, int answer, int pass,
            String edustatus) {
        super();
        this.num = num;
        this.lvl_code = lvl_code;
        this.userid = userid;
        this.regdate = regdate;
        this.answer = answer;
        this.pass = pass;
        this.edustatus = edustatus;
    }
 
    public int getNum() {
        return num;
    }
 
    public void setNum(int num) {
        this.num = num;
    }
 
    public int getLvl_code() {
        return lvl_code;
    }
 
    public void setLvl_code(int lvl_code) {
        this.lvl_code = lvl_code;
    }
 
    public String getUserid() {
        return userid;
    }
 
    public void setUserid(String userid) {
        this.userid = userid;
    }
 
    public java.sql.Timestamp getRegdate() {
        return regdate;
    }
 
    public void setRegdate(java.sql.Timestamp regdate) {
        this.regdate = regdate;
    }
 
    public int getAnswer() {
        return answer;
    }
 
    public void setAnswer(int answer) {
        this.answer = answer;
    }
 
    public int getPass() {
        return pass;
    }
 
    public void setPass(int pass) {
        this.pass = pass;
    }
 
    public String getEdustatus() {
        return edustatus;
    }
 
    public void setEdustatus(String edustatus) {
        this.edustatus = edustatus;
    }
}

 

 

 AdminLmsService.java

import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.json.simple.JSONObject;
 
public class AdminLmsService 
{
    private HttpServletRequest request;
    private HttpServletResponse response;
    private HttpSession session;
    private String viewPath = "/WEB-INF/jsp/lms";
    
    public AdminLmsService(HttpServletRequest request, HttpServletResponse response) {
        this.request = request;
        this.response = response;
        this.session = request.getSession();
    }
    public String exec() 
    {
        String cmd = request.getParameter("cmd");
        if (cmd==null || cmd.equals("list")) {
            
            List<HwStatusVO> adminList = getlist();
            request.setAttribute("adminList", adminList);
            return viewPath+"/adminList.jsp";
        }
        else if(cmd.equals("detail"))
        {
            int num=Integer.parseInt(request.getParameter("num"));
            HwStatusVO hvo = getDetail(num);
            request.setAttribute("hvo", hvo);
            return viewPath+"/adminDetail.jsp";
        }
        else if(cmd.equals("checked"))
        {
            boolean checked = checked();
            Map<String,Object> map = new HashMap<>();
            map.put("checked", checked);
            sendJSONStr(map);
        }
        else if(cmd.equals("userdetail"))
        {
            int num=Integer.parseInt(request.getParameter("num"));
            String userid = request.getParameter("userid");
            HwStatusVO hvo = getDetail(num);
            
            UserVO uvo = getUser(userid);
            String uid=uvo.getUserid();
            String uph = uvo.getUserphone();
            String uemail = uvo.getUseremail();
            
            request.setAttribute("hvo", hvo);
            request.setAttribute("uid", uid);
            request.setAttribute("uph", uph);
            request.setAttribute("uemail", uemail);
            return viewPath+"/userDetail.jsp";
        }
        
        return null;
    }
    
    private List<HwStatusVO> getlist()
    {
        AdminLmsDAO ado = new AdminLmsDAO();
        List<HwStatusVO> adminList = ado.getAdminList();
        return adminList;
    }
    
    private HwStatusVO getDetail(int num)
    {
        AdminLmsDAO ado = new AdminLmsDAO();
        HwStatusVO hvo = ado.getDetail(num);
        return hvo;
    }
    
    private boolean checked()
    {
        String sNum = request.getParameter("num");
        String sPass = request.getParameter("pass");
        String sEdustatus = request.getParameter("edustatus");
        
        HwStatusVO hvo = new HwStatusVO();
        hvo.setNum(Integer.parseInt(sNum));
        hvo.setPass(Integer.parseInt(sPass));
        hvo.setEdustatus(sEdustatus);
        
        AdminLmsDAO dao = new AdminLmsDAO();
        return dao.checkAnswer(hvo);
    }
    private UserVO getUser(String userid)
    {
        AdminLmsDAO ado = new AdminLmsDAO();
        UserVO uvo= ado.getUser(userid);
        return uvo;
    }
    
    public void sendJSONStr(Map<String, Object> map){
        JSONObject jsobj = new JSONObject(map);
        String jsStr = jsobj.toJSONString();
        PrintWriter out;
        try {
            out = response.getWriter();
            out.print(jsStr);
            out.flush();
            
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

 

 

 

AdminLmsDAO.java

import java.security.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
 
public class AdminLmsDAO 
{
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;
    
    private Connection getConn()
    {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "TIGER");
            this.conn = conn;
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public List<HwStatusVO> getAdminList()
    {
        getConn();
        
        
        try {
            String sql="SELECT * FROM hwstatus ORDER BY regdate";
            this.pstmt=conn.prepareStatement(sql);
            this.rs=pstmt.executeQuery();
            
            List<HwStatusVO> list = new ArrayList<>();
                while(rs.next())
                {
                    HwStatusVO hvo = new HwStatusVO();
                    hvo.setNum(rs.getInt("num"));
                    hvo.setUserid(rs.getString("userid"));
                    hvo.setLvl_code(rs.getInt("lvl_code"));
                    hvo.setRegdate(rs.getTimestamp("regdate"));
                    hvo.setAnswer(rs.getInt("answer"));
                    hvo.setPass(rs.getInt("pass"));
                    hvo.setEdustatus(rs.getString("edustatus"));
                    
                    list.add(hvo);
                    
                }
                return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public HwStatusVO getDetail(int num)
    {
        getConn();
        
        try {
 
            String sql="SELECT * FROM hwstatus WHERE num=?";
            this.pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1, num);
            this.rs=pstmt.executeQuery();
    
            if(rs.next())
            {
                int insertnum = rs.getInt("num");
                int lvl_code = rs.getInt("lvl_code");
                String userId = rs.getString("userid");
                java.sql.Timestamp regDate = rs.getTimestamp("regdate");
                int answer = rs.getInt("answer");
                String pass = rs.getString("pass");
                String edustatus = rs.getString("edustatus");
    
                
                HwStatusVO hvo = new HwStatusVO();
                hvo.setNum(insertnum);
                hvo.setUserid(userId);
                hvo.setLvl_code(lvl_code);
                hvo.setRegdate(regDate);
                hvo.setAnswer(answer);
                hvo.setPass(Integer.parseInt(pass));
                hvo.setEdustatus(edustatus);
                
                return hvo;
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public boolean checkAnswer(HwStatusVO hvo)
    {
        getConn();
         try {
        
            String sql1="UPDATE hwstatus SET pass=? , edustatus=? WHERE num=?";
            this.pstmt= conn.prepareStatement(sql1);
            pstmt.setInt(1, hvo.getPass());
            pstmt.setString(2, hvo.getEdustatus());
            pstmt.setInt(3,hvo.getNum());
            
            if(pstmt.executeUpdate()>0)
            {
                String sql2="SELECT pass FROM hwstatus WHERE num=?";
                this.pstmt= conn.prepareStatement(sql2);
                pstmt.setInt(1, hvo.getNum());
                
                
                if(hvo.getPass()==2)
                {
                    String sql3="UPDATE learn_history SET lvl_code=lvl_code+1 WHERE userid=(SELECT userid FROM hwstatus WHERE num=?)";
                    this.pstmt= conn.prepareStatement(sql3);
                    pstmt.setInt(1,hvo.getNum());
                }
            }
            return pstmt.executeUpdate()>0;
                  
         } catch (SQLException e) {
        
        e.printStackTrace();
         }finally {
        closeAll();
        }
         return false;
    }
    public UserVO getUser(String userid)
    {
        getConn();
        try {
 
            String sql="SELECT * FROM lmsuser";
            this.pstmt=conn.prepareStatement(sql);
            this.rs=pstmt.executeQuery();
 
            if(rs.next())
            {
                String insertid = rs.getString("userid");
                String userph = rs.getString("userphone");
                String useremail = rs.getString("useremail");
                
                UserVO uvo = new UserVO();
                uvo.setUserid(insertid);
                uvo.setUserphone(userph);
                uvo.setUseremail(useremail);
                
                return uvo;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

 

 

adminList.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>이용자 답안 보기</title>
<script src="https://code.jquery.com/jquery-3.6.1.min.js" 
integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous">
</script>
<script>
  $(document).ready(function(){
      changeColor();
  })
 
  function changeColor(){
      $('#table tr').mouseover(function(){
         $(this).addClass('changeColor');
      }).mouseout(function() {
         $(this).removeClass('changeColor');
      });
  }
</script>
<style type="text/css">
    @import url(//fonts.googleapis.com/earlyaccess/jejumyeongjo.css);
 
    .jm-font{
        font-family: 'Jeju Myeongjo', cursive;
        color: black;
        font-size:3em;
    }
    .main-font{
        font-family: 'Jeju Myeongjo', serif;
        color: black;
        font-size:1.5em;}
    .changeColor {
      background-color: #bff0ff;}
    
    .wrapper {
    display: flex;
    justify-content: center;
    align-items: center;
    min-height: 100vh;
    }
    main { width:fit-content; margin:1em auto; text-align: center;}
    main h1{ text-align: center; }
    table { border:1px solid black; border-spacing:0; border-collapse: collapse; 
        padding:0.5em; }
    td,th { border-bottom:1px dashed black; padding:0.3em 1em; 
        border-right: 1px solid black;}
    th { background-color:rgb(248,242,24);}
    tr {background-color:rgb(255,255,255);}
    div.links { width:fit-content; margin:1em auto; }
</style>
</head>
<body style="background-color: rgb(227,209,254);">
<div class="wrapper">
<main class="main-font">
<h1 class="jm-font">💁‍♂️이용자 현황 보기</h1>
<form id="Check" onsubmit="return CheckAnswer();">
<input type="hidden" id="cmd" name="cmd" value="adminCheck">
<table id="table">
<tr><th>1️⃣이용자ID</th><th>2️⃣학습 레벨</th><th>3️⃣학습일</th><th>4️⃣답안</th><th>5️⃣결과</th></tr>
<c:forEach var="hvo" items="${adminList}">
 
<tr>
    <td><a href="adminlms?cmd=userdetail&num=${hvo.num}">${hvo.userid}</a> </td>
    <td>${hvo.lvl_code} </td>
    <td>${hvo.regdate} </td>
    <td>${hvo.answer} </td>
    <td><c:choose>
        <c:when test="${hvo.pass==0}"><a href="adminlms?cmd=detail&num=${hvo.num}">채점하러가기</a></c:when>
        <c:when test="${hvo.pass==1}">❌</c:when>    
        <c:when test="${hvo.pass==2}">⭕</c:when></c:choose></td>
</tr>
</c:forEach>
</table>
</form>
</main>
</div>
</body>
</html>

실행 결과 :

 

 

adminDetail.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
 
<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
<script>
function checkAnswer()
{    
    var obj =$('#check').serialize();
    
    $.ajax({
        url:'adminlms',
        method:'post',
        cache:false,
        data:obj,
        dataType:'json',
        success:function(res){
            alert(res.checked? '채점완료' : '채점실패');
            if(res.checked) location.href='adminlms?cmd=list';
        },
        error:function(xhr,status,err){
             alert("에러:" + err);
         }
    });
    return false;
}
 
</script> 
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>체크 페이지 </title>
 
<style type="text/css">
    @import url(//fonts.googleapis.com/earlyaccess/jejumyeongjo.css);
 
    .jm-font{
        font-family: 'Jeju Myeongjo', serif;
        color: black;
        font-size:2em;
    }
    .main-font{
        font-family: 'Jeju Myeongjo', serif;/*웹 폰트 지정*/
        color: black;
        font-size:1.5em;
    }
    .wrapper {
    display: flex;
    justify-content: center;
    align-items: center;
    }
    main { width: fit-conent; margin:1em auto;}
    main h3 { text-align: center; }
    label { display:inline-block; margin-right:1em; width:fit-conent;
         text-align:center; padding:0.5em;}
    .container {width:fit-content; padding:1em; border:1px solid black;}
    .contents > label { position:relative;}
    .btn {font-size: 1.5em;}
</style>
</head>
<body style="background-color: rgb(227,209,254);">
<span class="wrapper">
<main class="main-font">
<h3 class="jm-font">📜채점 및 피드백</h3>
<form id="check" onsubmit="return checkAnswer();">
<input type="hidden" name="cmd" value="checked">
 <input type="hidden" name="num" value="${hvo.num}">
 <div class="container">
    <div>
        <label>1️⃣이용자ID :</label> 
        ${hvo.userid}
    </div>
    <div>
        <label>2️⃣학습 레벨 :</label> 
        ${hvo.lvl_code}
    </div>
    <div>
        <label>3️⃣학습일 :</label>
        ${hvo.regdate}
    </div>
    <div>
        <label>4️⃣답안 :</label>
        ${hvo.answer}
    </div>
    <div>
        <label>5️⃣결과 :</label>
        <input type="radio" name="pass" value="1">불통과
        <input type="radio" name="pass" value="2">통과
    </div>
    <div class="contents">
        <label>6️⃣지도내용 작성</label>
        <div><textarea cols="30" rows="10" id="edustatus" name="edustatus"></textarea></div>
    </div>
</div>
<div class="btn">
<button type="submit">채점완료</button>
<button type="reset">초기화</button>
</div>
</form>
</main>
</body>
</span>
</html>

실행결과 : 

리스트에서 채점하러가기 링크를 누르면 나오는 페이지

 

채점완료 후

 

 

userDetail.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>   
<%
    String uid = (String) request.getAttribute("uid");
    String uph = (String) request.getAttribute("uph");
    String uemail = (String) request.getAttribute("uemail");
%>
   
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>유저 정보 보기</title>
</head>
<style type="text/css">
    @import url(//fonts.googleapis.com/earlyaccess/jejumyeongjo.css);
 
    .jm-font{
        font-family: 'Jeju Myeongjo', serif;
        color: black;
        font-size:2.5em;
    }
    .main-font{
        font-family: 'Jeju Myeongjo', serif;
        color: black;
        font-size:2em;
    }
    .wrapper {
    display: flex;
    justify-content: center;
    align-items: center;
    }
    main { width: fit-conent; margin: auto;}
    main h1 { text-align: center; }
    label { display:inline-block; margin-right:1em; width:fit-conent;
         text-align:center; padding:0.5em;}
    .container {width:fit-content; padding:1em; border:1px solid black;}
    .contents > label { position:relative;}
    a { width: fit-conent; margin:auto;}
</style>
<body style="background-color: rgb(227,209,254);">
<span class="wrapper">
<main class="main-font">
<h1 class="jm-font"> 🙋‍♂️이용자 정보 </h1>
<div class="container">
    <div>
        <label>1️⃣이용자ID :</label> 
        <%=uid%>
    </div>
    <div>
        <label>2️⃣전화 번호 :</label> 
        <%=uph %>
    </div>
    <div>
        <label>3️⃣이메일 :</label>
        <%=uemail%>
    </div>
    <div>
        <label>4️⃣채점 결과 :</label>
        <c:choose>
            <c:when test="${hvo.pass==0}"><a href="adminlms?cmd=detail&num=${hvo.num}">채점하러가기</a></c:when>
            <c:when test="${hvo.pass==1}">불통</c:when>    
            <c:when test="${hvo.pass==2}">통과</c:when></c:choose>
    </div>
    <div>
        <label>5️⃣피드백 :</label>
        ${hvo.edustatus}
    </div>
</div>
 
<a href="adminlms?cmd=detail&num=${hvo.num}">🤦‍♂️채점결과 및 피드백 수정하기</a>
<p>
<a href="adminlms?cmd=list">↩목록 돌아가기</a>
</main>
</span>
</body>
</html>

이용자의  정보를 보거나 채점결과 및 지도내용을 수정하기 위한 페이지

 

수정하기 버튼을 누르면 수정 페이지로 넘어간다

 

수정결과