환경 구축 (9)

src

 

board/boardDAO.java

package board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.sql.SQLException;

import util.databaseUtil;

public class boardDAO {

    // 게시글 추가
    public int write(String userId, String boardTitle, String boardContent, String fileName, String fileRealName) {

        String SQL = "INSERT INTO board VALUES (null, ?, ?, ?, ?, ?, NOW())";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            pstmt.setString(2, boardTitle);
            pstmt.setString(3, boardContent);
            pstmt.setString(4, fileName);
            pstmt.setString(5, fileRealName);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return -1;

    }

    // 게시글 수정
    public int update(int boardId, String userId, String boardTitle, String boardContent, String fileName, String fileRealName) {

        String SQL = "UPDATE board SET boardTitle = ?, boardContent = ?, fileName = ?, fileRealName = ? WHERE userId = ? AND boardId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, boardTitle);
            pstmt.setString(2, boardContent);
            pstmt.setString(3, fileName);
            pstmt.setString(4, fileRealName);
            pstmt.setString(5, userId);
            pstmt.setInt(6, boardId);
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;

    }

    // 게시글 삭제
    public int delete(String userId, int boardId) {

        String SQL = "DELETE FROM board WHERE userId = ? AND boardId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            pstmt.setInt(2, boardId);
            return pstmt.executeUpdate(); // 성공적이면 1을 반환
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;

    }

    // 메인 페이지
    public List<boardDTO> getRecentPosts(int numberOfPosts) {

        List<boardDTO> recentPosts = new ArrayList<>();
        String SQL = "SELECT boardId, userId, boardTitle, boardDate FROM board ORDER BY boardDate DESC LIMIT ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, numberOfPosts);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                boardDTO post = new boardDTO();
                post.setboardId(rs.getInt("boardId"));
                post.setuserId(rs.getString("userId"));
                post.setboardTitle(rs.getString("boardTitle"));
                post.setboardDate(rs.getTimestamp("boardDate"));
                recentPosts.add(post);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recentPosts;

    }

    // 마이 페이지
    public List<boardDTO> myView(String userId) {

        List<boardDTO> boardList = new ArrayList<>();
        String SQL = "SELECT * FROM board WHERE userId = ? ORDER BY boardId DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                boardDTO boardDto = new boardDTO();
                boardDto.setboardId(rs.getInt("boardId"));
                boardDto.setuserId(rs.getString("userId"));
                boardDto.setboardTitle(rs.getString("boardTitle"));
                boardDto.setboardContent(rs.getString("boardContent"));
                boardDto.setboardDate(rs.getTimestamp("boardDate"));
                boardList.add(boardDto);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return boardList;

    }

 

board/boardDTO.java 

package board;

import java.sql.Timestamp;

public class boardDTO {

    int boardId;
    String userId;
    String boardTitle;
    String boardContent;
    String fileName;
    String fileRealName;
    Timestamp boardDate;

    // boardId
    public int getboardId() {
        return boardId;
    }
    public void setboardId(int boardId) {
        this.boardId = boardId;
    }

    // userId
    public String getuserId() {
        return userId;
    }
    public void setuserId(String userId) {
        this.userId = userId;
    }

    // boardTitle
    public String getboardTitle() {
        return boardTitle;
    }
    public void setboardTitle(String boardTitle) {
        this.boardTitle = boardTitle;
    }

    // boardContent
    public String getboardContent() {
        return boardContent;
    }
    public void setboardContent(String boardContent) {
        this.boardContent = boardContent;
    }

    // fileName
    public String getfileName() {
        return fileName;
    }
    public void setfileName(String fileName) {
        this.fileName = fileName;
    }

    // fileRealName
    public String getfileRealName() {
        return fileRealName;
    }
    public void setfileRealName(String fileRealName) {
        this.fileRealName = fileRealName;
    }

    // boardDate
    public Timestamp getboardDate() {
        return boardDate;
    }
    public void setboardDate(Timestamp boardDate) {
        this.boardDate = boardDate;
    }

    public boardDTO() {

    }

