야구 선수 게시판 예제 만들기

2020. 6. 16. 17:04

기본구조

데이터베이스 테이블 추가

팀목록 테이블

CREATE TABLE TEAMLIST 
(
  ID NUMBER NOT NULL 
, TEAMNAME VARCHAR2(50) 
, CONSTRAINT TEAMLIST_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

ALTER TABLE TEAMLIST
ADD CONSTRAINT TEAMLIST_UK1 UNIQUE 
(
  TEAMNAME 
)
ENABLE;

선수목록 테이블

CREATE TABLE PLAYERLIST 
(
  ID NUMBER NOT NULL 
, PLAYERNUM NUMBER 
, PLAYERNAME VARCHAR2(20) 
, POSITION VARCHAR2(20) 
, TEAMNAME VARCHAR2(50) 
, CONSTRAINT PLAYERLIST_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

ALTER TABLE PLAYERLIST
ADD CONSTRAINT PLAYERLIST_FK1 FOREIGN KEY
(
  TEAMNAME 
)
REFERENCES TEAMLIST
(
  TEAMNAME 
)
ON DELETE SET NULL ENABLE;

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	response.sendRedirect("/baseball/team?cmd=home");
%>

home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>야구선수 정보</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
    <div class="row">
        <div class="col-sm-3">
       	  <h2>팀 목록</h2>           
		  <table class="table table-hover">
		    <thead>
		      <tr>
		        <th>번호</th>
		        <th>팀 이름</th>
		      </tr>
		    </thead>
		    <tbody>
		    	<c:forEach var="team" items="${teamList}">
			      <tr>
			        <td>${team.id}</td>
			        <td onclick="getPlayer('${team.teamName}')" style="cursor: pointer;">${team.teamName}</td>
			      </tr>
		    	</c:forEach>
		    </tbody>
		  </table>
        </div>
        <div class="col-sm-3">
        	<h2>선수 목록</h2>           
		  <table class="table table-success">
		    <thead>
		      <tr>
		        <th>선수 번호</th>
		        <th>선수 이름</th>
		      </tr>
		    </thead>
		    <tbody id="player__list">
		    </tbody>
		  </table>
        </div>
        <div class="col-sm-6">
        	<h2>선수 상세</h2>           
		  <table class="table table-info">
		    <thead>
		      <tr>
		        <th>선수 번호</th>
		        <th>선수 이름</th>
		        <th>포지션</th>
		      </tr>
		    </thead>
		    <tbody id="player__desc">
		    </tbody>
		  </table>
        </div>
    </div>
</div>

<script type="text/javascript">

	function getPlayer(teamName) {
		$.ajax({
			type: "get",
			url: "/baseball/player?cmd=getPlayer&teamName="+teamName,
			dataType: "json"
		}).done(function(result) {
			
			$("#player__list").empty();
			for (let player of result) {
				var string =
					"			      <tr>\r\n" + 
					"			        <td>"+player.playerNum+"</td>\r\n" + 
					"			        <td onclick=\"getDesc('"+player.playerNum+"','"+teamName+"')\" style=\"cursor: pointer;\">"+player.playerName+"</td>\r\n" + 
					"			      </tr>";
					
				$("#player__list").append(string);
					
			}
			
			$("#player__desc").empty();
			
		}).fail(function(result) {
			alert("검색에 실패하였습니다.");
		});
	}
	
	function getDesc(playerNum, teamName) {
		
		$.ajax({
			type: "get",
			url: "/baseball/player?cmd=getDesc&teamName="+teamName+"&playerNum="+playerNum,
			dataType: "json"
		}).done(function(result) {
			
			$("#player__desc").empty();

			var string =
				"			      <tr>\r\n" + 
				"			        <td>"+result.playerNum+"</td>\r\n" + 
				"			        <td>"+result.playerName+"</td>\r\n" + 
				"			        <td>"+result.position+"</td>\r\n" +
				"			      </tr>";
				
			$("#player__desc").append(string);

		}).fail(function(result) {
			alert("검색에 실패하였습니다.");
		});
		
	}

</script>

</body>
</html>

Dbconn.java

package com.jaybon.baseball.db;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConn {
	public static Connection getConnection() {
		try {
			Context initContext = new InitialContext();
			Context envContext = (Context) initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource) envContext.lookup("jdbc/myoracle");
			Connection conn = ds.getConnection();
			PreparedStatement pstmt;
			ResultSet rs;
			System.out.println("DBConn : 데이터베이스 연결성공");
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("DBConn : 데이터베이스 연결실패");
			System.out.println("DBConn : Message : " + e.getMessage());
		}
		return null;
	}

	public static void close(Connection conn, PreparedStatement pstmt) {
		try {
			conn.close();
			pstmt.close();
		} catch (Exception e) {
			System.out.println("DB종료시 오류가 발생 : " + e.getMessage());
		}
	}

	public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		try {
			conn.close();
			pstmt.close();
			rs.close();
		} catch (Exception e) {
			System.out.println("DB종료시 오류가 발생 : " + e.getMessage());
		}
	}

}

