웹프로그래밍 설계 및 실습

1. 웹프로그래밍 설계 및 실습 DB & JDBC 이경화 (oracle@ssu.ac.kr)

2. First off http://java.sun.com/ http://java.sun.com/j2se/1.4.2/docs/api/index.html http://java.sun.com/javase/6/docs/api/ [영문API_1.6] http://apollo89.com/java/ko/api/ [한글API_1.6] http://en.wikipedia.org/wiki/Main_Page [Wikipedia]

3. Contents • Overview of DB & DBMS • Overview of SQL(Structured Query Language) • Overview of JDBC (Java DB Connection Driver) • JDBC API in Java

4. DB & DBMS ? • DB (Database) • 데이터를 조직적으로 통합해 구조화시켜 놓은 데이터의 집합체 • DBMS (Database Management System) • 데이터베이스를 생성하고 검색하며, 추가, 삭제를 원활히 하려는 프로그램의 집합 • 특징 • 데이터 구조 정의 • 데이터 검색과 갱신 • 데이터 추가와 삭제 • 복수의 사용자로부터의 데이터 처리 및 실행 제어 • 정보보호

5. DBMS (Database Management System)? • A DBMS(Database Management System) is a set of computer programs that controls the creation, maintenance, and the use of the database of an organization and its end users. • It provides facilities for controlling data access, enforcing data integrity, managing concurrency controlled, restoring database.

6. DBMS 데이터 모델 • 계층형 데이터 모델 • 네트워크형 데이터 모델 • 관계형 데이터 모델 • 객체지향 데이터 모델

7. SQL (Structured Query Language)? • 관계형 데이터베이스로부터 데이터 관리를 위해 고안된 컴퓨터 언어이다. • 자료의 검색과 수정, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리 역할을 포함한다. • 관계형 모델을 위한 최초의 언어이며 많은 관계형 데이터베이스들이 SQL을 사용하고 있다.

8. SQL (Structured Query Language)? • SQL is a database computer language designed for managing data in RDBMS (Relational DataBase Management Systems). • Its scope includes data query and update, schema creation and modification, and data access control. • SQL was one of the first languages for relational model and became the most widely used language for relational databases.

9. SQL 명령어 (1/2) • DDL (Data Definition Language) • computer language for defining data structures • 데이터 정의 • DML (Data Manipulation Language) • computer languages used by computer programs and/or database users to insert, delete and update data in a database • 데이터 조작 • DCL (Data Control Language) • computer language and a subset of SQL used to control access to data in a database • authorizes users and groups of users to access and manipulate data • 데이터 제어

10. Create Table 테이블 생성 테이블 구조 변경 Alter Table DDL Drop Table 테이블 삭제 데이터 조회 Select 데이터 추가 Insert DML SQL Update 데이터 수정 Delete 데이터 삭제 권한 부여 Grant DCL 권한 해제 Revoke SQL 명령어 (2/2) ※ 참고자료 첨부

11. Create Table 문장 SQL 기본 - DDL • 기본 테이블 생성 • 구문 • 사용 예 • SQL이 지원하는 데이터 타입 • INTEGER, SMALLINT, FLOAT(n), DECIMAL(i,j), CHAR(n), VARCHAR(n), DATE, TIME … CREATE TABLE기본_테이블_이름 ( 열_이름 데이터_타입 [NOT NULL] {, 열_이름 데이터_타입 [NOT NULL]}*); create table orders ( order_num integer not null , cost integer not null , good_name char(20) not null); ※ 참고자료 첨부

12. Alter Table 문장 SQL 기본 - DDL • 생성된 테이블에 새로운 열 추가 • 구문 • 사용 예 ALTER TABLE테이블_이름 ADD열_이름 데이터_타입; alter table orders add address char(60);

13. Drop Table 문장 SQL 기본 - DDL • 생성된 기본 테이블 제거 • 구문 • 사용 예 DROP TABLE기본_테이블_이름; drop table orders;

