[Spring] 07-4. 활용 코드 예시(DB)

 

 

간단한 로그인 / 로그아웃 / 글 작성 / 글 검색 코드(DB)

<BoardDAO>

package com.koreait.app.biz.board;

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

import org.springframework.stereotype.Repository;

import com.koreait.app.biz.common.JDBCUtil;

@Repository
public class BoardDAO {
	private final String SELECTALL = "SELECT BID,CONTENT,WRITER FROM BOARD";
	private final String SEARCH_CONTENT = "SELECT BID, CONTENT, WRITER \r\n"
            								+ "FROM BOARD \r\n"
            								+ "WHERE CONTENT LIKE CONCAT('%', ?, '%')"; // 내용으로 검색
	private final String SEARCH_WRITER = "SELECT BID,CONTENT,WRITER FROM BOARD WHERE WRITER=?"; // 아이디로 검색
	
	private final String INSERT = "INSERT INTO BOARD (CONTENT,WRITER) VALUES(?,?)";
	
	public List<BoardDTO> selectAll(BoardDTO boardDTO) {
		System.err.println("com.koreait.app.biz.board.selectAll 시작");
		List<BoardDTO> datas=new ArrayList<BoardDTO>();
		
		Connection conn=JDBCUtil.connect();
		PreparedStatement pstmt=null;
		try {
			if(boardDTO.getCondition().equals("SEARCH_CONTENT")&& boardDTO.getKeyword()!=null) { // 제목으로 검색(검색어가 없을 때는 아무것도 반환x)
				System.out.println("제목으로 검색~~~");
				pstmt = conn.prepareStatement(SEARCH_CONTENT);
				
				System.err.println("제목 검색어 = [ "+boardDTO.getKeyword()+" ]");
				pstmt.setString(1, boardDTO.getKeyword());
			}
			else if(boardDTO.getCondition().equals("SEARCH_WRITER")&& boardDTO.getKeyword()!=null) { // 아이디 검색(검색어가 없을 때는 아무것도 반환x)
				System.out.println("아이디로 검색~~~");
				
				pstmt = conn.prepareStatement(SEARCH_WRITER);
				System.err.println("아이디 검색어 = [ "+boardDTO.getKeyword()+" ]");
				pstmt.setString(1, boardDTO.getKeyword());				
			}
			else if(boardDTO.getCondition().equals("ALL")) {
				pstmt = conn.prepareStatement(SELECTALL); // 전체 검색
			}
			else {
				System.err.println("사용자 입력 오류");
				return datas;
			}
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()) {
				BoardDTO data=new BoardDTO();
				data.setBid(rs.getInt("BID"));
				data.setContent(rs.getString("CONTENT"));
				data.setWriter(rs.getString("WRITER"));
				datas.add(data);
			}
		} catch (SQLException e) {
			System.err.println("com.koreait.app.biz.board.selectAll SQL문 실패");
			e.printStackTrace();
		}	
		System.err.println("com.koreait.app.biz.board.selectAll 성공");
		return datas;
	}
	public BoardDTO selectOne(BoardDTO boardDTO) {
		return null;
	}
	public boolean insert(BoardDTO boardDTO) {
		Connection conn=JDBCUtil.connect();
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setString(1, boardDTO.getContent());
			pstmt.setString(2, boardDTO.getWriter());
			int result=pstmt.executeUpdate();
			if(result <= 0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}	
		
		return true;
	}
	public boolean update(BoardDTO boardDTO) {
		return false;
	}
	public boolean delete(BoardDTO boardDTO) {
		return false;
	}
}

 

<MemberDAO>

package com.koreait.app.biz.member;

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

import org.springframework.stereotype.Repository;

import com.koreait.app.biz.common.JDBCUtil;

@Repository
public class MemberDAO {
	private final String SELECTONE = "SELECT MID FROM MEMBER WHERE MID=? AND PASSWORD=?";
	
	public List<MemberDTO> selectAll(MemberDTO memberDTO) {
		return null;
	}
	public MemberDTO selectOne(MemberDTO memberDTO) {
		System.err.println("com.koreait.app.biz.member.selectOne 시작");
		MemberDTO data=null;
		
		Connection conn=JDBCUtil.connect();
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(SELECTONE);
			System.err.println("아이디 = [ "+memberDTO.getMid()+" ]");
			pstmt.setString(1, memberDTO.getMid());
			System.err.println("패스워드 = [ "+memberDTO.getPassword()+" ]");
			pstmt.setString(2, memberDTO.getPassword());
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()) {
				System.err.println("com.koreait.app.biz.member.selectOne 검색 성공");
				data=new MemberDTO();
				data.setMid(rs.getString("MID"));
			}
		} catch (SQLException e) {
			System.err.println("com.koreait.app.biz.member.selectOne SQL문실패");
			e.printStackTrace();
		}
		System.err.println("com.koreait.app.biz.member.selectOne 성공");
		return  data;
	}
	public boolean insert(MemberDTO memberDTO) {
		return false;
	}
	public boolean update(MemberDTO memberDTO) {
		return false;
	}
	public boolean delete(MemberDTO memberDTO) {
		return false;
	}
}

 

 

 

2024.10.11