28일차 : JDBC 실습1 - 커뮤니티( 로그인 / 회원가입 / 게시판 )

20230104

 

[ DB ] : 실습을 위해 생성한 계정 / 테이블 / 시퀀스

-- 20230104 : JDBC(KH커뮤니티) 실습
CREATE USER jdbcex IDENTIFIED BY 1234;
GRANT CONNECT, RESOURCE TO jdbcex;

-- 회원 테이블
CREATE TABLE EXAM_MEMBER(
    MEMBER_NO       NUMBER          PRIMARY KEY,
    MEMBER_ID       VARCHAR2(20)    UNIQUE NOT NULL,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(20)    NOT NULL,
    MEMBER_PHONE    CHAR(11)        NOT NULL
);
CREATE SEQUENCE EXAM_MEMBER_SEQ;

-- 게시판 테이블
CREATE TABLE EXAM_BOARD(
    BOARD_NO        NUMBER              PRIMARY KEY,
    BOARD_TITLE     VARCHAR2(100)       NOT NULL,
    BOARD_CONTENT   VARCHAR2(2000)      NOT NULL,
    BOARD_WRITER    NUMBER  REFERENCES EXAM_MEMBER ON DELETE SET NULL,
    READ_COUNT     NUMBER  default 0   NOT NULL,
    WRITE_DATE      DATE                NOT NULL
);
CREATE SEQUENCE EXAM_BOARD_SEQ;

 

 

[ JAVA 클래스 ] 

[ MemberStart / Member / Board / MemberConroller / MemberDao / MemberService() / JDBCTemplate() ]

 

Member.java

package kr.or.iei.member.vo;

public class Member {

	private int memberNo;
	private String memberId;
	private String memberPw;
	private String memberName;
	private String memberPhone;
	
	public Member() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Member(int memberNo, String memberId, String memberPw, String memberName, String memberPhone) {
		super();
		this.memberNo = memberNo;
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.memberPhone = memberPhone;
	}
	
	public int getMemberNo() {
		return memberNo;
	}
	public void setMemberNo(int memberNo) {
		this.memberNo = memberNo;
	}
	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 getMemberPhone() {
		return memberPhone;
	}
	public void setMemberPhone(String memberPhone) {
		this.memberPhone = memberPhone;
	}		
}

 

Board.java : 보통 테이블이 생성되면 VO도 생성된다.

보통 테이블 따라 VO의 변수가 정해지는데, 필요에 따라 추가가능 - 현재 BOARD테이블의 WRITENAME처럼

( 단, getter / setter 만드는걸 잊지말기! )

package kr.or.iei.member.vo;

public class Board{

	private int boardNo;
	private String boardTitle;
	private String boardContent;
	private int boardWriter;
	private int readCount;
	private String writeDate;
	private String writeName;
	
	public Board() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Board(int boardNo, String boardTitle, String boardContent, int boardWriter, int readCount,
			String writeDate, String writeName) {
		super();
		this.boardNo = boardNo;
		this.boardTitle = boardTitle;
		this.boardContent = boardContent;
		this.boardWriter = boardWriter;
		this.readCount = readCount;
		this.writeDate = writeDate;
	}
	public int getBoardNo() {
		return boardNo;
	}
	public void setBoardNo(int boardNo) {
		this.boardNo = boardNo;
	}
	public String getBoardTitle() {
		return boardTitle;
	}
	public void setBoardTitle(String boardTitle) {
		this.boardTitle = boardTitle;
	}
	public String getBoardContent() {
		return boardContent;
	}
	public void setBoardContent(String boardContent) {
		this.boardContent = boardContent;
	}
	public int getBoardWriter() {
		return boardWriter;
	}
	public void setBoardWriter(int boardWriter) {
		this.boardWriter = boardWriter;
	}
	public int getReadCount() {
		return readCount;
	}
	public void setReadCount(int readCount) {
		this.readCount = readCount;
	}
	public String getWriteDate() {
		return writeDate;
	}
	public void setWriteDate(String writeDate) {
		this.writeDate = writeDate;
	}	
	public String getWriteName() {
		return writeName;
	}
	public void setWriteName(String writeName) {
		this.writeName = writeName;
	}
}

 

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 {
	//Connection 연결
	//close - conn, stmt, rset
	// commit / rollback
	
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","jdbcex","1234");
			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;
	}
	
	public static void commit(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.commit();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void rollback(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.rollback();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	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) {
		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();
		}
	}
}

 

