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();