CHAP 11. 데이터베이스와 JSP

PreparedStatement 사용하기 커넥션풀과 DBCP 사용 CONTENTS 데이터베이스/스키마/테이블/레코드 SQL 쿼리 기초 JDBC 프로그래밍 JDBC URL Statement 사용하기 ResultSet 사용하기 PreparedStatement 사용하기 커넥션풀과 DBCP 사용 JSP 2.0 Programming

DBMS(Database Management System) 데이터를 저장했다가 필요할 때에 사용하는 것 DBMS(Database Management System) 데이터베이스를 관리하는 시스템 JSP 2.0 Programming

데이터의 추가/조회/변경/삭제 데이터의 무결성 유지 트랜잭션 관리 데이터의 백업 및 복원 데이터 보안 DBMS의 주요 기능 JSP 2.0 Programming

칼럼의 모음을 레코드(record)라고 표현 테이블 & 레코드 테이블 - 데이터가 저장되는 가상의 장소 테이블은 1개 이상의 칼럼으로 구성 각 칼럼은 타입을 가지며, 제약(값의 길이, 가질 수 있는 값 등)을 갖는다. 이런 테이블의 구성을 스키마(schema)라고 함 칼럼의 모음을 레코드(record)라고 표현 하나의 테이블은 여러 개의 레코드로 구성

테이블 - 데이터가 저장되는 장소 스키마 - 테이블의 구조에 대한 정보 저장 테이블과 스키마 [테이블 스키마 예] 필드이름 필드타입 길이 MEMBERID VARCHAR 10 PASSWORD NAME 20 EMAIL 80 JSP 2.0 Programming

레코드 - 스키마에 정의된 필드에 해당하는 값을 갖는 하나의 행 레코드가 모여서 테이블을 구성한다. 테이블과 레코드 레코드 - 스키마에 정의된 필드에 해당하는 값을 갖는 하나의 행 레코드가 모여서 테이블을 구성한다. MEMBERID PASSWORD NAME EMAIL javaman java 최범균 javaman@a.com jspman jsp 최모모 jspman@a.com 레코드(Record) JSP 2.0 Programming

주요키(Primary Key) 인덱스 주요키와 인덱스 각각의 레코드를 구별하기 위해 사용되는 것 각 레코드가 서로 다른 값을 갖는 칼럼 주요키 값을 이용해서 빠른 검색 가능 인덱스 지정한 칼럼에 맞춰 데이터의 정렬 순서를 미리 계산 주요키도 인덱스의 종류 인덱스로 사용되는 칼럼은 중복된 값을 가질 수도 있음

데이터베이스 프로그래밍의 일반적인 순서 DBMS 클라이언트 - 데이터베이스를 사용하는 어플리케이션 1. 연결 데이터 베이스 데이터 베이스 2. 명령어 전송(SQL 실행) 3. 결과 전송 4. 연결종료 DBMS 클라이언트 - 데이터베이스를 사용하는 어플리케이션 DBMS - 데이터베이스를 관리해주는 시스템 데이터베이스 - 데이터를 저장할 공간 * 이 세가지 요소는 DB 프로그래밍의 필수 요소 JSP 2.0 Programming

SQL이란? SQL은 'Strutured Query Language'의 약자로서 데이터베이스로부터 데이터를 조회하고 삭제하는 등의 데이터베이스 작업을 수행할 때 사용하는 질의 언어 * 거의 모든 데이터베이스 프로그래밍은 SQL을 사용해서 데이터를 처리한다. JSP 2.0 Programming

SQL 기초 - 주요 타입 SQL 타입 설명 CHAR 확정 길이의 문자열을 저장. 표준의 경우 255 글자까지만 저장할 수 있다. VARCHAR 가변 길이의 문자열을 저장. 표준의 경우 255 글자까지만 저장할 수 있다. LONG VARCHAR 긴 가변 길이의 문자열을 저장 NUMERIC 숫자를 저장 DECIMAL 십진수를 저장 INTEGER 정수를 저장 TIMESTAMP 날짜 및 시간을 저장 TIME 시간을 저장 DATE 날짜를 저장 CLOB 대량의 문자열 데이터를 저장 BLOB 대량의 바이너리 데이터를 저장

