학원 #76일차: 프로젝트에 MyBatis 적용: MyBatis가 join 결과를 다루는 방법, 프로그램에서 사용하는 객체를 listener에서 준비시키기, sqlSessionFactory 공유,

  public Board findByNo(int no) throws Exception {
    // mybatis 객체 준비

    // => mybatis 설정 파일을 읽어 입력 스트림을 준비한다.
    // 입력 스트림에서 mybatis 설정 값을 읽어 SqlSessionFactory를 만든다.
    SqlSessionFactory sqlSessionFactory 
    = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("com/eomcs/pms/conf/mybatis/-config.xml"));

    // => SqlSessionFactory에서 SqlSession 객체를 얻는다.
    //    SqlSession 객체는 SQL문을 실행하는 객체다.
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      Board board = sqlSession.selectOne("BoardDao.findByNo", no);
      sqlSession.update("BoardDao.updateViewCount", no);
      // SqlSession 객체에게 별도 파일에 분리한 SQL을 찾아 실행하라고 말한다.
      return board;
    }
  }

  @Override
  public int update(Board board) throws Exception {
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(
        Resources.getResourceAsStream("com/eomcs/pms/conf/mybatis-config.xml"));

    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.update("BoardDao.update", board);
    }
  }

public class BoardDaoImpl implements com.eomcs.pms.dao.BoardDao{

  Connection con;

  public BoardDaoImpl(Connection con) {
    this.con = con;
  }

  @Override
  public int insert(Board board) throws Exception {
    InputStream inputStream = Resources.getResourceAsStream(
        "com/eomcs/pms/conf/mybatis-config.xml");
    SqlSessionFactory sqlSessionFactory =
        new SqlSessionFactoryBuilder().build(inputStream);

    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {

      return sqlSession.insert("BoardDao.insert", board);
    }
  }

  @Override
  public int delete(int no) throws Exception {
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(
        Resources.getResourceAsStream("com/eomcs/pms/conf/mybatis-config.xml"));

    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.delete("BoardDao.delete", no);
    }
  }
  //,,,,

public class AppInitListener implements ApplicationContextListener {
  @Override
  public void contextInitialized(Map<String,Object> context) {
    System.out.println("프로젝트 관리 시스템(PMS)에 오신 걸 환영합니다!");

    // 시스템에서 사용할 객체를 준비한다.
    try {
      Connection con = DriverManager.getConnection(
          "jdbc:mysql://localhost:3306/studydb?user=study&password=1111");
      context.put("con", con);
      
      
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(
          Resources.getResourceAsStream("com/eomcs/pms/conf/mybatis-config.xml"));
      context.put("sqlSessionFactory", sqlSessionFactory);

    } catch (Exception e) {
      System.out.println("DB 커넥션을 준비하는 중에 오류 발생");
      e.printStackTrace();
    }
  }

BoardDao boardDao = new BoardDaoImpl(sqlSessionFactory);

Map<String,Command> commandMap = new HashMap<>();

commandMap.put("/board/add", new BoardAddCommand(boardDao, memberDao));
commandMap.put("/board/list", new BoardListCommand(boardDao));
commandMap.put("/board/detail", new BoardDetailCommand(boardDao));
commandMap.put("/board/update", new BoardUpdateCommand(boardDao));
commandMap.put("/board/delete", new BoardDeleteCommand(boardDao));
//..
context.put("commandMap", commandMap);

public class MemberDaoImpl implements com.eomcs.pms.dao.MemberDao {

  SqlSessionFactory sqlSessionFactory;

  public MemberDaoImpl(SqlSessionFactory sqlSessionFactory) {
    this.sqlSessionFactory = sqlSessionFactory;
  }

  @Override
  public int insert(Member member) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.insert("MemberDao.insert", member);
    }
  }

  @Override
  public int delete(int no) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.delete("MemberDao.delete", no);
    }
  }

  @Override
  public Member findByNo(int no) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.selectOne("MemberDao.findByNo", no);
    }
  }

  @Override
  public Member findByName(String name) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      List<Member> members = sqlSession.selectList("MemberDao.findByName", name);
      if (members.size() > 0) {
        return members.get(0);
      } else {
        return null;
      }
    }
  }

  @Override
  public List<Member> findAll() throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      return sqlSession.selectList("MemberDao.findAll");
    }
  }
  // select는 값은 조회하는 것이기 때문에 autocommit을 설정할 필요가 없다.

  @Override
  public int update(Member member) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
      return sqlSession.update("MemberDao.update", member);
    }
  }

  @Override
  public List<Member> findByProjectNo(int projectNo) throws Exception {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      return sqlSession.selectList("MemberDao.findByProjectNo", projectNo);
    }// select는 값은 조회하는 것이기 때문에 autocommit을 설정할 필요가 없다.
  }

  @Override
  public Member findByEmailPassword(String email, String password) throws Exception {
    HashMap<String,Object> map = new HashMap<>();
    map.put("email", email);
    map.put("password", password);

    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      return sqlSession.selectOne("MemberDao.findByEmailPassword", map);
    }// select는 값은 조회하는 것이기 때문에 autocommit을 설정할 필요가 없다.
  }
}

