[Servlet] Servlet 정리2 - JDBC
[목차]
4. JDBC
1) 오라클 드라이버 로딩
2) DB서버에 연결하기(Connection 설정)
3) SQL문 실행을 담당하는 객체를 생성
4) SQL문 실행
5) 자원반납
* DAO(Data Access Object)
* DBUtil
* DTO(Date To Object)
[내용]
[오라클에 접속해서 JBC를 사용하기 전에 해야할 일]
1) 오라클 드라이버를 다운로드 받는다.
- 제조사 홈페이지를에서 다운로드 받는다
- 오라클 JDBC 위치 : 오라클 설치시 해당경로에 설치된다.(11g XE)
| C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar |
2) 이클립스 설정
① [Package Explorer]창에서 [Project] 오른쪽 클릭
② 오른쪽 클릭창 - [Build path] - [Configure Build patch]
③ 대화상자에서 세번째 탭인 [Libaries] Tab 선택
④ [Add External JARs] 선택 후 상기의 오라클 JDBC 위치의 [ojdbc6.jar ] 선택 -> [Apply]
4. JDBC : 자바에서 DBMS 연동을 위한 표준 API(java에서 SQL을 실행하기 위해서 제공되는 표준 API)
- driver : 오라클회사에서 자바로 오라클을 접속하기 위해 내부에서 처리해야하는 기능을 구현해 놓은 클래스들을 라이브러리로 만들어 놓은것(ojdbc~.jar)
| oracle.jdbc.driver | OracleDriver.class |
- API를 이용해서 작업
- 자바 프로그램으로 insert, delete, update, select를 실행.
1) 오라클 드라이버 로딩
[오라클 드라이버명]
oracle.jdc.driver.OracleDriver
java.lang패키지에 Class라는 클래스를 이용해서 오라클 드라이버의 핵심 클래스를 메모리에 로딩
핵심클래스를 드라이버클래스라 하며 드라이버 클래스는 어떤 DBMS를 쓰느냐에 따라서 달라진다.
public static Class<?> forName(String className) throws ClassNotFoundExceptionClass.forName("패키지를 포함한 오라클 드라이버 클래스명");
Class.forName("oracle.jdc.driver.OracleDriver");
2) DB서버에 연결하기(Connection 설정)
=> java.sql패키지의 API를 이용
DriverManager의 getConnection메소드를 이용해서 DB서버에 연결.
public static Connection getConnection(String url, String user, String password) throws SQLException ① static메소드이므로 클래스이름으로 액세스
② throws하고 있는 SQLException에 대한 처리
③ 매개변수
url : 연결문자열(접속하기 위한 정보를 하나의 문자열로 만들어 놓은 것, 어떤 DBMS를 쓰느냐에 따라서 다른
현상의 문자열을 작성 - 제조사 홈페이지 참고)
user : 사용자계정 (기본값 : scott)
password : 계정 패스워드
[오라클 url]
| jdbc:oracle:thin: | @ip:port: | 데이터베이스서비스명 |
| 프로토콜 | DBMS가 설치되어있는 PC의 ip와 port | 오라클 설치 시 셋팅하는 값으로 달라질 수 있다.(express버전은 xe) |
| jdbc:oracle:thin: | @192.168.0.19:1521: | xe |
④ 리턴타입
java.sql.Connection이 리턴
DriverManager클래스의 getConnection메소드는 DB서버에 연결하고 연결정보를 객체로 만들어서 리턴한다.
매개변수에 어떤 DBMS를 사용하냐에 따라 다른 Connection객체의 오버라이딩 메소드가 실행됨.(다형성 적용)
⑤ 사용방법
Connection con = DriverManager.getConnection(url, user, password);
// oracle.jdbc.driver.T4CConnection 리턴
3) SQL문 실행을 담당하는 객체를 생성
[상속구조]
| Statement | : 정적SQL을 실행할 때 사용(보안취약 - SQL Injection공격 취약) |
| ↑ | |
| PrspareStatement | : 동적 SQL을 실행할 때 사용(secure coding에 적합 - 정부권고사항) |
| ↑ | |
| CallableStatement | : 각 DBMS에 특화된 SQL로 작성된 명령문을 실행 : 오라클 (PL-SQL) |
① Statement객체 이용
Connection객체가 갖고 있는 createStatement메소드를 이용
[형식]
Statement stmt = con.createStatement();
//stmt객체는 오라클 드라이버에 포함되어 있는 클래스 객체가 리턴
String sql = "select * from mydept where deptno='"+deptno+"' and deptname ='"+deptname+"'";
* sql insaction
select * from mydept where deptno=002 -- and deptname='인사';
select * from mydept where deptno='' or '1'='1'-- -- 주석문 뒤의 조건문이 다 맞지않아도 검색된다.
=> Statement는 보안취약
② PreparedStatement객체 이용
- Connection객체의 prepareStatement메소드를 이용해서 생성
- PreparedStatement객체를 만들때 미리 sql문을 파싱 후 외부에서 값만 입력받아서 매핑.
- Statement의 단점 보완(보안)
- 컬럼명은 입력값으로 처리하지 못한다.
- 외부에서 입력받아 처리해야하는값은 ?로 대신해서 sql문을 만든다.
1) sql문을 전달하여 PreparedStatement객체를 생성
String sql = "select * from mydept where deptno = ? and deptname = ?";
PrepaeredStatement ptmt = con.prepareStatement(sql);
2) ?에 값을 셋팅
외부에서 전달받은 값을 ?에 설정해야 sql문이 완성
PrepaeredStatement객체에 있는 set~~~메소드를 이용해서 ?에 값을 셋팅
set~~~메소드는 타입과 연결이 되는 메소드로 ResultSet의 get~~~메소드와 동일한 방법으로 해석
ptmt.setString(A,"B") // A : ?의 순서, B : ?에 셋팅할 값
ptmt.setString(1,"002") // 첫번째 물음표에 문자열 "002"를 셋팅 한다.
// (오라클 타입 : varchar2, char)
4) SQL문 실행
① Statement를 사용
1) executeUpdate(sql) : insert, delete, update 명령문을 실행.
매개변수로 sql을 전달하면 실행결과로 몇개의 row가 반영되었는 지 리턴.
int result = stmt.executeUpdate(sql명령문);
// 몇개의 행이 삽입, 삭제, 수정되었는 지 리턴.
2) executeQuery(sql) : select 명령문을 실행
실행한 후 조회된 테이블을 리턴, DBMS에서 조회된 테이블을 사용하기 위해 모델링된 자바의 객체가 ResultSet.
실제로는 어떤 DBMS가 사용되었는지에 따라 다른 ResultSet객체가 리턴.
- sql-plus프로그램을 이용해서 select문을 실행할때 결과로 보여지는 2차원 표의 데이터를 자바에서 이용할 수
있도록 모델링한 클래스가 ResultSet.
2-1) select문을 실행하면 ResultSet을 리턴하기 때문에 ResultSet타입의 변수를 선언해서 결과를 참조
ResultSet rs = stmt.executeQuery(sql명령문); 2-2) ResultSet에 있는 테이블의 데이터를 읽기 위해서 ResultSet에서 제공하는 메소드를 이용해서 작업
Cursor를 다음 레코드로 이동하면서 레코드 개수만큼 반복작업을 실행.
ResultSet에 있는 next()를 호출하면 다음 레코드를 커서를 이동할 수 있다. next()는 커서를 이동했을 때 레코드가
존재하면 true를 리턴하고 없으면 false를 리턴.
while(rs.next()){ // 레코드 개수만큼 실행
// 레코드의 각 컬럼을 읽는다.
} 2-3) 한 번에 하나의 칼럼만 읽을 수 있다. ResultSet의 get~~~메소드를 이용해서 칼럼값을 가져온다.
타입에 따라서 구분
- 오라클의 타입이 varchar2나 char인 경우 : java의 String
getString(칼럼명) or getString(칼럼의 순서 index)
칼럼의 순서 index : 원본 테이블에 정의된 칼럼의 순서가 아니라 조회된 테이블의 칼럼순서로 1부터 시작.
- 오라클 타입이 number(정수) : java의 int
getInt(칼럼명) or getInt(칼럼index)
- 오라클 타입이 date : java.sql.Date클래스
while(rs.next()){
// 레코드 개수만큼 실행.
// 커서를 하나씩 다음으로 이동.
// 다음 레코드가 있는 경우 true, 없으면 false리턴
rs.getString("name"); => name컬럼으로 정의되어 있는 칼럼의 입력된 값을 읽어서 출력
rs.getString(2); => 조회된 테이블의 2번째 칼럼의 입력된 데이터를 읽어서 출력
}
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String user = "scott";
String password = "tiger";
String sql = "select * from mydept where deptno='"+deptno+"' and deptname ='"+deptname+"'";
try {
//1. 드라이버 로딩
Class.forName("oracle.jdbc.driver.OracleDriver");
//2. DB연결
Connection con = DriverManager.getConnection(url, user, password);
//3. sql문법 객체생성
Statement stmt = con.createStatement();
//4. sql문 실행
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData md= rs.getMetaData();
int columCount = md.getColumnCount();
System.out.println("칼럼 수 : "+columCount);
while(rs.next()) {
for(int i=1;i<=columCount;i++) {
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
② PrepaeredStatement
1) excuteUpdate() : insert, delete, update
2) excuteQuery() : select
=> 리턴유형, 실행, 처리 모두 Statement메소드와 동일하지만 sql을 매개변수로 전달하지않는다.
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String user = "scott";
String password = "tiger";
String sql = "select * from mydept where deptno=? and deptname =?";
System.out.println(sql);
try {
//1. 드라이버 로딩
Class.forName("oracle.jdbc.driver.OracleDriver");
//2. DB 연결
Connection con = DriverManager.getConnection(url, user, password);
//3. sql문법 객체생성
PreparedStatement ptmt = con.prepareStatement(sql);
//4. 외부에서 전달받을 값 설정
ptmt.setString(1, deptno);
ptmt.setString(2, deptname);
//5. sql문 실행
ResultSet rs = ptmt.executeQuery();
ResultSetMetaData md= rs.getMetaData();
int columCount = md.getColumnCount();
while(rs.next()) {
for(int i=1;i<=columCount;i++) {
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
* like 사용
String sql = "select * from mydept where loc like ?";
ptmt.setString(1, "%"+loc+"%");
5) 자원반납
- 클라이언트가 점유해서 사용하던 자원을 반납해야한다.(메모리 해제)
- Connection, Statement, ResultSet 메모리해제. close메소드를 이용.
- 가장 마지막에 생성된 객체부터 메모리 해제한다.
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String user = "scott";
String password = "tiger";
String sql = "select * from mydept where deptno=? and deptname =?";
Connection con = null;
PreparedStatement ptmt = null;
ResultSet rs = null;
System.out.println(sql);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, user, password);
ptmt = con.prepareStatement(sql);
ptmt.setString(1, deptno);
ptmt.setString(2, deptname);
rs = ptmt.executeQuery();
while (rs.next()) {
System.out.print(rs.getString(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getString(3) + "\t");
System.out.print(rs.getString(4) + "\t");
System.out.print(rs.getString(5) + "\t");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (con != null) con.close();
if (ptmt != null) ptmt.close();
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
* DAO(Data Access Object) : 동일한 테이블을 액세스하는 기능은 하나의 객체로 정의하고 사용
class 테이블명DAO{
public void insert(){
}
public void update(){
}
public void delete(){
}
public void select(){
}
}public class MyDeptDAO {
//insert
public void insert(String deptno, String deptname, String loc, String tel, String mgr) {
String sql = "insert into mydept values(?, ?, ?, ?, ?)";
Connection con = null;
PreparedStatement ptmt = null;
try {
con = DBUtil.getConnect();
ptmt = con.prepareStatement(sql);
ptmt.setString(1, deptno);
ptmt.setString(2, deptname);
ptmt.setString(3, loc);
ptmt.setString(4, tel);
ptmt.setString(5, mgr);
int result =ptmt.executeUpdate();
System.out.println(result+"개 행 삽입 성공");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(null, ptmt, con);
}
}
public void delete(String deptno) {
String sql="delete from mydept where deptno=?";
Connection con = null;
PreparedStatement ptmt = null;
try {
con = DBUtil.getConnect();
ptmt = con.prepareStatement(sql);
ptmt.setString(1, deptno);
int result = ptmt.executeUpdate();
System.out.println(result+"개의 데이터가 삭제되었습니다.");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(null, ptmt, con);
}
}
public void select(String deptno, String deptname) {
String sql = "select * from mydept where deptno=? and deptname =?";
Connection con = null;
PreparedStatement ptmt = null;
ResultSet rs = null;
try {
con = DBUtil.getConnect();
ptmt = con.prepareStatement(sql);
ptmt.setString(1, deptno);
ptmt.setString(2, deptname);
rs = ptmt.executeQuery();
ResultSetMetaData md= rs.getMetaData();
int columCount = md.getColumnCount();
while(rs.next()) {
for(int i=1;i<=columCount;i++) {
System.out.print(rs.getString(i)+"\t");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs, ptmt, con);
}
}
public void update(String deptno, String deptname) {
StringBuffer sql=new StringBuffer();
Connection con = null;
PreparedStatement ptmt = null;
sql.append("update mydept ");
sql.append("set deptname=?");
sql.append("where deptno=?");
try {
con = DBUtil.getConnect();
ptmt = con.prepareStatement(sql.toString());
ptmt.setString(1, deptname);
ptmt.setString(2, deptno);
int result = ptmt.executeUpdate();
System.out.println(result+"개 행이 업데이트 되었습니다.");
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(null, ptmt, con);
}
}
}
* DBUtil : DB연동과 관련된 기능을 유틸리티처럼 제공하는 클래스
1) Driver Loading
2) DB서버 연결
3) 자원반납
=> Utility처럼 사용할 것으로 static으로 구현
public class DBUtil {
// 1. 드라이버 로딩
// => 메소드마다 드라이버를 로딩하는 작업을 구현하지않고 클래스 로더가 클래스를
// 로딩할때 한번만 실행되도록 하기 위해서
// static 블럭안에 드라이버 로딩 코드를 작성한다.
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 2. DB서버 연결
// SQL문을 실행할때마다 연결을 해야한다.
public static Connection getConnect(){
Connection con = null;
String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
String user = "scott";
String password = "tiger";
try {
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
// 3. 자원반납
public static void close(ResultSet rs, Statement stmt, Connection con) {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(con != null) con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 4. Connection, Statement, ResultSet을 반납하는 메소드 각각 작성가능.
}
* DTO(Date To Object) : Data를 객체로 변환. 테이블의 row로 객체를 생성 (테이블명+VO or 테이블명+DTO)
class 테이블명DTO{
변수
생성자()
toString()
getter/setter()
}public class MyDeptDTO {
private String deptno;
private String deptname;
private String loc;
private String tel;
private String mgr;
public MyDeptDTO() {
}
public MyDeptDTO(String deptno, String deptname, String loc, String tel, String mgr) {
super();
this.deptno = deptno;
this.deptname = deptname;
this.loc = loc;
this.tel = tel;
this.mgr = mgr;
}
@Override
public String toString() {
return "MyDeptDTO [deptno=" + deptno + ", deptname=" + deptname
+ ", loc=" + loc + ", tel=" + tel + ", mgr="+ mgr + "]";
}
public String getDeptno() {
return deptno;
}
public void setDeptno(String deptno) {
this.deptno = deptno;
}
public String getDeptname() {
return deptname;
}
public void setDeptname(String deptname) {
this.deptname = deptname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getMgr() {
return mgr;
}
public void setMgr(String mgr) {
this.mgr = mgr;
}
}@WebServlet(name = "deptInsert", urlPatterns = { "/dept/Insert.do" })
public class DeptInsertServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
System.out.println("서블릿 요청");
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
//1. 사용자가 입력한 값을 추출
PrintWriter out = response.getWriter();
String deptno = request.getParameter("deptno");
String deptname = request.getParameter("deptname");
String loc = request.getParameter("loc");
String tel = request.getParameter("tel");
String mgr = request.getParameter("mgr");
//2. 비지니스 메소드 호출
MyDeptDAO dao = new MyDeptDAO();
MyDeptDTO dept = new MyDeptDTO(deptno, deptname, loc, tel, mgr);
System.out.println("servelt => "+dept);
int result = dao.insert(dept);
//int result = dao.insert(deptno, deptname, loc, tel, mgr);
//3. 결과값 출력
String msg = "";
if(result>0) {
msg = result+"개 행 삽입 성공";
}else {
msg = "삽입 실패("+result+")";
}
out.println("<h1>"+msg+"</h1>");
}
}| [html] | request ---------> <-------- response |
[Servlet] 요청정보추출 비지니스 메소드 call 결과처리 |
[DAO] sql처리 insert(), update(), delete(), select() |
[DBUtil] 드라이버로딩 Connection생성 자원반납 |
[DBMS] |
| [DTO] 변수 생성자 getter/setter toString() |
|||||