Transcript Chap_11

11장 데이터베이스와 JSP의 연동
이장에서 배울 내용 : JSP페이지와 데이터베이스와의 연동을
위한 데이터베이스 연결 기술인 JDBC의 개념과 JSP페이지에
서 JDBC를 사용하여 데이터베이스를 연동한 웹 어플리케이션
을 작성을 학습한다. 또한 DBCP API를 사용한 커넥션 풀도 설
정한다.
김은옥([email protected])
목차
데이터베이스의 개요 및 설치
 이클립스에서 [Data Source Explorer]뷰를 사용
한 데이터베이스 직접 제어
 SQL(Structured Query Language) 쿼리의 개요
 JDBC를 사용한 JSP와 데이터베이스의 연동
 자카르타 DBCP API를 이용한 커넥션 풀
(connection pools) 설정

데이터베이스의 개요 및 설치

데이터베이스와 DBMS(Database
Management System)
 데이터베이스
 데이터의
효율적인 관리를 목적으로 하는 데이터의 집
합으로, 데이터를 지속적으로 관리하는 것이 목적
 DBMS
 데이터를
안정적으로 보관할 수 있는 다양한 기능을 제
공.
 주요한 기능: 데이터의 삽입/수정/삭제, 데이터의 무결
성 유지, 트랜잭션관리, 데이터의 백업 및 복원, 데이터
보안기능.
데이터베이스의 개요 및 설치

MySQL 및 MySQL드라이버 설치
 MySQL
다운로드 및 설치
 DBMS
 http://dev.mysql.com/downloads/
사이트에서 다운로드
받아 설치
 MySQL
드라이버 다운로드 및 설치
 MySQL과
프로그래밍과 연동시 필요
 http://dev.mysql.com/downloads/에서 다운로드 받아
설치
데이터베이스의 개요 및 설치

MySQL 및 MySQL드라이버 설치
 이클립스
프로젝트에 MySQL 커넥터 드라이버 연
결
 MySQL
커넥터 드라이버는 JDBC를 사용한 DB연동이 필
요한 프로젝트에서 설정


각, 프로젝트마다 다른 DBMS를 사용할 수 있게 하기 위한 것
동적 웹 프로젝트에서 DB연동을 할 때, 해당드라이버
는 반드시[WebContent]-[WEB-INF]-[lib]폴더에 위치
데이터베이스의 개요 및 설치

MySQL 및 MySQL드라이버 설치
 MySQL에
데이터베이스 추가 (데이터베이스명:
basicjsp)
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqladmin -u root -p create
basicjsp
데이터베이스의 개요 및 설치

MySQL 및 MySQL드라이버 설치
 생성된
데이터베이스에 사용자계정 추가 및 권한
설정
 로컬호스트(localhost)에
접근할 수 있는 권한
grant select, insert, update, delete, create, drop, alter
on basicjsp.* to 'jspid'@'localhost'
identified by 'jsppass';
 모든
서버(%)에 접근할 수 있는 권한
grant select, insert, update, delete, create, drop, alter
on basicjsp.* to 'jspid'@'%'
identified by 'jsppass';
이클립스에서 [DATA SOURCE EXPLORER]뷰를
사용한 데이터베이스 직접 제어

[Data Source Explorer]뷰에서 데이터베이스 커넥
션 설정
① 데이터베이스 커넥션을 설정하기 위해 이클립스창
의 아래에 위치한 [Data Source Explorer]뷰를 선택.
 ② [Data Source Explorer]뷰의 내용이 표시되면
[Database Connections]항목을 선택 후 마우스 오른쪽
버튼을 눌러 [New...]메뉴를 선택
 ③[New Connection Profile]창이 표시되면, [Connection
Profile Type]항목에서 [MySQL]을 선택하고 [Name]항목
에 "mysqlconn"을 입력 후 [Next]버튼을 클릭

