목차 돌아가기
Java 언어를 사용해서 DB와 통신하기 위한 방벙베 대해서 학습
23-1 기본 SQL
CREATE TABLE member (
memId VARCHAR2(10) CONSTARINT mmdId_pk PRIMARY KEY,
memPw VARCHAR2(10),
memMail VARCHAR2(15),
memPurcNum NUMBER(3) DEFAULT 0 CONSTARINT memPurcNum_ck CHECK (memPurcNum) < 3 )
)l
INSERT INTO member (memId, memPw, memMail)
values ('b', 'bb', 'bbb@gmail.com');
테이블에서 'memId'가 'b'인 회원을 삭제한다.
DELETE FROM memeber WHERE memId = 'b';
SELECT * FROM member;
DROP TABLE member;
23-2 JDBC
JDBC 흐름
중요내용
SQL작성 및 전송 까지는 개발자가 직접해야한다. (매번 바뀌니깐)하지만 자원해제는 개발자가 딱 한 번만만 해도 된다. 그래서 JdbcTemplate가 나오는 것이다.
JDBC INSERT
@Override
public int memberInsert(Member member) {
int result = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userid, userpw);
String sql = "INSERT INTO member (memId, memPw, memMail) values (?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, member.getMemId());
pstmt.setString(2, member.getMemPw());
pstmt.setString(3, member.getMemMail());
result = pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
JDBC SELECT
@Override
public Member memberSelect(Member member) {
Member mem = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userid, userpw);
String sql = "SELECT * FROM member WHERE memId = ? AND memPw = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, member.getMemId());
pstmt.setString(2, member.getMemPw());
rs = pstmt.executeQuery();
while (rs.next()) {
String memId = rs.getString("memid");
String memPw = rs.getString("mempw");
String memMail = rs.getString("memMail");
int memPurcNum = rs.getInt("memPurcNum");
mem = new Member();
mem.setMemId(memId);
mem.setMemPw(memPw);
mem.setMemMail(memMail);
mem.setMemPurcNum(memPurcNum);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return mem;
}
JDBC UPDATE
@Override
public int memberUpdate(Member member) {
int result = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userid, userpw);
String sql = "UPDATE member SET memPw = ?, memMail = ? WHERE memId = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, member.getMemPw());
pstmt.setString(2, member.getMemMail());
pstmt.setString(3, member.getMemId());
result = pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
JDBC DELETE
@Override
public int memberDelete(Member member) {
int result = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userid, userpw);
String sql = "DELETE member WHERE memId = ? AND memPw = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, member.getMemId());
pstmt.setString(2, member.getMemPw());
result = pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
소스들의 자원해제 부분이 너무 중복된 코드들이 많다라는 단점이 발생한다. 해결 : JDBC Template 사실상 JDBC는 쓸 일이 없고 JDBC Template를 사용한다.
JDBC Template
Java언어를 사용해서 DB 연동하는 방법 학습
24-1 JDBC의 단점을 보완한 JdbcTemplate
반복되고 동일한 작업을 막기 위해 'JdbcTemplte'를 사용
24-2 DataSource클래스
제공 클래스
c3p0 : 커넥션 풀 할 때 이용
pom.xml 의존설정 maven에 없기 때문에 pom.xml에 추가해줘야 하는 내용
<!-- DB -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>12.1.0.2</version>
</dependency>
<!-- JDBC Template 사용 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
<!-- c3p0 사용 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5</version>
</dependency>
Template 사용 Template 사용 하므로 연결부터 해제까지 각각 안해줘도 된다. 방법은 3가지 정도 있다.
방법(3가지) 1. PreparedStateMentSetter().. 2. PreparedSattementCreator().. 3. RowMapper<Member>()..
//데이터소스(데이터정보저장) 만들기
private DriverManegerDataSource datasource;
//처음에 초기 설정 한 번만 해준다.
public MemberDao(){
datasource = new DriverManegerDataSource();
datasource.setDriver(driver);
datasource.setJdbcUrl(url);
datasource.setUser(userId);
datasource.setPassword(userpw);
template = new JdbcTemplate();
template.setDataSource(datasource);
}
//1. 주입 insert
@Override
public int memberInsert(Member member){
...
int result = template.update(sql,member.getMemId(),member.getMempw(),member.getMail());
...
return result;
}
//2. 주입 Select
@Override
public Member memberSelect(final Member member) {
List<Member> members = null;
//final로 해주면 다른 곳에서 사용 못함
final String sql = "SELECT * FROM member WHERE memId = ? AND memPw = ?";
members = template.query(sql, new Object[]{member.getMemId(), member.getMemPw()}, new RowMapper<Member>() {
@Override
public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
Member mem = new Member();
mem.setMemId(rs.getString("memId"));
mem.setMemPw(rs.getString("memPw"));
mem.setMemMail(rs.getString("memMail"));
mem.setMemPurcNum(rs.getInt("memPurcNum"));
return mem;
}
});
if(members.isEmpty())
return null;
return members.get(0);
}