✿∘˚˳°∘°
27일차 : JDBC - Singleton 본문
20230103
-- 테이블 추가 --
CREATE TABLE DEL_MEMBER(
MEMBER_ID VARCHAR2(20) PRIMARY KEY,
MEMBER_NAME VARCHAR2(30) NOT NULL,
MEMBER_PHONE CHAR(13),
OUT_DATE DATE
);현재 삭제로직 : 삭제할 아이디를 입력받아서 MEMBER_TBL에서 삭제
바뀔 삭제로직 : 1. 삭제할 아이디 입력받기
2. MEMBER_TBL에서 삭제할 회원의 아이디, 이름, 전화번호 조회
3. MEMBER_TBL에서 회원 삭제
4. DEL_MEMBER에 삭제회원 INSERT
변경된 MemberController.java - deleteMember()
public void deleteMember() {
System.out.println("\n----- 회원 탈퇴 -----\n");
System.out.print("아이디를 입력하세요 : ");
String memberId = sc.next();
Member m = dao.selectMemberId(memberId);
if(m == null) {
System.out.println("회원정보를 찾을 수 없습니다.");
return;
}
int result = dao.deleteMember(memberId);
int result2 = dao.insertDel(m);
if(result > 0 && result2 > 0) {
System.out.println("탈퇴 성공");
}else {
System.out.println("탈퇴 실패");
}
}dao.selectMemberId(memberId)를 통해 2. 삭제할 회원의 정보를 조회
기존 dao.deleteMember(memberId)를 통해 3. 회원삭제
dao.insertDel(m)을 이용하여 4.DEL_MEMBER 테이블에 삭제된 회원 INSERT
추가된 MemberDao.java - insertDel()
public int insertDel(Member m) {
Connection conn = null;
PreparedStatement pstmt = null;
int result = 0;
String query = "insert into del_member values(?, ?, ?, sysdate)";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberId());
pstmt.setString(2, m.getMemberName());
pstmt.setString(3, m.getMemberPhone());
result = pstmt.executeUpdate();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}문제점 ) INSERT의 결과가 에러가 발생해도 멤버 삭제가 이루어진다
(메소드가 따로이기 때문에 삭제 후 바로 COMMIT이 되어버림. 트랜잭션 관리가 정상적으로 이루어지지 않는다.)
그러므로 하나의 CONNECTION을 통해서 두 개의 결과를 확인하고 COMMIT/ROLLBACK을 해야 함
이제 CONNECTION은 DAO에서 만들지 않는다. - 작업을 모아서 처리해줄 클래스를 추가
--- 회원관리프로그램 만들기 : connection작업을 다른 클래스에서 해주기 ---
[ JAVA 클래스 ] - [ memberStart.java / member.java / memberConroller.java / memberDao.java / memberService() ]
-- memberService() : CONNECTION 작업을 해줄 클래스 / Dao를 호출하여 DB관련 작업을 끝내고 값을 Controller에 넘김
-- Service가 하는 일 : Connection 관리(Dao에 있던 Connection부분만 떼어옴)
Connection생성, close, commit, rollback
Member.java
package kr.or.member.vo;
public class Member {
private String memberId;
private String memberPw;
private String memberName;
private String memberAddr;
private String memberPhone;
private int memberAge;
private String memberGender;
private String enrollDate; //가입일을 String으로 변경
public Member() {
super();
// TODO Auto-generated constructor stub
}
public Member(String memberId, String memberPw, String memberName, String memberAddr, String memberPhone,
int memberAge, String memberGender) {
super();
this.memberId = memberId;
this.memberPw = memberPw;
this.memberName = memberName;
this.memberAddr = memberAddr;
this.memberPhone = memberPhone;
this.memberAge = memberAge;
this.memberGender = memberGender;
}
public Member(String memberId, String memberPw, String memberName, String memberAddr, String memberPhone,
int memberAge, String memberGender, String enrollDate) {
super();
this.memberId = memberId;
this.memberPw = memberPw;
this.memberName = memberName;
this.memberAddr = memberAddr;
this.memberPhone = memberPhone;
this.memberAge = memberAge;
this.memberGender = memberGender;
this.enrollDate = enrollDate;
}
public String getMemberId() {
return memberId;
}
public void setMemberId(String memberId) {
this.memberId = memberId;
}
public String getMemberPw() {
return memberPw;
}
public void setMemberPw(String memberPw) {
this.memberPw = memberPw;
}
public String getMemberName() {
return memberName;
}
public void setMemberName(String memberName) {
this.memberName = memberName;
}
public String getMemberAddr() {
return memberAddr;
}
public void setMemberAddr(String memberAddr) {
this.memberAddr = memberAddr;
}
public String getMemberPhone() {
return memberPhone;
}
public void setMemberPhone(String memberPhone) {
this.memberPhone = memberPhone;
}
public int getMemberAge() {
return memberAge;
}
public void setMemberAge(int memberAge) {
this.memberAge = memberAge;
}
public String getMemberGender() {
return memberGender;
}
public void setMemberGender(String memberGender) {
this.memberGender = memberGender;
}
public String getEnrollDate() {
return enrollDate;
}
public void setEnrollDate(String enrollDate) {
this.enrollDate = enrollDate;
}
}
MemberController의 전역변수 / 생성자 선언
private Scanner sc;
private MemberService service;
public MemberController() {
super();
sc = new Scanner(System.in);
service = new MemberService();
//MemberController 가 service를 호출하고 MemberService가 dao를 호출
}MemberService에서 전역변수 / 생성자 선언
private MemberDao dao;
public MemberService() {
super();
dao = new MemberDao();
}0. MemberController - main()
public void main() {
while(true) {
System.out.println("\n----- 회원 관리 프로그램 v3 -----\n");
System.out.println("1. 전체 회원 조회");
System.out.println("2. 아이디로 회원 조회");
System.out.println("3. 이름으로 회원 조회");
System.out.println("4. 회원 가입");
System.out.println("5. 회원 정보 수정");
System.out.println("6. 회원 탈퇴");
System.out.println("0. 프로그램 종료");
System.out.print("선택 >> ");
int sel = sc.nextInt();
switch(sel) {
case 2:
selectMemberId();
break;
case 5:
updateMember();
break;
case 6:
deleteMember();
break;
case 0:
System.out.println("프로그램을 종료합니다.");
return;
default:
System.out.println("잘못 입력 하셨습니다.");
}
}
}
2. 아이디로 회원 조회
MemberController.java
public void selectMemberId() {
//쿼리 : select * from member_tbl where member_id = ?
//쿼리문을 가정먼저 생각하는 이유 : 사용자에게 입력받을 값이 있는 지 확인하기 위해
System.out.print("조회할 회원 아이디 입력 : ");
String memberId = sc.next();
//controller는 아이디를 주고 요청만 할뿐 이제 나머지는 service가 dao와 알아서 작업해서 Member형식으로 리턴해줄것
Member m = service.selectMemberId(memberId);
if( m == null ) {
System.out.println("회원을 조회할 수 없습니다.");
}else {
System.out.println("아이디 : "+m.getMemberId());
System.out.println("비밀번호 : "+m.getMemberPw());
System.out.println("이름 : "+m.getMemberName());
System.out.println("주소 : "+m.getMemberAddr());
System.out.println("전화번호 : "+m.getMemberPhone());
System.out.println("나이 : "+m.getMemberAge());
System.out.println("성별 : "+m.getMemberGender());
System.out.println("가입일 : "+m.getEnrollDate());
}
}MemberService.java
public Member selectMemberId(String memberId) {
Connection conn = null;
Member m = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "jdbc","1234");
m = dao.selectMemberId(conn, memberId);
//조회를 위한 코드이므로 추가작업(commit, rollback)이 없다
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return m;
}MemberDao.java
public Member selectMemberId(Connection conn, String memberId) {
PreparedStatement pstmt = null;
ResultSet rset = null;
Member m = null;
String query = "select * from member_tbl where member_id = ?";
try {
//1.드라이버연결 2.dbms연결(connection) 작업이 service에서 다 처리되고 매개변수로 conn을 받음
//dao에서는 바로 pstmt(3번)부터 시작
pstmt = conn.prepareStatement(query);
pstmt.setString(1, memberId);
rset = pstmt.executeQuery();
if(rset.next()) {
m = new Member();
m.setMemberId(rset.getString("member_id"));
m.setMemberPw(rset.getString("member_pw"));
m.setMemberName(rset.getString("member_name"));
m.setMemberAddr(rset.getString("member_addr"));
m.setMemberPhone(rset.getString("member_phone"));
m.setMemberAge(rset.getInt("member_age"));
m.setMemberGender(rset.getString("member_gender"));
m.setEnrollDate(rset.getString("enroll_date"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
rset.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return m;
}
5. 회정 정보 수정
MemberController.java
public void updateMember() {
//쿼리문 : update member_tbl set member_pw = ?, member_addr = ?, member_phone = ? where member_id = ?
System.out.print("아이디 입력 : ");
String memberId = sc.next();
System.out.print("변경할 비밀번호 입력 : ");
String memberPw = sc.next();
System.out.print("변경할 주소 입력 : ");
sc.nextLine();
String memberAddr = sc.nextLine();
System.out.print("변경할 전화번호 입력 : ");
String memberPhone = sc.next();
Member m = new Member();
m.setMemberId(memberId);
m.setMemberPw(memberPw);
m.setMemberAddr(memberAddr);
m.setMemberPhone(memberPhone);
int result = service.updateMember(m);
if(result > 0) {
System.out.println("변경 완료");
} else {
System.out.println("변경 실패");
}
}
MemberService.java
public int updateMember(Member m) {
Connection conn = null;
int result = 0;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "jdbc", "1234");
result = dao.updateMember(conn, m);
if(result > 0) {
conn.commit();
}else {
conn.rollback();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
MemberDao.java
public int updateMember(Connection conn, Member m) {
PreparedStatement pstmt = null;
int result = 0;
String query = "update member_tbl set member_pw = ?, member_addr = ?, member_phone = ? where member_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberPw());
pstmt.setString(2, m.getMemberAddr());
pstmt.setString(3, m.getMemberPhone());
pstmt.setString(4, m.getMemberId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
6. 회원 정보 삭제
MemberController.java
public void deleteMember() {
//select * from member_tbl where member_id = ?
//delete from member_tbl where member_id = ?
//insert into del_member values(아이디, 이름, 전화번호, sysdate); - 입력x 조회o
System.out.print("아이디 입력 : ");
String memberId = sc.next();
int result = service.deleteMember(memberId);
if(result > 0) {
System.out.print("탈퇴 완료");
}else {
System.out.println("탈퇴 실패");
}
}
MemberService.java
public int deleteMember(String memberId) {
Connection conn = null;
int result = 0;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc","1234");
Member m = dao.selectMemberId(conn, memberId);
if(m != null) {
result = dao.deleteMember(conn, memberId);
if(result > 0) {
result = dao.delMemberInsert(conn, m);
if(result > 0) {
//조회도 되고, 삭제도 되고, insert도 된 후 commit
conn.commit();
} else {
conn.rollback();
}
}else {
//member_tbl에 delete가 실패하면, del_member에 insert를 하지않고
//member_tbl에서 삭제된 데이터를 복구
conn.rollback();
}
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
MemberDao.java
public int delMemberInsert(Connection conn, Member m) {
PreparedStatement pstmt = null;
int result = 0;
String query = "insert into del_member values(?, ?, ?, sysdate)";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberId());
pstmt.setString(2, m.getMemberName());
pstmt.setString(3, m.getMemberPhone());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}기존처럼 INSERT에서 실패해도 삭제는 COMMIT 되어 버리는 상황을 개선하여,
SELECT/DELETE/INSERT 작업이 모두 이루어져야만 COMMIT이 되는 코드로 변경
--- 회원관리프로그램 만들기 : SINGLETON ---
[ JAVA 클래스 ] - [ memberStart.java / member.java / memberConroller.java /
memberDao.java / memberService() / JDBCTemplate() ]
싱글톤(Singleton) 패턴
: 디자인 패턴 중 하나, 생성자가 여러 차례 호출되더라도 생성되는 객체는 1 개인구조.
: 프로그램이 구동되는 동안 1개의 객체가 계속 사용됨
JDBCTemplate() : Singleton패턴을 이용하여 동작하게 함
1. Connetcion 생성 후 리턴
2. Commit 처리 / Rollback 처리
3. 자원반환(conn, stmt, rset)
>> 총 6개의 메소드 생성
Member.java
package kr.or.iei.member.vo;
public class Member {
private String memberId;
private String memberPw;
private String memberName;
private String memberAddr;
private String memberPhone;
private int memberAge;
private String memberGender;
private String enrollDate;
public Member() {
super();
// TODO Auto-generated constructor stub
}
public Member(String memberId, String memberPw, String memberName, String memberAddr, String memberPhone,
int memberAge, String memberGender) {
super();
this.memberId = memberId;
this.memberPw = memberPw;
this.memberName = memberName;
this.memberAddr = memberAddr;
this.memberPhone = memberPhone;
this.memberAge = memberAge;
this.memberGender = memberGender;
}
public Member(String memberId, String memberPw, String memberName, String memberAddr, String memberPhone,
int memberAge, String memberGender, String enrollDate) {
super();
this.memberId = memberId;
this.memberPw = memberPw;
this.memberName = memberName;
this.memberAddr = memberAddr;
this.memberPhone = memberPhone;
this.memberAge = memberAge;
this.memberGender = memberGender;
this.enrollDate = enrollDate;
}
public String getMemberId() {
return memberId;
}
public void setMemberId(String memberId) {
this.memberId = memberId;
}
public String getMemberPw() {
return memberPw;
}
public void setMemberPw(String memberPw) {
this.memberPw = memberPw;
}
public String getMemberName() {
return memberName;
}
public void setMemberName(String memberName) {
this.memberName = memberName;
}
public String getMemberAddr() {
return memberAddr;
}
public void setMemberAddr(String memberAddr) {
this.memberAddr = memberAddr;
}
public String getMemberPhone() {
return memberPhone;
}
public void setMemberPhone(String memberPhone) {
this.memberPhone = memberPhone;
}
public int getMemberAge() {
return memberAge;
}
public void setMemberAge(int memberAge) {
this.memberAge = memberAge;
}
public String getMemberGender() {
return memberGender;
}
public void setMemberGender(String memberGender) {
this.memberGender = memberGender;
}
public String getEnrollDate() {
return enrollDate;
}
public void setEnrollDate(String enrollDate) {
this.enrollDate = enrollDate;
}
}
JDBCTemplate.java
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTemplate {
// 이 클래스에서 해야할 일
// 1. Connetcion 생성 후 리턴
// 2. Commit 처리 / Rollback 처리
// 3. 자원반환(conn, stmt, rset)
// 4. 총 6개의 메소드 생성
//Connetcion 생성 후 리턴
public static Connection getConnection() {
//static : 객체를 만들어서 쓰는게 아니라 그냥 실행하자마자 쓰는것이기 때문에 static을 붙여줘야함
// static으로 선언할 경우, 자바가 컴파일되는 시점(클래스로드시점)에 정의 됨
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbc", "1234");
//자동으로 commit되는 기능을 비활성화(내가 트랜잭션을 관리하기 위하여)
conn.setAutoCommit(false);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//commit 처리
public static void commit(Connection conn) {
try {
//conn.isClosed();//Connetion이 닫혔는지 확인
if(conn != null && !conn.isClosed()) {
conn.commit();//Connection이 정상일 경우에만 commit
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//rollback 처리
public static void rollback(Connection conn) {
try {
//conn.isClosed();//Connetion이 닫혔는지 확인
if(conn != null && !conn.isClosed()) {
conn.rollback();//Connection이 정상일 경우에만 commit
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//자원반환(conn, stmt, rset)
public static void close(Connection conn) {
try {
if(conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(Statement stmt) {
//PreparedStatement 는 Statement를 상속했기 때문에
// 확장성을 고려하여 Statement를 close
try {
if(stmt != null && !stmt.isClosed()) {
stmt.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void close(ResultSet rset) {
try {
if(rset != null && !rset.isClosed()) {
rset.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}MemberConroller - 전역변수 / 생성자
private Scanner sc;
private MemberService service;
public MemberController() {
super();
sc = new Scanner(System.in);
service = new MemberService();
}MemberService.java - 전역변수 / 생성자
private MemberDao dao;
public MemberService() {
super();
dao = new MemberDao();
}
0. MemberController.java - main()
public void main() {
while(true) {
System.out.println("\n----- 회원 관리 프로그램 v5 -----\n");
System.out.println("1. 전체 회원 조회");
System.out.println("2. 아이디로 회원 조회");
System.out.println("3. 이름으로 회원 조회");
System.out.println("4. 회원 정보 등록");
System.out.println("5. 회원 정보 수정");
System.out.println("6. 회원 정보 삭제");
System.out.println("0. 프로그램 종료");
System.out.print("선택 >>");
int sel = sc.nextInt();
switch(sel) {
case 1:
selectAllMember();
break;
case 2:
selectMemberId();
break;
case 3:
selectMemberName();
break;
case 4:
insertMember();
break;
case 5:
updateMember();
break;
case 6:
deleteMember();
break;
case 0:
System.out.println("프로그램을 종료합니다.");
return;
}
}
}
1. 전체 회원 조회
MemberController.java
public void selectAllMember() {
//쿼리 : select * from member_tbl
ArrayList<Member> list = service.selectAllMember();
System.out.println("\n----- 전체 회원 조회 -----\n");
System.out.println("아이디\t비밀번호\t이름\t주소\t\t전화번호\t나이\t성별\t가입일");
System.out.println("---------------------------------------------------------------");
for(Member m : list) {
System.out.print(m.getMemberId()+"\t");
System.out.print(m.getMemberPw()+"\t");
System.out.print(m.getMemberName()+"\t");
System.out.print(m.getMemberAddr()+"\t");
System.out.print(m.getMemberPhone()+"\t");
System.out.print(m.getMemberAge()+"\t");
System.out.print(m.getMemberGender()+"\t");
System.out.println(m.getEnrollDate());
}
}
MemberService.java
public ArrayList<Member> selectAllMember() {
Connection conn = JDBCTemplate.getConnection();
ArrayList<Member> list = dao.selectAllMember(conn);
JDBCTemplate.close(conn);
return list;
}
MemberDao.java
public ArrayList<Member> selectAllMember(Connection conn) {
PreparedStatement pstmt = null;
ResultSet rset = null;
ArrayList<Member> list = new ArrayList<Member>();
String query = "select * from member_tbl";
try {
pstmt = conn.prepareStatement(query);
rset = pstmt.executeQuery();
while(rset.next()) {
Member m = new Member();
m.setMemberId(rset.getString("member_id"));
m.setMemberPw(rset.getString("member_pw"));
m.setMemberName(rset.getString("member_name"));
m.setMemberAddr(rset.getString("member_addr"));
m.setMemberPhone(rset.getString("member_phone"));
m.setMemberAge(rset.getInt("member_age"));
m.setMemberGender(rset.getString("member_gender"));
m.setEnrollDate(rset.getString("enroll_date"));
list.add(m);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(rset);
JDBCTemplate.close(pstmt);
}
return list;
}
2. 아이디로 회원 조회
MemberController.java
public void selectMemberId() {
System.out.print("아이디 입력 : ");
String memberId = sc.next();
Member m = service.selectMemberId(memberId);
if(m == null) {
System.out.print("회원정보를 찾을 수 없습니다.");
}else {
System.out.println("아이디 : "+m.getMemberId());
System.out.println("비밀번호 : "+m.getMemberPw());
System.out.println("이름 : "+m.getMemberName());
System.out.println("주소 : "+m.getMemberAddr());
System.out.println("전화번호 : "+m.getMemberPhone());
System.out.println("나이 : "+m.getMemberAge());
System.out.println("성별 : "+m.getMemberGender());
System.out.println("가입일 : "+m.getEnrollDate());
}
}
MemberService.java
public Member selectMemberId(String memberId) {
Connection conn = JDBCTemplate.getConnection(); //커넥션 생성완료
Member m = dao.selectMemberId(conn, memberId);
JDBCTemplate.close(conn);
return m;
}
MemberDao.java
public Member selectMemberId(Connection conn, String memberId) {
PreparedStatement pstmt = null;
ResultSet rset = null;
Member m = null;
String query = "select * from member_tbl where member_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, memberId);
rset = pstmt.executeQuery();
if(rset.next()) {
m = new Member();
m.setMemberId(rset.getString("member_id"));
m.setMemberPw(rset.getString("member_pw"));
m.setMemberName(rset.getString("member_name"));
m.setMemberAddr(rset.getString("member_addr"));
m.setMemberPhone(rset.getString("member_phone"));
m.setMemberAge(rset.getInt("member_age"));
m.setMemberGender(rset.getString("member_gender"));
m.setEnrollDate(rset.getString("enroll_date"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(rset);
JDBCTemplate.close(pstmt);
}
return m;
}
3. 이름으로 회원 조회
MemberController.java
public void selectMemberName() {
//쿼리 : select * from member_tbl where member_name like %?%
System.out.print("이름 입력 : ");
String memberName = sc.next();
ArrayList<Member> list = service.selectMemberName(memberName);
if(list.size() > 0) {
System.out.println("아이디\t비밀번호\t이름\t주소\t\t전화번호\t나이\t성별\t가입일");
System.out.println("---------------------------------------------------------------");
for(Member m : list) {
System.out.print(m.getMemberId()+"\t");
System.out.print(m.getMemberPw()+"\t");
System.out.print(m.getMemberName()+"\t");
System.out.print(m.getMemberAddr()+"\t");
System.out.print(m.getMemberPhone()+"\t");
System.out.print(m.getMemberAge()+"\t");
System.out.print(m.getMemberGender()+"\t");
System.out.println(m.getEnrollDate());
}
}else {
System.out.println("회원정보를 찾을 수 없습니다.");
}
}
MemberService.java
public ArrayList<Member> selectMemberName(String memberName) {
Connection conn = JDBCTemplate.getConnection();
ArrayList<Member> list = dao.selectMemberName(conn, memberName);
JDBCTemplate.close(conn);
return list;
}
MemberDao.java
가입일을 Date가 아닌 String 타입으로 선언한 이유
-- DB날짜 형식 저장된 데이터를 원하는 형식으로 출력하기위해
방법1. ORACLE에서 TO_CHAR 함수사용 : java에서는 문자열(String)로 처리( java의 쿼리문에서 작성해줘야함)
방법2. ORACLE에서 DATE 타입으로 처리 : java에서 java.sql.Date로 처리 -> SimpleDateFormat 원하는 형식으로 변경
public ArrayList<Member> selectMemberName(Connection conn, String memberName) {
PreparedStatement pstmt = null;
ResultSet rset = null;
ArrayList<Member> list = new ArrayList<Member>();
//String query = "select * from member_tbl where member_name like ?";
String query = "select member_id, member_pw, member_name, member_addr, member_phone, member_age, member_gender, to_char(enroll_date, 'yyyy\"년\"mm\"월\"dd\"일\"') as enroll_date from member_tbl where member_name like ?";
/*
select member_id, member_pw, member_name, member_addr,
member_phone, member_age, member_gender,
to_char(enroll_date, 'yyyy\"년\"mm\"월\"dd\"일\"') as enroll_date
from member_tbl where member_name like ?";
--> enroll_date의 형식을 지정해주기위한 방법1. ORACLE에서 TO_CHAR를 사용하는 방법
--> 별명을 설정해주지 않을 경우, rset.getString("컬럼명")에서 컬럼명에
--> to_char(enroll_date, 'yyyy\"년\"mm\"월\"dd\"일\"')을 가져와야해서 코드가 복잡해진다.
--> ['yyyy\"년\"mm\"월\"dd\"일\"'] 에서 \가 생기는 이유 : 형식 지정을위해 큰따옴표를 사용했기때문에, escape문자를 사용해준 것
*/
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "%"+memberName+"%");
rset = pstmt.executeQuery();
while(rset.next()) {
Member m = new Member();
m.setMemberId(rset.getString("member_id"));
m.setMemberPw(rset.getString("member_pw"));
m.setMemberName(rset.getString("member_name"));
m.setMemberAddr(rset.getString("member_addr"));
m.setMemberPhone(rset.getString("member_phone"));
m.setMemberAge(rset.getInt("member_age"));
m.setMemberGender(rset.getString("member_gender"));
m.setEnrollDate(rset.getString("enroll_date"));
list.add(m);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(pstmt);
JDBCTemplate.close(rset);
}
return list;
}
4. 회원 정보 등록
MemberController.java
public void insertMember() {
//insert into member_tbl values(?,?,?,?,?,?,?,sysdate);
String memberId = "";
while(true) {
System.out.print("아이디 : ");
memberId = sc.next();
if(service.selectMemberId(memberId) != null ) {
System.out.println("이미 존재하는 아이디 입니다.");
} else {
break;
}
}
System.out.print("비밀번호 : ");
String memberPw = sc.next();
System.out.print("이름 : ");
String memberName = sc.next();
System.out.print("주소 : ");
sc.nextLine();
String memberAddr = sc.nextLine();
System.out.print("전화번호 : ");
String memberPhone = sc.next();
System.out.print("나이 : ");
int memberAge = sc.nextInt();
System.out.print("성별[남/여] : ");
String memberGender = sc.next();
Member m = new Member(memberId, memberPw, memberName, memberAddr, memberPhone, memberAge, memberGender);
int result = service.insertMember(m);
if(result > 0) {
System.out.println("등록 성공!");
}else {
System.out.println("등록 실패!");
}
}
MemberService.java
public int insertMember(Member m) {
Connection conn = JDBCTemplate.getConnection();
int result = dao.insertMember(conn, m);
if(result > 0) {
JDBCTemplate.commit(conn);
}else {
JDBCTemplate.rollback(conn);
}
JDBCTemplate.close(conn);
return result;
}
MemberDao.java
public int insertMember(Connection conn, Member m) {
PreparedStatement pstmt = null;
int result = 0;
String query = "insert into member_tbl values(?,?,?,?,?,?,?, sysdate)";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberId());
pstmt.setString(2, m.getMemberPw());
pstmt.setString(3, m.getMemberName());
pstmt.setString(4, m.getMemberAddr());
pstmt.setString(5, m.getMemberPhone());
pstmt.setInt(6, m.getMemberAge());
pstmt.setString(7, m.getMemberGender());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(pstmt);
}
return result;
}
5. 회원 정보 수정
MemberController.java
public void updateMember() {
//쿼리문 : update member_tbl set member_pw = ?, member_addr = ?, member_phone = ? where member_id = ?;
Member m = new Member();
System.out.print("아이디 입력 : ");
m.setMemberId(sc.next());
if(service.selectMemberId(m.getMemberId()) == null) {
System.out.println("회원정보를 찾을 수 없습니다.");
return;
}
System.out.print("변경할 비밀번호 : ");
m.setMemberPw(sc.next());
System.out.print("변경할 주소 : ");
sc.nextLine();
m.setMemberAddr(sc.nextLine());
System.out.print("변경할 전화번호 : ");
m.setMemberPhone(sc.next());
int result = service.updateMember(m);
if(result > 0) {
System.out.println("수정 완료");
}else {
System.out.println("수정 실패");
}
}
MemberService.java
public int updateMember(Member m) {
Connection conn = JDBCTemplate.getConnection();
int result = dao.updateMember(conn, m);
if(result > 0) {
JDBCTemplate.commit(conn);
}else {
JDBCTemplate.rollback(conn);
}
JDBCTemplate.close(conn);
return result;
}
MemberDao.java
public int updateMember(Connection conn, Member m) {
PreparedStatement pstmt = null;
int result = 0;
String query = "update member_tbl set member_pw=?, member_addr=?, member_phone=? where member_id=?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberPw());
pstmt.setString(2, m.getMemberAddr());
pstmt.setString(3, m.getMemberPhone());
pstmt.setString(4, m.getMemberId());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(pstmt);
}
return result;
}
6. 회원 정보 삭제
MemberController.java
public void deleteMember() {
//select * from member_tbl where member_id = ? --id로조회이용
//delete from member_tbl where member_id = ?
//insert into del_member values(아이디, 이름, 전화번호, 탈퇴일);
System.out.print("아이디 입력 : ");
String memberId = sc.next();
int result = service.deleteMember(memberId);
if(result > 0) {
System.out.println("삭제 완료");
} else {
System.out.println("삭제 실패");
}
}
MemberService.java
public int deleteMember(String memberId) {
Connection conn = JDBCTemplate.getConnection();
int result = 0;
Member m = dao.selectMemberId(conn, memberId);
if( m != null) {
result = dao.deleteMember(conn, memberId);
if(result > 0) {
result = dao.delMemberInsert(conn, m);
if(result > 0) {
JDBCTemplate.commit(conn);
}else {
JDBCTemplate.rollback(conn);
}
}else {
JDBCTemplate.rollback(conn);
}
}
JDBCTemplate.close(conn);
return result;
}
MemberDao.java : 해당 회원 삭제 후 DEL_MEMBER 테이블에 INSERT
public int deleteMember(Connection conn, String memberId) {
PreparedStatement pstmt = null;
int result = 0;
String query = "delete from member_tbl where member_id = ? ";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, memberId);
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(pstmt);
}
return result;
} public int delMemberInsert(Connection conn, Member m) {
PreparedStatement pstmt = null;
int result = 0;
String query = "insert into del_member values(?, ?, ?, sysdate)";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, m.getMemberId());
pstmt.setString(2, m.getMemberName());
pstmt.setString(3, m.getMemberPhone());
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCTemplate.close(pstmt);
}
return result;
}
[ 후기 ]
싱글톤패턴을 이용하여 코드를 짜니까 Connection 생성을 계속하지 않아도 되어서 너무너무 좋다.
물론 클래스파일을 왔다 갔다 해야 하는 번거로움은 있지만, 가독성도 좋아지고 메소드마다 코드가 짧아져서 편해졌다.
다 괜찮았지만, DELETE에서 수행해야 하는 쿼리문이 3개나 되다 보니 COMMIT/ROLLBACK을 어디서 수행해야하는지 한 번씩 막히는 부분이 있는데 좀 더 연습해 봐야겠다.
오늘 실수한 점
없음!
'국비수업 > JDBC' 카테고리의 다른 글
| 29일차 : JDBC 실습1. LIBRARY(도서대여) 프로그램 (0) | 2023.01.06 |
|---|---|
| 28일차 : JDBC 실습1 - 커뮤니티( 로그인 / 회원가입 / 게시판 ) (0) | 2023.01.05 |
| 26일차 : JDBC - PreparedStatement (0) | 2023.01.02 |
| 25일차 : JDBC - Statement (0) | 2022.12.30 |
'국비수업/JDBC' Related Articles
more
Comments