이클립스에서 [DATA SOURCE EXPLORER]뷰를
사용한 데이터베이스 직접 제어
[Specify a Driver and Connection Details]화면이
표시되면, [Drivers]항목의 [New Driver Definition]버
튼을 클릭
 ⑤[New Driver Definition]창이 표시되면, 화면의
[Name/Type], [JAR List], [Properties]탭에 각각 필요
한 설정을 차례로 지정 후 [Finish]버튼 클릭
④
이클립스에서 [DATA SOURCE EXPLORER]뷰를
사용한 데이터베이스 직접 제어

[Data Source Explorer]뷰에서 설정된 데이터베
이스 커넥션을 사용한 데이터베이스 제어
 쿼리문을
작성할 때는 스크랩북(scrapbook)을 생
성해서 함
 ① [Data Source Explorer]뷰에서
[mysqlconn(MySQL v.5.5.30)]을 선택 후 마우스 오
른쪽 버튼을 클릭해 [Open SQL Scrapbook]메뉴를
선택
이클립스에서 [DATA SOURCE EXPLORER]뷰를
사용한 데이터베이스 직접 제어
새 스크랩 북이 뷰에 표시되면 [Connection
profile]항목에 설정
②
 [Type]항목의
콤보상자를 사용해 [MySql_5.1]을 선택한
후, [Name]항목에서 [mysqlconn]을 선택하고 [Database]
항목에서 [basicjsp]를 선택
SQL구문을 입력 후 마우스로 구문을 드래그해
블록을 지정.
 ④ 지정한 블록에서 마우스 오른쪽 버튼을 클릭해
[Execute Selected Text]메뉴를 선택 또는 [Alt] +[x]
를 눌러 실행
③
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요
SQL은 Structured Query Language의 약자로 구
조화된 질의 언어.
 데이터베이스 생성부터 레코드 검색 등의 작
업을 수행할 때 사용.
 SQL문은 크게 데이터 정의문(Data Definition
Language, DDL), 제어문, 조작문(Data Control
Language), 쿼리(Query), 트랜잭션(Transaction)
처리

SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요
정의문(DDL)- CREATE, ALTER, DROP
 데이터 제어문(DCL) - GRANT, REVOKE
 데이터 조작문(DML) - SELECT, INSERT, DELETE,
 쿼리(Query) - SELECT
 트랜잭션(Transaction) 처리 - COMMIT, ROLLBACK
 데이터
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요

데이터 타입(Data Type)
숫자 타입
데이터 타입
저장 공간
표현 범위
TINYINT
1 byte
-128~127
UNSIGNED 0~255
SMALLINT
2 bytes
-32768~32767
UNSIGNED 0~65535
MEDIUMINT
3 bytes
-8388608~8388607
UNSIGNED 0~16777215
INT, INTEGER
4 bytes
-2147483648~2147483647
UNSIGNED 0~4294967295
8 bytes
-9223372036854775808~9223372036
854775807
UNSIGNED 18446744073709551615
BIGINT
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요

데이터 타입(Data Type)
날짜 및 시간 타입
데이터 타입
저장 공간
DATE
3 bytes
DATETIME
8 bytes
TIMESTAMP
4 bytes
문자열 타입
데이터 타입
저장 공간(저장 문자의 개수)
CHAR
1~255
VARCHAR
1~255
BLOB(Binary Large Object),
TEXT
1~65535
MEDIUMBLOB, MEDIUMTEX
T
1~1677215
LONGBLOB, LONGTEXT
1~4294967295
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요

테이블 생성 및 제거
 테이블
생성- CREATE문
CREATE TABLE table_name (
col_name1 type [PRIMARY KEY] [NOT NULL/NULL],
col_name2 type,
....
col_name3 type );
 테이블
제거- DROP문
DROP TABLE table_name;
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요

레코드 처리 작업
 레코드
추가 - Insert문
INSERT INTO table_name (col_name1,col_name2...)
VALUES (col_value1, col_value2...)
 레코드
