13장. 데이터베이스와 JSP 제13장

1. MySQL 설치 및 구성 MySQL 설치 MySQL Community Server 5.1 다운로드 URL: http://dev.mysql.com/downloads/mysql/5.1.html MySQL 5.1 설치시작 화면 설치유형 선택 화면 설치완료 화면 제13장

1. MySQL 설치 및 구성 MySQL 설치 MySQL 서버 설정 (1/2) 윈도우즈 서비스 등록 및 PATH 환경변수 설정 서버설정 시작 화면 서버설정의 첫 화면 MySQL 서버는 윈도우즈 OS의 기본 서버 프로그램 중 하나로 등록 PATH 환경변수에 MySQL 실행파일이 모여 있는 폴더를 등록하여 윈도우즈의 커맨드 창내의 어느 폴더에서나 MySQL 관련 명령어들을 실행할 수 있도록 함 제13장

1. MySQL 설치 및 구성 MySQL 설치 MySQL 서버 설정 (2/2) 관리자 비밀번호 설정 - 비밀번호를 입력하는 창에 자신만이 관리할 값을 입력하면 되지만 본 책에서는 "jspbook" 이라는 비밀번호를 입력 제13장

1. MySQL 설치 및 구성 MySQL 접속 및 테스트 cmd 창을 열어서 다음 명령어로 접속 관리자 패스워드 (본 책에서는 “jspbook”)을 입력 오른쪽 화면이 보이면 접속 성공 현재 등록된 데이터베이스 목록 출력하기 위해 접속상태에서 다음 명령어 입력 > mysql -u root -p netstat –na //3306 포트 사용 // 다른 cmd 창에서 show databases; 제13장

1. MySQL 설치 및 구성 새로운 데이터베이스 “jspbookdb” 생성 접속 상태에서 다음 명령어 실행 데이터베이스 목록 다시 보기 CREATE DATABASE jspbookdb default character set utf8 collate utf8_general_ci; show databases; > DROP database_name; use database_name; show tables; 제13장