Player.java

package com.jaybon.baseball.model;

import lombok.Builder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Player {
	
	int id;
	int playerNum;
	String playerName;
	String position;
	String teamName;

}

Team.java

package com.jaybon.baseball.model;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Team {
	
	int id;
	String teamName;
	
}

TeamRepository.java

package com.jaybon.baseball.repository;

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 com.jaybon.baseball.db.DBConn;
import com.jaybon.baseball.model.Team;

public class TeamRepository {
	private static final String TAG = "TeamListRepository : "; // TAG 생성 (오류 발견시 용이)
	private static TeamRepository instance = new TeamRepository();

	private TeamRepository() {
	}

	public static TeamRepository getInstance() {
		return instance;
	}

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	
	public List<Team> findAll() { // object 받기(안에 내용 다 받아야 하니까)
		final String SQL = "SELECT id, teamname FROM teamlist ORDER BY id asc";
		
		List<Team> teamList = null;
		try {
			conn = DBConn.getConnection(); // DB에 연결
			pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				teamList = new ArrayList<>();
				
				Team team = Team.builder()
						.id(rs.getInt(1))
						.teamName(rs.getString(2))
						.build();
				
				teamList.add(team);
				
				while (rs.next()) {
					
					Team team1 = Team.builder()
							.id(rs.getInt(1))
							.teamName(rs.getString(2))
							.build();
					
					teamList.add(team1);
					
				}
			}
			return teamList;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(TAG + "findAll : " + e.getMessage());
		} finally {
			DBConn.close(conn, pstmt, rs);
		}
		return null; // 실패시
	}
}

PlayerRepository.java

package com.jaybon.baseball.repository;

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 com.jaybon.baseball.db.DBConn;
import com.jaybon.baseball.model.Player;

public class PlayerRepository {
	private static final String TAG = "PlayerListRepository : "; // TAG 생성 (오류 발견시 용이)
	private static PlayerRepository instance = new PlayerRepository();

	private PlayerRepository() {
	}

	public static PlayerRepository getInstance() {
		return instance;
	}

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	public List<Player> findPlayerNumNameFromTeam(String teamName) { // object 받기(안에 내용 다 받아야 하니까)
		final String SQL = "SELECT playernum, playername  FROM playerlist WHERE teamname like ? ORDER BY playernum asc";
		
		List<Player> playerList = null;
		try {
			conn = DBConn.getConnection(); // DB에 연결
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, teamName);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				playerList = new ArrayList<>();
				
				Player player = Player.builder()
						.playerNum(rs.getInt(1))
						.playerName(rs.getString(2))
						.build();
				
				playerList.add(player);
				
				while (rs.next()) {
					
					Player player1 = Player.builder()
							.playerNum(rs.getInt(1))
							.playerName(rs.getString(2))
							.build();
					
					playerList.add(player1);
					
				}
			}
			return playerList;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(TAG + "findPlayerNumNameFromTeam : " + e.getMessage());
		} finally {
			DBConn.close(conn, pstmt, rs);
		}
		return null; // 실패시
	}
	
	public Player findPlayerDesc(String teamName, int playerNum) { // object 받기(안에 내용 다 받아야 하니까)
		final String SQL = "SELECT playernum, playername, position  FROM playerlist WHERE teamname like ? and playernum = ?";
		
		Player player = null;
		try {
			conn = DBConn.getConnection(); // DB에 연결
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, teamName);
			pstmt.setInt(2, playerNum);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				player = Player.builder()
						.playerNum(rs.getInt(1))
						.playerName(rs.getString(2))
						.position(rs.getString(3))
						.build();
			}
			return player;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(TAG + "findDesc : " + e.getMessage());
		} finally {
			DBConn.close(conn, pstmt, rs);
		}
		return null; // 실패시
	}
}

Action.java

package com.jaybon.baseball.action;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public interface Action {
	public void execute(HttpServletRequest request, HttpServletResponse response) 
			throws ServletException, IOException;
}

TeamHomeAction.java

package com.jaybon.baseball.action.team;

import java.io.IOException;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.jaybon.baseball.action.Action;
import com.jaybon.baseball.model.Team;
import com.jaybon.baseball.repository.TeamRepository;