검색 - SELECT문
SELECT col_name1,col_name2
FROM table_name;
SQL(STRUCTURED QUERY LANGUAGE) 쿼리의 개
요

레코드 처리 작업

레코드 수정 - UPDATE문
UPDATE table_name SET col_name = value,.....
WHERE condition;

레코드 삭제 - DELETE문
DELETE FROM table_name
WHERE condition;
JDBC를 사용한 JSP와 데이터베이스의 연동
JSP에서 데이터베이스를 사용하는 프로그래
밍 하려면, JDBC(Java Database Connectivity)를
사용해 데이터베이스와 연동
 JDBC(Java Database Connectivity)

 자바
프로그램(JSP포함)과 관계형 데이터 원본(데
이터베이스, 테이블...)을 연결하는 인터페이스
 JDBC 라이브러리는 ‘java.sql’ 패키지에의 구현
JDBC를 사용한 JSP와 데이터베이스의 연동
JDBC를 사용한 JSP와 데이터베이스의 연동
 JDBC프로그램의 작성 단계

 1단계(JDBC
드라이버 Load) : 인터페이스 드라이
버(interface driver)를 구현(implements)하는 작업
으로, Class 클래스의 forName() 메소드를 사용해
서 드라이버를 로드.
 MySQL
드라이버 로딩
Class.forName ("com.mysql.jdbc.Driver");
 Oracle 10g또는 11g thin 드라이버 로딩
Class.forName ("oracle.jdbc.driver.OracleDriver");
JDBC를 사용한 JSP와 데이터베이스의 연동
 2단계(Connection
객체 생성) : Connection 객체를
연결하는 것으로 DriverManager에 등록된 각 드라
이버들을 getConnection(String url)메소드를 사용
해서 식별.
 MySQL
사용시 Connection 객체 생성
Connection conn= DriverManage.getConnection
("jdbc:mysql://localhost:3306/basicjsp","jspid","jsppass");
 Oracle 사용시 Connection 객체 생성
Connection conn= DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
JDBC를 사용한 JSP와 데이터베이스의 연동
 3단계
(Statement/PrepardStatement/CallableStatement
객체 생성) : sql쿼리를 생성하며, 반환된 결과를 가
져오게 할 작업영역을 제공.
 3단계부터는
JDBC드라이버에 구애 받지 않음.
Statement stmt = con.createStatement();
JDBC를 사용한 JSP와 데이터베이스의 연동
 4단계(Query
수행) :
Statement/PrepardStatement/CallableStatement
객체가 생성되면, 객체의 executeQuery()메소드나
executeUpdate()메소드를 사용해서 쿼리를 실행
 stmt.executeQuery()
:recordSet 반환 => Select 문에서 사
용
ResultSet rs = stmt.executeQuery ("select * from 소속기관");
 stmt.executeUpdate():
성공한row 수 반환 => Insert 문,
Update 문, Delete 문에서 사용
String sql="update member set passwd='3579' where id='abc'";
stmt.executeUpdate(sql);
JDBC를 사용한 JSP와 데이터베이스의 연동
 5단계(ResultSet
처리) : executeQuery()메소드는 수
행 결과로 ResultSet을 반환.
 ResultSet
객체로부터 원하는 데이터를 추출.
 데이터를 추출하는 방법은 ResultSet 객체에서 next()메
소드를 사용해 한 행씩 이동하면서 getXxx()를 이용해서
원하는 필드 값을 추출.
 문자열 데이터를 갖는 필드는 rs.getString("name") 혹은
rs.getString(1) 로 사용.
ResultSet의 첫 번째 필드는 1부터 시작.
 rs.getString("name")과 같이 필드명을 사용하는 것이 권장 형태.

JDBC를 사용한 JSP와 데이터베이스의 연동

JDBC프로그래밍에 사용되는 객체
 DriverManager
클래스 : 커넥션을 만드는 역할
 Class.forName()
메소드를 사용해서 생성
 Class.forName("com.mysql.jdbc.Driver")메소드의 매개변
