oracle[동영상강의] oracle11_join(jdbc 실습, mybatis)

412 No

[동영상강의] oracle11_join(jdbc 실습, mybatis)

2017-09-03 18:46:12 By 머나먼나라 0 1086 댓글수 4 포스트 하기

11월 2024
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

select studno, st.name, d.dname, p.name pname
,( select sum(point) from subject s, lecture l
where  l.subject_code=s.subject_code 
    and l.studno=st.studno) point
from student st, department d, professor p
where st.deptno1=d.deptno and st.profno=p.profno

context.xml

    <Resource 
       name="oraDB" 
       auth="Container" 
       driverClassName="oracle.jdbc.driver.OracleDriver" 
       maxTotal="50" 
       maxIdle="50" 
       maxWaitMillis="-1"
       url="jdbc:oracle:thin:@localhost:1521:xe" 
       username="hr" 
       password="1111" 
       type="javax.sql.DataSource" />

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
</script>
</head>
<body>
<c:redirect url="/student/student.jsp"></c:redirect> 
</body>
</html>

list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
</script>
</head>
<body>
<!-- list.jsp -->
<table border="1" width="400px">
  <tr>
  	<th>일련번호</th>
  	<th>학번</th>
  	<th>이름</th>
  </tr>
<!-- 
varStatus="루프의 상태변수"  count 1부터 index 0부터 -->  
<c:forEach var="row" items="${list}" varStatus="s">
  <tr> 
  	<td>${s.count}</td>
  	<td>${row.studno}</td>
  	<td>${row.name}</td>
  </tr>
</c:forEach>
<!-- 페이지 네비게이션 출력 -->  
  <tr>
    <td colspan="3" align="center">
<c:forEach var="num" begin="${page.blockStart}"
	end="${page.blockEnd}">
	  <c:choose>
	  	<c:when test="${num == page.curPage}">
	  	  <span style="color:red">${num}</span>
	  	</c:when>
	  	<c:otherwise>
	  	  <a href=
"javascript:list('${num}','${search_option}','${keyword}')">${num}</a>	
	  	</c:otherwise>
	  </c:choose>
</c:forEach>    
    </td>
  </tr>
</table>
</body>
</html>

student.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
$(document).ready(function(){
	list('1','name',''); //기본 검색 옵션
	//검색 버튼 클릭
	$("#btnSearch").click(function(){
		var num="${page.curPage}";
		if(num=="") num=1;
		var search_option=$("#search_option").val();
		var keyword=$("#keyword").val();
		list(num,search_option,keyword);
	});
});
function list(num,search_option,keyword){
	var param="curPage="+num
		+"&search_option="+search_option
		+"&keyword="+keyword;
	$.ajax({
		type: "post",
		url: "${path}/students/list.do",
		data: param,
		success: function(result){
			$("#result").html(result);			
		}
	});
}
</script>
</head>
<body>
<!-- student.jsp -->
<h2>학생 목록</h2>
<select id="search_option">
  <option value="all">전체</option>
  <option value="name">이름</option>
  <option value="studno">학번</option>
</select>
<input id="keyword">
<button id="btnSearch" type="button">검색</button>

<div id="result">학생 목록 출력 영역</div>
</body>
</html>

class StudentDTO

package student.dto;

public class StudentDTO {
	private int studno; // 학번
	private String name; // 학생 이름
	private int deptno1; // 제1전공코드
	private String dname; // 학과명
	private int profno; // 교수사번
	private String pname; // 지도교수 이름
	private int point; //신청학점
	public int getPoint() {
		return point;
	}
	public void setPoint(int point) {
		this.point = point;
	}
	
	// 기본생성자
	public StudentDTO() {
	}

	// 매개변수가 있는 생성자(studno,name,deptno1,profno)
	public StudentDTO(int studno, String name, int deptno1, int profno) {
		super();
		this.studno = studno;
		this.name = name;
		this.deptno1 = deptno1;
		this.profno = profno;
	}

	public int getStudno() {
		return studno;
	}

