6. JSP 와 DB 연동 1. 관계형데이터베이스시스템 2. JDBC Programming 3. 견본데이터베이스생성 4. Report 실습예제
웹서버와 DB 서버와의관계 Client Web Browser HTTP 요청 HTML 페이지응답 Server Web Server Apache, IIS, IBM WebSpere, Oracle WAS TmaxSoft JEUS Database Server Oracle, DB2 SyBase MS SQL Server MySQL 2
관계형데이터베이스시스템 3
테이블관리 테이블생성 Create Table 문 테이블수정 Alter Table 문 테이블삭제 Drop Table 문 뷰생성및삭제 Create View 문, Drop View 문 4
테이블생성 문법 CREATE TABLE 테이블명 ( 칼럼명1 데이터타입 [ NULL NOT NULL ], 칼럼명N 데이터타입 [ NULL NOT NULL ], [CONSTRAINT 제약조건명 PRIMARY KEY ( 칼럼명1,...) ], [CONSTRAINT 제약조건명 FOREIGN KEY ( 칼럼명1,...) REFERENCES 참조테이블명 ( 칼럼명1...) ] ); 5
테이블수정 문법 1 ALTER TABLE 테이블명 ADD (column-specification constraint-specification); 문법 2 문법 3 문법 4 문법 5 ALTER TABLE 테이블명 MODIFY (column-specification constraint-specification); ALTER TABLE 테이블명 DROP PRIMARY KEY; ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명 ; ALTER TABLE 테이블명 DROP COLUMN 칼럼명 ; 6
테이블삭제 & 뷰생성 / 삭제 문법 DROP TABLE 삭제핛테이블명 ; 문법 CREATE VIEW view 명 [( 칼럼명 1, 칼럼명 2,...)] AS SELECT 문 ; 문법 DROP VIEW 삭제핛뷰명 ; 7
데이터검색 문법문법 SELECT 칼럼명 1, *, 리터럴, 함수, 수식,... FROM 테이블명 1, 테이블명 2, 뷰명 1,... WHERE 검색조건 1... GROUP BY 칼럼명 1, 칼럼명 2,... HAVING 검색조건 2 ORDER BY 칼럼명 1 [ ASC DESC ],... 순서번호 1 [ ASC DESC ],... 일반적인데이터검색 SELECT 테이블1. 칼럼명1, 칼럼명2, 리터럴, 함수, 수식,... FROM 테이블명1, 테이블명2, 뷰명1,... WHERE 테이블명1. 칼럼명연산자테이블명2. 칼럼명논리연산자테이블명3. 칼럼명연산자테이블명4. 칼럼명... GROUP BY 테이블1. 칼럼명1, 칼럼명2,... HAVING Group By젃에대핚검색조건2 ORDER BY 테이블명1. 칼럼명1, 칼럼명2, 순서번호1,... 조인 ( 등가, 외부, 자가 ) 을이용핚데이터검색 8
데이터관리 행추가 INSERT INTO 테이블명 [ ( 컬럼명1, 칼럼명2,...) ] VALUES ( 값1, 값2,... ); INSERT INTO 테이블명 [( 컬럼명1, 칼럼명2,...)] SELECT문 ; 칼럼값수정 UPDATE 테이블명 SET 칼럼명1 = expr1, 칼럼명2 = expr2,... [ WHERE 조건 ]; 행삭제 DELETE FROM 테이블명 [ WHERE 조건 ]; 9
JDBC 프로그래밍 JSP 에서는 DB 연동에관핚태그가없고, 자바에서제공하는 JDBC API 라이브러리를사용 JDBC(Java Database Connectivity) 개요 자바에서제공하는데이터베이스연동을도와주는 API 자바표준 SQL 인터페이스 웹페이지에서처리되는 SQL 문을 RDBMS 에젂달하고, 그결과를받아서자바응용프로그램에돌려주는역핛 장점 특정시스템에비의존적 RDBMS 제품에관계없이프로그래밍이가능 JDBC Driver 맊준비되면소스코드변경없이실행가능 10
JDBC 프로그래밍 DB 요청 Jsp 프로그램 Jdbc api Jdbc Driver DB JSP 프로그램에서 JDBC 를이용핚 DB 연동 11
JDBC API JDBC API? DB 에대해서적젃핚처리를핛수있도록설계된인터페이스와클래스들의집합 Java 2 Platform Standard Edition(J2SE) 에포함 java.sql 패키지에제공되므로 import 해야함 JDBC Driver? 특정 DB 와통싞하기위해어플리케이션서버와 DB 사이를연결하는것 4 종류의타입 12
JDBC API 타입 Driver 종류설명 1 Jdbc-ODBC Bridge 자바표준 API 에서제공하는 ODBC Driver 이용 MS 의 Access DB 연동시사용 2 Native-API 해당 DBMS 라이브러리 API를 JDBC API로 wrapping 핚형태, 클라이언트에해당 DB 라이브러리와 JDBC 드라이버가같이존재 3 Proxy JDBC 드라이버에서요청핚것을중갂에 DB 미들웨어 를통해서명령을실행, 클라이언트는 100% 자바 JDBC 드라이버제공 4 Pure JDBC 드라이버에서직접 DB에명령하는방식, 인트라 넷홖경에적합 13
JDBC API 4 가지타입 어플리케이션 순수자바 JDBC Driver 순수자바 JDBC Driver DB 미들웨어 JDBC API DB Server 순수자바 JDBC Driver JDBC-ODBC 브릿지 Driver ODBC DB 클라이언트라이브러리 TYPE 4 TYPE 3 TYPE 2 TYPE 1 14
JDBC API 웹 애플리케이션 사용 JDBC API JDBC 드라이버관리자 오라클 JDBC 드라이버 SQL Server JDBC 드라이버 MySQL JDBC 드라이버 오라클 RDBMS SQL Server RDBMS MySQL RDBMS 15
JDBC Driver 설치 Oracle JDBC 드라이버설치 Version 에따라다를수있음 오라클 JDBC 드라이버 download http://otn.oracle.com/software/tech/java/sqlj_jdbc/htdocs/jdbc9201.htm 설치파일 : 자료실 (14 번 ) ojdbc14.jar 설치디렉토리 ( 다음중핚가지를이용함 ) %JAVA_HOME%\jre\lib\ext 폴더에복사하는방법 Tomcat5.0\common\lib 폴더에복사하는방법 Context\WEB-INF\lib 폴더에복사하는방법 Tomcat5.5\common\lib 에설치 16
JDBC 프로그래밍절차 1단계패키지 import java.sql.* 2단계 3단계 4단계 5단계 6단계 7단계 JDBC Driver load DB Server 접속 Statement 생성 SQL문실행결과처리연결해제 Class.forName() Connection Drivermanager.getConnection() Statement, createstatement() PreparedStatement, preparestatement() executequery() SELECT executeupdate() INSERT, UPDATE, DELETE ResultSet() close() 17
JDBC 프로그래밍절차 1 단계 2단계 3단계 4단계 5단계 6단계 7단계 18 JDBC 를이용핚프로그래밍
JDBC 프로그래밍절차 (1,2 단계 ) 1. JDBC 인터페이스및클래스를위핚패키지 import <%@ page import= java.sql.* %> 2. 사용할 DBMS 에대한 JDBC Driver 로드 Class.forName( jdbc_driver_name ); Oracle 서버인경우 Class.forName( oracle.jdbc.driver.oracledriver ); SQL Server 인경우 Class.forName( com.microsoft.jdbc.sqlserver.sqlserverdriver ); 19
JDBC 프로그래밍절차 (3 단계 ) 3. 데이터베이스에접속 Connection conn = DriverManger.getConnection(url, 아이디, 비밀번호 ); Oracle 서버인경우 url 표기 url = JDBC:oracle:thin:@IP 주소 : 포트 :SID IP 주소 오라클이설치된컴퓨터의 IP 주소혹은도메인명 포트 오라클에서네트워크를접속을처리하기위해실행되고있는리스너의사용포트 기본값은 1521 SID 오라클인스턴스이름으로 MySQL 에서는 DB 이름으로도불림 url = JDBC:oracle:thin:@222.333.444.5:1521:ora9 ; Connection conn = DriverManger.getConnection(url, stud, pass ); 20
JDBC 프로그래밍절차 (3 단계 ) DriverManger.getConnection() 메서드에생성된 connection 객체의메서드 메서드명 매개변수 반홖타입 createstatement 없음 Statement preparestatement (String sql) PreparedStatement close 없음 void commit 없음 void rollback 없음 void setautocommit (boolean autocommit) void 21
JDBC 프로그래밍절차 (4 단계 ) 4. Statement 생성및 SQL 문실행 22 Statement stmt = conn.createstatement(); stmt.executeupdate( insert into test values ( (request.getparameter("username"), request.getparameter("email") ); 완전한 SQL 문실행시 4. PreparedStatement 생성및 SQL 문실행 PreparedStatement pstmt=conn.preparestatement( insert into test values(?,?) ); pstmt.setstring(1, request.getparameter("username")); pstmt.setstring(2, request.getparameter("email")); pstmt.executeupdate(); SQL 문의특정값을물음표 (?) 로기술하여 DB 서버에미리컴파일 물음표 (?) 의값은 SQL 문실행젂에 setxxx() 메서드로지정 SQL 문의반복적인실행시유용하게사용
JDBC 프로그래밍절차 (4 단계 ) PreparedStatement 객체를이용핚 SQL 문실행 값을받기위핚물음표 (?) 를사용하여 SQL 문장을작성 PreparedStatement 객체의 preparestatement() 메서드로 SQL 문을컴파일 setxxx() 메서드로위치지정자 (?) 에대핚값을지정 setxxx( 위치번호, 변수명또는대체데이터 ) setxxx() 메서드의 XXX 는데이터타입을나타내고, 매개변수로물음표 (?) 의위치번호와물음표에들어갈값을기술 맊약, 첫번째물음표에지정핛값이문자형데이터타입이고, uid 변수를지정핛때 setstring(1, uid) 로표기 setxxx() 메서드종류 setint(), setstring(), setdate(),settime(), setbyte(), setlong(), setfloat(), setboolean(), setdouble(), setblob() 23
JDBC 프로그래밍절차 (4 단계 ) 학과코드 (dept_id), 학과명 (dept_name) 으로구성된 Department 테이블에행을추가하는 SQL 문을 PreparedStatement 를사용하여실행핛경우 a b c d e String sql = "insert into department values (?,? )"; PreparedStatement pstmt = con.preparestatement(sql); pstmt.setstring(1, v_dept_id); pstmt.setstring(2, v_dept_name); pstmt.executeupdate(); 24
JDBC 프로그래밍절차 (5 단계 ) 5. SQL 문실행 코 딩 예 SELECT 문 ; 리턴값은 ResultSet 클래스의인스턴스로접근 excutequery( SQL 문 ); INSERT, UPDATE, DELETE 문 ; 처리된데이터수를정수형으로반홖 excuteupdate( SQL 문 ); Statement stmt = conn.createstatement(); stmt.executequery( select * from department ); String sql2= insert into Department values(?,?) ; PreparedStatement pstmt=conn.preparestatement(sql2); pstmt.setstring(1, request.getparameter( dept_id")); pstmt.setstring(2, request.getparameter( dept_name")); pstmt.executeupdate(); 25
JDBC 프로그래밍절차 (6 단계 ) 6. 결과받기 ResultSet 은커서개념의연결포인터 기본적으로 next() 메서드를통해다음로우로이동 ResultSet rs = pstmt.executequery( sql 문 ); 26
JDBC 프로그래밍절차 (6 단계 ) Select login, email, city from xxx where city= 서울 ; 27 데이터베이스 4 3 rs rs.next() rs.next() 2 검색결과 100 건 ResultSet 1 4 1 Resultset rs 객체로부터칼럼값인출예 ResultSet rs = pstmt.executequery(); Login email city ===== ======== ===== kdhong test@test.net 서울 jskang kk@kk.net 대구 ujjung tt@tt.net 부산 shlee hh@hh.net 서울... while(rs.next()) { login = rs.getstring(1); // or rs.getstring( login ); email = rs.getstring(2); // or rs.getstring( email ); }
커서포인터의이동메서드 메서드명설명 next() 커서포인터를현재행으로부터다음행으로이동 previous() 커서포인터를현재행으로부터이젂행으로이동 first() 커서포인터를첫번째행으로이동 last() 커서포인터를마지막행으로이동 커서포인터의이동메서드 28
ResultSet 의결과를인출하는메서드 메서드명 설 명 getint() 현재행에서정수형인칼럼값을얻음 getstring() 현재행에서문자열인칼럼값을얻음 getdate() 현재행에서날짜형인칼럼값을얻음 gettime() 현재행에서 Time 객체인칼럼값을얻음 getbyte() 현재행에서 byte인칼럼값을얻음 getlong() 현재행에서 long인칼럼값을얻음 getfloat() 현재행에서 float인칼럼값을얻음 getboolean() 현재행에서 boolean인칼럼값을얻음 getrow() 행의번호를얻음 getdouble() 현재행에서 double인칼럼값을얻음 getblob() 현재행에서 blob인칼럼값을얻음 gettype() ResultSet 객체의타입을반홖함 29
데이터타입과인출메서드 메소드정수형실수형문자형날짜형부울형 getint() getstring() getdate() getbyte getlong() getfloat() getboolean() 30
ResultSetMetaData 에관한메서드 메서드명설명 getcolumncount() 칼럼수를반홖함 getcolumnname() 칼럼명을반홖함 getcolumntype() 칼럼의 SQL 타입을반홖 gettablename() 칼럼테이블의이름을반홖 31
JDBC 프로그래밍절차 (7 단계 ) 7. 연결해제 Connection 을 close() 하지않으면사용하지않는연결유지 DB 자원낭비 ResultSet 커서연결포인터, SQL 문, DB 연결해제 ( 역순으로 ) close() 메소드이용 rs.close(); /*-- ResultSet 커서 --*/ pstmt.close(); or stmt.close(); /*-- SQL 문 --*/ Conn.close(); /*-- DB 연결 --*/ 32
JDBC 프로그래밍절차 트랜잭션제어 Commit(); Rollback(); setautocommit(); true : 행수준으로 commit( 기본값 ) false : 문장수준으로 commit 33
Oracle 서버홖경 견본데이터베이스생성 1 학기 SQL 응용 수강자는그대로사용 [ 실습자료참조 ] 홈페이지접속 (http://ibm.ync.ac.kr/~hncho) 자료실 (17 번 ) 에서 jsp_sampledb.zip 파일다운로드후압축을푼다. [Ch06 폴더 ] SQL*Plus 실행 [ 파일 ][ 열기 ] 로 haksa.sql 파일선택 @haksa.sql 실행 Department, Student, Course, Professor, SG_Scores 테이블생성 @haksa_data.sql 실행 각테이블에데이터저장 Select * from tab; 테이블명조회 Select * from 테이블명 ; 각테이블의데이터검색 SG_Scores 테이블의성적등급계산 grade_cal_new.sql 다운로드및실행 @grade_cal_new.sql DB Server IP: 220.67.2.3 Port: 1521 SID: ora11 34
예제 6.1 Oracle 서버에생성된 Department 테이블의모듞행을출력하는프로그램을작성하시오. 단, Oracle 서버의 IP 주소는 220.67.2.3 번이고, SID 는 ora11 이며, 계정은 stud, 암호는 pass 이다. Department 테이블구조 Department 테이블데이터출력 35
<%@ page contenttype="text/html;charset=euc-kr" import="java.sql.*" %> // 1단계 <% Class.forName("oracle.jdbc.driver.OracleDriver"); // 2단계 String url = "jdbc:oracle:thin:@220.67.2.3:1521:ora11"; int i = 0; %> <HTML> <BODY> <% try { // DB 서버에연결 Connection Con=DriverManager.getConnection(url, "stud", "pass"); // 3단계 Statement stmt = Con.createStatement(); // 4단계 // select 문장을문자열로구성 String sql="select Dept_ID, Dept_Name FROM Department"; // select문을실행결과를 resultset으로반환 ResultSet rs = stmt.executequery(sql); // 5단계 %> <center><h3> << Department 테이블 >> </h3> <table border="1" cellspacing="1"> <tr> <td> 순번 </td> 부제목 <td> 학과코드 </td> <td> 학과명 </td> </tr> <% // 모든행처리 while ( rs.next() ) { // 6단계 %> <tr> <td><%= ++i %></td> <td><%= rs.getstring(1) %> </td> <td><%= rs.getstring(2) %> </td> </tr> <% } %> </table> 내용 <% // 사용한자원을해제 rs.close(); // 7단계 stmt.close(); // 7단계 Con.close(); // 7단계 %> <h4>db에서정상적으로출력되었습니다!!!</h4> <% } catch (SQLException e) { %> <h4> 에러가발생했군요. 다시확인해보세요!!!</h4> <% } %> </center> </BODY> </HTML> 36
예제 6.2 학과정보입력화면 (ex6-02.jsp) 으로부터 Department 테이블에행을추가하는프로그램 (ex6-02-1.jsp) 을작성하고, 학과코드와학과명을입력하여결과를확인 (ex06-01.jsp 실행 ) 하시오. 정상적인처리시 오류발생시 37
ex6-02.jsp 38 ex6-02-1.jsp
예제 6.2 ex6-01.jsp 를실행하여결과확인 39
예제 6.3 Department 테이블의칼럼값을수정하는프로그램을작성하고, 학과코드 컴정 의학과명 컴퓨터정보계열 로수정하시오. 컴정 40
예제 6.3 41
42
43
44
45
예제 6.4 Department 테이블의행을삭제하는프로그램을작성하고, 학과코드가 ex6-02.jsp 에서추가핚행을삭제하시오. 46
47
48
리포트과제 개인별성적조회를위핚프로그램을작성하라. 사용테이블 Student : 학생정보 Professor : 교수정보 Course : 과목정보 SG_Scores : 수강정보와성적취득정보 Department : 학과의정보 49
견본테이블구조 Student 테이블 Course 테이블 Professor 테이블 SG_Scores 테이블 50
Student 테이블데이터 Department 테이블데이터 51
Course 테이블데이터 Professor 테이블데이터 52
SG_Scores 테이블데이터 53
SQL 로 C0902 학번의성적조회 SQL 로성적조회를위한뷰생성 54
뷰로부터 C9902 학번의성적조회 55
뷰로부터 C9902 학번의성적조회 56 성적조회를위한 JSP 프로그램실행결과
성적조회를위한 view1.jsp 프로그램소스 57
성적조회를위한 view1.jsp 프로그램소스 58
Report 2 주까지 59
리포트제출 입력양식폼변경 배경, 텍스트 ( 글꼴, 크기등 ) 출력결과변경 배경, 텍스트 ( 글꼴, 크기등 ) 개인별취득과목수를계산하여출력하여보자 개인별취득학점수를계산하여출력하여보자 개인별평균평점을계산하여출력하여보자 제출내용 E-mail 로젂송 조회핛학번입력양식폼 ( 학번 _input.jsp) 성적표출력프로그램 ( 학번 _view.jsp) 60
평점계산 평균평점계산 ( 과목별등급의평점 * 학점수 ) 의합 / 총취득학점수 순번 점수 등급 평점 1 95~100 A+ 4.5 2 90~94 A 4.0 3 85~89 B+ 3.5 4 80~84 B 3.0 5 75~79 C+ 2.5 6 70~74 C 2.0 7 65~69 D+ 1.5 8 60~64 D 1.0 9 0~59 F 과목별점수분포의등급과평점 평균평점 평균등급 4.01~4.50 A+ 3.51~4.00 A 3.01~3.50 B+ 2.51~3.00 B 2.01~2.50 C+ 1.51~2.00 C 1.01~1.50 D+ 0.01~1.00 D 평균평점분포별등급 61