14. Insert 문장 SQL 기본 - DML • 기존 테이블에 행을 삽입 • 구문 • 사용 예 INSERT INTO 테이블[(열_이름 {[, 열_이름]}*] VALUES (값_리스트); insert into orders (order_num, cost, good_name, address) values (1, 300, ‘컴퓨터’, ‘서울시 동작구 상도동’); insert into orders (order_num, cost, good_name) values (2, 400000, ‘컴퓨터’);

15. Select 문장(2/2) SQL 기본 - DML ※SQL에서 제공하는 집단함수(aggregation function) • 테이블의 한 열의 값 집합에 적용 • COUNT, SUM, AVG, MAX, MIN ※ LIKE문(정규 문법 표현) • ‘%’는 어떤 길이의 어느 문자와도 매치 selectcount(*) from orders where cost > 500; select * from orders where addresslike ‘서울%’;

16. Update 문장 SQL 기본 - DML • 기존 레코드의 열 값을 변경 • 구문 • 사용 예 UPDATE 테이블 SET 열_이름 = 산술식 {, 열_이름 = 산술식}* [WHERE 조건]; update orders set cost = cost * 1000 where cost < 500;

17. Delete 문장 SQL 기본 - DML • 기존 테이블의 행을 삭제 • 구문 • 사용 예 DELETE FROM 테이블 [WHERE 조건]; delete from orders where order_no= 1; 권장 delete from orders where good_name = ‘컴퓨터’;

18. JDBC (Java DataBase Connectivity)? • 자바 프로그래밍 언어로 만들어진 데이터베이스 조작 인터페이스 • 다양한 관계형 데이터베이스를 위한 일관된 인터페이스를 제공하는 API 집합 • 프로그래머가 SQL 요구를 만드는데 사용할, 일련의 객체지향 프로그램의 클래스들을 정의 • 어떤 데이터베이스를 사용하더라도 JDBC 드라이버만 제공 된다면 코드 수정 없이 바로 적용 가능 • java.sql package

19. JDBC (Java Database Connectivity)? • JDBC is API for the Java programming language that defines how a client may access a database. • It provides methods for querying and updating data in a database. • JDBC is oriented towards relational databases. • JDBC was first introduced in the Java 2 Platform, Standard Edition, version 1.1 (J2SE). • JDBC is implemented via classes in the java.sql package

20. JDBC Structure 출처 : Introduction to JDBC, SUN

21. JDBC Driver Type (1/2) • Type I: Bridge : JDBC-ODBC 브리지 타입 • JDBC에 들어온 모든 명령을 ODBC형으로 변환해 ODBC 드라이버로 전달 • Type II: Native : Native-API/Partly Java 타입 • JDBC에 들어온 데이터베이스 명령을 DBMS 시스템 호출에 맞춰서 변환한 후 그 명령을 전달 • Type III: Middleware :명령어 변환 타입 • JDBC에 들어온 모든 명령을 데이터베이스 접근 미들웨어로 전송하고 이를 데이터베이스에 적합한 명령어로 변환 • Type IV: Pure : 직접 전송 타입 • 자바로 구현된 JDBC로 네트워크를 통해 데이터베이스로 직접 전송

22. 자바 애플리케이션 JDBC API JDBC 드라이버 관리자 JDBC 드라이버 API JDBC-ODBC 드라이버 Native-API 드라이버 Net-Protocol 드라이버 CLI (.lib) ODBC 드라이버 JDBC 미들웨어 DBMS JDBC Driver Type (2/2) JDBC loads a driver Native-Protocol 드라이버

23. JDBC API in Java 메서드 클래스 JDBC 프로그래밍 단계 System.setProperty() JDBC 드라이버 로드 Class.forName() JDBC driver class getConnection() 데이터베이스 연결 Java.sql.Connection createStatement() Java.sql.Statement Statement 생성 prepareStatement() Java.sql.PreparedStaement executeQuery() SQL문 전송 Java.sql.Statement executeUpdate() 결과 받기 Java.sql.ResultSet resultSet() 연결 해제 close() Java.sql.Connection

24. dataType JDBC class workflow DriverManager getConnection()  close() ResultSet Connection prepareStatement() getXXX() PreparedStatement setXXX() createStatement() executeQuery()executeUpdate() subclass Statement

25. JDBC 프로그래밍 (1/6) • JDBC 드라이버 로드 • 데이터베이스에 접속하기 위해서는 해당 JDBC 드라이버를 로드 해야함 • 드라이버에 따른 클래스의 이름 • ODBC : sun.jdbc.odbc.JdbcOdbcDriver • Microsoft SQL - com.microsoft.jdbc.sqlserver.SQLServerDriver • MySQL - com.mysql.jdbc.Driber • Oracle - oracle.jdbc.driver.OracleDriver 25 JSP Programming with a Workbook

26. JDBC 프로그래밍 (2/6) • 데이터베이스 연결 (Connection) • DriverManager 클래스의 getConnection() 메소드를 사용하여 데이터베이스에 연결 • 첫 번째 인자 형식 (url)

27. JDBC 프로그래밍 (3/6) • Statement 생성 (Statement, PreparedStatement) • SQL문 수행 • Connection 클래스의 createStatement() 메소드 사용 • PreparedStatement • SQL문을 미리 만들어 변수를 입력하는 방식 • 유지보수 측면에서 유리함 • Statement 클래스에 해당하는 메소드를 모두 사용할 수 있음

28. JDBC 프로그래밍 (4/6) statement vs. prepareStement Statement prepareStatement 쿼리문을 컴파일 해 저장 후 호출 시 인자 대입해 실행 쿼리문과 인자를 컴파일 후 실행 쿼리문이 동적인 경우 적합 쿼리문이 정적인 경우 적합 try{   String sql = “insert into member (id, pw)values(?,?)”;   pstmt = conn.prepareStatement(sql);  pstmt.setString(1, id); pstmt.setString(2, pw); rs = pstmt.exequteUpdate(); }catch(SQLException sql){   sql.toString(); }  try{ String sql = “insert into member(id, pw)values(‘”+id+ “’,’” +pw+ “’)”; stmt = conn.createStatement(); rs = stmt.exequteUpdate(sql); }catch(SQLException sql){ sql.toString(); } 

29. JDBC 프로그래밍 (5/6) • SQL문 전송 (executeQuery) • SQL문 처리 • executeQuery() 메소드 : SELECT 문 처리 • executeUpdate() 메소드 : INSERT, UPDATE, DELETE 문 처리

30. JDBC 프로그래밍 (6/6) • 처리 결과 리턴 (ResultSet) • 결과확인 • ResultSet 클래스에서 제공하는 메소드 (Demo program 참고) • 연결 해제 (close) • 모든 처리를 끝낸 후 close() 메소드를 사용하여 연결 해제 30 JSP Programming with a Workbook

31. DML : select (prepareStatement) JDBCSQL (1/6) • //SELECT field1, field2 FROM table_name WHERE field1=‘김’ • Connection conn = null; • PreparedStatement pstmt = null; • ResultSet rs = null; • conn = db.dbConn; • String sql = "SELECT seq, userid, username,userpwFROM user WHERE sql=?“; • pstmt = conn.prepareStatement(sql); • pstmt.setInt (1, seqVal); 31

32. ResultSet rs = pstmt.executeQuery(); • while (rs.next ()) { int seqVal = rs.getInt (“seq"); String idVal = rs.getString(“userid"); String nameVal = rs.getString(“username"); • String pwVal = rs.getString(“userpw"); • System.out.println(seqVal + ”,” + idVal + ”,” + nameVal + pwVal +”/n”);} 32

33. DML : insert (prepareStatement) JDBCSQL (2/6) • //INSERTINTO user(field1, field2) VALUES(‘val1’,’val2’) • int insCnt = 0; • Connection conn = null; • PreparedStatement pstmt = null • conn = db.dbConn; • String sql = “INSERT INTO user (seq, userid, username, userpw) VALUES(user_seq.nextval, ?,?,?)”; • pstmt = conn.prepareStatement(sql); • pstmt.setString (1, userid); • pstmt.setString (2, username); • pstmt.setString (3, userpw); • insCnt = pstmt.executeUpdate(); • System.out.println (insCnt + " rows were inserted"); 33

34. DML : insert (createStatement) //INSERTINTO member(field1, field2) VALUES(‘val1’,’val2’) int insCnt = 0; Connection conn = null; Statement stmt = null; conn = db.dbConn; String sql = “INSERT INTO user(userid, username, userpw) VALUES (‘khlee', ‘ 이경화’,‘1111‘)”; stmt = conn.createStatement(); insCnt = stmt.executeUpdate(sql); System.out.println (insCnt + " rows were inserted"); JDBCSQL (3/6) 34

35. DML : update (prepareStatement) JDBCSQL (4/6) • //UPDATE table_name SET field1='value‘, field2='value2' WHERE field3='010‘ • int seqVal = Integer.parseInt(request.getParameter(“seq")); • String useridVal = request.getParameter(“userid"); • String usernameVal = request.getParameter(“username"); • String userpwVal = request.getParameter(“userpw"); 35

36. int uptCnt = 0; • Connection conn = null; • PreparedStatement pstmt = null; • conn = db.dbConn; • String sql = “UPDATE member SET userid=?, username=?, userpw=?WHERE seq=?"; • pstmt = conn.prepareStatement(sql); • pstmt.setString(1, useridVal); • pstmt.setString(2,usernameVal);pstmt.setString(3,userpwVal); • pstmt.setInt(4,seqVal); • uptCnt = pstmt.executeUpdate(); • System.out.println (uptCnt + " rows were updated"); 36

37. DML : delete (prepareStatement) JDBCSQL (5/6) • //DELETE FROM table_name WHERE field1='value‘ • int seqVal = Integer.parseInt(request.getParameter(“seq")); • int delCnt = 0; • Connection conn = null; • PreparedStatement pstmt = null; • conn = db.dbConn; • String sql = “DELETE FROM member WHERE seq=?”; • pstmt = conn.prepareStatement(sql); • pstmt.setInt(1,seqVal); • uptCnt = pstmt.executeUpdate(); • System.out.println (delCnt + " rows were deleted"); 37

38. Demo Program • #1 Database, Table, Record 생성 • #2 connTest

39. #1. Database 접속 Mysql 접속명령 mysql–ulkh –p0000; use mydb; Oracle 접속명령 sqlplus lkh/0000;

40. MySQL일 경우 #1. Database, Table, Record 생성 (1/3) • Create Database • Create Table CREATE DATABASE mydb /* DEFAULT CHARACTER SET euckr */; 이미 생성되어 있을 경우 생성하지 않음 Oracle일 경우 기본은 XE DROP TABLE IF EXISTS user; CREATE TABLE user( seq int(11) NOT NULL auto_increment, userid varchar(20) not NULL, username varchar(20) default NULL, PRIMARY KEY (seq) ); • Insert Data insert into user values('1', 'myid', '홍길동'); insert into user values('2','misskim','김나나'); insert into user values('3','another','김나나'); select * from user;

41. #1. Database, Table, Record 생성 (2/3) Create Table Oracle일 경우 DROP TABLE user; CREATE TABLE user( id number NOT NULL, userid varchar2(20) not NULL, name varchar2(20) default NULL, PRIMARY KEY (id) ); • Create Sequence (시퀀스) Create sequence member_seq increment by 1 -- 증가값(1씩증가) start with 1 -- 시작값(1부터 시작) nomaxvalue -- 최대값 제한 없음 nocycle nocache;

42. Oracle일 경우 #1. Database, Table, Record 생성 (3/3) • Insert Data insert into user values(member_seq.nextval, 'myid', '홍길동‘,’1111’); insert into user values(member_seq.nextval,'misskim',‘아무개‘,’1111’); insert into user values(member_seq.nextval,'another','김나나‘,’1111’); select * from user; 1. 시퀀스 이용해서 데이터 삽입insert into 테이블명(필드명..) values (시퀀스명.nextval, ..);2. 현재 시퀀스 값 읽어 오기select 시퀀스명.currval from 테이블명;3. 시퀀스 삭제drop sequence 시퀀스명; 42

43. package db; • import java.sql.Connection; • import java.sql.DriverManager; • import java.sql.ResultSet; • import java.sql.PreparedStatement; • public class DBManager { • public ConnectiondbConn() { • Connection conn = null; • //protocol://[hostname]:[port number]/[DB name] • String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"; • //String url= "jdbc:mysql://127.0.0.1:3306/mydb"; • String uid = "lkh"; • String upw = "0000"; • try { • Class.forName("oracle.jdbc.driver.OracleDriver"); • //Class.forName("com.mysql.jdbc.Driver").newInstance(); • conn = DriverManager.getConnection(url , uid, upw); • } catch(Exception e) { • e.printStackTrace(); • } • return conn; • } db>DBManager.java

44. public void dbClose(Connection conn, PreparedStatement stmt, ResultSet rs) { try { if(rs != null ) { rs.close(); } if(stmt != null) {stmt.close(); } if(conn != null) {conn.close();} } catch (Exception e ) { e.printStackTrace(); } } public void dbClose(Connection conn, PreparedStatement stmt) { try { if(stmt != null) {stmt.close(); } if(conn != null) {conn.close();} } catch (Exception e ) { e.printStackTrace(); } } }

45. #2. connTest (2/3) <%@ page language="java" contentType="text/html; charset=EUC-KR" pageEncoding="EUC-KR" import="db.DBManager, java.sql.*"%> <html> <head><title>JDBC connection tset</title></head> <body> <h3>Demo #3 : DB 연결 테스트 </h3><hr> <% DBManagerdb = new DBManager(); Connection conn= db.dbConn(); String result = ""; if(conn != null) { result = "success"; } else { result = "faild"; } %> DB 연결 결과 : <%=result%> </body> </html> db>conn_test.jsp

46. #2. connTest (3/3) http://127.0.0.1/db/conn_test.jsp