GDJ 24/04/18 (DAO, VO, 쇼핑몰 프로젝트)

 - 고도화 DAO 작업 마무리
 - shop 디자인 작업

 - oracle-sql 프로젝트 생성
 - DAO, VO
	- DBHelper.java
	- DeptDAO.java
	- EmpDAO.java

	- Dept.java (VO)
	- Emp.java (VO)
	
	- q001VoOrMap.jsp

 - github oracle-sql 레퍼지토리 생성

 - 오라클 클라우드 사용자 연동

로컬일 때 Oracle Cloud 접속 (사용자추가)

DBHelper.java

package dao;

import java.io.FileReader;
import java.sql.*;
import java.util.Properties;

public class DBHelper {
	public static Connection getConnection() throws Exception{
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		// String dbUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
		// String dbUser = "admin";
		
		// FileReader fr = new FileReader("D:\\dev\\auth\\oracle.properties");
		FileReader fr = new FileReader("C:\\Users\\GD\\Desktop\\oracle\\oracle\\oracledb.properties");
		Properties prop = new Properties();
		prop.load(fr);
		
		String dbUrl = prop.getProperty("url");
		String dbUser = prop.getProperty("id");
		String dbPw = prop.getProperty("pw");
		
		Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPw);
		return conn;
	}
	
	public static void main(String[] args) throws Exception {
		Connection conn = DBHelper.getConnection();
		System.out.println(conn);
	}
}

Emp.java (VO)

package vo;

public class Emp {
	
	public String ename;
	public String job;
	public String hireDate;
	public double sal;
	public double comm;
	public int empNo;
	public int mgr;
	public int deptNo;
	
}

Dept.java (VO)

package vo;
	// Value Object
	// read-only의 단순한 값 형태만 저장하기 위한 파일
	// getter 기능만 존재

	// DTO (Date Transfer Object)

	// VO > DTO, Domain

public class Dept {
	
	public String dname;
	public String loc;
	public int deptNo;
	
}

DBHelper.java

package dao;

import java.io.FileReader;
import java.sql.*;
import java.util.Properties;

public class DBHelper {
	public static Connection getConnection() throws Exception{
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		String dbUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
		String dbUser = "admin";
		
		FileReader fr = new FileReader("D:\\dev\\auth\\oracle.properties");
		Properties prop = new Properties();
		prop.load(fr);
		
		String dbPw = prop.getProperty("dbPw");
		
		Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPw);
		return conn;
	}
	
	public static void main(String[] args) throws Exception {
		Connection conn = DBHelper.getConnection();
		System.out.println(conn);
	}
}

EmpDAO.java

package dao;

import java.sql.*;
import java.util.*;

import vo.Emp;

public class EmpDAO {
	
	// 조인으로 Map을 사용하는 경우
	public static ArrayList<HashMap<String, Object>> selectEmpAndDeptList() throws Exception{
		
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		
		Connection conn = DBHelper.getConnection();
		
		String sql = "SELECT e.empNo empNo, e.ename ename, e.deptNo deptNo, d.dname dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			HashMap<String, Object> m = new HashMap<>();
			m.put("empNo", rs.getInt("empNo"));
			m.put("ename", rs.getString("ename"));
			m.put("deptNo", rs.getInt("deptNo"));
			m.put("dname", rs.getString("dname"));
			list.add(m);
		}
		
		conn.close();
		return list;
	}
	
	// VO 사용
	public static ArrayList<Emp> selectEmpList() throws Exception{
		ArrayList<Emp> list = new ArrayList<>();
		
		Connection conn = DBHelper.getConnection();
		
		String sql = "SELECT empNo, ename, sal FROM emp";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			Emp e = new Emp();
			e.empNo = rs.getInt("empNo");
			e.ename = rs.getString("ename");
			e.sal = rs.getDouble("sal");
			list.add(e);
		}
		
		conn.close();
		return list;
	}
}

DeptDAO.java

package dao;

import java.sql.*;
import java.util.*;
import vo.Dept;

public class DeptDAO {
	// VO 사용
	public static ArrayList<HashMap<String, Object>> selectDeptOnOffList() throws Exception{
		ArrayList<HashMap<String, Object>> list = new ArrayList<>();
		
		Connection conn = DBHelper.getConnection();
		
		String sql = "SELECT deptNo, dname, loc, 'ON' onOff FROM dept";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		while(rs.next()) {
			HashMap<String, Object> m = new HashMap<String, Object>();
			m.put("deptNo", rs.getInt("deptNo"));
			m.put("dname", rs.getString("dname"));
			m.put("loc", rs.getString("loc"));
			m.put("onOff", rs.getString("onOff"));
			list.add(m);
		}
		
		conn.close();
		return list;
	}
}

q001VoOrMap.jsp (Oracle DB DAO + VO 예제)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="vo.*" %>
<%@ page import="dao.*" %>

<!-- Controller -->
<%
	// 모델
	// ArrayList<Dept> deptList = DeptDAO.selectDeptList();
	ArrayList<Emp> empList = EmpDAO.selectEmpList();
	ArrayList<HashMap<String, Object>> deptOnOffList = DeptDAO.selectDeptOnOffList();
	ArrayList<HashMap<String, Object>> empAndDeptList = EmpDAO.selectEmpAndDeptList();

%>

<!-- View -->
<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>q001VoOrMap - Query예제 001</title>
</head>
<body>
	<h1>Dept List</h1>
	<table border=1>
		<tr>
			<th>deptNo</th>
			<th>dname</th>
			<th>loc</th>
		</tr>
		<%
			for(HashMap<String, Object> m : deptOnOffList){
		%>
			<tr>
				<td><%=(Integer)(m.get("deptNo"))%></td>
				<td><%=(String)(m.get("dname"))%></td>
				<td><%=(String)(m.get("loc"))%></td>
			</tr>
		<%
			}
		%>
	</table>
	
	<h1>Emp List</h1>
	<table border=1>
		<tr>
			<th>empNo</th>
			<th>ename</th>
			<th>sal</th>
		</tr>
		<%
			for(Emp e : empList){
		%>
			<tr>
				<td><%=e.empNo%></td>
				<td><%=e.ename%></td>
				<td><%=e.sal%></td>
			</tr>
		<%
			}
		%>
	</table>
	
	<h1>Dept + onOff List</h1>
	<table border=1>
		<tr>
			<th>deptNo</th>
			<th>dname</th>
			<th>loc</th>
			<th>onOff</th>
		</tr>
		<%
			for(HashMap<String, Object> m : deptOnOffList){
		%>
			<tr>
				<td><%=(Integer)(m.get("deptNo"))%></td>
				<td><%=(String)(m.get("dname"))%></td>
				<td><%=(String)(m.get("loc"))%></td>
				<td><%=(String)(m.get("onOff"))%></td>
			</tr>
		<%
			}
		%>
	</table>
	
	<h1>Emp And Dept List</h1>
	<table border=1>
		<tr>
			<th>empNo</th>
			<th>ename</th>
			<th>deptNo</th>
			<th>dname</th>
		</tr>
		<%
			for(HashMap<String, Object> m : empAndDeptList){
		%>
			<tr>
				<td><%=(Integer)(m.get("empNo"))%></td>
				<td><%=(String)(m.get("ename"))%></td>
				<td><%=(Integer)(m.get("deptNo"))%></td>
				<td><%=(String)(m.get("dname"))%></td>
			</tr>
		<%
			}
		%>
	</table>
	
	<h1>git connect test</h1>
</body>
</html>

q001VoOrMap.jsp 출력결과