(22.12.16)Spring 프레임워크 : 스프링 프레임워크와 오라클 연동하기 spring framework, oracle , jdbc, ojdbc

●●Spring 프레임워크 < - >  오라클 연결 방법●●

 

 

 

1번 방법

jdbc 저수준 코드를 사용하는 방법(프로젝트에 jdbc 드라이버만 등록해주고 코딩만 해주면 된다.)

 

2번 방법

스프링 프레임워크에서 제공해주는 jdbc 라이브러리 사용

 

3번 방법

2번 방법 + ORM 프레임워크(MyBatis) 사용하기

4번 방법

JPA(Java Persistence Api) 사용하기

 

 

 

---

 

●●1번 방법: 프로젝트에 jdbc 드라이버 설치 후 저수준 코드를 사용하는 방법●●

프로젝트에 jdbc 드라이버만 등록해주고 코딩만 해주면 된다.

 

 

먼저 jdbc 라이브러리를 설치하고 싶은 프로젝트에 우클릭하여 [Build Path] - [Configure Build Path]에 들어간다.

 

 

 

그다음 Classpath를 선택하고 [Add External JARs] 눌러 ojdbc 파일이 들어 있는 경로를 찾아서 추가한 뒤 [Apply] [Apply and Close] 해주면 된다.

 

 

생성된 결과 : 

 

 

 

---

●●Oracle SCOTT계정의 EMP table과 Spring 연동해보기●●

 

 

TestController.java

import java.util.HashMap;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.ezen.spring.web.service.EmpDAOService;
import com.ezen.spring.web.vo.Emp;
import lombok.extern.slf4j.Slf4j;
 
//@RestController 모든 메소드가 Responsebody를 갖게된다.
@Controller
@RequestMapping("/test")
@Slf4j
public class TestController 
{   
    @Autowired
    private EmpDAOService svc;
    
    @GetMapping("/add")
    public String showAddForm()
    {
        return "/emp/empAdd";
    }
    @PostMapping("/add")
    @ResponseBody
    public Map<String,Object> addEmp(Emp emp)
    {
        boolean added = svc.add(emp);
        Map<String,Object> map = new HashMap<>();
        map.put("added", added);
        return map;
    }
    @GetMapping("/list")
    public String list(Model m)
    {
       m.addAttribute("list", svc.getList());
       return "/emp/empList";
    }
    @GetMapping("/detailEmp/empno={num}")
    public String detail(@PathVariable(name="num") int num, Model m)
    {
       m.addAttribute("emp", svc.detailEmp(num));
       return "/emp/empDetail";
    }
    @GetMapping("/update/empno={num}")
    public String showUpdateForm(@PathVariable(name="num") int num, Model m)
    { 
        m.addAttribute("emp", svc.detailEmp(num));
        return "/emp/empUpdate";
    }
    @PostMapping("/updateResult")
    @ResponseBody
    public Map<String,Boolean> updateResult(Emp emp)
    {
       boolean updated = svc.update(emp);
       Map<String,Boolean> map = new HashMap<>();
       map.put("updated", updated);
       return map;
    }
    @PostMapping("/delete")
    @ResponseBody
    public Map<String,Boolean> delete(int empno)
    {
       boolean deleted = svc.delete(empno);
       Map<String,Boolean> map = new HashMap<>();
       map.put("deleted", deleted);
       return map;
    }
}

 

 

 

EmpDAOService.java

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ezen.spring.web.dao.EmpDAO;
import com.ezen.spring.web.vo.Emp;
 
@Service
public class EmpDAOService 
{
    @Autowired
    private EmpDAO dao;
    
    public boolean add(Emp emp) {
        return dao.addEmp(emp);
    }
    
    public List<Emp> getList() {
        return dao.getList();
    }
    public Emp detailEmp(int empno)
    {
        return dao.getEmpByEmpno(empno);
    }
    public boolean update(Emp emp)
    {
        return dao.updateEmp(emp);
    }
    public boolean delete(int empno)
    {
        return dao.deleteEmp(empno);
    }
}

 

 

 

EmpDAO.java

EMP 테이블을 복사하여 EMP2 테이블을 만들어 활용하였다.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Component;
import com.ezen.spring.web.vo.Emp;
 