MemberController의 변수선언과 생성자

	private Scanner sc;
	private MemberService service;
	private Member loginMember; // null이면 로그인이 안된상태
	boolean bool;

	public MemberController() {
		super();
		sc = new Scanner(System.in);
		service = new MemberService();
		loginMember = null;//로그인이 안된상태, 로그인이 되었으면 해당 회원정보를 담아 둘 변수
		bool = true;
	}

 

MemberService의 변수선언과 생성자

	private MemberDao dao;

	public MemberService() {
		super();
		dao = new MemberDao();
	}

 

MemberController - main()

	public void main() {
		while (bool) {
			if(loginMember == null) {
				menu();				
			}else {
				loginMenu();
			}
		}
	}

 

[ 1 ] menu() : 로그인 전 메뉴화면

	public void menu() {
		System.out.println("\n----- KH 커뮤니티 -----\n");
		System.out.println("1. 로그인하기");
		System.out.println("2. 회원가입");
		System.out.println("3. 아이디 찾기");
		System.out.println("0. 프로그램 종료");
		System.out.print("선택 >> ");
		int sel = sc.nextInt();

		switch (sel) {
		case 1:
			login();
			break;
		case 2:
			insertMember();
			break;
		case 3:
			searchIdMember();
			break;
		case 0:
			System.out.println("프로그램을 종료 합니다");
			bool = false;
			break;
		default:
			System.out.println("잘못입력하셨습니다.");
		}
	}

 

1 - 1.로그인하기

MemberController.java

	public void login() {
		// select * from exam_member where member_id = ? and member_pw = ?;
		System.out.println("\n----- 로그인 -----\n");
		System.out.print("ID 입력 : ");
		String memberId = sc.next();
		System.out.print("PW 입력 : ");
		String memberPw = sc.next();

		Member m = service.login(memberId, memberPw);
		if (m != null) {
			loginMember = m; // 조회된 회원정보를 로그인변수에 대입
			System.out.println("로그인 성공!");
		} else {
			System.out.println("아이디 또는 비밀번호를 확인하세요.");
		}
	}

MemberService.java

	public Member login(String memberId, String memberPw) {
		Connection conn = JDBCTemplate.getConnection();
		Member m = dao.login(conn, memberId, memberPw);
		JDBCTemplate.close(conn);
		return m;
	}

MemberDao.java

	public Member login(Connection conn, String memberId, String memberPw) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;

		Member m = null;
		
		String query = "select * from exam_member where member_id = ? and member_pw = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberId);
			pstmt.setString(2, memberPw);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				m = new Member();
				m.setMemberNo(rset.getInt("member_no"));
				m.setMemberId(rset.getString("member_id"));
				m.setMemberPw(rset.getString("member_pw"));
				m.setMemberName(rset.getString("member_name"));
				m.setMemberPhone(rset.getString("member_phone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(rset);
			JDBCTemplate.close(pstmt);
		}
		return m;
	}

 

1 - 2. 회원가입