수로 "com.mysql.jdbc.Driver"과 같은 특정 드라이버 클래
스를 지정
JDBC를 사용한 JSP와 데이터베이스의 연동
 Connection
인터페이스 : 특정 데이터 원본에 대한
커넥션은, Connection인터페이스가 구현된 클래스
의 객체로 표현.
 DriverManger클래스의
getConnection()메소드를 사용해,
Connection객체 conn을 얻어냄
Connection conn = DriverManger.getConnection(
"jdbc:mysql://localhost:3306/basicjsp","jspid","jsppass");
JDBC를 사용한 JSP와 데이터베이스의 연동
 Statement
 쿼리를
인터페이스
실행 시 사용
 처리 속도가 느려 현재 거의 사용 안 함
JDBC를 사용한 JSP와 데이터베이스의 연동
 PreparedStatement
인터페이스
 쿼리를
실행 시 사용
 선 컴파일 되기 때문에 속도 빠름
 Connection 객체의 prepareStatement()메소드를 사용해
서 객체를 생성
 prepareStatement()메소드 SQLException예외를 발생시
키기 때문에, 반드시 예외 처리
try {
PreparedStatement pstmt=conn.prepareStatement(sql);
}catch(SQLException e) {
e.printStackTrace();
}
JDBC를 사용한 JSP와 데이터베이스의 연동
 PreparedStatement
인터페이스
 각각의
인수에 대해 위치홀더(placeholder)를 사용하여
SQL문장을 정의할 수 있게 해줌. 위치홀더는 물음표(?)
로 표현되며 실행시간 동안 인수 값을 위한 공간을 확보
하는 역할
try {
String sql= "insert into member values (?,?,?,?)";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1,id);
pstmt.setString(2,passwd);
생략...
}catch(SQLException e) {
e.printStackTrace();
}
JDBC를 사용한 JSP와 데이터베이스의 연동
 PreparedStatement
인터페이스
 PreparedStatement
객체는 각각의 SQL 데이터 타입을
처리할 수 있는 setXxx()메소드를 제공.
여기서 Xxx는 해당테이블의 해당필드의 데이터 타입과 관련.
 해당필드의 데이터 타입이 문자열이면 setString()이 되고, 해당
필드의 데이터 타입이 int이면 setInt()가 된다.

 setXxx(num,

num: 파라미터 인덱스로서 위치홀더(?)와 대응.


var)메소드는 두개의 매개변수를 가짐.
첫 번째 위치홀더(?)에 대응되면 1이고, 다음 위치홀더(?)와 대응
부터 1씩 값을 증가.
var: 해당필드에 저장할 데이터 값을 기술. 변수를 사용해도 됨.
JDBC를 사용한 JSP와 데이터베이스의 연동
 CallableStatement
 Connection
인터페이스
객체의 prepareCall()메소드를 사용해서 객
체를 생성.
 prepareCall()메소드 SQLException예외를 발생시키기 때
문에, 반드시 예외 처리.
 스토어드 프로시저 (Stored Procedure)를 사용하기 위해
사용
try {
CallableStatement cstmt= connection. prepareCall()
}catch(SQLException e) {
e.printStackTrace();
}
JDBC를 사용한 JSP와 데이터베이스의 연동
 ResultSet
인터페이스
 Select문을
사용한 쿼리문은 executeQuery()메소드를 사
용하는데, 이 메소드의 수행이 성공 시, 결과물로
ResultSet객체가 반환.
 ResultSet은 쿼리의 결과로 생성된 테이블(레코드셋, 레
코드들)을 가짐.
JDBC를 사용한 JSP와 데이터베이스의 연동
 ResultSet
인터페이스
 ResultSet객체는
‘커서(cursor)'라 불리는 것을 가지고 있
는데, 이것을 사용해 ResultSet 객체에서 특정 레코드를
참조.
 커서는 초기에 첫 번째 레코드(행)의 직전 즉, 필드명이