	public void setStudno(int studno) {
		this.studno = studno;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getDeptno1() {
		return deptno1;
	}

	public void setDeptno1(int deptno1) {
		this.deptno1 = deptno1;
	}

	public String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public int getProfno() {
		return profno;
	}

	public void setProfno(int profno) {
		this.profno = profno;
	}

	public String getPname() {
		return pname;
	}

	public void setPname(String pname) {
		this.pname = pname;
	}

	@Override
	public String toString() {
		return "StudentDTO [studno=" + studno + ", name=" + name + ", deptno1=" + deptno1 + ", dname=" + dname + ", profno="
				+ profno + ", pname=" + pname + "]";
	}

}

class StudentDAO

package student.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import config.MybatisService;
import student.dto.DepartmentDTO;
import student.dto.ProfessorDTO;
import student.dto.StudentDTO;

public class StudentDAO {
	//싱글톤 패턴
	private static StudentDAO instance;
	private StudentDAO() {
	}
	public static StudentDAO getInstance() {
		if(instance==null) {
			instance = new StudentDAO();
		}
		return instance;
	}
	// overload(오버로딩) 
	public List<StudentDTO> list(
			String search_option, String keyword
			,int start, int end){
		List<StudentDTO> list=null;
		try(SqlSession session=
				MybatisService.getFactory().openSession()) {
			Map<String,Object> map=
					new HashMap<String,Object>();
			map.put("search_option", search_option);
			map.put("keyword", keyword);
			map.put("start", start);
			map.put("end", end);
			list=session.selectList("student.list", map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	
	public List<ProfessorDTO> profList(int deptno){
		List<ProfessorDTO> list=
				new ArrayList<ProfessorDTO>();
		SqlSession session=null;
		try {
			session 
				= MybatisService.getFactory().openSession();
			list=session.selectList("prof.profList",deptno);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if( session != null ) session.close();
		}
		return list;
	}
	
	public int count(
			String search_option,String keyword){
		int result=0;
		try(SqlSession session=
				MybatisService.getFactory().openSession()) {
			Map<String,Object> map=
					new HashMap<String,Object>();
			map.put("search_option", search_option);
			map.put("keyword", keyword);
			result=session.selectOne(
					"student.studentCount",map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<DepartmentDTO> departmentList(){
		List<DepartmentDTO> list=
				new ArrayList<DepartmentDTO>();
		SqlSession session=null;
		try {
			session=
					MybatisService.getFactory().openSession();
			list=session.selectList("dept.deptList");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(session != null) session.close();
		}
		return list;
	}
	
	
	public List<StudentDTO> list(){
		List<StudentDTO> list=null;
		SqlSession session=null; //mybatis 실행객체
		try {
			//SqlSession 객체 생성
			session = 
					MybatisService.getFactory().openSession();
// selectList("네임스페이스.SQL의id")
			list=session.selectList("student.studentList");
//			int count=0;
//			for(StudentDTO dto : list){
//				count=session.selectOne(
//					"lecture.lectureCount",dto.getStudno() );
//				dto.setPoint(count); 
//			} 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if(session != null) session.close();//세션닫기
		}
		return list;
	}
	public void insert(StudentDTO dto) {
		SqlSession session
			=MybatisService.getFactory().openSession();
		session.insert("student.studentAdd", dto);
//select 이외의 문장은 commit()을 해야 함		
		session.commit();
		session.close(); //SqlSession 닫기
	}
	public void delete(int studno){
		SqlSession session=
				MybatisService.getFactory().openSession();
		session.delete("student.studentDel", studno);
		session.commit();
		session.close();
	}
}

class DB

package config;

import java.sql.Connection;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DB {
	public static Connection dbConn(){
		//context.xml에 설정된 dbcp에서 커넥션을 가져옴
		DataSource ds=null; //javax.sql
		Connection conn=null;
		try {
			//context.xml을 분석하는 객체
			Context ctx=new InitialContext();//javax.naming
			// context.xml의 Resource 태그 검색
//			ds=
//(DataSource)ctx.lookup("java:comp/env/myDB");
			ds=
(DataSource)ctx.lookup("java:comp/env/oraDB");			
			conn = ds.getConnection(); //커넥션을 할당받음
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn; //커넥션 리턴
	}
}

class MybatisService

package config;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisService {
// SqlSessionFactoryBuilder => SqlSessionFactory
//		=> SqlSession	
	//SqlSession 객체 생성기
	private static SqlSessionFactory factory;
	static {
		try {
			// Java Resources의 src
			Reader r = Resources.getResourceAsReader(
					"config/sqlMapConfig.xml");
			//SqlSessionFactory 생성기
			factory = new SqlSessionFactoryBuilder()
					.build(r);
			r.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static SqlSessionFactory getFactory() {
		return factory;
	}
}

sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- xml 지시어 -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 알리아스 설정 -->
	<typeAliases>
<!-- typeAlias type="전체경로" alias="별칭" -->	
	  <typeAlias type="emp.dto.EmpDTO" alias="e" />
	</typeAliases>
	<!-- db연결 참조코드 -->
	<environments default="">
		<environment id="">
			<transactionManager type="JDBC" />
			<dataSource type="JNDI">
				<property name="data_source" 
				value="java:comp/env/oraDB" />
			</dataSource>
		</environment>
	</environments>
	<!-- 실제 sql query -->
	<mappers>
		<mapper resource="emp/mapper/emp.xml" />
		<mapper resource=
			"student/mapper/student.xml" />		
		<mapper resource=
			"student/mapper/dept.xml" />	
		<mapper resource=
			"student/mapper/prof.xml" />	
		<mapper resource=
			"student/mapper/lecture.xml" />		
		<mapper resource=
			"memo/mapper/memo.xml" />	
		<mapper resource=
			"board/mapper/board.xml" />												
	</mappers>
</configuration>

mapper

student.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- student.xml -->
<mapper namespace="student">
<!--테이블의 필드명과 dto의 필드명은 반드시 일치해야 함 -->
<!--  	<select id="studentList"
		resultType="student.dto.StudentDTO"> 
	select studno, s.name, d.dname, p.name pname
	from student s, department d, professor p
	where s.deptno1=d.deptno and s.profno=p.profno	
	</select> -->
 <!-- ${변수} 따옴표 제거, #{변수} 따옴표 포함 -->
 	<select id="list"
 		resultType="student.dto.StudentDTO">
		select *  
		from (
		  select rownum as rn, A.*
		  from (  
		    select studno,name 
		    from student
	  <choose>
 		<when test="search_option != 'all'">
 		  where ${search_option} 
 		  	like '%'|| #{keyword} ||'%'	
 		</when>
 		<when test="search_option == 'all'">
 		  where name like '%'|| #{keyword} ||'%' 
 		    or studno like '%'||#{keyword}||'%'
 		</when>
 	  </choose>
		    order by studno
		  ) A
		)
		where rn between #{start} and #{end} 		
 	</select>
 	
 	<select id="studentCount" resultType="int">
 		select count(*) from student
	  <choose>
 		<when test="search_option != 'all'">
 		  where ${search_option} 
 		  	like '%'|| #{keyword} ||'%'	
 		</when>
 		<when test="search_option == 'all'">
 		  where name like '%'|| #{keyword} ||'%' 
 		    or studno like '%'||#{keyword}||'%'
 		</when>
 	  </choose>
 	</select>
 
 
 	<select id="studentList"
		resultType="student.dto.StudentDTO">  
select studno, st.name, d.dname, p.name pname
,( select sum(point) from subject s, lecture l
where  l.subject_code=s.subject_code 
	and l.studno=st.studno) point
from student st, department d, professor p
where st.deptno1=d.deptno and st.profno=p.profno 
	</select> 
	
	<insert id="studentAdd">
	insert into student 
		(studno,name,id,jumin,deptno1,profno) 
	values (#{studno},#{name},' ',' '
		,#{deptno1},#{profno} )	
	</insert>
	
	<delete id="studentDel">
		delete from student where studno=#{studno}
	</delete>
</mapper>

class StudentController

package student;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import student.dao.StudentDAO;
import student.dto.DepartmentDTO;
import student.dto.ProfessorDTO;
import student.dto.StudentDTO;

@WebServlet("/student_servlet/*")
public class StudentController extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String url=request.getRequestURL().toString();
		String path=request.getContextPath();
		StudentDAO dao=StudentDAO.getInstance();
		if(url.indexOf("list.do") != -1) {
			long start=System.currentTimeMillis();
			List<StudentDTO> list=dao.list();
			long end=System.currentTimeMillis();
			System.out.println("실행시간:"+(end-start)); 
			Map<String,Object> map
				=new HashMap<String,Object>();
			map.put("list", list);
			map.put("count", list.size());
			request.setAttribute("map", map);
			String page="/sql03/student_list.jsp";
			RequestDispatcher rd=
					request.getRequestDispatcher(page);
			rd.forward(request, response);
		}else if(url.indexOf("insert.do") != -1){
			
//학번,이름을 받아옴			
			int studno=
Integer.parseInt(request.getParameter("studno"));
			String name=request.getParameter("name");
			int deptno1=Integer.parseInt(
					request.getParameter("deptno1"));
			int profno=Integer.parseInt(
					request.getParameter("profno"));
			StudentDTO dto=
new StudentDTO(studno, name, deptno1, profno);
//dao에 insert 요청			
			dao.insert(dto); 
		}else if(url.indexOf("deptList.do") != -1){
			List<DepartmentDTO> list=dao.departmentList();
			request.setAttribute("list", list);
			System.out.println(list);
			String page="/sql03/dept_list.jsp";
			RequestDispatcher rd
				=request.getRequestDispatcher(page);
			rd.forward(request, response); 
		}else if(url.indexOf("prof.do") != -1){
			int deptno = Integer.parseInt(
					request.getParameter("deptno"));
			List<ProfessorDTO> list=dao.profList(deptno);
			request.setAttribute("list", list);
			String page="/sql03/prof_list.jsp";
			RequestDispatcher rd=
					request.getRequestDispatcher(page);
			rd.forward(request, response);
		}else if(url.indexOf("del.do") != -1){
			int studno=Integer.parseInt(
					request.getParameter("studno"));
			dao.delete(studno);
//삭제 후 student.jsp 페이지로 이동			
			String page=path+"/sql03/student.jsp";
			response.sendRedirect(page);
		}
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

출력 =>