11장. 데이터베이스와 JSP의 연동 이 장에서 배울 내용 : JSP페이지와 데이터베이스와의 연동을 위한 데이터베이스 연결 기술인 JDBC의 개념과 JSP페이지에서 JDBC를 사용하여 데이터베이스를 연동한 웹 애플리케이션을 작성을 학습한다. 또한 DBCP API를 사용한 커넥션 풀도 설정한다. 김은옥(oda94@naver.com)

목차 데이터베이스의 개요 및 설치 이클립스에서 [Data Source Explorer]뷰를 사용한 데이터베이스 직접 제어 SQL(Structured Query Language) 쿼리의 개요 JDBC를 사용한 JSP와 데이터베이스의 연동 자카르타 DBCP API를 이용한 커넥션 풀(connection pools) 설정

데이터베이스의 개요 및 설치 데이터베이스와 DBMS(Database Management System) 데이터베이스 DBMS 데이터의 효율적인 관리를 목적으로 하는 데이터의 집합으로, 데이터를 지속적으로 관리하는 것이 목적 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]뷰의 내용이 표시되면 [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]뷰를 사용한 데이터베이스 직접 제어 쿼리문을 작성할 때는 스크랩북(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문은 크게 데이터 정의문(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 BIGINT 8 bytes -9223372036854775808~9223372036854775807 UNSIGNED 18446744073709551615

SQL(Structured Query Language) 쿼리의 개요 데이터 타입(Data Type) 날짜 및 시간 타입 데이터 타입 저장 공간 DATE 3 bytes DATETIME 8 bytes TIMESTAMP 4 bytes 문자열 타입 저장 공간(저장 문자의 개수) CHAR 1~255 VARCHAR BLOB(Binary Large Object), TEXT 1~65535 MEDIUMBLOB, MEDIUMTEXT 1~1677215 LONGBLOB, LONGTEXT 1~4294967295

SQL(Structured Query Language) 쿼리의 개요 테이블 생성 및 제거 테이블 생성- CREATE문 테이블 제거- DROP문 CREATE TABLE table_name ( col_name1 type [PRIMARY KEY] [NOT NULL/NULL], col_name2 type, .... col_name3 type ); DROP TABLE table_name;

SQL(Structured Query Language) 쿼리의 개요 레코드 처리 작업 레코드 추가 - Insert문 레코드 검색 - SELECT문 INSERT INTO table_name (col_name1,col_name2...) VALUES (col_value1, col_value2...) SELECT col_name1,col_name2 FROM table_name;

SQL(Structured Query Language) 쿼리의 개요 레코드 처리 작업 레코드 수정 - UPDATE문 레코드 삭제 - DELETE문 UPDATE table_name SET col_name = value,..... WHERE condition; DELETE FROM table_name WHERE condition;

JDBC를 사용한 JSP와데이터베이스의 연동 JSP에서 데이터베이스를 사용하는 프로그래밍 하려면, JDBC(Java Database Connectivity)를 사용해 데이터베이스와 연동 JDBC(Java Database Connectivity) 자바 프로그램(JSP포함)과 관계형 데이터 원본(데이터베이스, 테이블...)을 연결하는 인터페이스 JDBC 라이브러리는 ‘java.sql’ 패키지에의 구현

JDBC를 사용한 JSP와데이터베이스의 연동 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 문에서 사용 stmt.executeUpdate(): 성공한row 수 반환 => Insert 문, Update 문, Delete 문에서 사용 ResultSet rs = stmt.executeQuery ("select * from 소속기관"); 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와데이터베이스의 연동 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, var)메소드는 두개의 매개변수를 가짐. num: 파라미터 인덱스로서 위치홀더(?)와 대응. 첫 번째 위치홀더(?)에 대응되면 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 pools)로 회수.

자카르타 DBCP API를 이용한 커넥션 풀(connection pools) 설정 ① DBCP API관련 jar파일 설치 ② DBCP에 관한 정보 설정 - server.xml ③ JNDI 리소스 사용 설정 - web.xml ④ JSP페이지에서 커넥션 풀 사용

자카르타 DBCP API를 이용한 커넥션 풀(connection pools) 설정 ① DBCP API관련 jar파일 설치 압축이 해제된 폴더에서 각각 commons-collections-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();