public class TeamHomeAction implements Action{

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		TeamRepository teamRepository = TeamRepository.getInstance();
		
		List<Team> teamList = teamRepository.findAll();
		
		request.setAttribute("teamList", teamList);
		
		RequestDispatcher rd = request.getRequestDispatcher("home.jsp");
		rd.forward(request, response);
		
	}
	
}

PlayerGetPlayerAction.java

package com.jaybon.baseball.action.player;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.jaybon.baseball.action.Action;
import com.jaybon.baseball.model.Player;
import com.jaybon.baseball.repository.PlayerRepository;

public class PlayerGetPlayerAction implements Action{

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String teamName = request.getParameter("teamName");
		
		PlayerRepository playerRepository = PlayerRepository.getInstance();
		
		List<Player> playerList = playerRepository.findPlayerNumNameFromTeam(teamName);
		
		Gson gson = new Gson();
		
		String playerListJson = gson.toJson(playerList);
		
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json; charset=utf-8");
		PrintWriter pw = response.getWriter();
		
		pw.println(playerListJson);
		
	}

}

PlayerGetDescAction.java

package com.jaybon.baseball.action.player;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;
import com.jaybon.baseball.action.Action;
import com.jaybon.baseball.model.Player;
import com.jaybon.baseball.repository.PlayerRepository;

public class PlayerGetDescAction implements Action{

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String teamName = request.getParameter("teamName");
		int playerNum = Integer.parseInt(request.getParameter("playerNum"));
		
		
		PlayerRepository playerRepository = PlayerRepository.getInstance();
		
		Player player = playerRepository.findPlayerDesc(teamName, playerNum);
		
		Gson gson = new Gson();
		
		String playerJson = gson.toJson(player);
		
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json; charset=utf-8");
		PrintWriter pw = response.getWriter();
		
		pw.println(playerJson);
		
	}

}

TeamController.java

package com.jaybon.baseball.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;

import com.jaybon.baseball.action.Action;
import com.jaybon.baseball.action.team.TeamHomeAction;



// http://localhost:8000/blog/user
@WebServlet("/team")
public class TeamController extends HttpServlet {
	private final static String TAG = "BaseballController : ";
	private static final long serialVersionUID = 1L;

	public TeamController() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doProcess(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doProcess(request, response);
	}

	protected void doProcess(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// http://localhost:8000/product/user?cmd=test
		String cmd = request.getParameter("cmd");
		System.out.println(TAG + "router : " + cmd);
		Action action = router(cmd);
		action.execute(request, response);
	}

	public Action router(String cmd) {
		if (cmd.equals("home")) {
			return new TeamHomeAction();
		} 
		return null;
	}
}

PlayerController.java

package com.jaybon.baseball.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;

import com.jaybon.baseball.action.Action;
import com.jaybon.baseball.action.player.PlayerGetDescAction;
import com.jaybon.baseball.action.player.PlayerGetPlayerAction;



// http://localhost:8000/blog/user
@WebServlet("/player")
public class PlayerController extends HttpServlet {
	private final static String TAG = "PlayerController : ";
	private static final long serialVersionUID = 1L;

	public PlayerController() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doProcess(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doProcess(request, response);
	}

	protected void doProcess(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// http://localhost:8000/product/user?cmd=test
		String cmd = request.getParameter("cmd");
		System.out.println(TAG + "router : " + cmd);
		Action action = router(cmd);
		action.execute(request, response);
	}

	public Action router(String cmd) {
		if (cmd.equals("getPlayer")) {
			return new PlayerGetPlayerAction();
		} else if (cmd.equals("getDesc")) {
			return new PlayerGetDescAction();
		} 
		return null;
	}
}

결과

'Web > Jsp' 카테고리의 다른 글

블로그에 네이버 로그인 api 넣기 (0)	2020.06.18
카카오 로그인 api (0)	2020.06.17
이클립스 eclipse sts툴 java / html / javascript / jsp 자동완성 (2)	2020.06.16
product 페이지 예제 만들기 (0)	2020.06.15
블로그 댓글 지우기 (0)	2020.06.15

Jaybon 의 지식창고

야구 선수 게시판 예제 만들기

기본구조

데이터베이스 테이블 추가

index.jsp

home.jsp

Dbconn.java

Player.java

Team.java

TeamRepository.java

PlayerRepository.java

Action.java

TeamHomeAction.java

PlayerGetPlayerAction.java

PlayerGetDescAction.java

TeamController.java

PlayerController.java

결과

'Web > Jsp' 카테고리의 다른 글

+ Recent posts

티스토리툴바