1. MySQL 설치 및 구성 MySQL GUI Tools 설치 다운로드 URL: http://dev.mysql.com/downloads/gui-tools/5.0.html Database 선택 // use db_name MySQL Query Browser의 사용 show databases; show tables; 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 JDBC의 정의 및 필요성 JDBC(Java DataBase Connectivity) Java를 이용한 데이터베이스 접속과 SQL 문장의 실행, 그리고 그 결과로 얻어진 데이터의 핸들링을 제공하는 방법과 절차에 대한 규약 개발자는 MySQL, Oracle, MS-SQL Server 데이터베이스 등에 독립적으로 표준화된 하나의 API 사용방법만 숙지하면 된다. DB 벤더 또는 써드 파티(third party)에서 제공하는 JDBC 드라이버가 필요 JNDI (Java Naming and Directory Interface)를 사용하여 JDBC 설정 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 MySQL JDBC의 설치 및 설정 MySQL 데이터베이스 처리용 JDBC인 "MySQL Connector/J" 다운로드 다운로드 URL: http://dev.mysql.com/downloads/connector/j 압축을 풀면 나오는 폴더 안에 mysql-connector-java-5.1.7-bin.jar 파일을 Tomcat의 설치폴더 바로 밑의 lib 폴더에 복사 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 JNDI (Java Naming and Directory Interface) JSP 및 Servlet으로부터 MySQL을 접근하기 위하여 MySQL을 자원(Resource)으로 간주하여 이 자원을 Tomcat에 등록하여 사용하는 최신 방법 현재의 c:\apache-tomcat-*.*.*\conf\server.xml 내용 <GlobalNamingResources> <!-- Editable user database that can also be used by UserDatabaseRealm to authenticate users --> <Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" /> </GlobalNamingResources> 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 JNDI (Java Naming and Directory Interface) 설정 server.xml에 mysql을 전역자원(global resource)으로 등록 <GlobalNamingResources> <!-- Editable user database that can also be used by UserDatabaseRealm to authenticate users --> <Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" /> <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource" username="root" password="jspbook" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/jspbookdb?characterEncoding=UTF-8" validationQuery="select 1" autoReconnect="true" maxActive="100" maxIdle="30" maxWait="3000" /> </GlobalNamingResources> 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 좀 더 빠른 시간 내에 데이터베이스 작업을 끝낼 수 있다는 장점이 있음 또한, 커넥션 풀은 동시에 생성될 수 있는 커넥션 수를 관리하므로 동시 접속자수가 갑자기 증가하여도 이상을 방지할 수 있다. JNDI를 통한 DB 접속 방법에는 기본적으로 커넥션 풀 기법이 내장 Connection Pool 설명 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 maxActive 사용이 되고 있는(active) 최대 커넥션의 개수를 의미 maxActive="100"으로 설정하면 총 100개의 커넥션까지는 동시 데이터베이스 접속을 허용하겠다는 의미 (maxActive="-1": 제한 없음) maxIdle 사용되지 않는(idle) 커넥션을 커넥션 풀에 저장해 둘 수 있는 최대 수 maxIdle="30"으로 설정하면 사용되지 않는 커넥션이 풀에 최대 30개 까지만 존재함 일반적으로 커넥션 풀이 초기화되면 maxIdle에 지정된 수 이상은 생성되지 않는다. (minActive="-1": 제한 없음) maxWait (단위: ms) 동시 접속자수가 많아져서 사용가능한 커넥션이 없을 때 maxWait 에 지정된 시간만큼 기다린다. 이 시간이 지나도 사용가능한 커넥션이 발견되지 않을 시에는 Exception이 발생된다. maxWait="3000"는 사용가능한 커넥션이 커넥션 풀에 없을 때 사용가능한 것이 나타날 때까지 3초간 기다린다는 의미 (maxWait="-1": 제한 없음) 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 설정 내용을 포함한 server.xml 예제 server.xml에 mysql을 전역자원(global resource)으로 등록 [예제 13.1] Tomcat 설치 폴더\conf\server.xml 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 <?xml version='1.0' encoding='utf-8'?> ... <Server port="8005" shutdown="SHUTDOWN"> <!--APR library loader. Documentation at /docs/apr.html --> <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" /> <!--Initialize Jasper prior to webapps are loaded. Documentation at /docs/jasper-howto.html --> <Listener className="org.apache.catalina.core.JasperListener" /> <!-- JMX Support for the Tomcat server. Documentation at /docs/non-existent.html --> <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" /> <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" /> <!-- Global JNDI resources Documentation at /docs/jndi-resources-howto.html --> 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 설정 내용을 포함한 server.xml 예제 [예제 13.1] Tomcat 설치 폴더\conf\server.xml 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 <GlobalNamingResources> <!-- Editable user database that can also be used by UserDatabaseRealm to authenticate users --> <Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" /> <Resource name="jdbc/mysql" auth="Container" t ype="javax.sql.DataSource" username="root" password="jspbook" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/jspbookdb?characterEncoding=UTF-8" validationQuery="select 1" maxActive="100" maxIdle="30" maxWait="3000" /> </GlobalNamingResources> ... 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 설정 내용을 포함한 context.xml 예제 [예제 13.2] Tomcat 설치 폴더\conf\context.xml 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 <?xml version='1.0' encoding='utf-8'?> ... <!-- The contents of this file will be loaded for each web application --> <Context> <!-- Default set of monitored resources --> <WatchedResource>WEB-INF/web.xml</WatchedResource> <!-- Uncomment this to disable session persistence across Tomcat restarts --> <!-- <Manager pathname="" /> --> <!-- Uncomment this to enable Comet connection tacking (provides events on session expiration as well as webapp lifecycle) --> <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" /> <ResourceLink name="jdbc/mysql" global="jdbc/mysql" type="javax.sql.DataSource" /> </Context> name과 global 속성의 값은 server.xml의 <Resource> 태그의 name 속성 값과 동일하고 type 속성 값도 <Resource> 태그의 type 속성 값과 동일하다. 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 설정 내용을 포함한 web.xml 예제 jspbook 웹 애플리케이션들이 전역자원을 사용할 수 있도록 설정 [예제 12.3] jspbook\WEB-INF\web.xml <?xml version="1.0" encoding="utf-8"?> <web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5"> ... <resource-ref> <description>MYSQL POOL</description> <res-ref-name>jdbc/mysql</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app> <res-ref-name>태그의 값으로 적힌 "jdbc/mysql”은 반드시 server.xml 및 context.xml 내의 <Resource> 태그 및 <ResourceLink> 태그의 name 속성 값과 동일. 한편 <res-type>태그의 값도 server.xml 및 contex..xml에서 type속성에 설정한 값과 동일. 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 MySQL 접속 유틸리티인 ConnectionContext 작성 (sjc로 compile) [예제 12.4] jspbook\WEB-INF\java_sources\ConnectionContext.java 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 package thinkonweb.util; import java.sql.*; import javax.naming.*; import javax.sql.DataSource; public class ConnectionContext { private static String jndiName = "jdbc/mysql"; private static Connection conn = null; public static Connection getConnection() { if (conn!= null) return conn; try { Context initContext = (Context)new InitialContext().lookup("java:comp/env/"); DataSource ds = (DataSource)initContext.lookup(jndiName); conn = ds.getConnection(); } catch(Exception e) { e.printStackTrace(); } getConnection() 메소드 만약 이미 생성된 java.sql.Connection 객체가 있다면 새롭게 생성하지 않고 이미 생성되어 있는 것을 바로 리턴한다. Tomcat 컨테이너 설정 정보(server.xml) 및 jspbook 웹 어플리케이션의 설정 정보(web.xml)의 MySQL 설정 내용을 참조하여 java.sql.Connection 객체를 새롭게 생성하고 메소드를 호출한 측으로 그 객체를 리턴한다. 제13장