    public boardDTO(int boardId, String userId, String boardTitle, String boardContent, String fileName, String fileRealName, Timestamp boardDate) {
        this.boardId = boardId;
        this.userId = userId;
        this.boardTitle = boardTitle;
        this.boardContent = boardContent;
        this.fileName = fileName;
        this.fileRealName = fileRealName;
        this.boardDate = boardDate;
    }

}

 

user/userDAO.java

package user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import util.databaseUtil;

public class userDAO {

    // 로그인
    public int login(String userId, String userPassword) {

        String SQL = "SELECT userPassword FROM user WHERE userId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                if (rs.getString(1).equals(userPassword)) {
                    return 1; // 로그인 성공
                }
                else {
                    return 0; // 비밀번호가 틀림
                }
            }
            return -1; // 아이디가 존재하지 않음
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -2; // 데이터베이스 오류

    }

    // 회원가입
    public int join(userDTO user) {

        String SQL = "INSERT INTO user VALUES (?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, user.userId);
            pstmt.setString(2, user.userPassword);
            pstmt.setString(3, user.userName);
            pstmt.setString(4, user.userPhone);
            pstmt.setString(5, user.userEmail);
            pstmt.setInt(6, user.userAge);
            pstmt.setString(7, user.userAddress);
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1; // 회원가입 실패

    }

    // 아이디 중복 확인
    public int checkId(String userId) {

        String SQL = "SELECT COUNT(*) AS Count FROM user WHERE userId = ?;";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("Count");  // 아이디 존재
            } else {
                return 0;  // 아이디 존재하지 않음
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;

    }

    // 회원정보 수정
    public int update(userDTO user) {

        String SQL = "UPDATE user SET userPassword = ?, userName = ?, userPhone = ?, userEmail =?, userAge = ?, userAddress = ? WHERE userId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, user.userPassword);
            pstmt.setString(2, user.userName);
            pstmt.setString(3, user.userPhone);
            pstmt.setString(4, user.userEmail);
            pstmt.setInt(5, user.userAge);
            pstmt.setString(6, user.userAddress);
            pstmt.setString(7, user.userId);
            return pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;

    }

    // 회원탈퇴
    public int delete(String userId) {

        String SQL = "DELETE FROM user WHERE userId= ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            int result = pstmt.executeUpdate();

            if (result == 1) {
                return 1; // 회원탈퇴 성공
            } else {
                return -1; // 회원탈퇴 실패
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -2;

    }

    // 비밀번호 확인
    public boolean checkPassword(String userId, String userPassword) {
        String SQL = "SELECT userPassword FROM user WHERE userId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getString("userPassword").equals(userPassword);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;

    }

    // 회원정보 가져오기
    public userDTO userInfo(String userId) {

        String SQL = "SELECT * FROM user WHERE userId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        userDTO userDto = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                userDto = new userDTO();
                userDto.setuserId(rs.getString("userId"));
                userDto.setuserPassword(rs.getString("userPassword"));
                userDto.setuserName(rs.getString("userName"));
                userDto.setuserPhone(rs.getString("userPhone"));
                userDto.setuserEmail(rs.getString("userEmail"));
                userDto.setuserAge(rs.getInt("userAge"));
                userDto.setuserAddress(rs.getString("userAddress"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return userDto;
    }

}

 

user/userDTO.java

package user;

public class userDTO {

    String userId;
    String userPassword;
 		String userName;
		String userPhone;
		String userEmail;
		int userAge;
		String userAddress;

		// userID
    public String getuserId() {
        return userId;
    }
    public void setuserId(String userId) {
        this.userId = userId;
    }

		// userPassword
    public String getuserPassword() {
        return userPassword;
    }
    public void setuserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

		// userName
    public String getuserPassword() {
        return userName;
    }
    public void setuserName(String userName) {
        this.userName = userName;
    }

		// userPhone
    public String getuserPassword() {
        return userPhone;
    }
    public void setuserPhone(String userPhone) {
        this.userPhone = userPhone;
    }

		// userEmail
    public String getuserPassword() {
        return userEmail;
    }
    public void setuserEmail(String userEmail) {
        this.userEmail = userEmail;
    }

		// userAge
    public int getuserAge() {
        return userAge;
    }
    public void setuserAge(int userAge) {
        this.userAge = userAge;
    }

		// userAddress
    public String getuserAddress() {
        return userAddress;
    }
    public void setuserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    public userDTO() {

    }

		public userDTO(String userId, String userPassword, String userName, String userPhone, String userEmail, int userAge, String userAddress) {
        this.userId = userId;
        this.userPassword = userPassword;
				this.userName = userName;
				this.userPhone = userPhone;
				this.userEmail = userEmail;
				this.userAge = userAge;
				this.userAddress = userAddress;
    }

}

 

comment/commentDAO.java

package comment;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.databaseUtil;

public class commentDAO {

    // 댓글 추가
    public int write(int boardId, String userId, String commentContent) {

        String SQL = "INSERT INTO comment VALUES (null, ?, ?, ?, NOW())";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, boardId);
            pstmt.setString(2, userId);
            pstmt.setString(3, commentContent);
            return pstmt.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

/*
    // 댓글 수정
    public int update(int bbsID, int commentID,String commentContent) {

		    String SQL="UPDATE comment SET commentContent = ? WHERE bbsID = ? AND commentId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

		    try {
            conn = databaseUtil.getConnection();
			      pstmt = conn.prepareStatement(SQL);
      			pstmt.setString(1, commentContent);
	      		pstmt.setInt(2, bbsID);
	      		pstmt.setInt(3, commentID);
	      		return pstmt.executeUpdate();  //insert,delete,update			
	    	} catch(Exception e) {
	      		e.printStackTrace();
	    	} finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;

    }
*/

    // 댓글 삭제
    public int delete(String userId, int commentId) {

        String SQL = "DELETE FROM comment WHERE userId = ? AND commentId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, userId);
            pstmt.setInt(2, commentId);
            return pstmt.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;  //데이터베이스 오류

    }

    // 댓글 불러오기
    public List<commentDTO> commentList(int boardId) {

        List<commentDTO> commentList = new ArrayList<>();
        String SQL = "SELECT * FROM comment WHERE boardId = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, boardId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                commentDTO commentDto = new commentDTO();
                commentDto.setcommentId(rs.getInt("commentId"));
                commentDto.setboardId(rs.getInt("boardId"));
                commentDto.setuserId(rs.getString("userId"));
                commentDto.setcommentContent(rs.getString("commentContent"));
                commentDto.setcommentDate(rs.getTimestamp("commentDate"));
                commentList.add(commentDto);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return commentList;
    }

    // 댓글 카운트
    public int count(int boardId) {

        String SQL = "SELECT COUNT(*) AS Count FROM comment WHERE boardId = ?;";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = databaseUtil.getConnection();
            pstmt = conn.prepareStatement(SQL);
            pstmt.setInt(1, boardId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("Count");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

}

 

comment/commentDTO.java

package comment;

import java.sql.Timestamp;

public class commentDTO {

    int commentId;
		String userId;
    int boardId;
		String commentContent;
		Timestamp commentDate;

		// commentId
    public int getcommentId() {
        return commentId;
    }
    public void setcommentId(int commentId) {
        this.commentId = commentId;
    }

		// userId
    public String getuserId() {
        return userId;
    }
    public void setuserId(String userId) {
        this.userId = userId;
    }

		// boardId
    public int getboardId() {
        return boardId;
    }
    public void setboardId(int boardId) {
        this.boardId = boardId;
    }

		// commentContent
    public String getcommentContent() {
        return commentContent;
    }
    public void setcommentContent(String commentContent) {
        this.commentContent = commentContent;
    }

		// commentDate
    public Timestamp getcommentDate() {
        return commentDate;
    }
    public void setcommentDate(Timestamp commentDate) {
        this.commentDate = commentDate;
    }

    public commentDTO() {

    }

		public commentDTO(int commentId, String userId, int boardId, String commentContent, Timestamp commentDate) {
        this.commentId = commentId;
        this.userId = userId;
				this.boardId = boardId;
				this.commentContent = commentContent;
				this.commentDate = commentDate;

    }

}

 

util/databaseUtil.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;

public class databaseUtil {

    public static Connection getConnection() {
        try {
            String dbURL = "~~~~~";
            String dbID = "~~~~~";
            String dbPW = "~~~~~~";
            Class.forName("com.mysql.jdbc.Driver");
		        return DriverManager.getConnection(dbURL, dbID, dbPW);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}