[JDBC] 간단한 게시판 구현해보기

1) 테이블 생성 및 샘플 데이터 추가하기

create table boards(
	no int primary key auto_increment,
	title varchar(100) not null,
	content longtext not null,
	writer varchar(50) not null,
	date datetime default now()
);

insert into boards(no, title, content, writer, date)
values(1, "여름", "너무 덥다.", "홍길동",now()),
	(2, "겨울", "너무 춥다.", "김철수",now());
    
select * from boards;

 

2) print 메소드 클래스

package crud;

public class IO {
	public static void p(String msg) {
		System.out.print(msg);
	}
	
	public static void pl(String msg) {
		p(msg + "\n");
	}
}

 

3) DTO 클래스 (getter/setter 메소드가 존재하는 클래스)

package crud;

import java.util.Date;
import lombok.Data;

// getter, setter, toString 메소드를 생성해주는 롬복 사용
@Data
public class Boards {
	// 멤버 변수
	private int no;
	private String title;
	private String content;
	private String writer;
	private Date date;
	
	 // 생성자
	public Boards() {
		
	}
	
	public Boards(int no, String title, String content, String writer, Date date) {
		this.no = no;
		this.title = title;
		this.content = content;
		this.writer = writer;
		this.date = date;
	}
}

 

4) 게시판의 기능 메소드 클래스

4-1. 데이터베이스 접속하기

package crud;

import static crud.IO.p;
import static crud.IO.pl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import javax.naming.spi.DirStateFactory.Result;

public class BoardExApp {
	private Scanner sc;
	private Connection conn;
	
	private final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
	private final String JDBC_URL = "jdbc:mariadb://localhost/practice";
	private final String USER = "root";
	private final String PASS = "mariadb";
	
	public BoardExApp() {
		sc = new Scanner(System.in);
		getConnection();
	}
	
	// 데이터베이스 접속하기
	public void getConnection() {
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
     // 생략
 }

 

4-2. 게시물 목록 출력 및 메인 메뉴 선택하기

package crud;

import static crud.IO.p;
import static crud.IO.pl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import javax.naming.spi.DirStateFactory.Result;

public class BoardExApp {
	private Scanner sc;
	private Connection conn;
	
		// 게시물 목록을 출력하는 메소드
	public void list() {
		pl("");
		pl("[게시물 목록]");
		pl("--------------------------------------------------------------");
		System.out.printf("%-6s%-12s%-16s%-40s\n", "no", "writer", "date", "title");
		pl("--------------------------------------------------------------");

		String sql = """
				SELECT no, title, content, writer, date
				FROM boards
				ORDER BY no DESC;
				""";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				Boards boards = new Boards();
				boards.setNo(rs.getInt("no"));
				boards.setTitle(rs.getString("title"));
				boards.setContent(rs.getString("content"));
				boards.setWriter(rs.getString("writer"));
				boards.setDate(rs.getDate("date"));

				System.out.printf("%-6s%-12s%-16s%-40s\n", boards.getNo(), boards.getWriter(), 
                				boards.getDate(), boards.getTitle());
			}
			rs.close();
			pstmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
			exit();
		}
		mainMenu();
	}
	
	// 메인 메뉴 선택하는 메소드
	public void mainMenu() {
		pl("");
		pl("--------------------------------------------------------------");
		pl("메인 메뉴 : 1. Create | 2. Read | 3. Clear | 4. Exit");
		p("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		pl("");
		
		switch(menuNo) {
		case "1":
			create();
			break;
		case "2":
			read();
			break;
		case "3":
			clear();
			break;
		case "4":
			exit();
			break;
		default:
			pl("메뉴를 다시 선택해주세요.");
			break;
		}
	}
    
     // 생략
}

 

4-3. 게시물 CRUD (생성, 조회, 수정, 삭제)

package crud;

import static crud.IO.p;
import static crud.IO.pl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import javax.naming.spi.DirStateFactory.Result;

public class BoardExApp {
	private Scanner sc;
	private Connection conn;
    
    // 생략
	