MemberController.java

	public void insertMember() {
		Member m = new Member();
		System.out.println("\n----- 회원 가입 -----\n");
		System.out.print("ID 입력 : ");
		m.setMemberId(sc.next());
		System.out.print("PW 입력 : ");
		m.setMemberPw(sc.next());
		System.out.print("이름 입력 : ");
		m.setMemberName(sc.next());
		System.out.print("전화번호 입력('-'제외) : ");
		m.setMemberPhone(sc.next());

		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 exam_member values(exam_member_seq.nextval, ?, ?, ?, ? )";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, m.getMemberId());
			pstmt.setString(2, m.getMemberPw());
			pstmt.setString(3, m.getMemberName());
			pstmt.setString(4, m.getMemberPhone());
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

1 - 3. 아이디 찾기

MemberController.java

	public void searchIdMember() {
		// select member_id from exam_member where member_name = ? and member_phone = ?;

		System.out.println("\n----- 아이디 찾기 -----\n");
		System.out.print("이름 입력 : ");
		String memberName = sc.next();
		System.out.print("전화번호 입력 : ");
		String memberPhone = sc.next();

		String memberId = service.searchIdMember(memberName, memberPhone);
		if (memberId != null) {
			System.out.println("아이디는 [ " + memberId + " ] 입니다.");
		} else {
			System.out.println("일치하는 정보가 없습니다.");
		}
	}

MemberService.java

	public String searchIdMember(String memberName, String memberPhone) {
		Connection conn = JDBCTemplate.getConnection();
		String memberId = dao.searchIdMember(conn, memberName, memberPhone);
		
		JDBCTemplate.close(conn);
		return memberId;
	}

MemberDao.java

	public String searchIdMember(Connection conn, String memberName, String memberPhone) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		String memberId = null;
		String query = "select member_id from exam_member where member_Name = ? and member_phone = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberName);
			pstmt.setString(2, memberPhone);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				memberId = rset.getString("member_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(rset);
		}	
		return memberId;
	}

 

 

