(DAO에 들어갈 데이터를 관리하는 Java 클래스)
- package : edu.global.ex.vo;
- class : DeptVO
package edu.global.ex.vo;
/*이름 널? 유형
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
*/
// VO : DB를 객체화시킨다.
public class DeptVO {
/* 데이터베이스의 내용에 맞게 변수를 선언한다 */
private int deptno;
private String dname;
private String loc;
public DeptVO() {
/* default constructor */
}
/* Source > Generate Constructor Using Fields */
public DeptVO(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
/* Source > Generate Getters and Setters */
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
(DB에 접속하는 부분)
- package : edu.global.ex.dao;
- class : DeptDAO
package edu.global.ex.dao;
import java.sql.*;
import java.util.*;
import edu.global.ex.vo.DeptVO;
public class DeptDAO {
/* DB Connection part */
private String url = "jdbc:oracle:thin:@localhost:1521:xe";
private String uid = "scott";
private String upw = "tiger";
/* default constructor */
public DeptDAO() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
/* DB Query 작성을 위한 객체 */
public List<DeptVO> deptSelect() {
List<DeptVO> vos = new ArrayList<DeptVO>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
String sql = "SELECT * FROM DEPT"; // String 안에는 ; 넣지 말것
con = DriverManager.getConnection(url, uid, upw);
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
/* VO에서 설정한 대로 이름과 자료형을 맞춰서 받아온다. */
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
DeptVO vo = new DeptVO(deptno, dname, loc); // vo 객체 생성
vos.add(vo); // vos 리스트에 vo 객체 추가
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vos;
}
}
dept_dao.jsp
<%@page import="edu.global.ex.vo.DeptVO"%>
<%@page import="java.util.List"%>
<%@page import="edu.global.ex.dao.DeptDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>DEPT_DAO</title>
</head>
<body>
<%
DeptDAO dao = new DeptDAO();
List<DeptVO> vos = dao.deptSelect();
for(DeptVO vo : vos){ // 하나씩 뽑아온다.
out.println("부서번호 : " + vo.getDeptno() + "<br>");
out.println("부서명 : " + vo.getDname() + "<br>");
out.println("위치 : " + vo.getLoc() + "<br>");
out.println("<hr />");
}
%>
</body>
</html>
EmpVO.java
package edu.global.ex.vo;
/*
이름 널? 유형
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
*/
public class EmpVO {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private int sal;
private int comm;
private int deptno;
public EmpVO() {
}
public EmpVO(int empno, String ename, String job, int mgr, String hiredate, int sal, int comm, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
EmpDAO.java
package edu.global.ex.dao;
import java.sql.*;
import java.util.*;
import edu.global.ex.vo.EmpVO;
public class EmpDAO {
private String url = "jdbc:oracle:thin:@localhost:1521:xe";
private String uid = "scott";
private String upw = "tiger";
public EmpDAO(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<EmpVO> empSelect() {
List<EmpVO> vos = new ArrayList<EmpVO>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
String sql = "SELECT * FROM EMP";
// SQL 문을 원하는 것에 맞추고 시작한다.
con = DriverManager.getConnection(url, uid, upw);
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
String hiredate = rs.getString("hiredate");
int sal = rs.getInt("sal");
int comm = rs.getInt("comm");
int deptno = rs.getInt("deptno");
EmpVO vo = new EmpVO(empno, ename, job, mgr, hiredate, sal ,comm, deptno); // vo 객체 생성
vos.add(vo); // vos 리스트에 vo 객체 추가
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vos;
}
}
emp_dao.jsp
<%@page import="edu.global.ex.vo.EmpVO"%>
<%@page import="java.util.List"%>
<%@page import="edu.global.ex.dao.EmpDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>EMP_DAO</title>
</head>
<body>
<%
EmpDAO dao = new EmpDAO();
List<EmpVO> vos = dao.empSelect();
for(EmpVO vo : vos){
out.println("사원번호 : " + vo.getEmpno() +"<br>");
out.println("이름 : " + vo.getEname() +"<br>");
out.println("직업 : " + vo.getJob() +"<br>");
out.println("매니저 : " + vo.getMgr() +"<br>");
out.println("입사일 : " + vo.getHiredate() +"<br>");
out.println("급여 : " + vo.getSal() +"<br>");
out.println("커미션 : " + vo.getComm() +"<br>");
out.println("부서번호 : " + vo.getDeptno() +"<br>");
out.println("<hr />");
}
%>
</body>
</html>
EmpVO.java
package edu.global.ex.vo;
import java.util.Date; // util Date
/*
이름 널? 유형
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
*/
public class EmpVO {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate; // String, Date, Timestamp 등으로 표현할 수 있다.
private int sal;
private int comm;
private int deptno;
public EmpVO() {
}
public EmpVO(int empno, String ename, String job, int mgr, Date hiredate, int sal, int comm, int deptno) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
EmpDAO.java
package edu.global.ex.dao;
import java.sql.Connection;
import java.sql.Date; // SQL 사용하므로, sql로 import
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import edu.global.ex.vo.EmpVO;
public class EmpDAO {
private String url = "jdbc:oracle:thin:@localhost:1521:xe";
private String uid = "scott";
private String upw = "tiger";
public EmpDAO() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<EmpVO> empSelect() {
List<EmpVO> vos = new ArrayList<EmpVO>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
String sql = "SELECT * FROM EMP";
con = DriverManager.getConnection(url, uid, upw);
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
Date hiredate = rs.getDate("hiredate");
int sal = rs.getInt("sal");
int comm = rs.getInt("comm");
int deptno = rs.getInt("deptno");
EmpVO vo = new EmpVO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
vos.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vos;
}
}
EmpVO vo = new EmpVO();
vo.setHiredate(hireDate);
vo.setEmpno(empno);
emp_dao.jsp
<%@page import="edu.global.ex.vo.EmpVO"%>
<%@page import="java.util.List"%>
<%@page import="java.util.Date"%>
<%@page import="edu.global.ex.dao.EmpDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>EMP_DAO</title>
</head>
<body>
<%
EmpDAO dao = new EmpDAO();
List<EmpVO> vos = dao.empSelect();
for(EmpVO vo : vos){
out.println("사원번호 : " + vo.getEmpno() +"<br>");
out.println("이름 : " + vo.getEname() +"<br>");
out.println("직업 : " + vo.getJob() +"<br>");
out.println("매니저 : " + vo.getMgr() +"<br>");
out.println("입사일 : " + vo.getHiredate() +"<br>");
out.println("급여 : " + vo.getSal() +"<br>");
out.println("커미션 : " + vo.getComm() +"<br>");
out.println("부서번호 : " + vo.getDeptno() +"<br>");
out.println("<hr />");
}
%>
</body>
</html>
<Resource
auth="Container"
driverClassName="oracle.jdbc.OracleDriver"
maxIdle="10"
maxTotal="20"
maxWaitMillis="-1"
name="jdbc/oracle"
password="tiger"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
username="scott"
/>
<!--
auth : 컨테이너를 자원 관리자로 기술
name : JDBC이름, 변경 가능
driverClassName : JDBC 드라이버
type : 웹에서 이 리소스를 사용할 때 DataSource로 리턴됨
username : 접속계정
password : 접속할 계정 비밀번호
loginTimeout : 연결 끊어지는 시간
maxActive : 최대 연결 가능한 Connection수 (기본 20개)
maxIdle : Connection pool 유지를 위해 최대 대기 connection 숫자
maxWait : 사용 가능한 커넥션이 없을 때 커넥션 회수를 기다리는 시간 (1000 = 1초)
testOnBorrow : db에 test를 해볼 것인지
-->
DeptDAO.java
package edu.global.ex.dao;
import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import edu.global.ex.vo.DeptVO;
public class DeptDAO {
// url, uid, upw를 지우고 (커넥션 풀에 추가됨) 새로운 코드를 작성한다.
private DataSource dataSource; // 커넥션 풀 객체
public DeptDAO() {
// 생성자 안의 try~catch를 지우고 (커넥션 풀에 추가됨) 새로운 코드를 작성한다.
// javax.naming import
try {
Context context = new InitialContext();
// context.xml 내용을 데려오는 객체
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
// context.xml의 name과 맞춰준다. context의 주소를 찾아서 dataSource 변수에 집어넣는다.
} catch (Exception e) {
e.printStackTrace();
}
}
public List<DeptVO> deptSelect() {
List<DeptVO> vos = new ArrayList<DeptVO>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
String sql = "SELECT * FROM DEPT";
//con = DriverManager.getConnection(url, uid, upw);
con = dataSource.getConnection(); // dataSource와 연결한다.
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
int deptno = rs.getInt("deptno");
String dname = rs.getString("dname");
String loc = rs.getString("loc");
DeptVO vo = new DeptVO(deptno, dname, loc);
vos.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vos;
}
}
EmpDAO.java
package edu.global.ex.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import edu.global.ex.vo.EmpVO;
public class EmpDAO {
private DataSource dataSource;
public EmpDAO() {
try {
Context context = new InitialContext();
dataSource = (DataSource) context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<EmpVO> empSelect() {
List<EmpVO> vos = new ArrayList<EmpVO>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
String sql = "SELECT * FROM EMP";
con = dataSource.getConnection();
st = con.createStatement();
rs = st.executeQuery(sql);
while (rs.next()) {
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
Date hiredate = rs.getDate("hiredate");
int sal = rs.getInt("sal");
int comm = rs.getInt("comm");
int deptno = rs.getInt("deptno");
EmpVO vo = new EmpVO(empno, ename, job, mgr, hiredate, sal, comm, deptno);
vos.add(vo);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (st != null)
st.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return vos;
}
}
1을 표현하는 방식
EL은 숫자 표현, 연산, 조건식, boolean, 문자 표현 등이 가능하다.
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id="empVO" class="edu.global.ex.vo.EmpVO" />
<jsp:setProperty name="empVO" property="ename" value="홍길동" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>EL_ex</title>
</head>
<body>
${empVO.ename}<br>
</body>
</html>