JAVA/프로그램 설계 및 제작
계시판 만들기 - DB
hyun9_9
2023. 12. 20. 17:05
MemberDTO
mid
mpw
name
BoardDTO
bid
title
writer
content
regdate
로그인
로그아웃
글 작성
- 반드시 로그인해야함
글 목록 출력
- 작성자 검색
- 글 제목 검색
- 시간순 정렬
- 글 작성자의 이름을 출력
글 내용 보기
- 내 글이라면 변경 및 삭제 가능
글 내용 변경
- 내 글만 변경 가능
글 삭제
- 내 글만 삭제 가능
이름으로 검색
제목으로 검색
client
package client;
import ctrl.CTRL;
public class Test {
public static void main(String[] args) {
CTRL app=new CTRL();
app.start();
}
}
ctrl
package ctrl;
import java.util.ArrayList;
import model.BoardDAO;
import model.BoardDTO;
import model.MemberDAO;
import model.MemberDTO;
import oracle.net.aso.a;
import oracle.net.aso.c;
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;
}
//id와 pw를 입력 받아서
MemberDTO mDTO=view.login();
//mDAO에게 SELECTONE을 시킨다
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.writing();
bDTO.setWriter(loginINFO.getMid());
Boolean flag=bDAO.insert(bDTO);
if(!flag) {
view.printFalse();
continue;
}
view.printTrue();
//bDAO insert
}
else if(action==4) {
//글 전체 데이터를 가져와서
ArrayList<BoardDTO>datas= bDAO.selectAll(null);
//보여줘
view.printDatas(datas);
}
else if(action==5) {// 글 내용 보기
BoardDTO bDTO=new BoardDTO();
//글 pk 입력
int bid=view.inputAction();
bDTO.setBid(bid);//selectOne(pk)
bDTO=bDAO.selectOne(bDTO);
//printdata 출력
view.printData(bDTO);
action= view.loginMenu(bDTO, loginINFO);
if(action==1) {
//변경
bDTO=view.writing();
bDTO.setBid(bid);
if(!bDAO.update(bDTO)) {
view.printFalse();
continue;
}
view.printTrue();
}
else if(action==2) {
//삭제
if(!bDAO.delete(bDTO)) {
view.printFalse();
continue;
}
view.printTrue();
}
}
else if(action==8) {//이름으로 검색
//이름을 받는다
BoardDTO bDTO=new BoardDTO();
String mid=view.inputMID();
bDTO.setSearchCondition("이름검색");
bDTO.setWriter(mid);
ArrayList<BoardDTO> datas=bDAO.selectAll(bDTO);
//printdatas출력
view.printDatas(datas);
}
else if(action==9) {//제목으로 검색
//제목을 받는다
BoardDTO bDTO=new BoardDTO();
String title=view.inputTITLE();
bDTO.setSearchCondition("제목검색");
bDTO.setTitle(title);
ArrayList<BoardDTO> datas=bDAO.selectAll(bDTO);
//printdatas출력
view.printDatas(datas);
}
}
}
}
model
BoardDAO
package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.net.aso.c;
public class BoardDAO {
private Connection conn; // DB와의 연결을 담당
private PreparedStatement pstmt; // CRUD 수행을 담당
//private static final String SELECTALL = "SELECT * FROM BOARD ORDER BY BID DESC";
private static final String SELECTALL ="SELECT BID,TITLE,WRITER,NAME\r\n"
+ "FROM BOARD\r\n"
+ "INNER JOIN MEMBER ON BOARD.WRITER=MEMBER.MID";
private static final String SELECTALL_TITLE = "SELECT * FROM BOARD WHERE TITLE LIKE '%'||?||'%' ORDER BY BID DESC";
private static final String SELECTALL_WRITER = "SELECT * FROM BOARD WHERE WRITER LIKE '%'||?||'%' ORDER BY BID DESC";
private static final String SELECTONE = "SELECT * FROM BOARD 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 {
ResultSet rs = null;
if (bDTO == null) {
pstmt = conn.prepareStatement(SELECTALL);
rs = pstmt.executeQuery();
} else if (bDTO.getSearchCondition().equals("제목검색")) {
pstmt = conn.prepareStatement(SELECTALL_TITLE);
pstmt.setString(1, bDTO.getTitle());
rs = pstmt.executeQuery();
} else if (bDTO.getSearchCondition().equals("이름검색")) {
pstmt = conn.prepareStatement(SELECTALL_WRITER);
pstmt.setString(1, bDTO.getWriter());
rs = pstmt.executeQuery();
}
while (rs.next()) {
BoardDTO data = new BoardDTO();
data.setBid(rs.getInt("BID"));
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"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return data;
}
public boolean insert(BoardDTO bDTO) {
int flag = -1;
conn = JDBCUtil.connect();
try {
pstmt = conn.prepareStatement(INSERT);
pstmt.setString(1, bDTO.getTitle());
pstmt.setString(2, bDTO.getWriter());
pstmt.setString(3, bDTO.getContent());
flag = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
if (flag > 0) {
return true;
}
return false;
}
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) {
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} 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();
} finally {
JDBCUtil.disconnect(pstmt, conn);
}
return true;
}
}
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;//글 작성시간 date
//import util.Date 사용
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
private String searchCondition;
public String getSearchCondition() {
return searchCondition;
}
public void setSearchCondition(String searchCondition) {
this.searchCondition = searchCondition;
}
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;
}
}
JDBCUtil
package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
// 다양한 DAO에서
// 연결(), 해제() 를 사용할 예정
// => 별도의 Util 클래스로 '모듈화'
public class JDBCUtil {
static final String driverName="oracle.jdbc.driver.OracleDriver";
static final String url="jdbc:oracle:thin:@localhost:1521:xe";
static final String user="teemo";
static final String passwd="1234";
public static Connection connect() {
Connection conn=null;
try {
// 1. driver를 메모리에 로드 == 적재
Class.forName(driverName);
// 2. conn 객체를 확보
conn=DriverManager.getConnection(url,user,passwd);
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void disconnect(PreparedStatement pstmt, Connection conn) {
try {
// 4. 연결 해제
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
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;
}
}
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;
}
}
SQL
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','관리자');
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값이 저장되어야만 한다
--date 타입으로 디폴트 : 현재 시스템 시간으로 설정
INSERT INTO BOARD (BID,TITLE,WRITER,CONTENT) VALUES((SELECT NVL(MAX(BID),10) + 1 FROM BOARD),'제목','admin','내용');
view
package view;
import java.util.ArrayList;
import java.util.Scanner;
import model.BoardDTO;
import model.MemberDTO;
public class VIEW {
private Scanner sc;
public VIEW() {
sc=new Scanner(System.in);
}
public int loginMenu(BoardDTO data,MemberDTO loginINFO) {
//지금 보고있는 글의 작성자가 로그인한 사람과 일치한다면
if(loginINFO!=null&&data.getWriter().equals(loginINFO.getMid())) {
System.out.println("1. 변경");
System.out.println("2. 삭제");
}
System.out.println("0. 메인화면으로 이동");
System.out.print("번호 입력 >> ");
return sc.nextInt();
}
public void printDatas(ArrayList<BoardDTO> datas) {
if(datas==null) {
System.out.println("출력할 정보가 없습니다..");
return;
}
for(BoardDTO data:datas) {
System.out.println("["+data.getBid()+"]"+data.getName()+" "+data.getTitle());
}
}
public void printData(BoardDTO data) {
if(data==null) {
System.out.println("출력할 정보가 없습니다..");
return;
}
System.out.println("["+data.getBid()+"]"+data.getWriter()+" "+data.getTitle()+" "+data.getRegdate());
System.out.println(data.getContent());
}
public void loginlogout(MemberDTO data) {
//로그인 상태라면
if(data!=null) {
System.out.println("안녕");
}else {
System.out.println("로그인 후 이용 바람");
}
//로그아웃 상태
}
public MemberDTO login() {
MemberDTO mDTO =new MemberDTO();
System.out.println("ID 입력 >>");
mDTO.setMid(sc.next());
System.out.println("PW입력 >>");
mDTO.setMpw(sc.next());
return mDTO;//으어 피곤해
}
public String inputMID() {
System.out.println("검색할 아이디 입력");
return sc.next();
}
public String inputTITLE() {
System.out.println("검색할 제목 입력");
return sc.next();
}
public void printMenu() {
System.out.println("0. 종료");
System.out.println("1. 로그인");
System.out.println("2. 로그아웃");
System.out.println("3. 글 작성");
System.out.println("4. 글 목록 출력");
System.out.println("5. 글 내용 보기");
System.out.println("8. 이름으로 검색");
System.out.println("9. 제목으로 검색");
}
public int inputAction() {
System.out.print("번호입력 >> ");
return sc.nextInt();
}
public void printTrue() {
System.out.println("성공!");
}
public void printFalse() {
System.out.println("실패...");
}
public BoardDTO writing() {
BoardDTO data =new BoardDTO();
System.out.println("제목 입력 : ");
data.setTitle(sc.next());
System.out.println("내용 입력 : ");
data.setContent(sc.next());
return data;
}
}