	// 1. 게시물 생성하기
	private void create() {
		// 게시물 작성하기
		Boards boards = new Boards();
		pl("[새 게시물 입력]");
		p("제목 : ");
		boards.setTitle(sc.nextLine());
		p("내용 : ");
		boards.setContent(sc.nextLine());
		p("작성자 : ");
		boards.setWriter(sc.nextLine());
		
		// 게시물 작성 후 게시여부 확인하기
		pl("--------------------------------------------------------------");
		pl("게시물을 올리겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		if(menuNo.equals("1")) {
			try {
				String sql = """
						INSERT INTO boards(title, content, writer, date)
						VALUES(?, ?, ?, now());
						""";
			
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, boards.getTitle());
				pstmt.setString(2, boards.getContent());
				pstmt.setString(3, boards.getWriter());
				pstmt.executeUpdate();
				pstmt.close(); 
			}catch(SQLException se) {
				se.printStackTrace();
				exit();
			}catch (Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}
	
	// 2. 게시물 읽기
	private void read() {
		pl("[게시물 읽기]");
		p("no : ");
		int no = Integer.parseInt(sc.nextLine());
		
		try {
			String sql = """
						SELECT no, title, content, writer, date
						FROM boards
						WHERE no = ?;
					""";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, no);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				Boards boards = new Boards();
				boards.setNo(rs.getInt("no"));
				boards.setTitle(rs.getString("title"));
				boards.setContent(rs.getString("content"));
				boards.setWriter(rs.getString("writer"));
				boards.setDate(rs.getDate("date"));
				
				pl("번호 : " + boards.getNo());
				pl("제목 : " + boards.getTitle());
				pl("내용 : " + boards.getContent());
				pl("작성자 : " + boards.getWriter());
				pl("날짜 : " + boards.getDate());
				
				pl("--------------------------------------------------------------");
				pl("보조 메뉴: 1. Update | 2. Delete | 3. List");
				pl("메뉴 선택 : ");
				String menuNo = sc.nextLine();
				pl("");
				
				if(menuNo.equals("1")) {
					update(boards);
				}else if(menuNo.equals("2")){
					delete(boards);
				}
			}
			rs.close();
			pstmt.close();
		}catch(SQLException se) {
			se.printStackTrace();
			exit();
		}catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		list();
	}
	
	// 3. 게시물 전체 삭제하기
	private void clear() {
		pl("[게시물 전체 삭제]");
		pl("--------------------------------------------------------------");
		pl("게시물을 삭제 하시겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		if(menuNo.equals("1")) {
			try {
				String sql = "TRUNCATE TABLE boards";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.executeUpdate();
				pstmt.close();
			}catch(Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}
	
	// 2-1. 게시물 수정하기
	public void update(Boards boards) {
		 pl("[수정 내용 입력]");
		 pl("제목: ");
		 boards.setTitle(sc.nextLine());
		 pl("내용: ");
		 boards.setContent(sc.nextLine());
		 pl("작성자: ");
		 boards.setWriter(sc.nextLine());
		 
		 // 게시물 수정 후 확인하기
		pl("--------------------------------------------------------------");
		pl("게시물을 수정 하시겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine(); 
		if(menuNo.equals("1")) {
			try {
				String sql = """
						UPDATE boards SET title = ?, content = ?, writer = ? 
						WHERE no = ?;
						""";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, boards.getTitle());
				pstmt.setString(2, boards.getContent());
				pstmt.setString(3, boards.getWriter());
				pstmt.setInt(4, boards.getNo());
				pstmt.executeUpdate();
				pstmt.close();
			}catch(SQLException se) {
				se.printStackTrace();
				exit();
			}catch(Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}
	
	// 2-2. 게시물 선택 삭제하기
	public void delete(Boards boards) {
		try {
			String sql = """
					DELETE FROM boards WHERE no = ?;
					""";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boards.getNo());
			pstmt.executeUpdate();
			
		}catch(SQLException se) {
			se.printStackTrace();
			exit();
		}catch(Exception e) {
			e.printStackTrace();
			exit();
		}
		list();
	}

}

- 게시물 생성하기

- 게시물 읽기

 

- 게시물 수정하기

 

- 1개 게시물 삭제하기

 

- 게시물 전체 삭제하기

 

4.4 게시판 프로그램 종료하기

package crud;

import static crud.IO.p;
import static crud.IO.pl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import javax.naming.spi.DirStateFactory.Result;

public class BoardExApp {
	private Scanner sc;
	private Connection conn;
	
    // 생략
    
	// 4. 게시판 프로그램 종료하기
	public void exit() {
		if(conn != null) {
			try {
				conn.close();
			}catch(SQLException se) {
				
			}
		}
		pl("----- 게시판 종료 -----");
		System.exit(0);
		
	}
	
}

 

전체코드

package crud;

import static crud.IO.p;
import static crud.IO.pl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import javax.naming.spi.DirStateFactory.Result;

public class BoardExApp {
	private Scanner sc;
	private Connection conn;

	private final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
	private final String JDBC_URL = "jdbc:mariadb://localhost/practice";
	private final String USER = "root";
	private final String PASS = "mariadb";

	public BoardExApp() {
		sc = new Scanner(System.in);
		getConnection();
	}

	// 데이터베이스 접속하기
	public void getConnection() {
		try {
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 게시물 목록을 출력하는 메소드
	public void list() {
		pl("");
		pl("[게시물 목록]");
		pl("--------------------------------------------------------------");
		System.out.printf("%-6s%-12s%-16s%-40s\n", "no", "writer", "date", "title");
		pl("--------------------------------------------------------------");

		String sql = """
				SELECT no, title, content, writer, date
				FROM boards
				ORDER BY no DESC;
				""";

		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				Boards boards = new Boards();
				boards.setNo(rs.getInt("no"));
				boards.setTitle(rs.getString("title"));
				boards.setContent(rs.getString("content"));
				boards.setWriter(rs.getString("writer"));
				boards.setDate(rs.getDate("date"));

				System.out.printf("%-6s%-12s%-16s%-40s\n", boards.getNo(), boards.getWriter(), 
                				boards.getDate(), boards.getTitle());
			}
			rs.close();
			pstmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
			exit();
		}
		mainMenu();
	}

	// 메인 메뉴 선택하는 메소드
	public void mainMenu() {
		pl("");
		pl("--------------------------------------------------------------");
		pl("메인 메뉴 : 1. Create | 2. Read | 3. Clear | 4. Exit");
		p("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		pl("");

		switch (menuNo) {
		case "1":
			create();
			break;
		case "2":
			read();
			break;
		case "3":
			clear();
			break;
		case "4":
			exit();
			break;
		default:
			pl("메뉴를 다시 선택해주세요.");
			break;
		}
	}

	// 1. 게시물 생성하기
	private void create() {
		// 게시물 작성하기
		Boards boards = new Boards();
		pl("[새 게시물 입력]");
		p("제목 : ");
		boards.setTitle(sc.nextLine());
		p("내용 : ");
		boards.setContent(sc.nextLine());
		p("작성자 : ");
		boards.setWriter(sc.nextLine());

		// 게시물 작성 후 게시여부 확인하기
		pl("--------------------------------------------------------------");
		pl("게시물을 올리겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		if (menuNo.equals("1")) {
			try {
				String sql = """
						INSERT INTO boards(title, content, writer, date)
						VALUES(?, ?, ?, now());
						""";

				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, boards.getTitle());
				pstmt.setString(2, boards.getContent());
				pstmt.setString(3, boards.getWriter());
				pstmt.executeUpdate();
				pstmt.close();
			} catch (SQLException se) {
				se.printStackTrace();
				exit();
			} catch (Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}

	// 2. 게시물 읽기
	private void read() {
		pl("[게시물 읽기]");
		p("no : ");
		int no = Integer.parseInt(sc.nextLine());

		try {
			String sql = """
						SELECT no, title, content, writer, date
						FROM boards
						WHERE no = ?;
					""";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, no);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				Boards boards = new Boards();
				boards.setNo(rs.getInt("no"));
				boards.setTitle(rs.getString("title"));
				boards.setContent(rs.getString("content"));
				boards.setWriter(rs.getString("writer"));
				boards.setDate(rs.getDate("date"));

				pl("번호 : " + boards.getNo());
				pl("제목 : " + boards.getTitle());
				pl("내용 : " + boards.getContent());
				pl("작성자 : " + boards.getWriter());
				pl("날짜 : " + boards.getDate());

				pl("--------------------------------------------------------------");
				pl("보조 메뉴: 1. Update | 2. Delete | 3. List");
				pl("메뉴 선택 : ");
				String menuNo = sc.nextLine();
				pl("");

				if (menuNo.equals("1")) {
					update(boards);
				} else if (menuNo.equals("2")) {
					delete(boards);
				}
			}
			rs.close();
			pstmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
			exit();
		} catch (Exception e) {
			e.printStackTrace();
			exit();
		}
		list();
	}

	// 3. 게시물 전체 삭제하기
	private void clear() {
		pl("[게시물 전체 삭제]");
		pl("--------------------------------------------------------------");
		pl("게시물을 삭제 하시겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		if (menuNo.equals("1")) {
			try {
				String sql = "TRUNCATE TABLE boards";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.executeUpdate();
				pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}

	// 4. 게시판 프로그램 종료하기
	public void exit() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException se) {

			}
		}
		pl("----- 게시판 종료 -----");
		System.exit(0);

	}

	// 2-1. 게시물 수정하기
	public void update(Boards boards) {
		pl("[수정 내용 입력]");
		pl("제목: ");
		boards.setTitle(sc.nextLine());
		pl("내용: ");
		boards.setContent(sc.nextLine());
		pl("작성자: ");
		boards.setWriter(sc.nextLine());

		// 게시물 수정 후 확인하기
		pl("--------------------------------------------------------------");
		pl("게시물을 수정 하시겠습니까? : 1. Ok | 2. Cancel");
		pl("메뉴 선택 : ");
		String menuNo = sc.nextLine();
		if (menuNo.equals("1")) {
			try {
				String sql = """
						UPDATE boards SET title = ?, content = ?, writer = ?
						WHERE no = ?;
						""";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, boards.getTitle());
				pstmt.setString(2, boards.getContent());
				pstmt.setString(3, boards.getWriter());
				pstmt.setInt(4, boards.getNo());
				pstmt.executeUpdate();
				pstmt.close();
			} catch (SQLException se) {
				se.printStackTrace();
				exit();
			} catch (Exception e) {
				e.printStackTrace();
				exit();
			}
		}
		list();
	}

	// 2-2. 게시물 선택 삭제하기
	public void delete(Boards boards) {
		try {
			String sql = """
					DELETE FROM boards WHERE no = ?;
					""";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boards.getNo());
			pstmt.executeUpdate();

		} catch (SQLException se) {
			se.printStackTrace();
			exit();
		} catch (Exception e) {
			e.printStackTrace();
			exit();
		}
		list();
	}

	// 실행하기
	public static void main(String[] args) {
		BoardExApp boardExApp = new BoardExApp();
		boardExApp.list();

	}

}