* JDBC(Java Database Connectivity)
- Java언어에서 데이터베이스에 접속할 수 있도록 하는 자바 API이다. JDBC는 데이터베이스에서 자료를 쿼리하거나 업데이트하는 방법을 제공한다.
- JDBC는 JDBC 인터페이스와 JDBC 드라이버로 구성되어 있다.
└JDBC 인터페이스 : 데이터베이스와 연동되는 프로그램을 작성할 수 있게 하는 도구
└JDBC 드라이버 : JDBC 인터페이스를 구현하여 실제로 DBMS를 작동 시켜서 질의를 던지고 결과를 받는다.
[참고] 드라이버는 DB 제작사에서 제공한다. 그러나 sun사에서는 드라이버를 제공하지 않고 interface를 제공한다.(jdbc odbc driver, windows에서 연동)
- JDBC Driver 4가지 종류
Type 1. JDBC-ODBC Bridge Plus ODBC Drive : JDBC 호출을 ODBC 호출로 변환하고, ODBC Driver로 보낸다. Type1을 사용하려면 클라이언트에 ODBC Driver와 JDBC Driver가 설치되어 있어야 한다. 이것은 매우 비효율적이고 제한이 많다. 시스템이 여러 단계를 거쳐 데이터베이스 호출을 전달하여 속도가 느리며, 기능이 어느 ODBC에서나 다룰 수 있는 것으로만 제한된다. 동적으로 드라이버를 다운로드하여 사용하는 환경에서는 사용하기 어려우며 고정된 드라이버가 설치되어 운영되는 환경에 적합하다. sun사에서 JVM에 기본적으로 제공하는 JDBC-ODBC Driver는 sun.jdbc.odbc.jdbc OdbcDriver이다.
Type 2. Native-API Partly-Java Driver : 일부가 Java로 되어 있는 JDBC Driver를 의미한다. 즉 자바 드라이버를 사용해서 특정 업체의 API와 상호 통신한다. 클라이언트 컴퓨터에 DB제조사가 만든 드라이버가 설치되어야 한다.(C, C++) 관리가 어렵다.
Type 3. JDBC-Net Pure Java Driver : DBMS 독립적인 Net-Protocol을 사용하여 미들웨어 계층의 서버와 통신하며, 이 미들웨어 서버는 JDBC호출을 각 특정 벤더 DBMS 호출로 변환한다. 이때 미들웨어 서버는 게이트웨이(Gateway)역할을 한다. 4가지 Type중에서 가장 융통성이 뛰어난 드라이버 Type이기 때문에 동적으로 다운로드되는 애플릿(Applet)같은 환경에 더욱 적합하며, 클라이언트가 정적으로 이루어졌다고 하더라도 Type3를 사용하면 시스템 유지보수가 더욱 쉬워진다.
Type 4. Native-Protocol Pure Java Driver : java에서 interface를 제공하고, DB제작사에서 만들어주는 드라이버이다. JDBC API 표준을 기준으로 만들었다기 보다는 DBMS 벤더가 표준을 기준으로 기능을 추가 또는 삭제하여 독자적인 형태로 만든 타입이다. 추가로 설치해야 할 라이브러리(Library)나 미들웨어가 필요없기 때문에 배포하기에도 가장 손쉬운 방법으로 제공한다. 따라서 Type4는 클라이언트가 동적인 환경에서 특정 DBMS만이 제공하는 기능들을 사용할 경우 적합하다. 클라이언트 컴퓨터에 설치되어 있지 않아도 사용 가능하며 속도가 빠르다. Driver 관리자가 필요하다.
[참고] 자세한 설명 참고. http://blog.naver.com/ikalsu99/50013516317 [출처] JDBC Driver Type|작성자 모험이
- java.sql package에서 class와 interface를 제공한다.
- JDBC보다는 DBCP를, DBCP보다는 프레임워크를 주로 사용한다.(DBCP, Framework은 JDBC기술을 기반으로 하여 발전시킨 기술이다.)
* 작업 순서 [참고]JDBC classpath 환경설정 먼저할 것
1. Driver Loading
2. 로딩된 드라이버를 관리하여 DB연결(Connection) 얻기
3. query 생성 객체
4. query 실행 후 결과 얻기
5. 연결 끊기(Close)
* 자세히
1. Driver Loading
- ojdbcX.jar(X는 버전)의 안을 들여다보면(oracle\jdbc\driver\OracleDriver.class) OracleDriver.class가 들어있다. 클래스는 객체화하여 사용해야 하므로 class클래스의 forName() 메서드를 사용하여 객체화 한다. 이때, forName() 메서드가 ClassNotFoundException을 throws하므로 try-catch문으로 묶어주어야 한다.
[참고] class.forName(String className) : 입력받은 클래스의 객체를 생성한다.
-------------------------------------------------------------------
package day1014;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
public class GetConnection {
public static void main(String[] args) {
//1. 드라이버 로딩
try {
Class.forName("oracle.jdbc.driver.OracleDriver"); // 객체화
System.out.println("드라이버 로딩 성공"); // 성공 확인 메시지
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
-------------------------------------------------------------------
2. 로딩된 드라이버를 관리하여 DB연결(Connection) 얻기
- DBMS와의 연결을 하기 위해 Connection을 사용한다. Connection은 DriverManager을 통해서 얻어올 수 있다. 인증 유무에 따라 해당 메서드를 적절히 사용하면 된다. 또한 SQLException을 throws하므로 try-catch문으로 묶어주어야 한다.
class DriverManager
static Connection getConnection(String url) throws SQLException;
static Connection getConnection(String url, Properties info) throws SQLException;
static Connection getConnection(String url, String user, String password) throws SQLException;
- url : jdbc:DBMS종류:Driver종류:@위치:Port:사용자 계정명
└위치 : ip, domain이 올 수 있다.(내 컴퓨터일 경우 'localhost', '127.0.01'로 표현 할 수도 있다.)
└Port : 윈도우용 오라클은 포트가 1521번으로 고정되어 있다.
└사용자 계정명 : 제어판 - 관리도구 - 서비스 - OracleServiceXXXX를 통해서 확인 가능하다. OracleService 뒤에 붙은것이 사용자 계정명이다.
- Connection은 commit과 rollback등을 할 수 있다.
Interface Connecton
void commit();
void rollback();
void rollback(Savepoint savepoint);
[참고] 사용자가 임의로 커밋을 수행하고자 할 때에는 'void setAutoCommit(boolean autoCommit)'을 사용하여 해당 속성을 false로 변경해주어야 한다.
------------------------------------------------------------------------------
package day1014;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
public class GetConnection {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("드라이버 로딩 성공");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "scott"; // 연습용 계정
String pw = "tiger";
try {
Connection con = DriverManager.getConnection(url, id, pw);
System.out.println("db연동 성공" + con);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
------------------------------------------------------------------------------
3. query 생성 객체
Interface Statement
└Interface PreparedStatement
└Interface CallableStatement
Connection Method
└Statement createStatement() throws SQLException;
└PreparedStatement prepareStatement(String sql) throws SQLException;
└CallableStatement prepareCall(String sql) throws SQLException;
[참고] SQLException을 throws하므로 try-catch문으로 감싸주어야한다. 아래 예제에서는 생략하였다.
Interface Statement
- 정적 SQL명령문을 실행하고 생성한 결과를 얻기 위해 Statement객체를 사용한다. 쿼리문을 실행할때마다 생성하여 DB작업을 수행하는 객체이다. 따라서 같은 쿼리가 반복되면 매번 실행시켜야하므로 효율성이 떨어진다.(예를들어, 총을 쏠 때마다 분해된 총을 조립하고 총알을 발포한다.)
ex) 동일한 쿼리가 여러번 실행되면 속도가 느려진다.
stmt.execute("sql A문");
stmt.executeUpdate("sql A문");
stmt.executeQuery("sql A문");
- 연결하고자 하는 테이블, 컬럼을 모르고 시작한다. 쿼리문의 형식을 그대로 사용하므로 해킹에 취약하다.[참고] SQL Injection
-----------------------------------------------------------------------------------------------
//Connection 얻기
con = getConnection();
//쿼리생성객체(Statement) 얻기. 여기서는 생략했지만 SQLException을 유발하므로 try-catch로 감싸주어야 한다.
stmt = con.createStatement();
//쿼리문 저장
String insert_cpemp = "insert into cp_emp2(empno, ename, sal) values("+ cev.getEmpno()+ ", '"+ cev.getEname()+ "', "+ cev.getSal() + ")";
-----------------------------------------------------------------------------------------------
Interface PreparedStatement
- SQL 명령문은 선행처리 컴파일되며 PreparedStatement객체에 저장된다. 그 후 이 객체를 이용하여 해당 명령문을 여러 번 효율적으로 실행할 수 있다.(Statement와는 다르게 완품의 총으로 총알을 발포하기만 하면 된다.)
- IN 매개변수의 값을 설정하기 위한 setXXX메소드는 입력 매개변수의 정의된 SQL유형과 호환 가능한 유형을 지정해야 한다. 예를 들어, IN매개변수의 SQL유형이 Integer인 경우, setInt를 사용해야 한다. 임의 매개변수 유형 변환이 필요한 경우, setObject메소드를 대상 SQL유형과 함께 사용한다.
- 사용하고자 하는 쿼리문을 객체가 미리 알고 있다. 쿼리를 생성하면 쿼리문은 한번만 생성되고 값만 변경되어 쿼리가 DB에서 수행된다.(Statement는 execute할 때 쿼리가 생성되며 날려보내지는데 반면 PreparedStatement는 미리 생성된 쿼리문을 execute로 날려보내기만 한다.)
- 값을 넣는 bind변수가 사용된다. bind변수는 왼쪽에서 오른쪽 순서대로 인덱스 번호가 부여된다. bind변수는 컬럼명, 테이블명에 대해서는 처리할 수 없다. 따라서 SQL Exception이 발생하기 매우 어렵다. [참고] bind변수
- SQL문을 작성할 때 문자열을 감싸는 '(싱글 쿼테이션)을 사용하지 않는다.
- SQL Injection이 발생하기 어렵다.
------------------------------------------------------------------------
PreparedStatement 예제
con = getConnection(); // 커넥션 얻는 함수 호출
// 부서번호, 부서명, 위치를 삽입하는 sql문. bind변수를 사용함으로 문자열이라도 '를 사용하지 않은 모습을 볼 수 있다.
String insert_cpdept = "insert into cp_dept(deptno, dname, loc) values (?,?,?)";
// PreparedStatement를 생성할 때 sql문을 넣어주어 생성한다. 위에서 말한 쿼리를 알고 시작한다가 이에 해당
pstmt = con.prepareStatement(insert_cpdept);
//바인드 변수에 값 넣기
//void setXXX(int parameterIndex, XXX x) 파라미터 인덱스와 해당 값에 맞는 데이터형 메서드를 불러 사용한다.
pstmt.setInt(1, cdv.getDeptno());
pstmt.setString(2, cdv.getDname());
pstmt.setString(3, cdv.getLoc());
------------------------------------------------------------------------
Interface CallableStatement
- CallableStatement는 저장된 SQL프로시저를 실행하기 위해 사용한다. JDBC는 모든 RDBMS를 위한 표준 방법으로 저장된 프로시저를 호출할 수 있는 저장된 프로시저 SQL 이스케이프를 제공한다.
- 사용법
CallableStatement cstmt = con.prepareCall("{call 프로시저명(파라미터 혹은 바인드 변수)}");
- 바인드 변수에 값 넣기
in parameter
cstmt.setInt(1,2014);
cstmt.setString(2,"수키");
out parameter(외부변수 등록)
- void java.sql.CallableStatement.registerOutParameter(int parameterIndex, int sqlType) throws SQLException
- 이때 데이터타입은 특정 DB에 귀속되게 사용하지 않는다. java.sql.Class Types의 Fields를 사용한다.
cstmt.registerOutParameter(index, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.VARCHAR);
================================================================================================
1. 기존의 sqlplus에서 사용 절차
외부변수 선언(var을 이용하여 선언) -> 프로시져 실행(이때 선언한 외부변수를 OUT 파라미터로 준다) -> 외부변수에 저장된 값 출력
-----------------------------------------------------------------------------------------------
2. CallableStatement 사용 예제
2-1. 기본형 데이터가 반환될 때
- create or replace procedure cpemp_select(i_empno in cp_emp2.empno%type, ename out cp_emp2.ename%type, sal out cp_emp2.sal%type)라는 프로시져를 호출하여 사용한다고 가정한다. 이때 해당 프로시져는 IN 파라미터는 number형으로 받고, OUT 파라미터는 각각 varchar2, number형으로 반환한다.
[참고] OUT 파라미터를 받을 때에는 registerOutParameter 함수를 사용한다.
void registerOutParameter(int parameterIndex, int sqlType)
Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType.
CallableStatement cstmt = null;
// 커넥션 얻기
con = getConnection();
// procedure 호출문을 파라미터로 받는 prepareCall 함수 콜
cstmt = con.prepareCall("{call cpemp_select(?, ?, ?)}");
// bind 변수에 값 넣기
// IN 파라미터
cstmt.setInt(1, empno);
// OUT 파라미터
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.NUMERIC);
-----------------------------------------------------------------------------------------------
2-2. 커서가 반환될 때
- create or replace procedure cpemp_select_all(cur_emp out sys_refcursor)라는 프로시져를 호출하여 사용한다고 가정한다. 이때 해당 프로시져는 해당 테이블의 모든 정보를 조회하므로 OUT 파라미터로 커서를 반환한다.
CallableStatement cstmt = null;
// 커넥션 얻기
con = getConnection();
// procedure 호출문을 파라미터로 받는 prepareCall 함수 콜
cstmt = con.prepareCall("{call cpemp_select_all(?)}");
//bind 변수에 값 넣기
//OUT 파라미터
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
================================================================================================
4. query 실행 후 결과 얻기
Statement Method
└boolean execute(String sql);
└int executeUpdate(String sql);
└ResultSet executeQuery(String sql);
- Query의 종류에 따라 반환값이 다르기 때문에 여러 메서드가 준비되어 있다.
- Create문의 경우 성공 여부(true/false)만을 반환하므로 execute() 메서드를 사용한다. 혹은 데이터베이스를 변경하지 않는 명령문의 경우 execute() 메서드를 사용한다.
- insert, delete문의 경우 0~n개의 성공 횟수를 반환하므로 executeUpdate() 메서드를 사용한다. 혹은 데이터베이스는 직접 변경하는 명령문(ex. create, insert, update, delete등)의 경우 executeUpdate() 메서드를 사용한다.
- 여러행을 조회할 경우 커서의 제어권을 가져야하므로 executeQuery()메서드를 사용한다.
Statement의 경우
===================================================================================================
1. insert, update, delete의 경우
--------------------------------------------------------------------------------------------------
// Connection 얻기
con = getConnection();
// 쿼리생성객체(Statement) 얻기
stmt = con.createStatement();
// 쿼리 수행 후 결과 얻기
String insert_cpemp = "insert into cp_emp2(empno, ename, sal) values("
+ cev.getEmpno()
+ ", '"
+ cev.getEname()
+ "', "
+ cev.getSal() + ")";
int cnt = stmt.executeUpdate(insert_cpemp); // DB의 암시적커서가 수행한 행의 수를 반환한다.
if (cnt == 1) {
flag = true;
}
/*
* 쿼리를 수행하는 컬럼의 속성도 숙지하고 있어야한다. 만일 PK속성이 있는 컬럼일경우 cnt == 1라고 조건을
* 주어야한다. why? PK는 중복이 안되기 때문에 1 혹은 예외만을 발생시킨다.
*/
insert -> 1 or 예외(PK, unique, size over)update -> 0 ~ ndelete -> 0 ~ n-> int cnt = stmt.executeUpdate();
cnt = 변경한 행의 수
--------------------------------------------------------------------------------------------------
[참고]
- 문자열 클래스 효율 비교 : String, StringBuffer(ver1.0) < StringBuilder(ver1.5)
- '+'는 컴파일시 append로 바뀌어서 연산된다. 따라서 사용자가 직접 append()로 연결해준것보다 느리다.
ex)
String update_query = "update cp_emp2 set ename = '"+cev.getEname()+"', sal = "+cev.getSal()+" where empno = "+cev.getEmpno();
보다
StringBuilder update_query = new StringBuilder();
update_query.append("update cp_emp2 set enam = '")
.append(cev.getEname()).append("', sal = ")
.append(cev.getSal()).append(" where empno = ")
.append(cev.getEmpno());
가 효율적인 면에서 더 좋다.
[참고] 메서드 체인 : 메서드.메서드.메서드...로 연결되어 있는 형태를 일컫는다.
2. select의 경우
2-1. 전체 조회
--------------------------------------------------------------------------------------------------
// Connection 얻기
con = getConnection();
// 쿼리생성객체 얻기
stmt = con.createStatement();
// 쿼리실행 후 결과 얻기
String select_query = "select empno, ename, sal from cp_emp2";
ResultSet rs = stmt.executeQuery(select_query);
// 반복문안에서 변수 선언하지 말 것! 반복될때마다 변수 생성, 할당이 이루어짐 -> 속도 저하
CpEmpVO cev = null;
while (rs.next()) {
// 레코드가 존재한다면(next()) 커서가 위치하고 있는 레코드의 컬럼값을 가져와서 vo객체에 넣는다.
cev = new CpEmpVO(rs.getInt("empno"), rs.getInt("sal"),
rs.getString("ename"));
// 생성된 VO객체를 관리하기 위해서 List에 추가한다.
list.add(cev);
}
--------------------------------------------------------------------------------------------------
- 명령문을 실행하여 생성된 데이터의 테이블에 대한 엑세스를 제공한다. 테이블 행은 순서대로 검색된다. 한 행에서 해당 열 값을 임의의 순서로 액세스할 수 있다.(자바는 아무것도 가지지않고(실제 데이터는 다른곳에 있음) 제어권만 가짐)
- 데이터의 현재 행을 가리키는 커서를 유지, 관리한다. 맨 처음에 커서는 첫번째 행 앞에 위치한다.
- getXXX 메서드를 제공한다. getXXX 메서드는 현재 행에 대한 열 값을 검색한다. 사용자는 열의 색인 번호 또는 열 이름을 사용하여 열 값을 검색 할 수 있다. 이때 DBMS에서 사용하는 데이터 타입과 Java에서 사용하는 데이터 타입을 맞춰주는 역할도 수행한다.
- 쿼리의 실행결과는 모른다. 실행 결과가 있다면 없을때까지 반복(while)한다. 한번도 실행하지 않을 수 있다.
ex)
while(rs.next()){
// 인덱스, 컬럼명 으로 오버로딩 되어있다. 자료형에 맞게 가져온다.
//int i = rs.getInt(1); or rs.nextInt("deptno");
int i = rs.nextInt("deptno");
String s = re.getString(1);
}
2-2. 선택 조회(해당 사원번호에 해당하는 데이터 조회)
--------------------------------------------------------------------------------------------------
// Connection 얻기
con = getConnection();
// 쿼리생성객체 얻기
stmt = con.createStatement();
// 쿼리 생성 후 결과 얻기
StringBuilder select_query = new StringBuilder();
select_query.append("select empno, ename, sal from cp_emp2 where empno = ")
.append(empno);
ResultSet rs = stmt.executeQuery(select_query.toString());
// 조회되는 행은 0~1이다.(empno가 중복되지 않는다고 가정)
if(rs.next()){ // 조회될 레코드가 있을 때
// 조회 결과로 VO를 생성
cev = new CpEmpVO(empno, rs.getInt("sal"), rs.getString("ename"));
}
--------------------------------------------------------------------------------------------------
PreparedStatement, CallableStatement는 더보기 참고
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
|
package kr.co.sist.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import kr.co.sist.vo.CpDeptVO;
import kr.co.sist.vo.ZipcodeVo;
/**
* DAO(Data Access Object) : 데이터베이스와 작업<br/>
* 화면구성, 연산을 하지 않는다.<br/>
* Singleton pattern을 적용한 클래스 : 실행되는 JVM에서 객체가 하나만 생성되고 사용되도록 만드는 패턴<br/>
* @author sist
*
*/
public class PreparedStatementDAO {
private static PreparedStatementDAO ps_dao;
/**
* 클래스 외부에서 객체화를 할 수 없다.
*/
/**
* 객체를 얻어가는 일<br/>
* 사용법) PreparedStatementDAO ps_dao = PreparedStatementDAO.getInstance();의 형식으로 하나의 객체를 얻어간다.<br/>
* @return
*/
public static PreparedStatementDAO getInstance(){
if(ps_dao == null){ // 객체가 생성되어있지 않다면
ps_dao = new PreparedStatementDAO();
}
return ps_dao;
}
/**
* DB와 연결된 Connection을 얻는 일
* @return 연결된 Connection
* @throws SQLException
*/
private Connection getConnection() throws SQLException{
Connection con = null;
//1. 드라이버 로딩
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2. 커넥션얻기
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "scott";
String pw = "tiger";
con = DriverManager.getConnection(url, id, pw);
return con;
}
/**
* cp_dept테이블에 추가 작업
* @param cdv 추가할 데이터를 가진 VO
* @return 추가 결과 true-성공
* @throws SQLException 추가되지 않은 상황
*/
public boolean insertCpdept(CpDeptVO cdv) throws SQLException{
boolean flag = false;
Connection con = null;
PreparedStatement pstmt = null;
// 스테이트먼트가 인잭션 취약점으로
try{
//2.
con = getConnection();
//3.
String insert_cpdept = "insert into cp_dept(deptno, dname, loc) values (?,?,?)";
pstmt = con.prepareStatement(insert_cpdept);
//바인드 변수에 값 넣기
pstmt.setInt(1, cdv.getDeptno());
pstmt.setString(2, cdv.getDname());
pstmt.setString(3, cdv.getLoc());
//4.
int cnt = pstmt.executeUpdate();
if(cnt == 1){
flag = true;
}
} finally {
//5.
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}
return flag;
}
/**
* 입력되는 부서번호를 가지고 부서명, 위치를 변경하는 일
* @param cdv 변경할 데이터를 가지고 있는 VO
* @return 변경 여부 true - 변경된 행 있음, false - 변경된 행 없음
* @throws SQLException 제약사항에 위배되는 경우
*/
public boolean updateCpDept(CpDeptVO cdv) throws SQLException{
boolean flag = false;
Connection con = null;
PreparedStatement pstmt = null;
try{
//2.
con = getConnection();
//3.
String update_cpdept = "update cp_dept set dname = ?, loc = ? where deptno = ?";
// 바인드 변수를 사용했기 때문에 끊어쓰지 않아도 되고 홀따옴표와 같은거솓 신경 안써도 됨
pstmt = con.prepareStatement(update_cpdept);
// 바인드 변수에 값 넣기
pstmt.setString(1, cdv.getDname());
pstmt.setString(2, cdv.getLoc());
pstmt.setInt(3, cdv.getDeptno());
//4.
int cnt = pstmt.executeUpdate();
if(cnt != 0){
flag = true;
}
} finally {
//5.
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}
return flag;
}
/**
* 입력되는 부서번호로 cp_dept테이블의 레코드를 삭제하는 일
* @param deptno 부서번호
* @return true - 삭제된 행 있음, false - 삭제된 행 없음
* @throws SQLException DB쪽에 문제가 발생하는 경우
*/
public boolean deleteCpDept(int deptno) throws SQLException{
boolean flag = false;
Connection con = null;
PreparedStatement pstmt = null;
try{
//2.
con = getConnection();
//3.
String delete_cpdept = "delete from cp_dept where deptno = ?";
pstmt = con.prepareStatement(delete_cpdept);
// 바인드 변수에 값 넣기(테이블명, 컬럼명은 사용 x)
pstmt.setInt(1, deptno);
//4.
int cnt = pstmt.executeUpdate();
if(cnt != 0){
flag = true;
}
} finally {
//5.
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}
return flag;
}
/**
* cp_dept테이블의 모든 레코드를 조회
* @return 모든 레코드를 가진 List
* @throws SQLException
*/
public List<CpDeptVO> selectAllCpDept() throws SQLException{
List<CpDeptVO> list = new ArrayList<CpDeptVO>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = getConnection();
String select_cpdept = "select deptno, dname, loc from cp_dept";
pstmt = con.prepareStatement(select_cpdept);
rs = pstmt.executeQuery();
CpDeptVO cdv = null; // while을 타지도 않는데 객체화하면 메모리 낭비 따라서 null(데이터가 있을때만 new)
while(rs.next()){ // 조회된 레코드가 있다면 컬럼은 vo에 저장하고 list에 추가
cdv = new CpDeptVO(rs.getInt("deptno"), rs.getString("dname"), rs.getString("loc"));
list.add(cdv);
}
} finally {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}
return list;
}
/**
* 입력된 부서번호로 cp_dept테이블의 부서정보 하나를 조회하는 일
* @param deptno 조회할 부서번호
* @return 조회한 부서명, 위치를 가진 VO
* @throws SQLException DB쪽에서 문제가 발생하는 경우
*/
public CpDeptVO selectCpDept(int deptno) throws SQLException{
CpDeptVO cdv = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = getConnection();
String select_cpdept = "select dname, loc from cp_dept where deptno = ?";
pstmt = con.prepareStatement(select_cpdept);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
if(rs.next()){
cdv = new CpDeptVO(deptno, rs.getString("dname"), rs.getString("loc"));
}
} finally {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(con != null)
con.close();
}
return cdv;
}
// 입력하는 동의 우편번호, 시도, 구군, 동, 번지를 조회한다.
public List<ZipcodeVo> selectZipcode(String dong) throws SQLException{
List<ZipcodeVo> list = new ArrayList<ZipcodeVo>();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = getConnection();
StringBuilder select_zipcode = new StringBuilder();
// 함수로인해 컬럼명이 바뀌면 에러가 발생한다.nvl(bunji,' ')
select_zipcode.append("select zipcode, sido, gugun, dong, nvl(bunji,' ') bunji ").
append("from zipcode").
append(" where dong like '%'||?||'%'");
/*
* '%?%' 부적합한 열 인덱스
* ? 상도동인 애들만
* ?% 문자열이 부적합합니다
* ?'%' 문자열이 올바르게 종료되지 않았습니다.
*
* like의 특수문자(%)를 사용하면 bind변수(?)를 인식하지 못한다.
* 특수문자를 '로 감싸서 일반문자로 만들고 쿼리문에 붙이도록 ||를 사용한다.
*/
pstmt = con.prepareStatement(select_zipcode.toString());
//바인드 변수에 값넣기
pstmt.setString(1, dong);
rs = pstmt.executeQuery();
ZipcodeVo zip = null;
while(rs.next()){
zip = new ZipcodeVo(rs.getString("zipcode"), rs.getString("sido"), rs.getString("gugun"), rs.getString("dong"), rs.getString("bunji"));
list.add(zip);
}
} finally {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(con!= null)
con.close();
}
return list;
}
}
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
|
package day1022;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import oracle.jdbc.OracleTypes;
import day1015.CpEmpVO;
/**
* DAO(Data Access Object) : DB 에 관련된 코드를 정의하는 클래스(데이터 처리의 목적)<br/>
* 화면 구성을 하지 않는다. <br/>
*
* @author sist
*/
public class CallableStatementDAO {
/**
* 드라이버를 로딩하여 DB연결을 얻어내는 일
*
* @return 연결된Connection
*/
private Connection getConnection() {
Connection con = null;
// 1. 드라이버로딩
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}// end catch
// 2. Connection얻기
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "scott";
String pw = "tiger";
try {
con = DriverManager.getConnection(url, id, pw);
} catch (SQLException e) {
e.printStackTrace();
}// end catch
return con;
}// getConnection
/**
* CP_EMP2 테이블에 레코드를 추가 하는 일
*
* @param cev 추가할 값을 가진 VO
* @return 작업결과의 메세지 '실패', '성공'
*/
public String insert(CpEmpVO cev) throws SQLException {
String msg = "";
Connection con = null;
CallableStatement cstmt = null;
try {
//2.
con = getConnection();
//3.
cstmt = con.prepareCall("{call cpemp_insert(?, ?, ?, ?)}");
// 바인드 변수에 값 넣기
// in parameter
cstmt.setInt(1, cev.getEmpno());
cstmt.setString(2, cev.getEname());
cstmt.setInt(3, cev.getSal());
// out parameter
cstmt.registerOutParameter(4, Types.VARCHAR);
//4.
cstmt.execute();
// 외부변수에 입력된 값 받기
msg = cstmt.getString(4);
} finally {
// 5. 연결 끊기
if (cstmt != null) {
cstmt.close();
}
// a-2. null값을 가진 참조형변수를 close()하게 되면 nullPointerException이 발생한다.
// 따라서 이를 방지하는 예방책이 필요하다.
if (con != null) {
con.close();
}
// b-2. 객체가 생성되어있다면 연결이 되어 있지 않더라도 close()로 인한 예외가 발생하지 않는다. close는
// 연결만을 해제한다. 객체는 살아있다.
}// end finally
return msg.trim();
}// insert
/**
* CP_EMP2테이블의 변경<br/>
* 사원번호에 해당하는 사원의 이름과,연봉을 변경한다.
*
* @param cev
* 변경할 값을가진 VO
* @return 변경작업결과 '성공', '실패'
*/
public String update(CpEmpVO cev) throws SQLException {
// 코딩시 기본 마음가짐은 부정적으로
String msg = "";
Connection con = null;
CallableStatement cstmt = null;
try {
//2.
con = getConnection();
//3.
cstmt = con.prepareCall("{call cpemp_update(?, ?, ?)}");
// 바인드 변수에 값 넣기
//in
cstmt.setInt(1, cev.getEmpno());
cstmt.setInt(2, cev.getSal());
//out
cstmt.registerOutParameter(3, Types.CHAR);
//4.
cstmt.execute();
msg = cstmt.getString(3);
//5.
} finally {
// 5. 연결끊기
if (cstmt != null) {
cstmt.close();
}// end if
if (con != null) {
con.close();
}// end if
}// end finally
return msg.trim();
}// update
/**
* CP_EMP2테이블 레코드 삭제<br/>
* 입력한사원번호에 해당하는 레코드 삭제
*
* @param empno
* 삭제할 사원번호
* @return 삭제작업 결과 true-삭제된 행있음, false-삭제된 행 없음
*/
public String delete(int empno) throws SQLException {
String msg = "";
Connection con = null;
CallableStatement cstmt = null;
try {
//2.
con = getConnection();
//3.
cstmt = con.prepareCall("{call cpemp_delete(?, ?)}");
//바인드 변수에 값 넣기
//in
cstmt.setInt(1, empno);
//out
cstmt.registerOutParameter(2, Types.VARCHAR);
//4.
cstmt.execute();
msg = cstmt.getString(2);
//5.
} finally {
// 5. 연결끊기
if (cstmt != null) {
cstmt.close();
}// end if
if (con != null) {
con.close();
}// end if
}// end finally
return msg;
}// update
/**
* CP_EMP2 테이블의 모든 레코드를 조회하여 반환하는 일
*
* @return 모든 레코드를 가진 List
*/
public List<CpEmpVO> select() throws SQLException {
List<CpEmpVO> list = new ArrayList<CpEmpVO>();
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
//2.
con = getConnection();
//3.
cstmt = con.prepareCall("{call cpemp_select_all(?)}");
//바인드 변수에 값 넣기
//out
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
//4.
cstmt.execute();
//외부변수에 설정된 값 얻기
rs = (ResultSet)cstmt.getObject(1);
CpEmpVO cev = null;
while(rs.next()){
cev = new CpEmpVO(rs.getInt("empno"), rs.getInt("sal"), rs.getString("ename"));
// 같은 이름의 객체(cev)를 관리하기 위해서 리스트에 추가
list.add(cev);
}
} finally {
// 5. 연결 끊기
if (rs != null) {
rs.close();
}// end if
if (cstmt != null) {
cstmt.close();
}// end if
if (con != null) {
con.close();
}// end if
}// end finally
return list;
}// select
/**
* 사원번호를 가지고 해당사원이 사원명, 연봉을 조회 하여 VO에 넣고 반환하는 일
*
* @param empno
* @return
*/
public CpEmpVO select(int empno) throws SQLException{
CpEmpVO cev = null;
Connection con = null;
CallableStatement cstmt = null;
try {
con = getConnection();
cstmt = con.prepareCall("{call cpemp_select(?, ?, ?)}");
cstmt.setInt(1, empno);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.NUMERIC);
cstmt.execute();
cev = new CpEmpVO(empno, cstmt.getInt(3), cstmt.getString(2));
} finally {
//5. 연결 끊기
if (cstmt != null) {
cstmt.close();
}// end if
if (con != null) {
con.close();
}// end if
}
return cev;
}// select
}// class
|
===================================================================================================
5. 연결 끊기(Close)
- 제일 중요한 단계. 연결 끊기는 각 클래스의 close()함수를 사용해서 끊어준다. 객체 생성 중에 에러가 발생하였다 해도 반드시 이 단계를 거칠 수 있게 finally로 묶어준다. 이때 close()는 생성한 순서의 반대로 제일 늦게 생성해준 객체부터 해제해 준다. 또한 연결만을 해체하며 객체는 살아있다.
------------------------------------------------------------------------------------
[insert 예제]
Connection con = null;
Statement stmt = null;
try {
// 2. Connection 얻기
con = getConnection();
// b-1. 위가 성공 했다고 가정한다면, 객체가 만들어지고 연결을 가지고 있는 상태
// 3. 쿼리생성객체(Statement) 얻기
stmt = con.createStatement();
// a-1. 만약 에러가 발생하면 에러는 throws되고 stmt는 null값을 가지게 되고
// 4. 쿼리 수행 후 결과 얻기
String insert_cpemp = "insert into cp_emp2(empno, ename, sal) values("
+ cev.getEmpno()
+ ", '"
+ cev.getEname()
+ "', "
+ cev.getSal() + ")";
int cnt = stmt.executeUpdate(insert_cpemp);
if (cnt == 1) { // insert성공시 플래그 변수를 true로 변경
flag = true;
}// end if
} finally { // 반드시 실행되게 finally로 묶어 준다.
// 5. 연결 끊기
if (stmt != null) {
stmt.close();
}
// a-2. null값을 가진 참조형변수를 close()하게 되면 NullPointerException이 발생한다.
// 따라서 이를 방지하기 위해 close()하기전 조건문을 사용하여 null인지 검사 후 실행한다.
if (con != null) {
con.close();
}
// b-2. 객체가 생성되어있다면 연결이 되어 있지 않더라도 close()로 인한 예외가 발생하지 않는다. close는
// 연결만을 해제한다. 객체는 살아있다.
}// end finally
------------------------------------------------------------------------------------
'java > 정리' 카테고리의 다른 글
| [java] JDBC classpath 환경설정, 드라이버 로딩 성공 여부 체크 (0) | 2014.10.14 |
|---|---|
| java & eclipse 오라클 JDBC 환경설정(윈도우) (0) | 2014.10.14 |