개발자로가는길

1. model

1) src - model.DataSource.java

package model; import javax.sql.DataSource; import org.apache.tomcat.dbcp.dbcp2.BasicDataSource; /* * javax.sql.DataSource Interface는 다양한 DBCP 구현체를 표준화 하는 인터페이스 * DataSource Interface 타입으로 DAO에서 사용하면 DBCP 구현체가 변경되도 별도 코드 수정 필요 없음 */ public class DataSourceManager { private static DataSourceManager instance=new DataSourceManager(); private DataSource dataSource; private DataSourceManager() { BasicDataSource dbcp=new BasicDataSource(); dbcp.setDriverClassName("org.mariadb.jdbc.Driver"); dbcp.setUrl("jdbc:mysql://localhost:3306/study_db"); dbcp.setUsername("study_user"); dbcp.setPassword("user"); //동시에 사용될 수 있는 커넥션 수 설정, 기본은 8 dbcp.setMaxTotal(20); dataSource=dbcp; } public static DataSourceManager getInstance() { return instance; } public DataSource getDataSource() { return dataSource; } }

2) src - model.ClientDTO

3) src - model.ClientDAO

4)src - model.BoardDTO

package model; public class BoardDTO { private int boardNo; private String boardTitle; private String boardContent; private String clientId; private String boardDate; private int boardCount; public BoardDTO() { super(); } public BoardDTO(int boardNo, String boardTitle, String boardContent, String clientId, String boardDate, int boardCount) { super(); this.boardNo = boardNo; this.boardTitle = boardTitle; this.boardContent = boardContent; this.clientId = clientId; this.boardDate = boardDate; this.boardCount = boardCount; } public int getBoardNo() { return boardNo; } public void setBoardNo(int boardNo) { this.boardNo = boardNo; } public String getBoardTitle() { return boardTitle; } public void setBoardTitle(String boardTitle) { this.boardTitle = boardTitle; } public String getBoardContent() { return boardContent; } public void setBoardContent(String boardContent) { this.boardContent = boardContent; } public String getClientId() { return clientId; } public void setClientId(String clientId) { this.clientId = clientId; } public String getBoardDate() { return boardDate; } public void setBoardDate(String boardDate) { this.boardDate = boardDate; } public int getBoardCount() { return boardCount; } public void setBoardCount(int boardCount) { this.boardCount = boardCount; } @Override public String toString() { return "BoardDTO [boardNo=" + boardNo + ", boardTitle=" + boardTitle + ", boardContent=" + boardContent + ", clientId=" + clientId + ", boardDate=" + boardDate + ", boardCount=" + boardCount + "]"; } }

5) src - mordel.BoardDAO

package model; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.sql.DataSource; public class BoardDAO { private static BoardDAO instance=new BoardDAO(); private DataSource dataSource; private BoardDAO(){ dataSource=DataSourceManager.getInstance().getDataSource(); } public static BoardDAO getInstance() throws ClassNotFoundException{ return instance; } public void closeAll(PreparedStatement pstmt,Connection con) throws SQLException{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con) throws SQLException{ closeAll(pstmt,con); if(rs!=null) rs.close(); } public Connection getConnection() throws SQLException{ return dataSource.getConnection(); } //여기서부터 코딩 }

※ DB connection 테스트

1) src - mordel.BoardDAO 에 checkDbconnect 추가

//DB Connection Test public void checkDbconnect()throws SQLException{ Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; ArrayList<ClientDTO> list=new ArrayList<ClientDTO>(); try { con=dataSource.getConnection(); String sql="select * from client"; pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()) { ClientDTO resultDTO=new ClientDTO(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)); list.add(resultDTO); } for(int i=0;i<list.size();i++) System.out.println(list.get(i)); }finally { closeAll(rs, pstmt, con); } }

2) src - test.TestDBConnection.java 

package test; import java.sql.SQLException; import model.BoardDAO; public class TestDBConnection { public static void main(String[] args) throws ClassNotFoundException, SQLException { BoardDAO.getInstance().checkDbconnect(); } }

[참고] Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory 에러발생 시