public class Project {
  //..
  private Member owner;
  private List<Member> members;
  //..
  public void setOwner(Member owner) {
    this.owner = owner;
  }
  public void setMembers(List<Member> members) {
    this.members = members;
  }
}

Project p = new Project();
p.setNo(rs.getInt("no"));
p.setTitle(rs.getString("title"));
p.setRegisteredDate(rs.getDate("cdt"));
p.setViewCount(rs.getInt("vw_cnt"));

Member m = new Member();
m.setNo(rs.getInt("no"));
m.setName(rs.getString("name"));
p.setOwner(m);

public class ProjectListCommand implements Command {
  ProjectDao projectDao;
  MemberDao memberDao;

  public ProjectListCommand(ProjectDao projectDao, MemberDao memberDao) {
    this.projectDao = projectDao;
    this.memberDao = memberDao;
  }

  @Override
  public void execute(Map<String,Object> context) {
    System.out.println("[프로젝트 목록]");

    try {
      List<Project> list = projectDao.findAll();
      System.out.println("번호, 프로젝트명, 시작일 ~ 종료일, 관리자, 팀원");

      for (Project project : list) {
        StringBuilder members = new StringBuilder();
        for (Member member : memberDao.findByProjectNo()) {
          if (members.length() > 0) {
            members.append(",");
          }
          members.append(member.getName());
        }

        System.out.printf("%d, %s, %s ~ %s, %s, [%s]\n",
            project.getNo(),
            project.getTitle(),
            project.getStartDate(),
            project.getEndDate(),
            project.getOwner().getName(),
            members.toString());
      }
    } catch (Exception e) {
      System.out.println("프로젝트 목록 조회 중 오류 발생!");
      e.printStackTrace();
    }
  }
}

@Override
public int insert(Project project) throws Exception {

  // 트랜젝션을 위해 오토커밋을 비활성화한다.
  try (SqlSession sqlSession = sqlSessionFactory.openSession()) {

    // 프로젝트 정보 입력
    int count = sqlSession.insert("ProjectDao.insert", project);

    // 프로젝트의 멤버 정보 입력
    for (Member member : project.getMembers()) {
      HashMap<String,Object> map = new HashMap<>();
      map.put("memberNo", member.getNo());
      // 객체에 프로젝트 no 프로퍼티가 등록되어있어야 한다.
      map.put("projectNo", project.getNo());
      sqlSession.insert("ProjectDao.insertMember", map);
    }
    
    // 묶인 작업이 다 끝났을 때 커밋을 한다.
    sqlSession.commit();
    return count;
  }
}

Cannot add or update a child row: a foreign key constraint fails (`studydb`.`pms_member_project`, CONSTRAINT `pms_member_project_fk2` FOREIGN KEY (`project_no`) REFERENCES `pms_project` (`no`))

@Override
// no 파라미터가 비어 있는 project 객체
public int insert(Project project) throws Exception {
  try (SqlSession sqlSession = sqlSessionFactory.openSession()) {

    // insert를 하면 project 객체의 no 값이 채워져야 한다.
    int count = sqlSession.insert("ProjectDao.insert", project);

    for (Member member : project.getMembers()) {
      HashMap<String,Object> map = new HashMap<>();
      map.put("memberNo", member.getNo());
      // 객체에 프로젝트 no 프로퍼티가 등록되어있어야 한다.
      map.put("projectNo", project.getNo());
      sqlSession.insert("ProjectDao.insertMember", map);
    }
    
    sqlSession.commit();
    return count;
  }
}

@Override
public int delete(int no) throws Exception {
  try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
    // 프로젝트에 소속된 모든 멤버를 삭제한다.
    sqlSession.delete("ProjectDao.deleteMembers", no);

    // => 프로젝트를 삭제한다.
    int count = sqlSession.delete("ProjectDao.delete", no);

    sqlSession.commit();
    return count;
  }
}

@Override
public int update(Project project) throws Exception {
  try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
    int count = sqlSession.update("ProjectDao.update", project);
    if (count == 0) {
      return 0;
    }

    // 프로젝트 팀원 변경한다.
    // => 기존에 설정된 모든 팀원을 삭제한다.
    sqlSession.delete("ProjectDao.deleteMembers", project.getNo());

    // => 새로 팀원을 입력한다.
    for (Member member : project.getMembers()) {
      HashMap<String,Object> map = new HashMap<>();
      map.put("memberNo", member.getNo());
      map.put("projectNo", project.getNo());
      sqlSession.insert("ProjectDao.insertMember", map);
    }
    sqlSession.commit();
    return 1;
  }
}