테이블 생성 쿼리: CREATE TABLE ... Syntax create table TABLENAME ( FIELD_NAME1 FIELD_TYPE1(LEN1), FIELD_NAME2 FIELD_TYPE2(LEN2), ..., FIELD_NAMEn FIELD_TYPEn(LENn) ) TABLENAME - 테이블을 식별할 때 사용할 이름 FIELD_NAME - 각 필드의 이름 FIELD_TYPE - 각 필드에 저장될 값의 타입 LEN - 저장될 값의 최대 길이 Example create table MEMBER ( MEMBERID VARCHAR(10) NOT NULL PRIMARY KEY, PASSWORD VARCHAR(10) NOT NULL, NAME VARCHAR(20) NOT NULL, EMAIL VARCHAR(80) ) JSP 2.0 Programming

데이터 삽입 쿼리: INSERT INTO ... VALUES ... Syntax insert into TABLENAME (FIELD1, FIELD2, .., FIELDn) values (VALUE1, VALUE2, .., VALUEn) TABLENAME - 테이블을 식별할 때 사용할 이름 FIELDx - 값을 지정할 필드의 이름 VALUEx - 같은 순서에 위치한 필드의 값 필드 목록을 생략하면 모든 필드에 대해서 값을 삽입한다. Example 필드 지정 insert into MEMBER (MEMBERID, PASSWORD, NAME) values ('madvirus', '1234', '최범균') 필드 지정 안함 insert into MEMBER values ('era13', '5678', '최범균', 'madvirus@empal.com') JSP 2.0 Programming

데이터 조회 쿼리: SELECT ... FROM ... WHERE ... Syntax select FIELD1, FIELD2, ..., FIELDn from TABLENAME [where 검색조건] [order by 정렬순서] TABLENAME - 테이블을 식별할 때 사용할 이름 FIELDx - 값을 읽어올 필드의 이름 where 절에 조건을 입력하면 조건에 해당하는 레코드의 값만 읽어온다. order by 절에 정렬 순서를 입력하면 지정한 순서대로 레코드를 읽어온다. select * from 과 같이 '*'를 사용하면 레코드의 모든 필드를 읽어온다. Example select * from MEMBER select * from MEMBER where NAME = '최범균' and EMAIL='madvirus@empal.com' select * from MEMBER where EMAIL <> '' select * from MEMBER where EMAIL is NULL select * from MEMBER where EMAIL is not NULL select * from EMPLOY where where SALARY >= 1000 and SALARY <= 2000 select * from MEMBER where NAME like '최%' select * from MEMBER order by NAME asc, MEMBERID desc JSP 2.0 Programming

sum() : 특정 필드의 합 max() : 특정 필드 중 최대 값 min() : 특정 필드 중 최소값 데이터 조회 쿼리: 집합 sum() : 특정 필드의 합 max() : 특정 필드 중 최대 값 min() : 특정 필드 중 최소값 count() : 검색된 레코드의 개수 Example select count(*) from MEMBER where NAME like '최%' select max(SALARY), min(SALARY) from EMPLOY JSP 2.0 Programming

데이터 수정 쿼리: UPDATE ... SET ... Syntax update TABLENAME set FIELD1=VALUE1, FIELD2=VALUE2, ... [where 조건절] TABLENAME - 테이블을 식별할 때 사용할 이름 FIELDx - 값을 읽어올 필드의 이름 VALUEx - 변경할 값 where 절이 존재하지 않으면 모든 레코드를 수정한다. where 절이 있는 경우, 조건에 해당하는 레코드만 수정한다. Example update MEMBER set ADDRESS = '서울시' where MEMBERID = 'myid' JSP 2.0 Programming

데이터 삭제 쿼리: DELETE FROM ... Syntax delete from TABLENAME [where 조건절] Example delete from MEMBER where MEMBERID = 'myid' JSP 2.0 Programming

두 개 이상의 테이블로부터 관련 있는 데이터를 읽어올 때 사용된다 조인(join) : 조회시 테이블의 연결 두 개 이상의 테이블로부터 관련 있는 데이터를 읽어올 때 사용된다 Syntax select A.필드1, A.필드2, B.필드3, B.필드4 from 테이블1 as A, 테이블2 as B where A.필드x = B.필드y 테이블1의 필드x와 테이블2의 필드y의 값이 같은 레코드를 하나의 행(row)으로 읽어온다. Example select * from MEMBER as A, MEMBER_ETC as B where A.MEMBERID = B.MEMBERID; JSP 2.0 Programming