2. JNDI를 활용한 DB 커넥션 풀 (Connection Pool) 설정 JSP에서 MySQL 접속 테스트하기 [예제 13.5] jspbook\ch13\hellodb.jsp 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 <%@ page contentType="text/html;charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> <html> <body> <% String msg = null; String sql = "SELECT 'hello jspbookdb!' AS msg"; Connection conn = ConnectionContext.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); if (rs.next()) msg = rs.getString("msg"); rs.close(); pstmt.close(); %> 쿼리문: <%=sql%><br/> 쿼리결과: <%=msg%><br/> </body> </html> 제13장

3. 데이터베이스와 SQL 데이터베이스란? 데이터베이스(Database) 검색과 변경이 쉽도록 조직화되어 있는 자료의 집합 관계형 데이터베이스 (Relational Database) 일련의 정형화된 테이블로 구성된 데이터 항목들의 집합체로서 그 데이터들은 다양한 방법으로 접근하거나 조합될 수 있다. 관계형 데이터베이스 관리 시스템(Relational Database Management System, RDBMS) 자료를 관계형 데이터베이스의 테이블들에 체계적으로 정리하여 모아두기 위해서 데이터베이스를 관리하여 주는 시스템 데이터를 저장하고 이 데이터로부터 유용한 정보를 얻어내기 위한 효율적이면서도 편리한 방법(SQL)을 사용자에게 제공 예 상용 RDBMS: 오라클(Oracle), MS-SQL Server 무료 RDBMS: MySQL 제13장

3. 데이터베이스와 SQL RDBMS에서 사용되는 주요 용어 테이블 필드(컬럼) 레코드(튜플) 기본키 (Primary Key) 여러 개의 레코드를 담고 있는 논리적인 구조로서 행과 열로 구성된 데이터의 모임 필드(컬럼) 테이블의 열에 해당되는 것으로 데이터의 자료형태, 데이터의 길이 등과 같은 특성을 나타낸다. 위 예에서 ‘학번’, ‘이름’, ‘나이’, ‘학과’는 필드(컬럼)명이 되고 필드명에 속하는 자료가 필드값이 된다. 레코드(튜플) 컬럼들의 집합으로 테이블의 각 행을 레코드라고 한다. 위 예에서 ‘200010001 김송이 20 컴퓨터학과’가 하나의 레코드가 된다. 하나의 테이블에 동일한 레코드가 중복되어서는 안된다. 기본키 (Primary Key) 단일 레코드의 식별자 역할을 하는 필드를 지칭한다. 그러므로 두 개 이상의 레코드가 동일한 기본키 값을 가질 수 없다. 위 예에서 ‘학번’ 필드가 기본키 역할을 하며 ‘김송이’ 이름을 지닌 레코드는 기본키 값으로서 200910001을 지닌다. 제13장

