oracle마카로닉스

417 No

[동영상강의] oracle16_jdbc 실습(테이블 4개 join), 복합키, subject 테이블, lecture 테이블, lecture_v 뷰

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

4월 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

-- 테이블 조인 실습
-- 과목 테이블(subject) : 과목코드, 과목명, 담당교수코드 학점수
-- 수강 테이블(lecture) : 학번, 과목코드
-- 출력 : 학번 /학과 /학년/이름/ 과목명/교수명/학점수
-- 학생, 학과, 과목, 수강, 교수

create table subject (
 subject_code varchar2(20) not null PRIMARY key,
 subject_name varchar2(20) not null,
 profno int not null,
 point int default 3
);

insert into subject values (1,  'java', '1001',3);
insert into subject values (2, 'c++', '1002' ,4);
insert into subject values (3, 'oracle', '1003',2 );

select * from subject;

commit;

-- 수강 테이블, 복합키

create table lecture(
  studno int not null,
  subject_code int not null,
  
  PRIMARY key(studno, subject_code) --복합키
);

insert into LECTURE VALUES (9411, 1);
insert into LECTURE VALUES (9411, 2);
insert into LECTURE VALUES (9411, 3);

commit;

select * from LECTURE;

-- 테이블 4개 조인
-- 출력 : 학번/이름/과목명/교수명/학점수

select l.STUDNO 학번, st.NAME 이름, sb.subject_name 과목명, p.name 교수명, sb.POINT 학점수
from LECTURE l, student st, subject sb, PROFESSOR p
where l.studno =st.STUDNO and l.SUBJECT_CODE=sb.SUBJECT_CODE
  and sb.PROFNO=p.PROFNO;
  
select l.STUDNO 학번, st.NAME 이름, sb.subject_name 과목명, p.name 교수명, sb.POINT 학점수
from 
  LECTURE l join student st on l.studno =st.STUDNO 
  JOIN subject sb on l.SUBJECT_CODE=sb.SUBJECT_CODE
  JOIN PROFESSOR p on sb.PROFNO=p.PROFNO ;

create or replace view lecture_v
as

select l.STUDNO , st.NAME sname,  sb.subject_name , p.name pname, sb.POINT 
from 
  LECTURE l join student st on l.studno =st.STUDNO 
  JOIN subject sb on l.SUBJECT_CODE=sb.SUBJECT_CODE
  JOIN PROFESSOR p on sb.PROFNO=p.PROFNO ;

select * from LECTURE_V;

commit;

=>출력

9411   서진수   java   조인형   3
9411   서진수   c++   박승곤   4
9411   서진수   oracle   송도권   2

lecture.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(){
	lecture_list();
});
function lecture_list(){
	$.ajax({
		type: "post",
		url: "${path}/lecture_servlet/list.do",
		success: function(result){
			$("#result").html(result);
		}
	});
}
</script>
</head>
<body>
<!-- WebContent/sql03/lecture.jsp -->
<h2>수강과목 목록</h2>
<div id="result"></div>

</body>
</html>

LectureDTO

package student.dto;

public class LectureDTO {
	private int studno;
	private String sname;
	private String subject_name;
	private String pname;
	private int point;
	//기본생성자
	public LectureDTO() {
	}
	//getter,setter
	public int getStudno() {
		return studno;
	}
	public void setStudno(int studno) {
		this.studno = studno;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public String getSubject_name() {
		return subject_name;
	}
	public void setSubject_name(String subject_name) {
		this.subject_name = subject_name;
	}
	public String getPname() {
		return pname;
	}
	public void setPname(String pname) {
		this.pname = pname;
	}
	public int getPoint() {
		return point;
	}
	public void setPoint(int point) {
		this.point = point;
	}

	@Override
	public String toString() {
		return "LectureDTO [studno=" + studno + ", sname=" + sname + ", subject_name=" + subject_name + ", pname=" + pname
				+ ", point=" + point + "]";
	}
}

class LectureController

package student;

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

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.LectureDAO;
import student.dto.LectureDTO;

@WebServlet("/lecture_servlet/*")
public class LectureController extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String url=request.getRequestURL().toString();
		LectureDAO dao=new LectureDAO();
		if(url.indexOf("list.do") != -1){
			List<LectureDTO> list=dao.subjectList();
			request.setAttribute("list", list);
			String page="/sql03/lecture_list.jsp";
			RequestDispatcher rd=
					request.getRequestDispatcher(page);
			rd.forward(request, response);
		}
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

class LectureDAO

package student.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import config.MybatisService;
import student.dto.LectureDTO;

public class LectureDAO {
	public List<LectureDTO> subjectList(){
		List<LectureDTO> list=new ArrayList<LectureDTO>();
		SqlSession session=null;
		try {
			session=
					MybatisService.getFactory().openSession();
			list = session.selectList("lecture.lectureList");
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			if(session != null) session.close();
		}
		return list;
	}
}

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>

lecture.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">
<!-- lecture.xml -->
<mapper namespace="lecture">
	<select id="lectureList"
		resultType="student.dto.LectureDTO">
		select * from lecture_v
	</select>
	
	<select id="lectureCount" resultType="int">
	select nvl(sum(point),0)
	from  lecture l, subject s
	where l.subject_code=s.subject_code
	    and studno=#{studno}
	</select>
	
</mapper>

lecture_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>
<table border="1">
	<tr>
		<th>학번</th>
		<th>이름</th>
		<th>과목명</th>
		<th>담당교수</th>
		<th>학점</th>
	</tr>
<c:forEach var="row" items="${list}">
	<tr>
		<td>${row.studno}</td>
		<td>${row.sname}</td>
		<td>${row.subject_name}</td>
		<td>${row.pname}</td>
		<td>${row.point}</td>
	</tr>
</c:forEach>	
</table>
</body>
</html>

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" />

http://localhost:2311/sql03/lecture.jsp

출력 화면>>>