DBMS의 종류에 상관없이 하나의 JDBC API를 사용해서 데이터베이스 작업을 처리 Java DataBase Connectivity의 약자로서 자바에서 데이터베이스와 관련된 작업을 처리할 수 있도록 도와주는 API DBMS의 종류에 상관없이 하나의 JDBC API를 사용해서 데이터베이스 작업을 처리 일단 익혀두면 하나의 JDBC API로 모든 DBMS에 대해서 데이터베이스 작업을 처리할 수 있음. JSP 2.0 Programming

JDBC 프로그래밍의 구조 JDBC 드라이버 데이터 베이스 자바 (웹) 어플리케이션 JDBC API 데이터 베이스 JDBC 드라이버 오라클, MS-SQL, DB2, MySQL 등 현재 사용되고 있는 거의 대부분의 DBMS가 자신에 알맞은 JDBC 드라어비를 제공하고 있기 때문에 JDBC 드라이버가 없어서 JSP에서 데이터베이스 프로그래밍을 할 수 없는 상황은 발생하지 않는다. JSP 2.0 Programming

쿼리 실행을 위한 Statement 객체 생성 쿼리 실행 쿼리 실행 결과 사용 Statement 종료 데이터베이스 커넥션 종료 JDBC 프로그래밍의 실행 순서 JDBC 드라이버 로딩 데이터베이스 커넥션 구함 쿼리 실행을 위한 Statement 객체 생성 쿼리 실행 쿼리 실행 결과 사용 Statement 종료 데이터베이스 커넥션 종료 JSP 2.0 Programming

JDBC 프로그래밍의 실행 순서: 예제 (1) Example 데이터베이스 처리에 필요한 클래스 import import java.sql.*; // 1. JDBC 드라이버 로딩 Class.forName("com.mysql.jdbc.Driver"); Connection conn = null; Statement stmt = null; ResultSet rs = null; 데이터베이스 처리에 필요한 클래스 import 1. JDBC 드라이버 로딩 JSP 2.0 Programming