- tomcat-juli.jar파일 다운로드(톰캣버전에 맞게) : https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-juli

- Java\jdk1.8.0_211\jre\lib\ext 안에 tomcat-juli.jar 파일을 넣는다.

2. controller

1) src - controller.DispatcherServelt.java

package controller; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("/DispatcherServlet") public class DispatcherServlet extends HttpServlet { private static final long serialVersionUID = 1L; public DispatcherServlet() { super(); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { handleRequest(request, response); } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); handleRequest(request, response); } private void handleRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{ try { String command=request.getParameter("command"); String url=HandlerMapping.getInstance().create(command).execute(request,response); if(url.trim().startsWith("redirect:")) response.sendRedirect(url.trim().substring(9)); else request.getRequestDispatcher(url).forward(request, response); } catch (Exception e) { e.printStackTrace(); response.sendRedirect("error.jsp"); } } }

2) src - controller.HandlerMapping.java

package controller; public class HandlerMapping { private static HandlerMapping instance=new HandlerMapping(); private HandlerMapping() {} public static HandlerMapping getInstance() { return instance; } public Controller create(String command) { Controller c=null; //메인 //회원관리 //게시판 return c; } }

3) src - controller.Controller.java(인터페이스)

package controller; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public interface Controller { public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception; }

* 기능 명세서

1. ERD

2. Class Diagram

1. 홈화면

   1) WebContent - index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Model1 Architecture</title> </head> <body> <h3>Model1 Architecture</h3> <p>Model1 JSP 파일 내에서 view와 컨트롤러 모두 실행되는 구조</p> <img src="model1.jpg"/> <ul> <li> <a href="allMemberCount.jsp">전체 회원수 보기</a> </li> <li> <form action="findMemberListByname.jsp"> 이름<input type="text" name="name" required="required"> <input type="submit" value="검색"> </form> </li> </ul> </body> </html>

   - 12번 째 줄 이미지  

   2) src - loader.DbDriverLoader.java

package loader; import javax.servlet.ServletContextEvent; import javax.servlet.ServletContextListener; import javax.servlet.annotation.WebListener; @WebListener public class DbDriverLoader implements ServletContextListener{ public DbDriverLoader() { } public void contextDestroyed(ServletContextEvent sce) { } public void contextInitialized(ServletContextEvent event) { try { Class.forName("org.mariadb.jdbc.Driver"); System.out.println("db driver loading..."); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }

   3) src - model.MemberVO.jsp

package model; public class MemberVO { private String id; private String name; private String email; public MemberVO(String id, String name, String email) { super(); this.id = id; this.name = name; this.email = email; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } @Override public String toString() { return "MemberVO [id=" + id + ", name=" + name + ", email=" + email + "]"; } }

   4) src - model.MemberDAO.java

package model; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; // Singleton Design Pattern 적용 public class MemberDAO { //singleton private static MemberDAO instance=new MemberDAO(); private MemberDAO() {} public static MemberDAO getInstance() { return instance; } //db connection public Connection getConnection() throws SQLException{ return DriverManager.getConnection("jdbc:mysql://localhost:3306/study_db","study_user","user"); } public void closeAll(PreparedStatement pstmt,Connection con) throws SQLException{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con) throws SQLException{ if(rs!=null) rs.close(); closeAll(pstmt,con); } }

2. 전체 회원수 

   1) WebContent - allMemberCount.jsp

<%@ page import="model.MemberDAO" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>model1 전체 회원수</title> </head> <body> <% MemberDAO dao=MemberDAO.getInstance(); %> 총회원수:<%=dao.getMemberCount() %> 명 </body> </html>

   2) src - model.MemberDAO.java

//회원수 전체를 가져오는 메소드 public int getMemberCount() throws SQLException{ int count=0; Connection con=null; ResultSet rs=null; PreparedStatement pstmt=null; try { con=getConnection(); String sql="select count(*) from member"; pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()) count=rs.getInt(1); } finally { closeAll(rs,pstmt, con); } return count; }

3. 회원 검색

   1) WebContent - findMemberListByname.jsp

