(22.12.01)Database : 사원정보 삭제하기, 추가하기, DB에서 테이블 생성, 날짜 가져오기, 시퀀스

●●사원정보 삭제하기●●

 

회원정보를 삭제하면 DB에서도 테이블에서 객체 정보를 삭제하기

 

 

empDAO.java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
public class EmpDAO 
{    
    private Connection conn;
    private Statement stmt;
    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<EmpVO> getList()
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();  
            this.rs = stmt.executeQuery("SELECT * FROM emp2");
            
            List<EmpVO> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public List<EmpVO> getEmpByDeptno(int deptno)
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT * FROM emp2 WHERE deptno=" + deptno;
            this.rs = stmt.executeQuery(sql);
            
            List<EmpVO> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public EmpVO getEmpByEmpno(int empno)
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT * FROM emp2 WHERE empno="+empno;
            this.rs = stmt.executeQuery(sql);
 
            if(rs.next())
            {
                int eno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                int deptno = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(eno);
                emp.setEname(ename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                return emp;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public EmpVO getEmpByEname(String ename)
    {
        getConn();
        
        try {
            String sql = "SELECT * FROM emp2 WHERE ename=?";
            this.pstmt =conn.prepareStatement(sql);
            pstmt.setString(1, ename);
            this.rs = pstmt.executeQuery();
 
            if(rs.next())
            {
                int eno = rs.getInt("EMPNO");
                String insertename = rs.getString("ENAME");
                int deptno = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(eno);
                emp.setEname(insertename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                return emp;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean updateEmp(EmpVO emp)
    {
        getConn();
        
        try {
            String sql = "UPDATE emp2 SET sal=?, deptno=? WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1, emp.getSal());
            pstmt.setInt(2, emp.getDeptno());
            pstmt.setInt(3, emp.getEmpno());
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public boolean deleteEmp(int empno)
    {
        getConn();
        
        try {
            String sql = "DELETE FROM emp2 WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,empno);
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    
    public List<Integer> getDeptnoList()
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT distinct deptno FROM emp2 ORDER BY deptno";
            this.rs = stmt.executeQuery(sql);
            
            List<Integer> list = new ArrayList<>();
            while(rs.next())
            {
                int deptno = rs.getInt("DEPTNO");
 
                list.add(deptno);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(stmt!=null) stmt.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
 

180~199 행 : 파라미터로 넘어온 사원 번호로 DB에 있는 테이블에서 사원번호로 데이터를 찾아 DELETE 해준다.

테이블의 행의 변화가 int rows = stmt.executeUpdate(sql);  이 로직에서 나타나는데 변화가 있다면 1이 나온다.

삼항조건식을 써서 boolean값을 리턴해준다. 

 

EmpService.java

import java.io.*;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
 
public class EmpService 
{
    private HttpServletRequest request;
    private HttpServletResponse response;
    private String viewPath = "/WEB-INF/jsp/emp";
    
    public EmpService(HttpServletRequest request, HttpServletResponse response) 
    {
        this.request = request;
        this.response = response;
    }
    
    public String exec() 
    {
        String cmd = request.getParameter("cmd");
        if(cmd==null || cmd.equals("list"))
        {
            List<EmpVO> list = getList();
            request.setAttribute("list", list);
            return viewPath + "/empList.jsp";
        }
        else if(cmd.equals("findByDeptno"))
        {
            int deptno = Integer.valueOf(request.getParameter("deptno"));
            List<EmpVO> list = getEmpByDeptno(deptno);
            request.setAttribute("list", list);
            return viewPath + "/empList.jsp";
        }
        else if(cmd.equals("getEmpByEmpno"))
        {
            int empno = Integer.valueOf(request.getParameter("empno"));
            EmpVO emp = getEmpByEmpno(empno);
            request.setAttribute("emp", emp);
            return viewPath + "/empDetail.jsp";
        }
        else if(cmd.equals("findByEname"))
        {
            String ename = request.getParameter("ename");
            EmpVO emp = getEmpByEname(ename);
            System.out.println("서비스사번:"+emp.getEmpno());
            request.setAttribute("emp", emp);
            return viewPath + "/empDetail.jsp";
            
        }
        else if(cmd.equals("editEmp"))
        {
            int empno = Integer.valueOf(request.getParameter("empno"));
            EmpVO emp = getEmpByEmpno(empno);
            request.setAttribute("emp", emp);
            return viewPath + "/empEdit.jsp";
        }
        else if(cmd.equals("updateEmp"))
        {
            boolean updated = updateResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("updated", updated);
            sendJSONStr(map);
        
        }
        else if(cmd.equals("deleteEmp"))
        {
            boolean deleted = deleteResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("deleted", deleted);
            sendJSONStr(map);
        
        }
        else if(cmd.equals("getDeptList"))
        {
            String strDeptlist = getDeptList();
            try {
                PrintWriter out = response.getWriter();
                out.print(strDeptlist);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
 
    public List<EmpVO> getList()
    {
        EmpDAO dao = new EmpDAO();
        List<EmpVO> list = dao.getList();
        return list;
    }
    
    public List<EmpVO> getEmpByDeptno(int deptno)
    {
        EmpDAO dao = new EmpDAO();
        List<EmpVO> list = dao.getEmpByDeptno(deptno);
        return list;
    }
    
    public EmpVO getEmpByEmpno(int empno)
    {
        EmpDAO dao = new EmpDAO();
        EmpVO emp = dao.getEmpByEmpno(empno);
        return emp;
    }
    public EmpVO getEmpByEname(String ename) {
        EmpDAO dao = new EmpDAO();
        EmpVO emp = dao.getEmpByEname(ename);
        return emp;    
    }
    public boolean updateResult() {
        String sEmpno = request.getParameter("empno");
        String sSal = request.getParameter("sal");
        String sDeptno = request.getParameter("deptno");
        
        EmpVO emp = new EmpVO();
        emp.setEmpno(Integer.parseInt(sEmpno));
        emp.setSal(Float.parseFloat(sSal));
        emp.setDeptno(Integer.parseInt(sDeptno));
        
        EmpDAO dao = new EmpDAO();
        return dao.updateEmp(emp);
    }
    public boolean deleteResult()
    {    
        int empno = Integer.valueOf(request.getParameter("empno"));
        EmpDAO dao = new EmpDAO();
        return dao.deleteEmp(empno);
    }
    public String getDeptList()
    {
        EmpDAO dao = new EmpDAO();
        List<Integer> list = dao.getDeptnoList();
        JSONArray jsArr = new JSONArray();
        for(int i=0;i<list.size();i++)
        {
            jsArr.add(list.get(i));
        }
        return jsArr.toJSONString();
    }
    public void sendJSONStr(Map<String, Object> map){
        JSONObject jsobj = new JSONObject(map);
        String jsStr = jsobj.toJSONString();
        
        try {
            PrintWriter out = response.getWriter();
            out.print(jsStr);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

68 ~ 76 행 : cmd가 deleteEmp이면 작동되는 로직으로 129 ~134행에서 넘어온 값을 받아 deleted에 저장하고 json문자열 배열로 보내준다. 이건 jsp에서 response에서 받아서 삭제 성공했는지 실패했는지 받는다.

129 ~134 행 : request객체에서 파라미터로 넘어온 사번을 저장하여 EmpDAO 클래스의 deleteEmp의 파라미터에 넣어준다.

 

empDetail.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>사원정보 상세보기</title>
<style>
    main { width: fit-conent; padding:1em; }
    h3 { width:fit-content; text-align: center; }
    label { display:inline-block; margin-right:1em; width:3em; 
        background-color:rgb(200,255,200); text-align:right; padding:0.5em;
    }
    div { margin-top:0.5em; }
</style>
<script src="https://code.jquery.com/jquery-3.6.1.min.js" 
integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous">
</script>
<script type="text/javascript">
    function deleteEmp(empno)
    {
        if(!confirm("정말로 현재 사원정보를 삭제하겠습니까?")) return;
 
        $.ajax({
            url:'oracle', 
            method:'post',
            data: {'cmd':'deleteEmp','empno':empno},
            cache : false,
            dataType: 'json',
            success: function(res){ 
                if(res.deleted){
                    alert(res.deleted ? '삭제 성공':'에러'); 
                    location.href ='oracle?cmd=list';
                }
            },
            error : function(xhr,status,err){
                alert(err);
            }
        });
        return false;
    }
</script>
</head>
<body>
<main>
<h3>사원정보 상세보기</h3>
<div><label>사번</label> ${emp.empno}</div>
<div><label>이름</label> ${emp.ename}</div>
<div><label>부서</label> ${emp.deptno}</div>
<div><label>급여</label> ${emp.sal}</div>
<div><label>입사</label> ${emp.hiredate}</div>
<a href="oracle?cmd=editEmp&empno=${emp.empno}">[수정]</a>
<a href="javascript:deleteEmp(${emp.empno});">[삭제]</a>
</main>
</body>
</html>

EmpService.java 74행에서 넘어온 json 문자열 값을 29행 res 에서 받는다. 넘어온 값이 참이면 삭제 성공을 거짓이면 에러를 alert창에 띄운다.

 

실행결과  :

프로그램 실행 후 첫 화면

 

 

7369 사원 삭제

 

 

확인하는 alert 창

 

 

 

삭제 성공

 

 

7369번 사원이 삭제된 걸 확인 할 수 있다.

 

---

 

●●사원 정보 추가하기●●

 

이용자에게 입력 받은 데이터를 서버에도 저장하면서 DB에도 저장하기

 

 

EmpDAO.java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
public class EmpDAO 
{    
    private Connection conn;
    private Statement stmt;
    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<EmpVO> getList()
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();  
            this.rs = stmt.executeQuery("SELECT * FROM emp2");
            
            List<EmpVO> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public List<EmpVO> getEmpByDeptno(int deptno)
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT * FROM emp2 WHERE deptno=" + deptno;
            this.rs = stmt.executeQuery(sql);
            
            List<EmpVO> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    public EmpVO getEmpByEmpno(int empno)
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT * FROM emp2 WHERE empno="+empno;
            this.rs = stmt.executeQuery(sql);
 
            if(rs.next())
            {
                int eno = rs.getInt("EMPNO");
                String ename = rs.getString("ENAME");
                int deptno = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(eno);
                emp.setEname(ename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                return emp;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean addEmp(EmpVO emp)
    {
        getConn();
        
        try {
            String sql = "INSERT INTO emp2 "
                    + "(empno, ename, deptno, sal, hiredate) VALUES(?,?,?,?,?)";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, emp.getEmpno());
            pstmt.setString(2, emp.getEname());
            pstmt.setInt(3, emp.getDeptno());
            pstmt.setFloat(4, emp.getSal());
            pstmt.setDate(5, emp.getHiredate());
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public EmpVO getEmpByEname(String ename)
    {
        getConn();
        
        try {
            String sql = "SELECT * FROM emp2 WHERE ename=?";
            this.pstmt =conn.prepareStatement(sql);
            pstmt.setString(1, ename);
            this.rs = pstmt.executeQuery();
 
            if(rs.next())
            {
                int eno = rs.getInt("EMPNO");
                String insertename = rs.getString("ENAME");
                int deptno = rs.getInt("DEPTNO");
                java.sql.Date hiredate = rs.getDate("HIREDATE");
                float salary = rs.getFloat("SAL");
                
                EmpVO emp = new EmpVO();
                emp.setEmpno(eno);
                emp.setEname(insertename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                return emp;
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean updateEmp(EmpVO emp)
    {
        getConn();
        
        try {
            String sql = "UPDATE emp2 SET sal=?, deptno=? WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1, emp.getSal());
            pstmt.setInt(2, emp.getDeptno());
            pstmt.setInt(3, emp.getEmpno());
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    public boolean deleteEmp(int empno)
    {
        getConn();
        
        try {
            String sql = "DELETE FROM emp2 WHERE empno=?";
            this.pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,empno);
 
            int rows = pstmt.executeUpdate();
 
            return rows>0 ? true : false;
 
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
    
    public List<Integer> getDeptnoList()
    {
        getConn();
        
        try {
            this.stmt = conn.createStatement();
            String sql = "SELECT distinct deptno FROM emp2 ORDER BY deptno";
            this.rs = stmt.executeQuery(sql);
            
            List<Integer> list = new ArrayList<>();
            while(rs.next())
            {
                int deptno = rs.getInt("DEPTNO");
 
                list.add(deptno);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(stmt!=null) stmt.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

125 ~ 149 행 : 파라미터로 emp 객체를 받아서 emp 객체 내에 있는 사번, 이름, 부서번호, 급여, 입사일을 preparestatment에 저장한다. 이렇게 하면 DB에 데이터가 저장되고, 행의 변화를 0보다 큰지 확인하여 boolean값으로 리턴해준다.

(행의 변화가 있다면 rows가 1이다.)

 

EmpService.java

import java.io.*;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
 
public class EmpService 
{
    private HttpServletRequest request;
    private HttpServletResponse response;
    private String viewPath = "/WEB-INF/jsp/emp";
    
    public EmpService(HttpServletRequest request, HttpServletResponse response) 
    {
        this.request = request;
        this.response = response;
    }
    
    public String exec() 
    {
        String cmd = request.getParameter("cmd");
        if(cmd==null || cmd.equals("list"))
        {
            List<EmpVO> list = getList();
            request.setAttribute("list", list);
            return viewPath + "/empList.jsp";
        }
        else if(cmd.equals("addEmp"))
        {
            return viewPath + "/empAdd.jsp";
        }
        else if(cmd.equals("addResultEmp"))
        {
            boolean added = addEmp();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("added", added);
            sendJSONStr(map);
        }
        else if(cmd.equals("findByDeptno"))
        {
            int deptno = Integer.valueOf(request.getParameter("deptno"));
            List<EmpVO> list = getEmpByDeptno(deptno);
            request.setAttribute("list", list);
            return viewPath + "/empList.jsp";
        }
        else if(cmd.equals("getEmpByEmpno"))
        {
            int empno = Integer.valueOf(request.getParameter("empno"));
            EmpVO emp = getEmpByEmpno(empno);
            request.setAttribute("emp", emp);
            return viewPath + "/empDetail.jsp";
        }
        else if(cmd.equals("findByEname"))
        {
            String ename = request.getParameter("ename");
            EmpVO emp = getEmpByEname(ename);
            System.out.println("서비스사번:"+emp.getEmpno());
            request.setAttribute("emp", emp);
            return viewPath + "/empDetail.jsp";
            
        }
        else if(cmd.equals("editEmp"))
        {
            int empno = Integer.valueOf(request.getParameter("empno"));
            EmpVO emp = getEmpByEmpno(empno);
            request.setAttribute("emp", emp);
            return viewPath + "/empEdit.jsp";
        }
        else if(cmd.equals("updateEmp"))
        {
            boolean updated = updateResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("updated", updated);
            sendJSONStr(map);
        
        }
        else if(cmd.equals("deleteEmp"))
        {
            boolean deleted = deleteResult();
            JSONObject jsObj = new JSONObject();
            Map<String, Object> map = new HashMap<>();
            map.put("deleted", deleted);
            sendJSONStr(map);
        
        }
        else if(cmd.equals("getDeptList"))
        {
            String strDeptlist = getDeptList();
            try {
                PrintWriter out = response.getWriter();
                out.print(strDeptlist);
                out.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
 
    public List<EmpVO> getList()
    {
        EmpDAO dao = new EmpDAO();
        List<EmpVO> list = dao.getList();
        return list;
    }
    
    public List<EmpVO> getEmpByDeptno(int deptno)
    {
        EmpDAO dao = new EmpDAO();
        List<EmpVO> list = dao.getEmpByDeptno(deptno);
        return list;
    }
    
    public EmpVO getEmpByEmpno(int empno)
    {
        EmpDAO dao = new EmpDAO();
        EmpVO emp = dao.getEmpByEmpno(empno);
        return emp;
    }
    public EmpVO getEmpByEname(String ename) {
        EmpDAO dao = new EmpDAO();
        EmpVO emp = dao.getEmpByEname(ename);
        return emp;    
    }
    public boolean updateResult() {
        String sEmpno = request.getParameter("empno");
        String sSal = request.getParameter("sal");
        String sDeptno = request.getParameter("deptno");
        
        EmpVO emp = new EmpVO();
        emp.setEmpno(Integer.parseInt(sEmpno));
        emp.setSal(Float.parseFloat(sSal));
        emp.setDeptno(Integer.parseInt(sDeptno));
        
        EmpDAO dao = new EmpDAO();
        return dao.updateEmp(emp);
    }
    public boolean deleteResult()
    {    
        int empno = Integer.valueOf(request.getParameter("empno"));
        EmpDAO dao = new EmpDAO();
        return dao.deleteEmp(empno);
    }
    public boolean addEmp() {
        String sEmpno = request.getParameter("empno");
        String sEname = request.getParameter("ename");
        String sDeptno = request.getParameter("deptno");
        String sSal = request.getParameter("sal");
        String sHiredate = request.getParameter("hiredate");
        java.sql.Date hiredate = java.sql.Date.valueOf(sHiredate);
        
        EmpVO emp = new EmpVO();
        emp.setEmpno(Integer.parseInt(sEmpno));
        emp.setEname(sEname);
        emp.setDeptno(Integer.parseInt(sDeptno));
        emp.setSal(Float.parseFloat(sSal));
        emp.setHiredate(hiredate);
        
        EmpDAO dao = new EmpDAO();
        return dao.addEmp(emp);
    }
    public String getDeptList()
    {
        EmpDAO dao = new EmpDAO();
        List<Integer> list = dao.getDeptnoList();
        JSONArray jsArr = new JSONArray();
        for(int i=0;i<list.size();i++)
        {
            jsArr.add(list.get(i));
        }
        return jsArr.toJSONString();
    }
    public void sendJSONStr(Map<String, Object> map){
        JSONObject jsobj = new JSONObject(map);
        String jsStr = jsobj.toJSONString();
        
        try {
            PrintWriter out = response.getWriter();
            out.print(jsStr);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

33~39행 :  넘어온 boolean값을 json 문자열로 jsp로 보내준다.

147~164행 : 파라미터로 넘어온 모든 값들을 emp객체에 담아서 EmpDAO의 addEmp() 메서드에 넣어준다.

 

 

empAdd.jsp

<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!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 type="text/javascript">
    function addEmp()
    {
        var empno = $('#empno').val();
        var ename = $('#ename').val();
        var deptno = $('#deptno').val();
        var sal = $('#sal').val();
        var hiredate = $('#hiredate').val();
        if(empno==''|| ename==''|| deptno==''|| sal==''|| hiredate==''){
            alert('모든 항목 입력은 필수입니다.');
            return false;
        }
        alert('정상적으로 입력하여 폼을 전송합니다.');
 
        var obj = $('#addEmp').serialize();
        $.ajax({
            url : 'oracle',
            method: 'post',
            data: obj,
            cache : false,
            dataType:'json',
            success:function(res){
                alert(res.added ? '추가 성공' : '추가 실패');
                location.href="oracle?cmd=list";
            },
            error : function(xhr,status,err){
                alert('에러:' + err);
            }
        });
        return false;
    }
</script>
</head>
<body>
<main>
<h3>사원 정보 추가</h3>
<form id="addEmp" onsubmit="return addEmp();">
    <input type="hidden" name="cmd" value="addResultEmp">
    <div><label>사번</label> 
        <input id="empno" type="number" name="empno" value="">
    </div>
    <div>
        <label for="ename">이름</label> 
        <input id="ename" type="text" name="ename" value="">
    </div>
    <div>
        <label for="deptno">부서</label> 
        <select id="deptno"  name="deptno">
        <option value="10">10</option>
        <option value="20">20</option>
        <option value="30">30</option>
        </select>
    </div>
    <div><label>급여</label> 
        <input id="sal" type="number" name="sal" value="">
    </div>
    <div><label>입사</label> 
        <input id="hiredate" type="date" name="hiredate" value="">
    </div>
    <div class="btn">
        <button type="reset">취소</button>
        <button type="submit">저장</button>
    </div>
</form>
</main>
</body>
</html>

데이터들이 넘어가기 전에 폼 체크를 해주고 서버에서 넘어온 json 문자열을 response에서 받아 추가가 성공했는지 실패했는지 alert 창으로 알려준다.

 

실행 결과 :

첫 실행화면

 

 

사원정보추가 링크를 눌렀을시

 

 

정보 입력

 

 

폼 체크

 

 

추가 성공

 

 

추가한 사원 정보가 나온 걸 확인할 수 있다.

 

---

 

●●DB에서 테이블 생성●●

 

새로운 board 테이블을 생성

DB의 Constraints : 제약조건
- not null : 컬럼값으로 null은 거부
- DEFAULT : 컬럼값을 지정하지 않으면 대신 사용되는 값
- UNIQUE :  해당 컬럼의 값내에서는 중복되지 않아야한다.
- PRIMARY KEY : NOT NULL + UNIQUE
- FOREIGN KEY : 외부 키(참조 키) 다른 테이블의 특정 컬럼 안에 있는 값만 사용

---

 

 

 

현재 시스템의 날짜 DB에 가져오기

SELECT SYSDATE AS today FROM dual; 
오라클에선 SELECT문에서 from을 생략할 수 없다. dual이란 형식적인 테이블(1행과 1열)
SELECT문은 FROM의 행의 수만큼 반복하여 값을 가져오는 특징이 있다.
dual 테이블이 1행만 있으므로 1개 값만 가져온다.
today란 컬럼 명에 현재 컴퓨터의 날짜 불러오기

 

---

DB에서 글을 작성하면 자동으로 보드아이디에 +1씩 증가시켜주기 위하여 오라클에 있는 기능중에 시퀀스 쓰기

기본 값을 1로 설정하고 증분을 1로 두고 캐시는 없음으로 설정해주었다.

 

 

 

게시판 글 작성할 때 보드 아이디 설정 넣는 법

 

 

DB에서 테이블 삭제 & 시퀀스 삭제 방법

DROP TABLE 테이블 명;

DROP SEQUENCE 시퀀스 명;