JDBC 프로그래밍의 실행 순서: 예제 (2) Example 2. 데이터베이스 커넥션 생성 try { String jdbcDriver = "jdbc:mysql://localhost:3306/chap11?" + "useUnicode=true&characterEncoding=euc-kr"; String dbUser = "jspexam"; String dbPass = "jspex"; String query = "select * from MEMBER order by MEMBERID"; // 2. 데이터베이스 커넥션 생성 conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass); // 3. Statement 생성 stmt = conn.createStatement(); // 4. 쿼리 실행 rs = stmt.executeQuery(query); // 5. 쿼리 실행 결과 출력 while(rs.next()) { String name= rs.getString("NAME") ; String id= rs.getString(“MemberID") ; int age = rs.getInt(“age”) ; } 2. 데이터베이스 커넥션 생성 3. 쿼리 실행을 위한 Statement 생성 4. 쿼리를 실행해서 결과를 ResultSet에 저장 5. ResultSet으로부터 쿼리 결과 읽어옴 JSP 2.0 Programming

JDBC 프로그래밍의 실행 순서: 예제 (3) Example 6. Statement와 ResultSet 닫음 } catch(SQLException ex) { // 에러 발생 } finally { // 6. 사용한 Statement 종료 if (rs != null) try { rs.close(); } catch(SQLException ex) {} if (stmt != null) try { stmt.close(); } catch(SQLException ex) {} // 7. 커넥션 종료 if (conn != null) try { conn.close(); } catch(SQLException ex) {} } 6. Statement와 ResultSet 닫음 7. DB 커넥션 닫음 JSP 2.0 Programming

JDBC 드라이버 로딩 try { Class.forName("JDBC드라이버 클래스의 완전한 이름"); Code try { Class.forName("JDBC드라이버 클래스의 완전한 이름"); } catch(ClassNotFoundException ex) { // 지정한 클래스가 존재하지 않을 경우 에러가 발생한다. // 에러 처리 } JDBC 드라이버 클래스 예 MySQL - com.mysql.jdbc.Driver 오라클 - oracle.jdbc.driver.OracleDriver JSP 2.0 Programming

JDBC URL jdbc:[DBMS]:[데이터베이스식별자] Syntax Example MySQL jdbc:mysql://localhost:3306/chap11?useUnicode=true&characterEncoding=utf-8 오라클 jdbc:oracle:thin:@172.0.0.1:1521:ORCL 주요 DBMS의 JDBC URL 구성 MySQL : jdbc:mysql://HOST[:PORT]/DBNAME[?param=value&param1=value2&...] Oracle: jdbc:oracle:thin:@HOST:PORT:SID MS SQL : jdbc:sqlserver://HOST[:PORT];databaseName=DB JSP 2.0 Programming

데이터베이스 연결 생성 → DB와 연결된 Connection 객체를 리턴한다. Code DriverManager.getConnection(String jdbcURL) DriverManager.getConnection(String jdbcURL, String user, String password) → DB와 연결된 Connection 객체를 리턴한다. jdbcURL - 데이터베이스를 나타내는 JDBC URL user - 데이터베이스 계정 password - 데이터베이스 계정 암호 Exception Code Connection conn = null; try { conn = DriverManager.getConnection(...); ... } catch(SQLException ex) { ... // 예외 처리 } 커넥션을 구하지 못하면 SQLException 예외를 발생 JSP 2.0 Programming

데이터베이스 커넥션 얻기: 예제 코드 Example Connection conn = null; try { String jdbcDriver = "jdbc:mysql://localhost:3306/chap11?" + "useUnicode=true&characterEncoding=utf-8"; String dbUser = "jspexam"; String dbPass = "jspex"; conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass); ... } catch(SQLException ex) { // 에러 발생 } finally { if (conn != null) try { conn.close(); } catch(SQLException ex) {} } JSP 2.0 Programming

Statement를 사용한 쿼리 실행 Statement stmt = null; Code Statement stmt = null; stmt = conn.createStatement(); stmt.executeQuery("select 쿼리... "); stmt.executeUpdate("insert,update,delete 쿼리..."); ResultSet executeQuery(String query) - SELECT 쿼리를 실행한다. SELECT 쿼리를 실행한 결과는 ResultSet에 저장된다. int executeUpdate(String query) - INSERT, UPDATE, DELETE 쿼리를 실행한다. 결과값은 삽입/변경/삭제 된 레코드의 개수 JSP 2.0 Programming

ResultSet으로부터 데이터 읽기 ResultSet.next() 메소드는 다음 행이 존재하면 true 리턴 ResultSet.getXXXX() 형태의 메소드를 사용하여 지정한 컬럼 데이터 읽어옴 Code rs = stmt.executeQuery("select * from member"); if (rs.next()) { // 다음행(첫번째 행)이 존재하면 rs.next()는 true를 리턴 // rs.next()에 의해 다음행(첫번째 행)으로 이동 String name = rs.getString("NAME"); ... } else { // 첫번째 행이 존재하지 않는다. 즉, 결과가 없다. } ResultSet.getXXXX() 메소드를 사용해서 결과로부터 필요한 데이터를 읽어온다. 데이터 조회 위한 주요 메서드 getString(), getInt(), getLong(), getFloat(), getDouble(), getTimestamp(), getDate(), getTime() JSP 2.0 Programming

ResultSet.next() 메소드의 이해 rs.next() : true rs.next() : true rs.next() : true rs.next() : false ► 1행 ► 1행 1행 1행 1행 2행 2행 ► 2행 2행 2행 3행 3행 3행 ► 3행 3행 ► JSP 2.0 Programming

ResultSet에서 데이터 조회하는 코드 1개 행 처리 1개 이상 행 처리 rs = stmt.executeQuery("select * from member"); if (rs.next()) { // 다음 행(첫 번째 행)이 존재하면 rs.next()는 true를 리턴 // rs.next()에 의해 다음 행(첫 번째 행)으로 이동 String name = rs.getString("NAME"); } else { // 첫 번째 행이 존재하지 않는다. 즉, 결과가 없다. } rs = stmt.executeQuery(...); if (rs.next()) { do { String name = rs.getString("NAME"); ... } while( rs.next() ); }

PreparedStatement를 사용한 쿼리 실행 Connection.prepareStatement() 메소드를 사용하여 PreparedStatement 생성 PreparedStatement의 set 메소드를 사용하여 필요한 값 지정 PreparedStatement의 executeQuery() 또는 executeUpdate() 메소드를 사용하여 쿼리를 실행. finally 블럭에서 사용한 PreparedStatement를 닫는다. (close() 메소드 실행) JSP 2.0 Programming

PreparedStatement를 이용한 처리 SQL의 틀을 미리 정해 놓고, 나중에 값을 지정하는 방식 PreparedStatement의 일반적 사용 쿼리 실행 관련 메서드 ResultSet executeQuery() - SELECT 쿼리를 실행할 때 사용되며 ResultSet을 결과값으로 리턴한다. int executeUpdate() - INSERT, UPDATE, DELETE 쿼리를 실행할 때 사용되며, 실행 결과 변경된 레코드의 개수를 리턴한다 pstmt = conn.prepareStatement( "insert into MEMBER (MEMBERID, NAME, EMAIL) values (?, ?, ?)"); pstmt.setString(1, "madvirus"); // 첫번째 물음표의 값 지정 pstmt.setString(2, "최범균"); // 두번째 물음표의 값 지정 pstmt.executeUpdate();

PreparedStatement의 값 바인딩 관련 메서드 설명 setString(int index, String x) 지정한 인덱스의 파라미터 값을 x로 지정한다. setInt(int index, int x) 지정한 인덱스의 파라미터 값을 int 값 x로 지정한다. setLong(int index, long x) 지정한 인덱스의 파라미터 값을 long 값 x로 지정한다. setDouble(int index, double x) 지정한 인덱스의 파라미터 값을 double 값 x로 지정한다. setFloat(int index, float x) 지정한 인덱스의 파라미터 값을 float 값 x로 지정한다. setTimestamp(int index, Timesetamp x) 지정한 인덱스의 값을 SQL TIMESTMAP 타입을 나타내는 java.sql.Timestamp 타입으로 지정한다. setDate(int index, Date x) 지정한 인덱스의 값을 SQL DATE 타입을 나타내는 java.sql.Date 타입으로 지정한다. setTime(int index, Time x) 지정한 인덱스의 값을 SQL TIME 타입을 나타내는 java.sql.Time 타입으로 지정한다.

PreparedStatement를 사용한 쿼리 실행 Code 물음표(?)를 사용해서 지정할 값의 위치 명시 PreparedStatement pstmt = null; pstmt = conn.prepareStatement( "insert into MEMBER (MEMBERID, NAME, EMAIL) values (?, ?, ?)"); pstmt.setString(1, memID); pstmt.setString(2, name); pstmt.setString(3, "no-mail@madvirus.net"); pstmt.executeUpdate(); 물음표 위치에 들어갈 값을 지정한다. 물음표의 인덱스 값은 1부터 시작 ResultSet executeQuery() - SELECT 쿼리를 실행한다. SELECT 쿼리를 실행한 결과는 ResultSet에 저장된다. int executeUpdate() - INSERT, UPDATE, DELETE 쿼리를 실행한다. 결과값은 삽입/변경/삭제 된 레코드의 개수 JSP 2.0 Programming

PreparedStatement의 사용 이유 반복해서 실행되는 동일 쿼리의 속도를 향상 DBMS가 PreparedStatement와 관련된 쿼리 파싱 회수 감소 값 변환 처리 작은 따옴표 등 값에 포함된 특수 문자의 처리 코드의 간결함 문자열 연결에 따른 코드의 복잡함 감소

PreparedStatement를 사용하는 이유 1, 반복해서 실행되는 동일 쿼리의 속도를 증가시키기 위해 Code String ids = {"abc", "def", "ghi", .... }; stmt = conn.createStatement(); stmt.executeQuery("select * form MEMBER where MEMBERID = '"+ids[0]+"'"); stmt.executeQuery("select * form MEMBER where MEMBERID = '"+ids[1]+"'"); stmt.executeQuery("select * form MEMBER where MEMBERID = '"+ids[2]+"'"); ‥‥‥ 매번 쿼리 분석 쿼리 파싱 회수 감소, 분석 시간 단축 String ids = {"abc", "def", "ghi" }; pstmt = conn.prepareStatement("select * from MEMBER where MEMBERID = ?"); for (int i = 0 ; i < ids.length ; i++) { pstmt.setString(1, ids[i]); pstmt.executeQuery(); } 한번만 쿼리 분석 JSP 2.0 Programming

PreparedStatement를 사용하는 이유 2, 쿼리에 들어갈 값을 자동으로 변환. 3, 쿼리 코드의 복잡성 없앰. Code String value = "최범\'균"; stmt.executeQuery("select * from member where "+ "name = '"+ value.replaceAll("'", "''") + "' "); 쿼리에 포함되는 값은 작은따옴표(')를 포함해서 특수 문자의 처리가 필요. 이에 따라 쿼리 코드가 복잡해짐 String value = "최범\'균"; pstmt = conn.prepareStatement("select * from member where name = ?"); pstmt.setString(1, value); pstmt.executeQuery(); 값에 포함된 특수문자를 PreparedStatement가 알아서 처리 이에 따라 코드도 간결해짐 JSP 2.0 Programming

JSP 2.0 Programming