3. 데이터베이스와 SQL SQL 소개 및 문법 SQL(Structured Query Language) SQL의 주요 기능 사용자가 데이터베이스 관리 시스템에게 어떤 일을 하라고 지시하기 위한 언어 데이터베이스에 저장된 자료를 조직, 관리, 검색하기 위한 언어 SQL의 주요 기능 데이터 정의 기능: 데이터베이스와 테이블의 생성, 소멸, 변경 등을 할 수 있게 한다. 데이터 검색 기능: 데이터베이스에 저장된 자료를 검색하여 보여 줄 수 있게 한다. 데이터 조작 기능: 테이블에 자료를 삽입하거나 수정, 삭제할 수 있게 한다. SQL 작성시 주의 사항 SQL 문은 대소문자 구별을 하지 않는다 세미콜론(;)을 명령문 맨 마지막에 반드시 붙인다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 정의 기능 SQL (DDL) 데이터베이스 및 테이블 생성 문법 JSP내에서 직접 수행하는 일은 그리 많지 않다. 대신에 콘솔창이나 “MySQL Query Browser" 내에서 해당 명령어를 대부분 수행한다. 문법 CREATE DATABASE database_name; 예: CREATE DATABASE jspbookdb; 설명: jspbookdb라는 이름의 데이터베이스를 만든다. CREATE TABLE table_name(column_name1 datat_type1, ... ); 예: CREATE TABLE Student(ID char(10) PRIMARY KEY, Name Char(10), Age int); 설명: Student라는 이름의 테이블을 만들고 테이블을 구성하는 컬럼은 학번(ID), 이름, 나이가 된다. 한편, 학번은 주요키로서 지정한다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 정의 기능 SQL MySQL Query Browser를 사용하여 실습 이미 jspbookdb가 생성되어 있다면 다음 명령어를 통해 사용할 데이터베이스를 변경 use jspbookdb; jspbookdb가 생성되어 있지 않다면 다음 명령어를 통해 생성 CREATE DATABASE jspbookdb; 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 정의 기능 SQL 다음 명령문를 통해 "Student" 테이블을 생성 CREATE TABLE Student(ID char(10) PRIMARY KEY, Name char(10), Age int, Major char(30)); 오른쪽 위 Schemata 창에서 마우스 오른쪽 버튼 -> ‘Refresh’로 확인 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 정의 기능 SQL 이미 만든 데이터베이스 및 테이블을 삭제하는 명령문 DROP DATABASE database_name; 예: DROP DATABASE jspbookdb; 설명: jspbookdb라는 이름의 데이터베이스를 삭제한다. 관련된 테이블등의 자료가 모두 삭제된다. DROP TABLE table_name; 예: DROP TABLE Student; 설명: Student라는 이름의 테이블을 삭제한다. 테이블에 담긴 자료가 함께 삭제된다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 정의 기능 SQL 이미 테이블을 변경하는 명령문 테이블에 새로운 컬럼을 추가하거나 삭제하는 기능등을 수행 문법 ALTER TABLE table_name ADD column_name datatype; 예: ALTER TABLE Student ADD Grade char(2); 설명: Student라는 이름의 테이블에 두 개의 문자로 구성된 char(2) 타입의 Grade 컬럼을 추가한다. ALTER TABLE table_name DROP column_name; 예: ALTER TABLE Student DROP Grade; 설명: Student라는 이름의 테이블에 존재하는 Grade 컬럼을 삭제한다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 검색 기능 SQL (DML) Asc: 오름차순, Desc: 내림차순 SELECT column_list FROM table_name [WHERE condition ...]; 예1: SELECT * FROM Student; 설명: Student 테이블에 저장되어 있는 모든 자료를 가져온다. 예2: SELECT ID, Name FROM Student; 설명: Student 테이블에서 학번(ID)와 이름 컬럼에 있는 모든 자료를 가져온다. 예3: SELECT ID FROM Student WHERE Age>=21 AND Age<= 23; 설명: Student 테이블에서 나이가 21세에서 23세 사이에 속하는 학생들의 학번만 가져온다. 예4: SELECT ID, Name FROM Student ORDER BY ID Asc; 설명: Student 테이블에서 학번과 이름 자료를 가져오는데 학번에 의한 오름차순으로 정렬하여 가져온다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 조작 기능 SQL 테이블에 새로운 자료를 삽입 테이블에 있는 레코드 삭제 INSERT INTO table_name [(column_list)] VALUES (column_values[, ... ]); 예: INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910001', '김송이', 20, 'computer science'); 설명: Student 테이블에 학번(ID)은 ‘200010001’이고 이름이 ‘김송이’ 이며 나이가 20살이고 학과는 컴퓨터학과인 학생에 대한 정보를 삽입한다. DELETE table_name [WHERE condition...]; 예1: DELETE Student; 설명: Student 테이블에 저장되어있는 모든 내용을 삭제한다. 테이블의 내용만 삭제할 뿐 테이블 자체까지 삭제되지는 않는다. 예2: DELETE Student WHERE Name='김송이'; 설명: Student 테이블에서 '김송이'라는 이름을 가진 레코드를 삭제한다. 제13장

