DAO / DTO 구현

Statement

- Java 자체로는 SQL 구문을 이해 못하기 때문에 Statement를 이용하여 SQL 구문을 실행시킴

PreparedStatement

- Statement보다 향상된 기능

- 매개변수 '?'에 값을 대입한다

 

Resultset

- 결과값을 저장하고, 저장한 값을 한 행 단위로 불러올 수 있음

- Statement 클래스의 executeQuery(String sql) 메소드를 통해 저장함

 

1. JdbcUtils

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

public class JdbcUtil {
	private static String className = "org.mariadb.jdbc.Driver";
	private static String url = "jdbc:mariadb://localhost:3306/myapp";
	private static String user = "*****";
	private static String password = "*****";
	private static Connection conn;
	
	public static Connection getConnection() {
			try {
				Class.forName(className);
				conn = DriverManager.getConnection(url, user, password);
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}  
			return conn;
	}
	
	public static void close(Connection conn) {
		if(conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static void close(PreparedStatement conn) {
		if(conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	public static void close(ResultSet conn) {
		if(conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {
		// 생성된 것의 역순으로 close
		close(rs);
		close(stmt);
		close(conn);
	}
		
}

 

2. DAO

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.comstudy.day15.JdbcUtil;

public class BoardDAO {
	private static String SELECT_ALL = "SELECT * FROM BOARD";
	private static String SELECT_ONE = "SELECT * FROM BOARD WHERE No=?";
	private static String INSERT = "INSERT INTO BOARD(title, content, author, writeday) values(?, ?, ?, ?)";
	private static String UPDATE = "UPDATE BOARD SET title=?, content=?, author=?, writeday=? WHERE bno=?";
	private static String DELETE = "DELETE FROM BOARD WHERE bno=?";
	
	private Connection conn; // DB와의 연결을 위해 conn 객체 생성
	private PreparedStatement stmt; // 매개변수 '?'에 값을 대입한다. statement보다 향상된 성능
	private ResultSet rs; // 결과값을 저장하고, 저장한 값을 한 행 단위로 불러올 수 있음
	
	// commitCheck
	private void commitCheck(int cnt) {
		try {
			if(cnt > 0) {
				conn.commit();
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// SELECT_ALL : List 생성
	public List<BoardDTO> selectAll() {
		List<BoardDTO> list = new ArrayList<BoardDTO>();
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.prepareStatement(SELECT_ALL);
			rs = stmt.executeQuery();
			if(rs.next()) {
				int bno = rs.getInt("bno");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String author = rs.getString("author");
				String writeday = rs.getString("writeday");
				list.add(new BoardDTO(bno, title, content, author, writeday));
			}
			commitCheck(stmt.executeUpdate());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
		return list;
	}
	
	// SELECT_ONE
	public BoardDTO selectOne(BoardDTO dto) {
		BoardDTO user = null;
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.prepareStatement(SELECT_ONE);
			stmt.setInt(1, dto.getBno());
			rs = stmt.executeQuery();
			if(rs.next()) {
				int bno = rs.getInt(1);
				String title = rs.getString(2);
				String content = rs.getString(3);
				String author = rs.getString(4);
				String writeday = rs.getString(5);
				user = new BoardDTO(bno, title, content, author, writeday);
			}
			commitCheck(stmt.executeUpdate());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
		return user;
	}
	
	// INSERT
	public void insert(BoardDTO dto) {
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.prepareStatement(INSERT);
			stmt.setString(1, dto.getTitle());
			stmt.setString(2, dto.getContent());
			stmt.setString(3, dto.getAuthor());
			stmt.setString(4, dto.getWriteday());
			rs = stmt.executeQuery();
			commitCheck(stmt.executeUpdate());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
	}
	
	// UPDATE
	public void update(BoardDTO dto) {
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.prepareStatement(UPDATE);
			stmt.setString(1, dto.getTitle());
			stmt.setString(2, dto.getContent());
			stmt.setString(3, dto.getAuthor());
			stmt.setString(4, dto.getWriteday());
			stmt.setInt(5, dto.getBno());
			rs = stmt.executeQuery();
			commitCheck(stmt.executeUpdate());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
	}
	
	// DELETE
	public void delete(BoardDTO dto) {
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.prepareStatement(DELETE);
			stmt.setInt(1, dto.getBno());
			rs = stmt.executeQuery();
			commitCheck(stmt.executeUpdate());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
	}
	
	public static void main(String[] args) {
		BoardDAO dao = new BoardDAO();
		
		// test
		BoardDTO dto = new BoardDTO(0, "타이틀", "콘텐츠", "저자", "날짜");
		dao.insert(dto);
		
		List<BoardDTO> list = dao.selectAll();
		for(BoardDTO user : list) {
			System.out.println(user);
		}
	}
}

 

3. DTO

public class BoardDTO {
	private int bno; // 보드의 번호
	private String title;
	private String content;
	private String author;
	private String writeday; // 날짜

	// 디폴트 생성자
	public BoardDTO() {
		this(0, "", "", "", "");
	}
	
	// 생성자
	public BoardDTO(int bno, String title, String content, String author, String writeday) {
		this.bno = bno;
		this.title = title;
		this.content = content;
		this.author = author;
		this.writeday = writeday;
	}

	// setters & getters
	public int getBno() {
		return bno;
	}

	public void setBno(int bno) {
		this.bno = bno;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public String getWriteday() {
		return writeday;
	}

	public void setWriteday(String writeday) {
		this.writeday = writeday;
	}

	// toString
	@Override
	public String toString() {
		return "BoardDTO [bno=" + bno + ", title=" + title + ", content=" + content + ", author=" + author
				+ ", writeday=" + writeday + "]";
	}
	
}

 

4. Result