Preface
이번 장에서는 JSP와 DB를 연결하는 방법을 공부했다.
나는 지금껏 MySQL DB를 이용했었는데, 책에선 Oracle을 다뤄 어쩔 수 없이 OracleDB를 다운로드받았다.
다만, 맥에선 Oracle을 지원하지 않아 Docker을 이용해 다운로드 받는 방법을 이용했다.
추후 개인적으로 JSP와 DB를 연동할 수 있으려면 커넥션 풀 등과 같은 다양한 기능의 연결 및 사용 방법을 잘 숙지해두어야 할 듯하다.
또, 현재 OracleDB에서 한글이 깨지는 현상이 발생해 해결 중인데, 해결 방법을 찾으면 따로 글을 업로드 할 계획이다.
1. 데이터베이스란?
- DB와 관련된 정보는 DB & SQL 카테고리에 있다.
- JSP에선 JDBC(Java Database Connectivity)를 통해 DB와 연동한다.
2. 오라클 사용자 계정 생성 및 권한 설정
- 역할(Role): 사용자가 다양한 권한을 효율적으로 관리할 수 있도록 관련된 권한끼리 묶어놓은 것
1) DB 관리자(DBA): 시스템 관리에 필요한 모든 권한을 부여한다.
2) 접속(connect): DB 접속에 필요한 가장 기본적인 시스템 권한 8가지를 묶은 권한
3) 객체 생성(resource): 객체(테이블, 뷰, 인덱스) 생성에 필요한 시스템 권한을 묶은 권한
- tab: 현재 접속한 계정에 생성된 테이블들의 목록을 확인할 수 있는 읽기 전용 테이블
→ 데이터 사전의 일종이다.
- 데이터 사전(Data Dictionary): 오라클에서 읽기 전용으로 제공되는 테이블이나 뷰들의 집합
→ DB 전반에 대한 정보를 제공한다.
1) USER_SEQUENCES: 시퀀스의 정보 조회
2) USER_INDEXES: 인덱스의 정보 조회
3) USER_VIEWS: 뷰의 정보 조회
4) USER_CONSTRAINTS: 제약조건에 대한 정보 조회
3. 테이블 및 시퀀스 생성
- 외래키(foreign key): 다른 테이블과의 관계를 설정해주는 키로, 다른 테이블에서 기본키로 지정된 컬럼만을 외래키로 사용할 수 있다.
- 테이블들의 관계는 동적으로 재정립할 수 있으므로, 외래키 지정은 테이블을 생성한 후 진행한다.
1) alter table 명령으로 설정한다.
2) 외래키 생성 시 제약조건의 이름을 지정할 수 있다.
alter table board
add constraint board_mem_fk foreign key (id)
references member (id);
- 시퀀스(Sequence): 순차적으로 증가하는 순번을 반환하는 DB 객체
1) nocycle 대신 cycle로 설정하면 최댓값까지 도달하면 최솟값부터 다시 시작한다.
2) cache로 설정하면 메모리에 시퀀스 값을 미리 할당해둔다.
create sequence seq_board_num
increment by 1 --1씩증가
start with 1
minvalue 1
nomaxvalue
nocycle --순환하지 않음
nocache; --캐시 안 함
- 더미(dummy) 데이터: 테스트를 위해 입력하는 가짜 데이터
- 처음 레코드를 입력하면 오라클은 입력된 레코드를 내부의 임시 테이블에 저장한다.
1) 오라클 내부에서는 레코드를 조회할 수 있으나, 외부에서는 입력된 레코드를 조회할 수 없는 상태이다.
2) 커밋(commit)이 완료되면 임시 테이블에 저장됐던 레코드가 실제 테이블에 적용되어 오라클 외부에서도 조회할 수 있게 된다.
4. JDBC 설정 및 DB 연결
- JDBC(Java Database Connectivity): 자바로 DB 연결 및 관련 작업을 할 때 사용하는 API
→ JDBC API를 사용하기 위해선 JDBC 드라이버가 있어야 한다.
- API(Application Programing Interface): 프로그램들이 서로 상호작용하는 것을 도와주는 매개체
- 드라이버 파일을 프로젝트와 연결하는 방법
1) WAS(Web Application Server: 톰캣)가 설치된 경로 하위의 lib 폴더에 드라이버 파일을 추가한다.
→ 차후 웹 애플리케이션 배포 파일에 드라이버가 포함되지 않는다.
2) 개별 프로젝트의 WEB-INF 하위의 lib 폴더에 드라이버 파일을 추가한다.
→ 작업 공간을 변경하거나 배포 시에도 드라이버가 함께 따라간다.
- JDBC 드라이버를 통해 DB와 연결하는 자바 클래스
1) Connection: DB와의 연결을 담당한다.
2) Statement: 인파라미터가 없는 정적 쿼리문을 실행할 때 사용된다.
3) PreparedStatement: 인파라미터가 있는 동적 쿼리문을 실행할 때 사용된다.
4) ResultSet: SELECT 쿼리문의 결과를 저장할 때 사용된다.
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletContext;
public class JDBConnect {
public Connection con;
public Statement stmt;
public PreparedStatement psmt;
public ResultSet rs;
public JDBConnect() {
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String id = "musthave";
String pwd = "1234";
con = DriverManager.getConnection(url, id, pwd);
System.out.println("DB 연결 성공(기본 생성자)");
} catch (Exception e) {
e.printStackTrace();
}
}
public JDBConnect(String driver, String url, String id, String pwd) {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, id, pwd);
System.out.println("DB 연결 성공(인수 생성자 1)");
} catch (Exception e) {
e.printStackTrace();
}
}
public JDBConnect(ServletContext application) {
try {
String driver = application.getInitParameter("OracleDriver");
Class.forName(driver);
String url = application.getInitParameter("OracleURL");
String id = application.getInitParameter("OracleId");
String pwd = application.getInitParameter("OraclePwd");
con = DriverManager.getConnection(url, id, pwd);
System.out.println("DB 연결 성공(인수 생성자 2)");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (psmt != null)
psmt.close();
if (con != null)
con.close();
System.out.println("JDBC 자원 해제");
} catch (Exception e) {
e.printStackTrace();
}
}
}
- Class 클래스의 forName( ) 메서드: new 키워드 대신 클래스명을 통해 직접 객체를 생성한 후 메모리에 로드하는 메서드
- DB에 연결하려면 URL, ID, PWD가 필요하다.
1) URL: "오라클 프로토콜@IP주소:포트번호:sid" 형식으로 구성된다.
2) sid: 오라클 인스턴스의 식별자
→ 자신의 sid를 확인하려면 sqlplus로 관리자 계정으로 접속한 후 "SELECT instance FROM v$thread;" 명령을 실행하면 된다.
"jdbc:oracle:thin:@localhost:1521:xe"
- DB 연결을 테스트하는 jsp 파일
<%@page import="common.DBConnPool"%>
<%@ page import="common.JDBConnect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>JDBC</title>
</head>
<body>
<h2>JDBC 테스트 1</h2>
<%
JDBConnect jdbc1 = new JDBConnect();
jdbc1.close();
%>
<h2>JDBC 테스트 2</h2>
<%
String driver = application.getInitParameter("OracleDriver");
String url = application.getInitParameter("OracleURL");
String id = application.getInitParameter("OracleId");
String pwd = application.getInitParameter("OraclePwd");
JDBConnect jdbc2 = new JDBConnect(driver, url, id, pwd);
jdbc2.close();
%>
<h2>JDBC 테스트 3</h2>
<%
JDBConnect jdbc3 = new JDBConnect(application);
jdbc3.close();
%>
<h2>커넥션 풀 테스트</h2>
<%
DBConnPool pool = new DBConnPool();
pool.close();
%>
</body>
</html>
- 실무에서는 DB 접속 정보를 클래스 안에서 모두 입력하는 방식을 사용하지 않는다.
→ web.xml에 입력해둔 후 필요할 때마다 application 내장 객체를 통해 얻어온다.
<context-param>
<param-name>OracleDriver</param-name>
<param-value>oracle.jdbc.OracleDriver</param-value>
</context-param>
<context-param>
<param-name>OracleURL</param-name>
<param-value>jdbc:oracle:thin:@localhost:1521:xe</param-value>
</context-param>
<context-param>
<param-name>OracleId</param-name>
<param-value>musthave</param-value>
</context-param>
<context-param>
<param-name>OraclePwd</param-name>
<param-value>1234</param-value>
</context-param>
- 접속 정보를 web.xml에 입력한 후 가져오는 방식 또한 DB 접속이 필요할 때마다 동일한 코드를 JSP에서 반복해서 기술해야 하므로 시스템 성능에 영향을 미친다.
→ 컨텍스트 초기화 매개변수를 생성자에서 직접 가져올 수 있도록 정의하는 것이 좋다. (위 자바 클래스의 세번째 생성자)
- application 내장 객체는 ServletContext 타입이다.
5. 커넥션 풀로 성능 개선
- 요청이 있을 때마다 DB와 새로 연결했다가 해제하면 시스템 성능에 영향을 미친다.
→ 커넥션 풀을 이용하는 것이 좋다.
- 커넥션 풀(connection pool): Connection 객체를 미리 생성해 풀에 넣어두고, 요청이 있을 때 이미 생성된 Connection 객체를 가져다 사용하는 기법
→ 사용이 완료된 객체는 연결을 해제하는 대신 풀에 반납하여 필요할 때 재사용할 수 있도록 한다.
- JSP 프로그맹 시 커넥션 풀을 직접 만들어 사용하지 않고 WAS가 제공하는 것을 이용하는 것이 좋다.
→ 애플리케이션마다 자원을 따로 관리하는 것은 비효율적이기 때문이다.
- 대부분의 WAS는 커넥션 풀을 비롯한 여러 자원을 JNDI 서비스로 제공한다.
→ JNDI(Java Naming and Directory Interface): 자바 소프트웨어에서 객체나 데이터의 전체 경로를 몰라도 이름만으로 찾아 쓸 수 있는 디렉터리 서비스 (객체 이름과 실제 객체를 연결해주는 역할을 한다.)
- WAS의 JNDI를 통해 커넥션 풀을 사용하는 개략적인 절차
1) WAS가 시작할 때 server.xml과 context.xml에 설정한 대로 커넥션 풀을 생성한다.
2) JSP 코드에서 JNDI 서버로부터 데이터소스 객체를 얻어온다.
3) 데이터소스로부터 커넥션 객체를 가져온다.
4) DB 작업을 수행한다.
5) 모든 작업이 끝나면 커넥션 객체를 풀로 반환한다.
- 데이터 소스: JDBC 연결을 생성해 제공해주는 객체
- 두 xml 파일의 역할
→ server.xml에서 커넥션 풀을 전역 자원으로 설정한 후 context.xml에서 이를 참조하는 링크를 추가한다.
1) server.xml: 서버 전체와 관련한 설정
2) context.xml: 각각의 웹 애플리케이션마다 하나씩 주어지는 자원을 설정
- server.xml의 <GlobalNamingResources>: 전역 자원을 등록하는 영역
1) driverClassName: JDBC 드라이버의 클래스명
2) type: 데이터소스로 사용할 클래스명
3) initialSize: 풀의 최초 초기화 과정에서 미리 만들어놓을 연결의 개수(디폴트: 0)
4) minIdle: 최소한으로 유지할 연결 개수(디폴트: 0)
5) maxTotal: 동시에 사용할 수 있는 최대 연결 개수(디폴트: 8)
6) maxIdle: 풀에 반납할 때 최대로 유지될 수 있는 연결 개수(디폴트: 8)
7) maxWaitMillis: 새로운 요청이 들어왔을 때 얼만큼 대기할지를 밀리초 단위로 기술
8) url: 오라클 연결을 위한 URL
9) name: 생성할 자원의 이름
10) username: 계정 아이디
11) password: 계정 패스워드
- javax.sql.DataSourrce: 물리적인 데이터소스와의 연결을 생성해주는 자바 표준 인터페이스
→ driverClassName 속성으로 지정한 oracle.jdbc.OracleDriver 클래스가 해당 인터페이스를 구현한다.
∴ 오라클이 제공하는 OracleDriver 클래스가 커넥션 풀 기능을 구현하며, 사용자는 자바 표준 인터페이스인 DataSource 형태로 받아 이용한다.
- 커넥션 풀을 이용하는 자바 연결 클래스
1) JNDI에서 Context는 이름과 실제 객체를 연결해주는 개념이다.
2) InitialContext: JNDI를 이용하기 위한 시작점이다.
→ 해당 객체의 lookup( ) 메서드에 이름을 제공하여 원하는 객체를 찾아올 수 있다.
3) java:comp/env: 현재 웹 애플리케이션의 루트 디렉터리
4) Connection 객체의 close( )를 호출하면 자동으로 풀로 반납된다.
package common;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class DBConnPool {
public Connection con;
public Statement stmt;
public PreparedStatement psmt;
public ResultSet rs;
public DBConnPool() {
try {
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
DataSource source = (DataSource) ctx.lookup("dbcp_myoracle");
con = source.getConnection();
System.out.println("DB 커넥션 풀 연결 성공");
} catch (Exception e) {
System.out.println("DB 커넥션 풀 연결 실패");
e.printStackTrace();
}
}
public void close() {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (psmt != null)
psmt.close();
if (con != null)
con.close();
System.out.println("DB 커넥션 풀 자원 반납");
} catch (Exception e) {
e.printStackTrace();
}
}
}
6. 간단한 쿼리 작성 및 실행
- JDBC에서 쿼리문은 java.sql.Statement 인터페이스로 표현되며, Statement 객체는 Connection 객체를 통해 얻어온다.
- Statement 계열의 인터페이스
1) Statement: 인파라미터가 없는 정적 쿼리를 처리할 때 사용
2) PreparedStatement: 인파라미터가 있는 동적 쿼리를 처리할 때 사용
3) CallableStatement: 프로시저나 함수를 호출할 때 사용
→ 프로시저(procedure): 특정 로직을 처리하기만 하고 결과값을 반환하지 않는 서브 프로그램
- 인파라미터(IN parameter): 미리 작성해둔 쿼리문에서 일부 값을 나중에 결정할 수 있게 해주는 매개변수
→ 쿼리문에서 물음표(?)로 표현한다.
- Statemente 계열의 객체로 쿼리문을 실행할 때 사용하는 메서드
1) executeUpdate( ): INSERT, UPDATE, DELETE 쿼리문을 실행할 때 사용한다.
→ 기존 레코드를 변화시키거나 새로운 레코드를 입력하는 쿼리문들이므로, 실행 후 영향을 받은 행의 개수가 int 형으로 반환된다.
2) executeQuery( ): SELECT 쿼리문을 실행할 때 사용한다.
→ SELECT는 기존 레코드를 조회하는 쿼리문이므로, 조회한 레코드들의 집합인 ResultSet 객체를 반환한다.
- 동적 쿼리문으로 회원을 추가하는 코드
1) setString( )의 첫 번째 매개변수는 인파라미터의 순서이다.
→ DB에선 인덱스가 1부터 시작한다.
2) PreparedStatement는 먼저 쿼리문의 틀을 준비해둔 후, 필요할 때 인파라미터를 설정해 사용하는 방식으로 동작한다.
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="common.JDBConnect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>JDBC</title>
</head>
<body>
<h2>회원 추가 테스트(executeUpdate() 사용)</h2>
<%
JDBConnect jdbc = new JDBConnect();
String id = "test1";
String pass = "1111";
String name = "테스트1회원";
String sql = "INSERT INTO member VALUES (?, ?, ?, sysdate)";
PreparedStatement psmt = jdbc.con.prepareStatement(sql);
psmt.setString(1, id);
psmt.setString(2, pass);
psmt.setString(3, name);
int inResult = psmt.executeUpdate();
out.println(inResult + "행이 입력되었습니다.");
jdbc.close();
%>
</body>
</html>
- 인파라미터 설정 시에는 데이터 타입에 맞는 set 메서드를 사용하면 된다.
1) void setInt(int index, int value)
2) void setDate(int index, Date value)
3) void setString(int index, String value)
- 정적 쿼리문으로 회원을 추가하는 코드
1) next( ) 메서드는 ResultSet 객체에서 다음 행(레코드)을 반환한다.
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="common.JDBConnect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>JDBC</title>
</head>
<body>
<h2>회원 목록 조회 테스트(executeQuery() 사용)</h2>
<%
JDBConnect jdbc = new JDBConnect();
String sql = "SELECT id, pass, name, regidate FROM member";
Statement stmt = jdbc.con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String id = rs.getString(1);
String pw = rs.getString(2);
String name = rs.getString("name");
java.sql.Date regidate = rs.getDate("regidate");
out.println(String.format("%s %s %s %s", id, pw, name, regidate) + "<br/>");
}
jdbc.close();
%>
</body>
</html>
- JDBC 프로그래밍의 순서
1) JDBC 드라이버 로드
2) DB 연결
3) 쿼리문 작성
4) 쿼리문 객체 생성
5) 쿼리 실행
6) 실행 결과 처리
7) 연결 해제
- ResultSet의 get 메서드들은 커서가 현재 가리키는 행의 컬럼값을 읽어온다.
1) 컬럼의 인덱스와 컬럼명 두 가지 모두 사용할 수 있다.
2) Int, Date, String 타입을 주로 사용한다.
'JSP > 성낙현의 JSP 자바 웹 프로그래밍' 카테고리의 다른 글
| 액션 태그(Action Tag) (0) | 2023.06.13 |
|---|---|
| 세션(Session) (2) | 2023.06.08 |
| 쿠키(Cookie) (0) | 2023.06.04 |
| 내장 객체의 영역(Scope) (0) | 2023.05.30 |
| 내장 객체(Implicit Object) (0) | 2023.05.29 |
댓글