package kr.co.js.jdbc.service;
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.js.jdbc.domain.MemberVO;
/**
* DAO 구현 클래스(concrete class)
*
* @author Administrator
*
*/
public class MemberDAOServiceImpl implements MemberDAOService {
// DB 연결정보
private static final String oracleDriver = "oracle.jdbc.OracleDriver";
private static final String oracleURL = "jdbc:oracle:thin:@localhost:1521:xe";
private static final String oracleID = "greenjava";
private static final String oraclePW = "greenjava";
// 싱글턴(singleton) 패턴
private static MemberDAOServiceImpl instance = null;
private MemberDAOServiceImpl() {
}
public static final MemberDAOServiceImpl getInstance() {
if (instance == null) {
instance = new MemberDAOServiceImpl();
}
return instance;
}
/**
* DB 연결(connect)
*
* @return DB연결 객체
*/
public Connection getConnection() {
Connection conn = null;
try {
Class.forName(oracleDriver);
try {
conn = DriverManager.getConnection(oracleURL, oracleID, oraclePW);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
System.out.println("DAO getConnection : " + oracleDriver);
e.printStackTrace();
}
return conn;
}
/**
* DB연결 자원 반납
*
* @param conn DB연결 객체
* @param pstmt SQL 해석 객체
* @param rs 결과셋 객체
*/
public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
try {
if (rs != null)
rs.close();// 유효성검사 후 자원 반납
if (pstmt != null)
pstmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
System.out.println("DB close");
e.printStackTrace();
}
}
//회원정보 삽입(가입,생성)
@Override
public void insertMember(MemberVO memberVo) {
Connection conn = null;// 연결 객체
PreparedStatement pstmt = null;// SQL 해석 객체
String sql = "INSERT INTO MEMBER VALUES (?,?,?,?,sysdate)";
conn = this.getConnection(); // DB연결
try {
pstmt = conn.prepareStatement(sql); // SQL 해석
pstmt.setString(1, memberVo.getMemberId());
pstmt.setString(2, memberVo.getMemberName());
pstmt.setString(3, memberVo.getMemberPw());
pstmt.setString(4, memberVo.getMemberAddr());
// pstmt.setDate(5, memberVo.getMemberJoindate());
if (pstmt.executeUpdate() == 1) {
System.out.println("성공적으로 회원 정보를 저장하였습니다.");
} else {
System.out.println("회원정보 저장에 실패 했습니다.");
}
} catch (SQLException e) {
System.out.println("insertMember SQLE : ");
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, null);
}
}
//전체회원 정보 조회(검색)
@Override
public List<MemberVO> getAllMembers() throws Exception {
List<MemberVO> members = new ArrayList<MemberVO>();//리턴값을 만들어줘야지~!
Connection conn = this.getConnection();// 연결 객체
PreparedStatement pstmt = null;// SQL 해석 객체
String sql = "SELECT * FROM member";
ResultSet rs = null;// row(결과) 집합(set)
// MemberVO member = new MemberVO();//주의 같은 레코드 반복 인쇄! =>오류!
MemberVO member =null;
try {
pstmt = conn.prepareStatement(sql); // SQL 해석
rs = pstmt.executeQuery();
while (rs.next()) {
member = new MemberVO();//대처법 : 레코드 (record,row)
member.setMemberId(rs.getString("member_id"));
member.setMemberName(rs.getString("member_name"));
member.setMemberPw(rs.getString("member_pw"));
member.setMemberAddr(rs.getString("member_addr"));
member.setMemberJoindate(rs.getDate("member_joindate"));
members.add(member);
}
// SQL 실행
} catch (Exception e) {
System.out.println("get All Member E : ");
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return members;
}
//개별 회원 정보 조회(검색)
@Override
public MemberVO getMember(String memberId) throws Exception {
Connection conn = this.getConnection();// 연결 객체
PreparedStatement pstmt = null;// SQL 해석 객체
String sql = "SELECT * FROM member WHERE member_id=?";
ResultSet rs = null;// row(결과) 집합(set)
MemberVO member = new MemberVO();
try {
pstmt = conn.prepareStatement(sql); // SQL 해석
pstmt.setString(1, memberId);
rs = pstmt.executeQuery();
while (rs.next()) {
member.setMemberId(rs.getString("member_id"));
member.setMemberName(rs.getString("member_name"));
member.setMemberPw(rs.getString("member_pw"));
member.setMemberAddr(rs.getString("member_addr"));
member.setMemberJoindate(rs.getDate("member_joindate"));
}
} catch (Exception e) {
System.out.println("select Member E : ");
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return member;
}
//개별 회원 정보 수정(갱신,변경)
@Override
public void updateMember(MemberVO memberVo) throws Exception {
Connection conn = this.getConnection();// 연결 객체
PreparedStatement pstmt = null;// SQL 해석 객체
// Statement 사용시 SQL
/*
* String sql = "UPDATE member SET member_pw = '"+
* memberVO.getMemberPw() +"'," +
* "member_addr = '"+memberVO.getMemberAddr()+"' " +
* "WHERE member_id = '"+memberVO.getMemberId()+"'";
*/
// PreparedStatement 사용시 SQL
/*
* String sql = "UPDATE member SET member_pw=?," +
* " member_addr=? " + " WHERE member_id=?";
*/
StringBuilder sql = new StringBuilder();
/*
* sql.append("UPDATE member SET "); sql.append(" member_pw=?,");
* sql.append(" member_addr=? "); sql.append("WHERE member_id=?");
*/
sql.append("UPDATE member SET ")
.append(" member_pw=?,")
.append(" member_addr=?")
.append(" WHERE member_id=?");
try {
pstmt = conn.prepareStatement(sql.toString());
// SQL 인자처리
pstmt.setString(1, memberVo.getMemberPw());
pstmt.setString(2, memberVo.getMemberAddr());
pstmt.setString(3, memberVo.getMemberId());
// SQL 실행
if (pstmt.executeUpdate() == 1) {
System.out.println("회원정보 수정에 성공하였습니다.");
} else {
System.out.println("회원정보 수정에 실패하였습니다.");
}
} catch (Exception e) {
System.out.println("uptateMember E : ");
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, null);
}
}
//개별 회원 정보 삭제
@Override
public void deleteMember(String memberId) throws Exception {
Connection conn = this.getConnection();// 연결 객체
PreparedStatement pstmt = null;// SQL 해석 객체
String sql = "DELETE member WHERE member_id=?";
try {
pstmt = conn.prepareStatement(sql); // SQL 해석
pstmt.setString(1, memberId);
if(pstmt.executeUpdate()==1){
System.out.println("회원정보를 성공적으로 삭제했습니다.");
}else{
System.out.println("회원정보 삭제에 실패했습니다.");
}
} catch (Exception e) {
System.out.println("delete Member E : ");
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, null);
}
return;
}
}