3. 데이터베이스와 SQL SQL의 명령문과 문법 데이터 조작 기능 SQL 테이블에 이미 입력되어있는 값을 갱신 UPDATE table_name SET column_name=value [WHERE condition...]; 예: UPDATE Student SET Age=25 WHERE ID='200910003'; 설명: Student 테이블에서 학번이 '200910003'인 학생의 나이를 25살로 갱신한다. 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 1) Connection 객체를 생성한다. 2) Statement 객체를 생성한다. 3) SQL 질의를 수행한다. 4) 데이터를 얻어오는 것과 관련된 경우 데이터를 ResultSet으로부터 가져온다. 만약 SQL문이 데이터 추출과 무관한 경우는 ResultSet에서의 데이터 추출은 생략된다. (e.g., CREATE, INSERT, UPDATE, DELETE 등) 5) 생성되어 있는 ResultSet 객체 및 Statement 객체를 닫는다. 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 1. Connection 객체 얻어오기 13.2.5절에서 소개한 thinkonweb.util.ConnectionContext를 활용하여 커넥션풀에서 java.sql.Connection 객체를 받아오는 방법을 사용 <%@ page contentType="text/html; charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> ... <% Connection conn = ConnectionContext.getConnection(); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 2. Statement 객체 생성 및 SQL 질의 수행 java.sql.Statement 객체 SQL 구문을 입력받아 질의를 수행하고 결과값을 가져올 수 있는 작업을 지원 Connection객체의 createStatement() 메소드를 이용해서 얻는다. Statement 객체를 얻으면 이 객체를 활용하여 직접 SQL 문을 수행시킬 수 있다. <%@ page contentType="text/html; charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> ... <% Connection conn = ConnectionContext.getConnection(); Statement stmt = conn.createStatement(); int rowCount = stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910001', '김송이', 20, 'computer science');"); ResultSet rs = stmt.executeQuery("SELECT * FROM Student"); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 3. Statement 객체 생성 및 SQL 질의 수행 java.sql.Statement 객체 이 객체에게 호출할 수 있는 메소드 종류 http://download.oracle.com/javase/6/docs/api/index.html 참조 메소드 리턴 타입 설명 executeQuery(String sql) java.sql.ResultSet SELECT 로 시작되는 SQL 구문처럼 데이터를 추출하기 위한 SQL 구문을 실행시키다. 수행 결과로서 추출 데이터를 간직하고 있는 ResultSet을 리턴한다. executeUpdate(String sql) int INSERT, UPDATE, DELETE로 시작되는 SQL 구문처럼 데이터 추출이 없는 SQL 구문을 실행시킨다. 수행 결과로서 SQL 구문에 의하여 영향을 받은 레코드 행 개수를 int형으로 리턴한다. 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 4. ResultSet에서의 데이터 추출 java.sql. ResultSet 객체 ResultSet 객체에 데이터를 가져온 후 이 객체로부터 실제 데이터를 추출해 내는 작업 수행 ResultSet 객체 구조 및 커서 이동 한번의 next() 메소드 호출은 레코드 행을 한 행씩 이동하게 해준다. 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet에서의 데이터 추출 java.sql. ResultSet 객체 next() 메소드의 반환값은 Boolean 형 java.sql. ResultSet 객체에 호출할 수 있는 메소드 getString(String columnName) getInt(String columnName) getFloat(String columnName) getLong(String columnName) getDate(String columnName) http://download.oracle.com/javase/6/docs/api/index.html 참조 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet에서의 데이터 추출 java.sql. ResultSet 객체 사용 법 <%@ page contentType="text/html; charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> ... <% Connection conn = ConnectionContext.getConnection(); Statement stmt = conn.createStatement(); int rowCount = stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910001', '김송이', 20, 'computer science');"); ResultSet rs = stmt.executeQuery("SELECT * FROM Student"); String id, name, major; int age; while(rs.next()){ id = rs.getString("ID"); name = rs.getString("Name"); age = rs.getInt("Age"); major = rs.getString("Major"); out.println(id + ": " + name + ", " + age + ", " + major + "</br>"); } %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 5. ResultSet 객체 및 Statement 객체 닫기 데이터베이스 작업이 끝나면 자원을 회수하고 가용자원을 늘리기 위해서 ResultSet 객체 및 Statement 객체를 닫아주어야 한다. <%@ page contentType="text/html; charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> ... <% … while(rs.next()){ id = rs.getString("ID"); name = rs.getString("Name"); age = rs.getInt("Age"); major = rs.getString("Major"); out.println(id + ": " + name + ", " + age + ", " + major + "</br>"); } rs.close(); stmt.close(); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 데이터베이스 활용 예제 [예제 13.6] jspbook\ch13\student-1.jsp 01 02 03 04 05 06 07 08 09 10 - 11 12 13 14 15 <%@ page contentType="text/html;charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> <html> <head><title>JDBC 예제-1</title></head> <body> <% Connection conn = ConnectionContext.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) AS recordCount FROM Student WHERE ID=200910001"); // ID가 200910001인 레코드 존재 확인 while(rs.next()){ int recordCount = rs.getInt("recordCount"); if (recordCount != 1) { // ID가 200910001인 레코드가 존재하지 않으면 true stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910001', '김송이', 20, 'computer science');"); //executeUpdate 질의의 리턴값은 일반적으로 사용하지 않음 stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910002', '김하늘', 20, 'computer science');"); 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 데이터베이스 활용 예제 [예제 13.6] jspbook\ch13\student-1.jsp 16 - 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910003', '오청하', 21, 'computer science');"); } rs = stmt.executeQuery("SELECT * FROM Student"); // SELECT 쿼리문 수행 String id, name, major; int age; while(rs.next()){ id = rs.getString("ID"); name = rs.getString("Name"); age = rs.getInt("Age"); major = rs.getString("Major"); out.println(id + ": " + name + ", " + age + ", " + major + "</br>"); out.println("</br>"); %> </body> </html> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 메타데이터(metadata) 일반적으로 "데이터에 관한 데이터"라고 정의되며 SQL 질의 수행 이후에 얻어온 정보자원의 속성을 기술하는 데이터 실제 얻고자 하는 학생 이름, 나이 등의 데이터는 아니지만 이 데이터와 직접적으로 혹은 간접적으로 연관된 정보를 제공하는 데이터 메타데이터 사용 이유 데이터를 소유하고 있는 측면에서는 관리의 용이성을, 데이터를 사용하고 있는 측면에서는 검색의 용이성 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 메타데이터(metadata) ResultSet 객체로부터 getMetaData() 라는 메소드를 통해서 ResultSetMetaData 객체를 얻어올 수 있다. http://download.oracle.com/javase/6/docs/api/index.html 참조 <% ... ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); for (int i=1; i<=numColumns; i++) { out.println("Column Number = " + i); out.println("Column Label = " + md.getColumnLabel(i)); out.println("Column Name = " + md.getColumnName(i)); out.println("Column Type = " + md.getColumnTypeName(i)); } %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 ResultSetMetaData 객체에 호출할 수 있는 메소드 메소드 리턴 타입 설명 getColumnCount() int ResultSet 객체에 있는 데이터 레코드에 대한 컬럼(필드) 개수를 리턴한다. getColumnLabel(int column) String 인자로 주어진 column 인덱스에 대한 필드 레이블을 리턴한다. 주의할 점은 첫 번째 column 인덱스는 1부터 시작한다. getColumnName(int column) 인자로 주어진 column 인덱스에 대한 필드이름을 리턴한다. 주의할 점은 첫 번째 column 인덱스는 1부터 시작한다. getColumnTypeName(int column) 인자로 주어진 column 인덱스에 대한 필드형(type)을 리턴한다. 주의할 점은 첫 번째 column 인덱스는 1부터 시작한다. 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 getColumnLabel(int column)과 getColumnName(int column)의 차이 위 예와 같이 AS 절을 사용하면 getColumnLabel(int column)이 돌려주는 값은 AS 절 이후에 지정한 단어가 리턴된다. AS 절을 사용하지 않으면 getColumnLabel(int column)과 getColumnName(int column)가 리턴하는 값에 차이가 없다. 주로 ResultSetMetaData 객체와 함께 사용하면서 ResultSet 객체에 호출할 수 있는 메소드 getString(int columnIndex) getInt(int columnIndex) getFloat(int columnIndex) getLong(int columnIndex) getDate(int columnIndex) SELECT count(*) AS recordCount FROM Student 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 ResultSetMetaData 객체와 함께 사용하면서 ResultSet 객체를 활용하는 방법 <% ... ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); while (rs.next()) { String rowBuffer = ""; for (int i=1; i<=numColumns; i++) { if (i == 3) rowBuffer += rowBuffer + " " + rs.getInt(i); else rowBuffer += rowBuffer + " " + rs.getString(i); } out.println(rowBuffer); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 예제 [예제 13.7] jspbook\ch13\student-2.jsp 01 02 03 04 05 06 07 08 09 10 - 11 12 13 14 15 16 17 18 19 20 21 22 <%@ page contentType="text/html;charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> <html> <head><title>JDBC 예제</title></head> <body> <% Connection conn = ConnectionContext.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) AS recordCount FROM Student WHERE ID=200910001"); // ID가 200910001인 레코드 존재 확인 while(rs.next()){ int recordCount = rs.getInt("recordCount"); if (recordCount != 1) { // ID가 200910001인 레코드 존재하지 않으면 true, executeUpdate 질의의 리턴값은 필요없음 stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910001', '김송이', 20, 'computer science');"); stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910002', '김하늘', 20, 'computer science');"); stmt.executeUpdate("INSERT INTO Student (ID, Name, Age, Major) VALUES ('200910003', '오청하', 21, 'computer science');"); } rs = stmt.executeQuery("SELECT ID AS S_ID, Name AS S_Name, Age AS S_Age, Major AS S_Major FROM Student"); // SELECT 쿼리수행 ResultSetMetaData md = rs.getMetaData(); // 결과데이터에 대한 메타데이터 생성 int numColumns = md.getColumnCount(); 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 ResultSet 메타데이터 활용하기 예제 [예제 13.7] jspbook\ch13\student-2.jsp 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 for (int i=1; i<=numColumns; i++) { out.println("Column Number = " + i + "</br>"); out.println("Column Label = " + md.getColumnLabel(i) + "</br>"); out.println("Column Name = " + md.getColumnName(i) + "</br>"); out.println("Column Type = " + md.getColumnTypeName(i) + "</br></br>"); } rs = stmt.executeQuery("SELECT * FROM Student WHERE age=20"); md = rs.getMetaData(); // age가 20인 레코드들을 얻어온다. numColumns = md.getColumnCount(); while (rs.next()) { String rowBuffer = ""; if (i == 3) rowBuffer += rs.getInt(i) + " "; else rowBuffer += rs.getString(i) + " "; out.println(rowBuffer + "</br>"); rs.close(); stmt.close(); %> </body> </html> // 컬럼의 메타정보 출력 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 PreparedStatement 활용하기 java.sql.PreparedStatement 클래스 java.sql.Statement 클래스를 확장한 것으로서 SQL 문을 미리 컴파일하여 기존의 java.sql.Statement 객체 보다 더욱 효율적으로 SQL 질의문을 처리하도록 지원 PreparedStatement 객체 Connection 객체의 prepareStatement() 메소드를 통해서 얻을 수 있다. 추가적으로 입력 파라미터의 위치를 지정할 수 있도록 prepareStatement() 메소드에 넣어주는 SQL 문에 물음표(?)를 사용할 수 있다. <% ... Connection conn = ConnectionContext.getConnection(); PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Student WHERE Age=? AND Major=?"); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 PreparedStatement 활용하기 PreparedStatement 객체 PreparedStatement 객체에게 setString(), setInt(), setDate() 등의 메소드를 다음과 같이 호출하여 물음표(?)가 있는 위치에 실제 파라미터 값을 넣어줄 수 있다. 파라미터 값을 모두 셋팅하면 바로 pstmt 객체에게 executeQuery() 메소드를 호출하여 ResultSet 객체를 얻어올 수 있다. <% ... Connection conn = ConnectionContext.getConnection(); PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Student WHERE Age=? AND Major=?"); pstmt.setInt(1, 20); pstmt.setString(2, "computer science"); ResultSet rs = pstmt.executeQuery(); rs.close(); pstmt.close(); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 PreparedStatement 활용하기 PreparedStatement 객체 INSERT 구문을 처리하는 PreparedStatement 객체 활용 법 <% ... Connection conn = ConnectionContext.getConnection(); PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Student (ID, Name, Age, Major) VALUES (?, ?, ?, ?);"); pstmt.setString(1, "200910004"); pstmt.setString(2, "김철수"); pstmt.setInt(3, 23); pstmt.setString(4, "Electronic Engineering"); pstmt.executeUpdate(); pstmt.close(); %> 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 PreparedStatement 활용하기 예제 [예제 13.8] jspbook\ch13\student-3.jsp 01 02 03 04 05 06 07 08 09 10 11 - 12 13 14 15 16 17 18 19 20 21 22 23 <%@ page contentType="text/html;charset=utf-8" %> <%@ page import="thinkonweb.util.ConnectionContext" %> <%@ page import="java.sql.*" %> <html> <head><title>JDBC 예제</title></head> <body> <h3>PreparedStatement 활용하기</h3> <% Connection conn = ConnectionContext.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) AS recordCount FROM Student WHERE ID=200910004"); // ID가 200910001인 레코드 존재 확인 while(rs.next()){ int recordCount = rs.getInt("recordCount"); if (recordCount != 1) { // ID가 200910001인 레코드가 존재하지 않으면 true PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Student (ID, Name, Age, Major) VALUES (?, ?, ?, ?);"); // INSERT 질의 지닌 PreparedStatement 객체 생성 pstmt.setString(1, "200910004"); pstmt.setString(2, "김철수"); pstmt.setInt(3, 23); pstmt.setString(4, "Electronic Engineering"); pstmt.executeUpdate(); // PreparedStatement 객체의 질의 수행 pstmt.close(); } // PreparedStatement 객체의 질의 내용 채움 제13장

4. JSP 및 Servlet에서 SQL 처리 프로그래밍 PreparedStatement 활용하기 예제 [예제 13.8] jspbook\ch13\student-3.jsp 24 25 - 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Student WHERE Age=? AND Major=?"); // SELECT 질의를 지닌 PreparedStatement 객체 생성 pstmt.setInt(1, 23); // PreparedStatement 객체의 질의 내용 채움 pstmt.setString(2, "Electronic Engineering"); rs = pstmt.executeQuery(); // PreparedStatement 객체의 질의 수행 String id, name, major; int age; while(rs.next()){ id = rs.getString("ID"); name = rs.getString("Name"); age = rs.getInt("Age"); major = rs.getString("Major"); out.println(id + ": " + name + ", " + age + ", " + major + "</br>"); } rs.close(); pstmt.close(); stmt.close(); %> </body> </html> 제13장