위치한 곳을 가리킴
JDBC를 사용한 JSP와 데이터베이스의 연동
 ResultSet
인터페이스
필드 값을 얻어내기 위해 ResultSet객체
는 getXxx()메소드를 제공.
 레코드셋에서
이때 Xxx는 해당필드의 데이터타입이 결정.
 해당필드의 데이터 타입이 문자열이면 getString()이 되고, 해당
필드의 데이터 타입이 int이면 getInt()가 됨.

자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정

Connection 객체는 새롭게 만들어질 때 많은
시스템 자원을 요구.
 이런
문제를 해결하기 위해 커넥션 풀에 커넥션 객
체들을 만들어 놓은 후, 커넥션 객체가 필요한 경
우 작성한 객체를 할당해 주고, 사용이 끝난 후에
는 다시 커넥션 풀로 회수 하는 방법을 사용.
 한번 만들어져서 사용된 커넥션 객체는 다시 커넥
션 풀(connection pools)로 회수.
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
자카르타 프로젝트의 DBCP API를 사용해서 커
넥션 풀을 사용하기 위한 순서
① DBCP API관련 jar파일 설치
② DBCP에 관한 정보 설정 - server.xml
③ JNDI 리소스 사용 설정 - web.xml
④ JSP페이지에서 커넥션 풀 사용

자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
① DBCP API관련 jar파일 설치
해제된 폴더에서 각각 commonscollections-3.2.1.jar, commons-dbcp-1.4.jar ,
commons-pool-1.6.jar파일을 복사해 공용 라이브
러리 폴더인 톰캣홈\lib 폴더 및 이클립스의 [프로
젝트]-[WebContent]-[WEB-INF]-[lib]폴더에 복사.
 JDBC 커넥터인 mysql-connector-java-5.1.23-bin.jar
파일을 복사해서 톰캣홈\common\lib 폴더에 복사
 압축이
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
② DBCP에 관한 정보 설정 - server.xml
 실제
서비스 환경인 톰캣홈\conf안에 있는
server.xml과 이클립스 가상환경의 [Servers][Tomcat v7.0 Server ~]안에 있는 server.xml에 정보
설정.
 <Resource>엘리먼트를
정의해
<GlobalNamingResources>엘리먼트와 <Context>엘리먼
트의 하위에 엘리먼트로 추가
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
② DBCP에 관한 정보 설정 - server.xml
<Resource name="jdbc/basicjsp"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
username="jspid"
password="jsppass"
url="jdbc:mysql://localhost:3306/basicjsp"
maxWait="5000"
/>
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
③ JNDI 리소스 사용 설정 - web.xml
 server.xml에
저장된 JNDI 리소스를 사용하려면
web.xml에 다음과 같이 <resource-ref>엘리먼트를
기술
<resource-ref>
<description>basicjsp db</description>
<res-ref-name>jdbc/basicjsp</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
④ JSP페이지에서 커넥션 풀 사용
 InitialContex객체를
 Context

생성
initCtx = new InitialContext();
(Context) initCtx.lookup("java:comp/env")에서 “”안
에 기술된 이름을 lookup()메소드를 사용해서 찾음
 Context
envCtx =
(Context) initCtx.lookup("java:comp/env");
자카르타 DBCP API를 이용한 커넥션 풀
(CONNECTION POOLS) 설정
④ JSP페이지에서 커넥션 풀 사용
 "java:comp/env"이름으로
찾아낸 Context객체를 가
지고 (DataSource)envCtx.lookup("jdbc/basicjsp");를
사용해 "jdbc/basicjsp"를 가지고 객체를 얻어내서
DataSource객체 타입으로 형 변환.
 DataSource
ds =
(DataSource)envCtx.lookup("jdbc/basicjsp");

ds.getConnection()메소드를사용해서 커넥션 풀로
부터 커넥션 객체를 할당 받음.
 Connection
conn = ds.getConnection();