재밌고 어려운 IT를 이해해보자~!

MVC with Oracle 2 본문

코리아IT핀테크과정

MVC with Oracle 2

언제나즐거운IT 2023. 12. 20. 17:38

JOIN

SELECT *
FROM BOARD
INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID;

SELECT BID,TITLE,WRITER,NAME
FROM BOARD
INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID
ORDER BY BID DESC;

SELECT BID,TITLE,WRITER,NAME
FROM BOARD
INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID
WHERE BID=11;

 

test01

CREATE TABLE MEMBER(
	MID VARCHAR(15) PRIMARY KEY,
	MPW VARCHAR(10) NOT NULL,
	NAME VARCHAR(15) NOT NULL
);

INSERT INTO MEMBER VALUES('teemo','1234','작은티모');
INSERT INTO MEMBER VALUES('admin','1234','관리자');

SELECT * FROM MEMBER;

DROP TABLE MEMBER;


SELECT * FROM MEMBER WHERE MID='teemo' AND MPW='1234';

test02

CREATE TABLE BOARD(
	BID INT PRIMARY KEY,
	TITLE VARCHAR(30) NOT NULL,
	WRITER VARCHAR(15) NOT NULL, -- FK(외래키,외부키)
	CONTENT VARCHAR(50) NOT NULL,
	REGDATE DATE DEFAULT SYSDATE
);
-- FK는 항상 연관된 테이블의 PK 값이 저장되어야만한다!!!!!

INSERT INTO BOARD (BID,TITLE,WRITER,CONTENT) VALUES((SELECT NVL(MAX(BID),10) + 1 FROM BOARD),'제목','admin','내용');

SELECT * FROM BOARD;

DROP TABLE BOARD;


SELECT * FROM BOARD ORDER BY BID DESC;

BoardDTO

package model;

import java.util.Date;

public class BoardDTO {
	private int bid;
	private String title;
	private String writer;
	private String content;
	private Date regdate;
	
	private String name;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public int getBid() {
		return bid;
	}
	public void setBid(int bid) {
		this.bid = bid;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	
}

 

BoardDAO

package model;

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

public class BoardDAO {
	
	private Connection conn; // DB와의 연결을 담당
	private PreparedStatement pstmt; // CRUD 수행을 담당

	private static final String SELECTALL="SELECT *\r\n"
			+ "	FROM BOARD\r\n"
			+ "	INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID ORDER BY BID DESC";
	private static final String SELECTONE="SELECT *\r\n"
			+ "	FROM BOARD\r\n"
			+ "	INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID WHERE BID=?";

	private static final String INSERT="INSERT INTO BOARD (BID,TITLE,WRITER,CONTENT) VALUES((SELECT NVL(MAX(BID),10) + 1 FROM BOARD),?,?,?)";
	private static final String UPDATE="UPDATE BOARD SET TITLE=?,CONTENT=? WHERE BID=?";
	private static final String DELETE="DELETE FROM BOARD WHERE BID=?";
	
