[Oracle]Database(Oracle) 데이터 HTML에 출력하기

 

 

 

empVO.java

package com.sist.dao;
 
public class empVO {
    private String ename;
    private String empno;
    private String job;
    private String hiredate;
    private double sal;
    private double comm;
    private int deptno;
    public String getEname() {
        return ename;
    }
    public void setEname(String ename) {
        this.ename = ename;
    }
    public String getEmpno() {
        return empno;
    }
    public void setEmpno(String empno) {
        this.empno = empno;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
    public String getHiredate() {
        return hiredate;
    }
    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }
    public double getSal() {
        return sal;
    }
    public void setSal(double sal) {
        this.sal = sal;
    }
    public double getComm() {
        return comm;
    }
    public void setComm(double comm) {
        this.comm = comm;
    }
    public int getDeptno() {
        return deptno;
    }
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
}
 

- 먼저 표에 필요한 데이터들을 VO에 입력하고 [Source] - [Generate Getters and Setters]로 메소드를 자동으로 생성 및 import 해준다.

 

 

 

 

empDAO.java

package com.sist.dao;
 
import java.util.*;
import java.sql.*;
 
public class empDAO {
    private Connection conn;
    private PreparedStatement ps;
    private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
    }
    public void getConnection() {
        try {
            conn=DriverManager.getConnection(URL, "hr","happy");
        }catch(Exception ex) {}
    }
    public void disConnection() {
        try {
            if(ps!=null)ps.close();
            if(conn!=null)conn.close();
        }catch(Exception ex) {}
    }
    public ArrayList<empVO> empAllData(){
        ArrayList<empVO> list=new ArrayList<empVO>();
        try{
            // 오라클 연결
            getConnection();
            // SQL 문장 전송
            String sql="SELECT DISTINCT ename, empno, job, hiredate, sal, comm, deptno "
                    + "FROM emp ORDER BY ename ASC";
            ps=conn.prepareStatement(sql); //executeQuery()
            // 결과값 받기
            ResultSet rs=ps.executeQuery();
            while(rs.next()) {
                empVO vo=new empVO();
                vo.setEname(rs.getString(1));
                vo.setEmpno(rs.getString(2));
                vo.setJob(rs.getString(3));
                vo.setHiredate(rs.getString(4));
                vo.setSal(rs.getDouble(5));
                vo.setComm(rs.getDouble(6));
                vo.setDeptno(rs.getInt(7));
                
                // 200개를 모아서 => 브라우저로 전송
                list.add(vo);
            }
            rs.close();
            // ArrayList에 값 채우기
        }catch(Exception ex) {
            // Error시에 에러 종류 확인
            System.out.println(ex.getMessage());
        }finally {
            //서버 종료
            disConnection();
        }
        return list;
    }
    // 상세보기
    public empVO empDetailData(int mno) {
        empVO vo=new empVO();
        try {
            getConnection();
            String sql="SELECT ename, empno, job, hiredate, sal, comm, deptno FROM emp WHERE mno="+mno;
        ps=conn.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        rs.next();
        //값을 채운다
        vo.setEname(rs.getString(1));
        vo.setEmpno(rs.getString(2));
        vo.setJob(rs.getString(3));
        vo.setHiredate(rs.getString(4));
        vo.setSal(rs.getDouble(5));
        vo.setComm(rs.getDouble(6));
        vo.setDeptno(rs.getInt(7));
        rs.close();
        }catch(Exception ex) {
            System.out.println(ex.getMessage());
        }
        finally {
            disConnection();
        }return vo;
    }
}

- DAO에서 Oracle과 연동하고 SQL문장 전송 명령어를 작성

 

 

 

 

 

 

empServlet.java

package com.sist.dao;
 
import java.io.*;
import java.util.ArrayList;
 
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("/empServlet")
public class empServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=EUC-KR");
        PrintWriter out=response.getWriter();
        // 데이터 읽기
        empDAO dao=new empDAO();
        ArrayList<empVO> list=dao.empAllData();
        // out=s.getOutputStream()
        // 브라우저에서 메모리에 출력된 HTML을 읽어 간다
        out.println("<html>");
        out.println("<body>");
        out.println("<center>");
        out.println("<h1> 사원 정보</h1>");
        out.println("<table width=1200 border=1 bodercolor=black>");
        out.println("<tr>");
        out.println("<th>이름</th>");
        out.println("<th>사원번호</th>");
        out.println("<th>직위</th>");
        out.println("<th>입사일</th>");
        out.println("<th>급여</th>");
        out.println("<th>성과금</th>");
        out.println("<th>부서번호</th>");
        out.println("</tr>");
        // for
        for(empVO vo:list)
        {
            out.println("<tr>");
            out.println("<td>" +vo.getEname()+"</td>");
            out.println("<td>"+vo.getEmpno()+"</td>");
            out.println("<td>" +vo.getJob()+"</td>");
            out.println("<td>" +vo.getHiredate()+"</td>");
            out.println("<td>" +vo.getSal()+"</td>");
            out.println("<td>" +vo.getComm()+"</td>");
            out.println("<td>" +vo.getDeptno()+"</td>");
            out.println("</tr>");
        }
        out.println("</table>");
        out.println("</center>");
        out.println("</body>");
        out.println("</html>");
    }
}

- [Servlet] 파일을 하나 만들어서 WEB으로 띄울 HTML코드를 입력

 

 

 

 

 

실행 화면 

 

 

 

ㄴ