지난 시간 내용
다대다(N:M) 관계
회원은 쿠폰을 여러개 가질 수 있고, 쿠폰도 회원을 여러개 가질 수 있다. 이러한 관계를 다대다 관계라고 한다.
다대다 관계에 대해서 조금 더 알아보자.
다대다관계를 설정하기 위해서는 관계테이블을 추가해줘야 한다.
여기서 고민이 생긴다 🤯
- 회원과 쿠폰을 합친 회원_쿠폰 DAO클래스를 만들것인가?
- 회원DAO 클래스에서 회원_쿠폰관계 테이블에 대한 SQL을 실행할 것인가?
Service
트랜직션 단위로 동작하는 메소드를 가지는 클래스
비지니스로직의 실행 메소드
비지니스로직은 트랜잭션 단위로 동작한다.
addBoard() 메소드를 수정해보자
- addBoard()는 2개의 sql문이 하나의 메소드 안에 있었다.
- 바람직 하지 않다! 바꾸어보자.
원래 BoardDao클래스
package com.example.jdbcexam01;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BoardDao {
List<Board> getBoards(int start, int size){
List<Board> list = new ArrayList<>();
try{
Connection conn = DBUtil.getConnection();
String sql = "select id, name, title, read_count, created from board order by created desc limit ?,?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,start); // binding
ps.setInt(2,size); // bindign
ResultSet rs = ps.executeQuery(); // (select)
while(rs.next()){
long id = rs.getLong("id");
String name = rs.getString("name");
String title = rs.getString("title");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
Board board = new Board(id, name, title, null, readcount, created);
list.add(board);
}
DBUtil.close(rs, ps , conn);
}catch (SQLException ex){
System.out.println("db error message"+ex.getMessage());
}
return list;
}
public int addBoard(Board board){
int updateCount = 0;
try{
Connection conn = DBUtil.getConnection(); //
String sql = "insert into board(name, title, content) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, board.getName());
ps.setString(2,board.getTitle());
ps.setString(3,board.getContent());
updateCount = ps.executeUpdate(); //executeU
DBUtil.close(ps , conn);
}catch (SQLException ex){
System.out.println("db error message"+ex.getMessage());
}
return updateCount;
}
public Board getBoard(long id){
Board board = null;
Connection conn = null;
try{
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
String sql = "select name, title, content, read_count, created from board where id = ?"; //
PreparedStatement ps = conn.prepareStatement(sql); //
ps.setLong(1, id);
ResultSet rs = ps.executeQuery(); // SQL(select)
while(rs.next()){
String name = rs.getString("name");
String title = rs.getString("title");
String content = rs.getString("content");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
board = new Board(id, name, title, content, readcount, created);
}
ps.close();
String sql2 = "update board\n" +
"set read_count = read_count+ 1\n" +
"where id = ?";
ps = conn.prepareStatement(sql2);
ps.setLong(1,id);
ps.executeUpdate();
conn.commit();
DBUtil.close(rs, ps , conn);
}catch (SQLException ex){
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
System.out.println("db error message"+ex.getMessage());
}
return board;
}
}getBoard메소드에는 sql문이 2개가 같이 있다. 이를 분리해 보자.
- updateReadCount 메소드
package com.example.jdbcexam01;
import com.mysql.cj.x.protobuf.MysqlxPrepare;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public int updateReadCount(long id) {
int updateCount = 0;
Board board = null;
Connection conn = null;
try {
conn= DBUtil.getConnection();
conn.setAutoCommit(false);
String sql2 = "update board\n" +
"set read_count = read_count+ 1\n" +
"where id = ?";
PreparedStatement ps = conn.prepareStatement(sql2);
ps.setLong(1,id);
updateCount = ps.executeUpdate();
conn.commit();
DBUtil.close(ps , conn);
}catch (SQLException ex){
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("db error message"+ex.getMessage());
}
return updateCount;
}
}
getBoard 메소드
public Board getBoard(long id) {
Board board = null;
Connection conn = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
String sql = "select name, title, content, read_count, created from board where id = ?"; //
PreparedStatement ps = conn.prepareStatement(sql); //
ps.setLong(1, id);
ResultSet rs = ps.executeQuery(); // SQL(select)
while (rs.next()) {
String name = rs.getString("name");
String title = rs.getString("title");
String content = rs.getString("content");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
board = new Board(id, name, title, content, readcount, created);
}
} catch (SQLException ex) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("db error message" + ex.getMessage());
}
return board;
}
위의 코드를 하나의 트랜잭션에서 처리해보자.
- BoardService클래스 만들기
package com.example.jdbcexam01;
import java.util.List;
public class BoardService {
public List<Board> getBoards(int start, int size){
BoardDao boardDao = new BoardDao();
List<Board> boards = boardDao.getBoards(start,size);
return boards;
}
public int addBoard(Board board){
BoardDao boardDao = new BoardDao();
int updateCount = boardDao.addBoard(board);
return updateCount;
}
public Board getBoard(long id){
BoardDao boardDao = new BoardDao();
Board board = boardDao.getBoard(id);
boardDao.updateReadCount(id);
return board;
}
}Board board = boardDao.getBoard(id);
boardDao.updateReadCount(id);위의 2개의 메소드가 하나의 트랜잭션으로 묶이려면 같은 Connection을 사용해야한다.
getBoard, updateReadCount 메소드를 보면,
위와 같이 각각 다른 커넥션을 사용하고 있다.
BoardService가 가지는 메소드는 여러개의 Dao의 메소드를 실행할 수 있어야 한다.
하나의 트랜잭션으로 묶여야 한다.
서비스가 가지는 메소드는 여러개의 Dao의 메소드를 실행할 수 있어야 한다. 하나의 트랜잭션으로 묶여야 한다.
아래와 같이 getBoard메소드를 바꾸어 주자
public Board getBoard(long id) {
BoardDao boardDao = new BoardDao();
Connection conn = null;
Board board = null;
try{
conn = DBUtil.getConnection();
board = boardDao.getBoard(id);
boardDao.updateReadCount(id);
}catch(Exception ex){
}
return board;
}DBUtil 클래스에서 AutoCommit을 false로 지정한다.
public static void close(Connection conn){
try {
conn.close();
} catch(SQLException e){
e.printStackTrace();
}
}DBUtil 클래스에 위와 같이 close메소드를 추가하자.
현재 AutoCommit이 false로 지정되어 있으므로, 다시 BoardService클래스의 getBoard메소드를 확인해보자.
public Board getBoard(long id){
BoardDao boardDao = new BoardDao();
Connection conn = null;
Board board = null;
try{
conn = DBUtil.getConnection();
board = boardDao.getBoard(id);
boardDao.updateReadCount(id);
conn.commit();
}catch(Exception ex){
try{
conn.rollback();
} catch(SQLException e){
e.printStackTrace();
}
}
DBUtil.close(conn);
return board;
}
}여기서 getBoard와 updateReadCount는 같은 Connection을 사용해야 한다.
board = boardDao.getBoard(id);
boardDao.updateReadCount(id);부분을 getBoard,updateReadCount메소드에 Connection 객체를 매개변수로 받도록 설정하자.
board = boardDao.getBoard(conn,id);
boardDao.updateReadCount(conn,id);👩💻 간단 게시판 코드
Board 클래스
package com.example.jdbcexam01;
import java.sql.Date;
public class Board {
private long id;
private String name;
private String title;
private String content;
public Board(){
}
public Board(String name, String title, String content) {
this.name = name;
this.title = title;
this.content = content;
}
public Board(long id, String name, String title, String content, long readCount, Date created) {
this.id = id;
this.name = name;
this.title = title;
this.content = content;
this.readCount = readCount;
this.created = created;
}
private long readCount;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
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 long getReadCount() {
return readCount;
}
public void setReadCount(long readCount) {
this.readCount = readCount;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
private Date created;
@Override
public String toString() {
return "Board{" +
"id=" + id +
", name='" + name + '\'' +
", title='" + title + '\'' +
", content='" + content + '\'' +
", readCount=" + readCount +
", created=" + created +
'}';
}
}
BoardDao 클래스
package com.example.jdbcexam01;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class BoardDao {
List<Board> getBoards(Connection conn, int start, int size) throws SQLException{
List<Board> list = new ArrayList<>();
String sql = "select id, name, title, read_count, created from board order by created desc limit ?,?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,start); // binding
ps.setInt(2,size); // bindign
ResultSet rs = ps.executeQuery(); // (select)
while(rs.next()) {
long id = rs.getLong("id");
String name = rs.getString("name");
String title = rs.getString("title");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
Board board = new Board(id, name, title, null, readcount, created);
list.add(board);
}
DBUtil.close(rs,ps);
return list;
}
public int addBoard(Connection conn, Board board) throws SQLException{
int updateCount = 0;
String sql = "insert into board(name, title, content) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, board.getName());
ps.setString(2,board.getTitle());
ps.setString(3,board.getContent());
updateCount = ps.executeUpdate(); //executeU
DBUtil.close(ps);
return updateCount;
}
public Board getBoard(Connection conn, long id) throws SQLException{
Board board = null;
String sql = "select name, title, content, read_count, created from board where id = ?"; //
PreparedStatement ps = conn.prepareStatement(sql); //
ps.setLong(1, id);
ResultSet rs = ps.executeQuery(); // SQL(select)
while (rs.next()) {
String name = rs.getString("name");
String title = rs.getString("title");
String content = rs.getString("content");
int readcount = rs.getInt("read_count");
Date created = rs.getDate("created");
board = new Board(id, name, title, content, readcount, created);
}
DBUtil.close(rs,ps);
return board;
}
public int updateReadCount(Connection conn, long id) throws SQLException {
int updateCount = 0;
String sql2 = "update board\n" +
"set read_count = read_count+ 1\n" +
"where id = ?";
PreparedStatement ps = conn.prepareStatement(sql2);
ps.setLong(1,id);
updateCount = ps.executeUpdate();
DBUtil.close(ps);
return updateCount;
}
}
BoardService 클래스
package com.example.jdbcexam01;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class BoardService {
public List<Board> getBoards(int start, int size){
BoardDao boardDao = new BoardDao();
List<Board> boards = new ArrayList<>();
Connection conn = null;
try{
conn = DBUtil.getConnection();
boards = boardDao.getBoards(conn,start,size);
}catch(Exception ex){
throw new RuntimeException(ex);
}finally {
DBUtil.close(conn);
}
return boards;
}
public int addBoard(Board board){
BoardDao boardDao = new BoardDao();
Connection conn = null;
int updateCount = 0;
try{
conn = DBUtil.getConnection();
updateCount = boardDao.addBoard(conn, board);
conn.commit();
}catch (Exception ex){
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
throw new RuntimeException(ex);
}finally{
DBUtil.close(conn);
}
return updateCount;
}
public Board getBoard(long id) {
BoardDao boardDao = new BoardDao();
Connection conn = null;
Board board = null;
try{
conn = DBUtil.getConnection();
board = boardDao.getBoard(conn, id);
boardDao.updateReadCount(conn, id);
conn.commit();
}catch(Exception ex){
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
DBUtil.close(conn);
return board;
}
}
DBUtil 클래스
package com.example.jdbcexam01;
import java.sql.*;
public class DBUtil {
public static Connection getConnection() throws SQLException {
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb","root","gkseogh1!23");
conn.setAutoCommit(false);
return conn;
}
public static void close(PreparedStatement ps) throws SQLException{
ps.close();
}
public static void close(ResultSet rs, PreparedStatement ps) throws SQLException{
rs.close();
ps.close();
}
public static void close(Connection conn){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
실행해보자. BoardServiceTest
public class BoardServiceTest {
public static void main(String[] args) {
try{
BoardService boardService = new BoardService();
List<Board> boards= boardService.getBoards(0,30);
for(Board board : boards){
System.out.println(board);
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}addBoard메소드 테스트하기
package com.example.jdbcexam01;
import java.util.List;
public class BoardServiceTest {
public static void main(String[] args) {
try{
BoardService boardService = new BoardService();
// List<Board> boards= boardService.getBoards(0,30);
// for(Board board : boards){
// System.out.println(board);
// }
Board board = new Board("HOLLYWOOD", "BLACKSKERT", "VERY GOOD MUSIC");
int updateCount = boardService.addBoard(board);
System.out.println(updateCount);
}catch(Exception ex){
ex.printStackTrace();
}
}
}
저장이 잘 되는 것을 확인할 수 있다.
id로 검색 조회하기
Board board = boardService.getBoard(6L);
System.out.println(board);
그래야만 한다