[ 2 ] loginMenu() : 로그인 후 메뉴화면

	public void loginMenu() {
		System.out.println("\n----- KH 커뮤니티 -----\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("7. 내 정보 변경");
		System.out.println("8. 회원탈퇴");
		System.out.println("0. 로그아웃");
		System.out.print("선택 >> ");
		int sel = sc.nextInt();
		
		switch(sel) {
		case 1:
			printAllBoard();
			break;
		case 2:
			printDetailBoard();
			break;
		case 3:
			boardUpload();
			break;
		case 4:
			updateBoard();
			break;
		case 5:
			deleteBoard();
			break;
		case 6:
			printMyInfo();
			break;
		case 7:
			updateMember();
			break;
		case 8:
			deleteMember();
			break;
		case 0:
			loginMember = null;
			break;
		default:
			System.out.println("잘못입력");
		}	
	}

 

2 - 1.게시물 목록 보기

MemberController.java

	public void printAllBoard() {
		System.out.println("\n----- 게시물 목록 -----\n");
		ArrayList<Board> list = service.printAllBoard();
		for(Board b : list) {
			System.out.println(b.getBoardNo()+"\t"+b.getBoardTitle()+"\t"+b.getWriteName()+"\t"+b.getReadCount()+"\t"+b.getWriteDate());
		}
	}

MemberService.java

	public ArrayList<Board> printAllBoard() {
		Connection conn = JDBCTemplate.getConnection();
		ArrayList<Board> list = dao.printAllBoard(conn);
		JDBCTemplate.close(conn);
		return list;
	}

MemberDao.java

	public ArrayList<Board> printAllBoard(Connection conn) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		ArrayList<Board> list = new ArrayList<Board>();
		String qeury = "select board_no, board_title, nvl(member_name,'탈퇴회원') as member_name, read_count, to_char(write_date, 'yyyy-mm-dd') as write_date from exam_board left join exam_member on(board_writer = member_no) ";
		
		try {
			pstmt = conn.prepareStatement(qeury);
			rset = pstmt.executeQuery();
			while(rset.next()) {
				Board b = new Board();
				b.setBoardNo(rset.getInt("board_no"));
				b.setBoardTitle(rset.getString("board_title"));	
				b.setWriteName(rset.getString("member_name"));						
				b.setReadCount(rset.getInt("read_count"));
				b.setWriteDate(rset.getString("write_date"));
				
				list.add(b);		
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(rset);
		}
		return list;
	}

 

2 - 2. 게시물 상세 보기

MemberController.java

	public void printDetailBoard() {
		// select board_no, board_title, board_content, member_name, read_count, write_date 
		// from exam_board join exam_member where board_no = ?;
		System.out.print("게시물 번호 입력 : ");
		int boardNo = sc.nextInt();
		Board b = service.printDetailBoard(boardNo);
		if(b != null) {
			System.out.println("게시물 번호 : "+b.getBoardNo());
			System.out.println("게시물 제목 : "+b.getBoardTitle());
			System.out.println("게시물 내용 : "+b.getBoardContent());
			System.out.println("게시물 작성자 : "+b.getWriteName());
			System.out.println("게시물 조회수 : "+b.getReadCount());
			System.out.println("게시물 작성일 : "+b.getWriteDate());
		}else {
			System.out.println("게시글이 없습니다.");
		}
	}

MemberService.java

	public Board printDetailBoard(int boardNo) {
		Connection conn = JDBCTemplate.getConnection();
		Board b = null;		
		
		int result = dao.readCount(conn, boardNo);
		if(result > 0) {
			b = dao.printDetailBoard(conn, boardNo);
			if(b != null) {
				JDBCTemplate.commit(conn);				
			}else {
				JDBCTemplate.rollback(conn);
			}
		}else {
			JDBCTemplate.rollback(conn);
		}		
		JDBCTemplate.close(conn);
		return b;
	}

MemberDao.java 1 : 조회수카운트

	public int readCount(Connection conn, int boardNo) {
		PreparedStatement pstmt = null;
		int result = 0;
		
		String query = "update exam_board set read_count = read_count + 1 where board_No = ? ";		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, boardNo);
			result = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

MemberDao.java 2 : 상세보기

	public Board printDetailBoard(Connection conn, int boardNo) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		Board b = null;
		String query = "select board_no, board_title, board_content, nvl(member_name,'탈퇴회원') as member_name, read_count, to_char(write_date, 'yyyy-mm-dd') as write_date from exam_board left join exam_member on(board_writer = member_no) where board_no = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, boardNo);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				b = new Board();
				b.setBoardNo(rset.getInt("board_no"));
				b.setBoardTitle(rset.getString("board_title"));
				b.setBoardContent(rset.getString("board_content"));
				b.setWriteName(rset.getString("member_name"));
				b.setReadCount(rset.getInt("read_count"));
				b.setWriteDate(rset.getString("write_date"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
			JDBCTemplate.close(pstmt);
		}		
		return b;
	}

 

2 - 3. 게시물 등록

MemberController.java

	public void boardUpload() {
		//insert into exam_board values(exam_board_seq, 제목, 내용, 작성자, 조회수, 작성일)
		System.out.print("제목 입력 : ");
		sc.nextLine();
		String boardTitle = sc.nextLine();
		System.out.print("내용 입력 : ");
		String boardContent = sc.nextLine();
		
		int result = service.boardUpload(boardTitle, boardContent, loginMember);
		if(result > 0) {
			System.out.println("게시글 등록 성공!");
		}else {
			System.out.println("등록 실패!");
		}
	}

MemberService.java

	public int boardUpload(String boardTitle, String boardContent, Member loginMember) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.boardUpload(conn, boardTitle, boardContent, loginMember);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

MemberDao.java

	public int boardUpload(Connection conn, String boardTitle, String boardContent, Member loginMember) {
		PreparedStatement pstmt = null;
		int result = 0;
		
		String query = "insert into exam_board values(exam_board_seq.nextval, ?, ?, ?, default, sysdate)";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, boardTitle);
			pstmt.setString(2, boardContent);
			pstmt.setInt(3, loginMember.getMemberNo());
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

2 - 4. 게시물 수정

MemberController.java

	public void updateBoard() {
		//update exam_board set board_title = ? , board_content = ? where board_no = ?;
		System.out.print("게시물 번호 입력 : ");
		int boardNo = sc.nextInt();
		// 작성자와 로그인한유저가 동일할 경우에만 수정가능
		// 작성자 정보 : select board_writer from board where board_no = ?
		// 상세보기에서 찾아오면 안되는 이유 
		//1. 조회 로직을보면 작성자번호는 조회X(작성자 이름만 하고있음) 
		//2. 불필요한 정보가 너무많다
		//3. 로직실행시 조회수가 올라가게 되어있음
		int boardWriter = service.selectBoardWriter(boardNo);
		if(boardWriter == 0) {
			System.out.println("게시물 번호를 확인하세요.");
		} else {
			if(boardWriter == loginMember.getMemberNo()) {
				System.out.print("제목 입력 : ");
				sc.nextLine();
				String boardTitle = sc.nextLine();
				System.out.print("내용 입력 : ");
				String boardContent = sc.nextLine();
				
				int result = service.updateBoard(boardNo, boardTitle, boardContent);
				if(result > 0) {
					System.out.println("게시글 수정 성공!");
				}else {
					System.out.println("게시글 수정 실패!");
				}				
			} else {
				System.out.println("작성자만 수정 할 수 있습니다.");
			}
		}
	}

MemberService.java 1. 작성자 체크

	public int selectBoardWriter(int boardNo) {
		Connection conn = JDBCTemplate.getConnection();
		int boardWriter = dao.selectBoardWriter(conn, boardNo);
		JDBCTemplate.close(conn);
		return boardWriter;
	}

MemberService.java 2. 게시물 수정

	public int updateBoard(int boardNo, String boardTitle, String boardContent, int memberNo) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.updateBoard(conn, boardNo, boardTitle, boardContent, memberNo);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		
		return result;
	}