<%@page import="model.MemberVO" %> <%@page import="java.util.ArrayList"%> <%@page import="model.MemberDAO"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>회원검색 결과</title> <style> table, td, th { border: 1px solid black; } table { border-collapse: collapse; width: 50%; } th,td { text-align: center; } </style> </head> <body> <% String name=request.getParameter("name"); ArrayList<MemberVO> list=MemberDAO.getInstance().findMemberListByname(name); if(list.isEmpty()){ %> <script type="text/javascript"> alert("<%=name%>라는 회원이 없습니다"); location.href="index.jsp"; </script> <% }else{ %> <%=name %> 회원 정보<br><br> <table> <thead> <tr> <th>순번</th><th>이름</th><th>아이디</th><th>이메일</th> </tr> </thead> <tbody> <%for(int i=0;i<list.size();i++){ %> <tr> <td><%=i+1 %></td> <td><%=list.get(i).getId() %></td> <td><%=list.get(i).getName() %></td> <td><%=list.get(i).getEmail() %></td> </tr> <% } %> </tbody> </table> <% } %> </body> </html>

   2) src - model.MemberDAO.java

//회원 검색 정보 가져오는 메소드 public ArrayList<MemberVO> findMemberListByname(String name) throws SQLException{ ArrayList<MemberVO> list=new ArrayList<MemberVO>(); Connection con=null; ResultSet rs=null; PreparedStatement pstmt=null; try { con=getConnection(); String sql="select id,name,email from member where name=?"; pstmt=con.prepareStatement(sql); pstmt.setString(1, name); rs=pstmt.executeQuery(); while(rs.next()) list.add(new MemberVO(rs.getString(1),rs.getString(2),rs.getString(3))); } finally { closeAll(rs, pstmt, con); } return list; }

[참고] Model1 개념 참고 https://thatisgood.tistory.com/entry/model1-vs-model2-%EC%B0%A8%EC%9D%B4%EC%A0%90

1. jdk에 MariaDB jdbc jar파일 넣기

① 다운로드 사이트 : https://downloads.mariadb.org/ 

- 다운로드 사이트에서 MariaDB Connector/J 2.4 Series 다운로드

② 아래쪽 MariaDB Connector/J .jar files 클릭

③ 중간에 "mariadb-java-client-2.4.2.jar" 파일 선택 > 다운로드 됨

④ 다운로드받은 파일을 본인의 JDK 경로에 넣어준다. 

    - C:\자바가설치된경로\jdk1.8.0_211\jre\lib\ext 안에 넣어준다.

2. 이클립스에서 Connection 테스트

① 이클립스를 켜서 Dynamic Web Project를 생성한다. 생성하고 나면 "Java Resources > Libraries > JRE System Library > mariadb-java-client-2.4.2.jar" 파일이 보인다.

② 먼저 HediSQL에서 DB,use를 생성하고 user에게 DB권한을 준다. 

# DATABASE 생성 CREATE DATABASE study_db default CHARACTER SET UTF8; # 생성된 DATABASE 확인 SHOW DATABASES; # 사용자 생성 CREATE USER 'study_user'@'localhost' identified by 'user'; # 생성된 사용자 조회 SELECT USER, HOST FROM USER; # 생성된 사용자(USER)에게 DB 권한 부여 GRANT ALL PRIVILEGES ON study_db.* TO study_user@'localhost' IDENTIFIED BY 'user';

③ src에 step1에 TestJDBC.jar 파일을 생성해준다.

package step1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class TestJDBC { public static void main(String[] args) throws SQLException { String driver="org.mariadb.jdbc.Driver"; String dbUrl="jdbc:mysql://localhost:3306/study_db"; try { //1.데이터베이스 드라이버 로딩 Class.forName(driver); System.out.println("DB Driver Loading"); //2.데이터베이스 서버와 연결 Connection con=DriverManager.getConnection(dbUrl,"study_user","user"); System.out.println("DB Connection:"+con); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }

④ 실행시 DB Connection 메세지가 정상적으로 뜨면 완료.