@Component
public class EmpDAO 
{
    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<Emp> getList()
    {
        getConn();
        
        try {
            String sql="SELECT * FROM emp2";
            this.pstmt=conn.prepareStatement(sql);
            this.rs=pstmt.executeQuery();
            
            List<Emp> list = new ArrayList<>();
            while(rs.next())
            {
                int empno = 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");
                
                Emp emp = new Emp();
                emp.setEmpno(empno);
                emp.setEname(ename);
                emp.setDeptno(deptno);
                emp.setHiredate(hiredate);
                emp.setSal(salary);
                
                list.add(emp);
            }
            return list;
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            closeAll();
        }
        return null;
    }
    public boolean addEmp(Emp 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 Emp getEmpByEmpno(int empno)
    {
        getConn();
        
        try {
            String sql = "SELECT * FROM emp2 WHERE empno="+empno;
            this.pstmt = conn.prepareStatement(sql);
            this.rs = pstmt.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");
                
                Emp emp = new Emp();
                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 updateEmp(Emp 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;
    }
    private void closeAll()
    {
        try {
            if(rs!=null) rs.close();
            if(pstmt!=null) pstmt.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

 

 

empAdd.jsp

<%@ page language="java" 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 obj = $('#empadd').serialize();
    $.ajax({
        url : '/test/add',
        method: 'post',
        data: obj,
        cache : false,
        dataType:'json',
        success:function(res){
            alert(res.added ? '추가 성공' : '추가 실패');
            location.href = '/test/list'
        },
        error : function(xhr,status,err){
            alert('에러:' + err);
        }
    });
    return false;
}
</script>
</head>
<body>
<h2>사원 추가</h2>
<form id="empadd"onsubmit="return addEmp();">
사번<input id="empno" type="number" name="empno" value=""><br>
이름<input id="ename" type="text" name="ename" value="입력해주세요"><br>
부서<input id="deptno" type="number" name="deptno" value=""><br>
연봉<input id="sal" type="number" name="sal" value=""><br>
입사일<input id="hiredate" type="date" name="hiredate" value=""><br>
<button type="submit">저장</button>
</form>
</body>
</html>

 

실행 결과 :

 

 

empList.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>
</head>
<body>
 
<h3> 사원 목록</h3>
<c:forEach var="line" items="${list}">
   ${line.empno}&emsp;
   <a href="/test/detailEmp/empno=${line.empno}">${line.ename}</a>&emsp;
   ${line.deptno}&emsp;
   ${line.sal}&emsp;
   ${line.hiredate}<br>
</c:forEach>
</body>
</html>

실행 결과 : 

 

 

empDetail.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>사원 상세 보기 보기 </title>
</head>
<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:'/test/delete', 
            method:'post',
            data: {'empno':${emp.empno}},
            cache : false,
            dataType: 'json',
            success: function(res){ 
                if(res.deleted){
                    alert(res.deleted ? '삭제 성공':'에러'); 
                    location.href ='/test/list';
                }
            },
            error : function(xhr,status,err){
                alert(err);
            }
        });
        return false;
    }
</script>
<body>
<h3> 사원 상세정보</h3>
 
사번:   ${emp.empno}&emsp;
이름:   ${emp.ename}&emsp;
부서번호:   ${emp.deptno}&emsp;
급여:   ${emp.sal}&emsp;
입사일:   ${emp.hiredate}<br>
 
[<a href="/test/list"> 등록된 이용자 정보 보기 </a>]
[<a href="/test/update/empno=${emp.empno}"> 수정 </a>]
[<a href="javascript:deleteEmp(${emp.empno});"> 삭제 </a>]
</body>
</html>

실행 결과 : 

 

 

삭제하기

삭제 후 리스트

 

 

empUpdate.jsp

<%@ page language="java" 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 updateEmp()
{
    var obj = $('#empupdate').serialize();
    $.ajax({
        url : '/test/updateResult',
        method: 'post',
        data: obj,
        cache : false,
        dataType:'json',
        success:function(res){
            alert(res.updated ? '수정 성공' : '수정 실패');
            location.href = '/test/list'
        },
        error : function(xhr,status,err){
            alert('에러:' + err);
        }
    });
    return false;
}
</script>
</head>
<body>
<h2>사원정보 수정</h2>
<form id="empupdate"onsubmit="return updateEmp();">
<input type="hidden" name="empno" value="${emp.empno }">
사번:  ${emp.empno }<br>
이름:  ${emp.ename }<br>
부서<input id="deptno" type="number" name="deptno" value="${emp.deptno }"><br>
연봉<input id="sal" type="number" name="sal" value="${emp.sal}"><br>
입사일:  ${emp.hiredate }<br>
<button type="submit">저장</button>
</form>
</body>
</html>

실행 결과 :

 

 

수정 결과