MemberDao.java 1. 작성자 체크

	public int selectBoardWriter(Connection conn, int boardNo) {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int boardWriter = 0;
		String query = "select board_writer from exam_board where board_no = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, boardNo);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				boardWriter = rset.getInt("board_writer");				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(rset);
			JDBCTemplate.close(pstmt);
		}
		return boardWriter;
	}

MemberDao.java 2. 게시물 수정

	public int updateBoard(Connection conn, int boardNo, String boardTitle, String boardContent, int memberNo) {
		PreparedStatement pstmt = null;
		int result = 0;
		String query = "update exam_board set board_title = ?, board_content = ? where board_no = ? and member_no = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, boardTitle);
			pstmt.setString(2, boardContent);
			pstmt.setInt(3, boardNo);
			pstmt.setInt(4, memberNo);
			result = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

2 - 5. 게시물 삭제

게시물 수정 로직과 동일 / 아래코드는 쿼리문에서 조건을 and로 두가지를 주어 삭제하는 방법을 사용해보았다.

(단, 이 방법을 사용하면 DB작업 오류시에도 작성자만 수정가능하다는 문구가 뜸)

MemberController.java

	public void deleteBoard() {
		//delete from exam_tbl where board_no = ? && board_writer = ?
		System.out.print("게시물 번호 입력 : ");
		int boardNo = sc.nextInt();
		int boardWriter = loginMember.getMemberNo();
		int result = service.deleteBoard(boardNo, boardWriter);
		if(result == 1) {
			System.out.println("게시글 삭제 성공!");
		}else {
			System.out.println("작성자만 삭제가 가능합니다.");
		}
	}

MemberService.java

	public int deleteBoard(int boardNo, int boardWriter) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.deleteBoard(conn, boardNo, boardWriter);
		if(result == 1) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

MemberDao.java

	public int deleteBoard(Connection conn, int boardNo, int boardWriter) {
		PreparedStatement pstmt = null;
		int result = 0;
		String query = "delete from exam_board where board_no = ? and board_writer = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, boardNo);
			pstmt.setInt(2, boardWriter);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

2 - 6. 내 정보 보기

MemberController.java

	public void printMyInfo() {
		System.out.println("\n----- 내 정보 보기 -----\n");
		System.out.println("회원 번호 : "+loginMember.getMemberNo());
		System.out.println("아이디 : "+loginMember.getMemberId());
		System.out.println("비밀번호 : "+loginMember.getMemberPw());
		System.out.println("이름 : "+loginMember.getMemberName());
		System.out.println("전화번호 : "+loginMember.getMemberPhone());
	}

 

2 - 7. 내 정보 변경

MemberController.java

	public void updateMember() {
		//update exam_member set member_pw = ?, member_phone = ? where member_no = ?;
		System.out.print("변경할 PW입력 : ");
		String memberPw = sc.next();
		System.out.print("변경할 전화번호 입력('-'생략) : ");
		String memberPhone = sc.next();
		
		int result = service.updateMember(memberPw, memberPhone, loginMember.getMemberNo());
		if(result > 0) {
			loginMember.setMemberPw(memberPw); //수정 성공 시 값을 업데이트 해줘야 6. 내정보보기에서도 반영된다.
			loginMember.setMemberPhone(memberPhone);
			System.out.println("정보 수정 성공");
		}else {
			System.out.println("수정 실패");
		}
	}

MemberService.java

	public int updateMember(String memberPw, String memberPhone, int memberNo) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.updateMember(conn, memberPw, memberPhone, memberNo);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

MemberDao.java

	public int updateMember(Connection conn, String memberPw, String memberPhone, int memberNo) {
		PreparedStatement pstmt = null;
		int result = 0;
		String query = "update exam_member set member_pw = ?, member_phone = ? where member_no = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, memberPw);
			pstmt.setString(2, memberPhone);
			pstmt.setInt(3, memberNo);
			result = pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

2 - 8. 회원 탈퇴(탈퇴 시, 게시글을 작성했었으면 [ 작성자 : 탈퇴회원 ] 으로 출력

MemberController.java

	public void deleteMember() {
		//delete from exam_member where member_no = ?
		System.out.print("정말 탈퇴하시겠습니까?[ 1. YES / 2. NO ] : ");
		int sel = sc.nextInt();
		if(sel == 1) {
			System.out.println("BYE~BYE~");
			int result = service.deleteMember(loginMember.getMemberNo());
			if(result > 0) {
				loginMember = null;
				System.out.println("탈퇴 완료");
			}else {
				System.out.println("탈퇴 실패");
			}
		}
	}

MemberService.java

	public int deleteMember(int memberNo) {
		Connection conn = JDBCTemplate.getConnection();
		int result = dao.deleteMember(conn, memberNo);
		if(result > 0) {
			JDBCTemplate.commit(conn);
		}else {
			JDBCTemplate.rollback(conn);
		}
		JDBCTemplate.close(conn);
		return result;
	}

MemberDao.java

	public int deleteMember(Connection conn, int memberNo) {
		PreparedStatement pstmt = null;
		int result = 0;
		String query = "delete from exam_member where member_no = ?";
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, memberNo);
			result = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTemplate.close(pstmt);
		}
		return result;
	}

 

[ 후기 ]

테이블이 고작 한개 더 늘었는데 너무 정신이 없었다. 신경써 줘야할 부분도 많고, 쿼리문을 잘 작성해야 효율적인 코드가 나온다는걸 체험할 수 있었다. 결과값은 어찌저찌 잘 나오는데 불필요한 부분을 너무 많이 작성하는 것 같아서 이런부분을 신경써야겠다. 

 

오늘 실수한 점

> 2 - 2. 게시물 상세보기 - 조회수카운트 메소드를 상세보기보다 나중에 실행하여 제대로된 값이 나오지않음

> 쿼리문 컬럼명 실수(자꾸 java변수명을 적음)

> 자원반환(conn, pstmt, rset close()) 자꾸 까먹음