	public ArrayList<BoardDTO> selectAll(BoardDTO bDTO){
		ArrayList<BoardDTO> datas=new ArrayList<BoardDTO>();
		
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTALL);
			ResultSet rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BoardDTO data=new BoardDTO();
				data.setBid(rs.getInt("BID"));
				data.setContent(rs.getString("CONTENT"));
				data.setRegdate(rs.getDate("REGDATE"));
				data.setTitle(rs.getString("TITLE"));
				data.setWriter(rs.getString("WRITER"));
				data.setName(rs.getString("NAME"));
				datas.add(data);
			}
			
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		
		return datas;
	}
	public BoardDTO selectOne(BoardDTO bDTO){
		BoardDTO data=null;
		
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTONE);
			pstmt.setInt(1, bDTO.getBid());
			ResultSet rs=pstmt.executeQuery();
			
			if(rs.next()) {
				data=new BoardDTO();
				data.setBid(rs.getInt("BID"));
				data.setContent(rs.getString("CONTENT"));
				data.setRegdate(rs.getDate("REGDATE"));
				data.setTitle(rs.getString("TITLE"));
				data.setWriter(rs.getString("WRITER"));
				data.setName(rs.getString("NAME"));
			}
			
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		
		return data;
	}
	public boolean insert(BoardDTO bDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(INSERT);
			pstmt.setString(1, bDTO.getTitle());
			pstmt.setString(2, bDTO.getWriter());
			pstmt.setString(3, bDTO.getContent());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	public boolean update(BoardDTO bDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(UPDATE);
			pstmt.setString(1, bDTO.getTitle());
			pstmt.setString(2, bDTO.getContent());
			pstmt.setInt(3, bDTO.getBid());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				System.out.println("에러~~~~~~~~~~");
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}
	public boolean delete(BoardDTO bDTO) {
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(DELETE);
			pstmt.setInt(1, bDTO.getBid());
			int result=pstmt.executeUpdate();
			if(result<=0) {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		return true;
	}

}

MemberDTO

package model;

public class MemberDTO {
	private String mid;
	private String mpw;
	private String name;
	public String getMid() {
		return mid;
	}
	public void setMid(String mid) {
		this.mid = mid;
	}
	public String getMpw() {
		return mpw;
	}
	public void setMpw(String mpw) {
		this.mpw = mpw;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

MemberDAO

package model;

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

public class MemberDAO {
	
	private Connection conn; // DB와의 연결을 담당
	private PreparedStatement pstmt; // CRUD 수행을 담당

	private static final String SELECTALL="";
	private static final String SELECTONE="SELECT * FROM MEMBER WHERE MID=? AND MPW=?";

	private static final String INSERT="";
	private static final String UPDATE="";
	private static final String DELETE="";
	
	private ArrayList<MemberDTO> selectAll(MemberDTO mDTO){
		return null;
	}
	public MemberDTO selectOne(MemberDTO mDTO){
		MemberDTO data=null;
		
		conn=JDBCUtil.connect();
		try {
			pstmt=conn.prepareStatement(SELECTONE);
			pstmt.setString(1, mDTO.getMid());
			pstmt.setString(2, mDTO.getMpw());
			ResultSet rs=pstmt.executeQuery();
			
			if(rs.next()) {
				data=new MemberDTO();
				data.setMid(rs.getString("MID"));
				data.setMpw(rs.getString("MPW"));
				data.setName(rs.getString("NAME"));
			}
			
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.disconnect(pstmt, conn);
		}
		
		return data;
	}
	private boolean insert(MemberDTO mDTO) {
		return false;
	}
	private boolean update(MemberDTO mDTO) {
		return false;
	}
	private boolean delete(MemberDTO mDTO) {
		return false;
	}

}

CTRL

package ctrl;

import java.util.ArrayList;

import model.BoardDAO;
import model.BoardDTO;
import model.MemberDAO;
import model.MemberDTO;
import view.VIEW;

public class CTRL {

	private MemberDAO mDAO;
	private BoardDAO bDAO;
	private VIEW view;
	private MemberDTO loginINFO;

	public CTRL() {
		mDAO=new MemberDAO();
		bDAO=new BoardDAO();
		view=new VIEW();
		loginINFO=null;
	}

	public void start() {
		while(true) {
			view.loginlogout(loginINFO);
			view.printMenu();
			int action=view.inputAction();
			if(action==0) {
				break;
			}
			else if(action==1) {
				if(loginINFO != null) {
					view.printFalse();
					continue;
				}
				MemberDTO mDTO=view.login();
				mDTO=mDAO.selectOne(mDTO);
				if(mDTO==null) {
					view.printFalse();
					continue;
				}
				loginINFO=mDTO;
				view.printTrue();
			}
			else if(action==2) {
				if(loginINFO == null) {
					view.printFalse();
					continue;
				}
				loginINFO=null;
				view.printTrue();
			}
			else if(action==3) {
				if(loginINFO==null) {
					view.printFalse();
					continue;
				}
				BoardDTO bDTO=view.boardInsert();
				bDTO.setWriter(loginINFO.getMid());
				if(!bDAO.insert(bDTO)) {
					view.printFalse();
					continue;
				}
				view.printTrue();
			}
			else if(action==4) {
				ArrayList<BoardDTO> datas=bDAO.selectAll(null);
				view.printDatas(datas);
			}
			else if(action==5) {
				int bid=view.inputBid();
				BoardDTO bDTO=new BoardDTO();
				bDTO.setBid(bid);
				bDTO=bDAO.selectOne(bDTO);
				view.printData(bDTO);
				action=view.loginMenu(bDTO, loginINFO);
				if(action==1) {
					bDTO=view.boardInsert();
					bDTO.setBid(bid);
					// bDTO.setWriter(loginINFO.getMid());
					//  코드가 없어도 되는 이유
					//   1. 이미 bDTO에 writer 정보가 있음
					//   2. update()에서 writer 정보를 필요로하지않음
					if(!bDAO.update(bDTO)) {
						view.printFalse();
						continue;
					}
					view.printTrue();
				}
				else if(action==2) {
					if(!bDAO.delete(bDTO)) {
						view.printFalse();
						continue;
					}
					view.printTrue();
				}
			}
		}
	}

}

 

 

 

같은 작성자가 작성한 글 모두 검색해주는 selectAll 로직!

ArrayList<BoardDTO> datas = new ArrayList<BoardDTO>();

			conn = JDBCUtil.connect();
			try {
				pstmt = conn.prepareStatement(SELECTALL);
				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					if (bDTO.getWriter().equals(rs.getString("WRITER"))) {
						BoardDTO data = new BoardDTO();
						data.setBid(rs.getInt("BID"));
						data.setContent(rs.getString("CONTENT"));
						data.setRegdate(rs.getDate("REGDATE"));
						data.setTitle(rs.getString("TITLE"));
						data.setWriter(rs.getString("WRITER"));
						datas.add(data);
					}
				}

				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.disconnect(pstmt, conn);
			}
			return datas;

 

'코리아IT핀테크과정' 카테고리의 다른 글

Web Crawling  (0) 2023.12.22
웹서버 프로그래밍 시험 오답노트  (0) 2023.12.21
MVC with Oracle 1  (0) 2023.12.19
MVC Pattern Practice 4  (0) 2023.12.18
MVC Pattern Practice 3  (0) 2023.12.17
공유하기 링크
'코리아IT핀테크과정' Related Articles more
Comments