erp프로젝트 구조

• CRUD
  C(insert), R(select), U(update), D(delete)

• CLRUD
  L(목록 select), R(조건(pk) select)
  

• 404 에러 -> not found 에러 콘솔에 뜨지않음
  => 서버상태 - 콘솔에 오류있는지 확인
  => tiles인식유무(설정파일명, view 이름,연결파일명)
  => path
  =>return하는뷰이름

• 500 에러 -> 런타임오류 콘솔에 뜸

[ConnectionPool]

1. 기존 커넥션을 관리하는 방법

=>요청이 들어오면 커넥션을 매번 직접 생성하는 방식
(1) 어플리케이션을 이용해서 커넥션을 요청
(2) 드라이버가 DB서버에 연결한다(TCP/IP통신)
=>연결하는 과정에서 네트워크 통신이 발생한다(3way handShake)
(3) 커넥션이 연결되면 계정과 계정패스워드를 이용해서 내부적으로 인증작업을 수행
(4) 인증이 완료되면 sql을 사용할 수 있는 세션이 만들어진다.
=> 같은 세션인 경우만 트랜잭션을 처리할 수 있다.
(5) 디비서버가 생성된 커넥션 정보를 리턴
(6) 커넥션을 이용해서 SQL문을 실행

2. 문제점

• 매번 커넥션을 만들때마다 네트워크 접속이 발생하므로 리소스를 많이 사용
• 시간이 지연

3. Connection Pool

• 해결하기 위해 동시간대 평균접속자수를 고려해서 커넥션을 미리 만들어서 관리하는 방법
• WAS가 시작될때 미리 만들어서 대기(DB서버와 네트워크로 연결도 미리 해놓음)
• 요청이 들어오면 커넥션 풀에서 커넥션을 꺼내서 반환(네트워크 연결하고 커넥션을 만드는 시간이 없어진다)
• SQL을 바로 실행할 수 있다
• SQL의 실행이 끝나면 커넥션을 종료하지 않고 커넥션풀로 다시 반환
• 사용도 편리하고 오픈소스로 제공되는 커넥션풀이 많이 있다.
• 기본으로 항상 커넥션풀을 사용한다.
• common-dbcp(데이터베이스커넥션풀),WAS마다 제공(tomcat-dbcp), HikariCP(spring boot 2.0부터 기본으로 포함, 많이사용)
• 커넥션풀을 어떻게 사용하냐에 따라 커넥션풀을 접근하는 방법이 다를것이므로 커넥션풀에서 커넥션을 꺼내는 방법을 표준으로 제공하는 인터페이스가
  jsvax.sql.DataSource
  

4. HikariCP설정

1) 라이브러리 추가

2) HikariConfig

• DB서버에 대한 정보를 설정
• 드라이버, 연결문자열, 계정, 계정패스워드
• 커넥션풀에 대한 설정
  (커넥션풀로 관리할 커넥션의 갯수, 연결 풀에서 HikariCP가 관리하는 유휴연결수, 연결을 위한 최대 유휴시간,...)

3) HikariDataSource

• HikariConfig를 기반으로 만들어진 커넥션풀에서 커넥션을 관리하는 객체
• 기본설정이 커넥션 10개 제공
  
  

순서

버전바꾸고
프로젝트퍼셋버전도변경
서블릿라이브러리교체
테스트라이브러리수정

		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.13.2</version>
			<scope>test</scope>
		</dependency> 
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>5.2.7.RELEASE</version>
			<scope>test</scope>
		</dependency>

159번 라인 소스,타겟변경

서블릿컨텍스트에 jdbc도 추가

메이븐업데이트

제이유닛테스트

<!-- HikariCP -->
		<dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>3.4.5</version>
		</dependency>

히카리씨피 등록하면서 jdbc연동 라이브러리 주석처리후 새로등록

실습

erp프로젝트 read/update

DeptController

다른부분생략

@Controller
@RequestMapping("/dept")
public class DeptController {
	DeptService service;
	
	@Autowired
	public DeptController(DeptService service) {
		super();
		this.service = service;
	}	

	//클릭한 부서 정보를 조회
	//action에 따라 read작업인지 update작업인지 구분
	@GetMapping("/read")
	public ModelAndView read(String deptno, String action) {
		System.out.println(deptno+" : "+action);
		ModelAndView mav = new ModelAndView();
		DeptDTO dept = service.read(deptno);
		String view="";
		if(action.equals("READ")) {
			view = "dept/read";
		}else {
			view = "dept/update";
		}
		mav.setViewName(view);
		mav.addObject("dept" ,dept);
		return mav;
	}
	
	//데이터수정하기
	//겟매핑, 포스트매핑은 스프링 4.3이상부터 가능
	@PostMapping("update.do")
	public String update(DeptDTO dept) {
		System.out.println(dept);
		service.update(dept);
		return "redirect:/dept/list";
	}
}

DeptDAOImpl

@Repository
public class DeptDAOImpl implements DeptDAO {
	private JdbcTemplate template;
	
	@Autowired
	public DeptDAOImpl(JdbcTemplate template) {
		super();
		this.template = template;
	}

@Override
	public DeptDTO read(String deptno) {
		return template.queryForObject("select * from dept where deptno=?", 
				new Object[] {deptno}, new DeptRowMapper());
	}

	@Override
	public int update(DeptDTO dept) {
		StringBuffer sql = new StringBuffer();
		sql.append("update dept ");
		sql.append("set mgr_id=?, deptaddr=?, depttel=? ");
		sql.append("where deptno=?");
		return template.update(sql.toString(), dept.getMgr_id(), dept.getDeptaddr(),
				dept.getDepttel(), dept.getDeptno());
	}

}

DeptServiceImpl

@Service
public class DeptServiceImpl implements DeptService {
	DeptDAO dao;
	
	@Autowired
	public DeptServiceImpl(DeptDAO dao) {
		super();
		this.dao = dao;
	}

	@Override
	public DeptDTO read(String deptno) {
		return dao.read(deptno);
	}

	@Override
	public int update(DeptDTO dept) {
		return dao.update(dept);
	}

}

dept-tiles.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE tiles-definitions PUBLIC
       "-//Apache Software Foundation//DTD Tiles Configuration 3.0//EN"
       "http://tiles.apache.org/dtds/tiles-config_3_0.dtd">
<tiles-definitions>
	<definition name="dept/register" extends="mainTemplate">
		<put-attribute name="menu" value="/WEB-INF/menu/insa_menu.jsp"/>
		<put-attribute name="content" value="/WEB-INF/dept/dept_register.jsp"/>
	</definition>
	<definition name="dept/list" extends="mainTemplate">
		<put-attribute name="menu" value="/WEB-INF/menu/insa_menu.jsp"/>
		<put-attribute name="content" value="/WEB-INF/dept/deptlist.jsp"/>
	</definition>
	<definition name="dept/read" extends="mainTemplate">
		<put-attribute name="menu" value="/WEB-INF/menu/insa_menu.jsp"/>
		<put-attribute name="content" value="/WEB-INF/dept/dept_read.jsp"/>
	</definition>
	<definition name="dept/update" extends="mainTemplate">
		<put-attribute name="menu" value="/WEB-INF/menu/insa_menu.jsp"/>
		<put-attribute name="content" value="/WEB-INF/dept/dept_update.jsp"/>
	</definition>
</tiles-definitions>

dept_read.jsp

<%@page import="com.multi.erp.dept.DeptDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet"
	href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script
	src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
			<div class="col-lg-10">
				<form role="form" class="form-horizontal" action="" method="get">
					<fieldset>
						<div id="legend">
							<legend>아래 양식을 작성해주세요.</legend>
						</div>
						<div class="form-group">
							<!-- 부서코드 -->
							<label class="control-label col-sm-2" for="deptcode">부서코드</label>
							<div class="col-sm-3">
								${dept.deptno }
							</div>
						</div>



						<div class="form-group">
							<!-- 부서명-->
							<label class="control-label col-sm-2" for="name">부서명</label>
							<div class="col-sm-3">
								<!-- 부서명을 이곳에 출력하세요 -->
								${dept.deptname }
							</div>
						</div>
						<div class="form-group">
							<!-- 아이디-->
							<label class="control-label col-sm-2" for="id">부서생성일</label>
							<div class="col-sm-3">
								<!-- 부서생성일 이곳에 출력하세요 -->
								${dept.deptStartDay }
							</div>
						</div>
						<div class="form-group">
							<!-- 주소-->
							<label class="control-label col-sm-2" for="addr">부서레벨</label>
							<div class="col-sm-3">
								<!-- 부서레벨을 이곳에 출력하세요 -->
								${dept.deptlevel }
							</div>
						</div>

						<div class="form-group">
							<!-- 입사날짜-->
							<label class="control-label col-sm-2" for="hiredate">부서스텝</label>
							<div class="col-sm-3">
								<!-- 부서스텝를 이곳에 출력하세요 -->
								${dept.deptstep }
							</div>
						</div>
						<div class="form-group">
							<!-- 포인트-->
							<label class="control-label col-sm-2" for="point">상위부서번호</label>
							<div class="col-sm-3">
								<!-- 상위부서번호를 이곳에 출력하세요 -->
								${dept.deptuppercode }
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">업무분류</label>
							<div class="col-sm-3">
								<!-- 업무분류를 이곳에 출력하세요 -->
								${dept.job_category }
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">관리자</label>
							<div class="col-sm-3">
								<!-- 관리자를 이곳에 출력하세요 -->
								${dept.mgr_id }
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">부서주소</label>
							<div class="col-sm-3">
								<!-- 부서주소를 이곳에 출력하세요 -->
								${dept.deptaddr }
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">전화번호</label>
							<div class="col-sm-3">
								<!-- 전화번호를 이곳에 출력하세요 -->
								${dept.depttel }
							</div>
						</div>
						<div class="form-group">
							<!-- Button -->
							<div class="col-sm-3 col-sm-offset-2">
								<input type="button" value="수정" class="btn btn-success"
								 onclick="location.href='/erp/dept/read.do?deptno=${dept.deptno}&action=UPDATE'" />
							</div>
						</div>
					</fieldset>
				</form>
			</div>
</body>
</html>

dept_update.jsp

<%@page import="com.multi.erp.dept.DeptDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet"
	href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script
	src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
	<%DeptDTO dept = (DeptDTO)request.getAttribute("dept"); %>
			<div class="col-lg-10">
				<form role="form" class="form-horizontal"
				 action="/erp/dept/update.do" method="post">
					<fieldset>
						<div id="legend">
							<legend>아래 양식을 작성해주세요.</legend>
						</div>
						<div class="form-group">
							<!-- 부서코드 -->
							<label class="control-label col-sm-2" for="deptcode">부서코드</label>
							<div class="col-sm-3">
								<%= dept.getDeptno() %>
								<input type="hidden" name="deptno" value="<%= dept.getDeptno() %>">
							</div>
						</div>



						<div class="form-group">
							<!-- 부서명-->
							<label class="control-label col-sm-2" for="name">부서명</label>
							<div class="col-sm-3">
								<!-- 부서명을 이곳에 출력하세요 -->
								<%= dept.getDeptname() %>
							</div>
						</div>
						<div class="form-group">
							<!-- 아이디-->
							<label class="control-label col-sm-2" for="id">부서생성일</label>
							<div class="col-sm-3">
								<!-- 부서생성일 이곳에 출력하세요 -->
								<%= dept.getDeptStartDay() %>
							</div>
						</div>
						<div class="form-group">
							<!-- 주소-->
							<label class="control-label col-sm-2" for="addr">부서레벨</label>
							<div class="col-sm-3">
								<!-- 부서레벨을 이곳에 출력하세요 -->
								<%= dept.getDeptlevel() %>
							</div>
						</div>

						<div class="form-group">
							<!-- 입사날짜-->
							<label class="control-label col-sm-2" for="hiredate">부서스텝</label>
							<div class="col-sm-3">
								<!-- 부서스텝를 이곳에 출력하세요 -->
								 <%= dept.getDeptstep() %>
							</div>
						</div>
						<div class="form-group">
							<!-- 포인트-->
							<label class="control-label col-sm-2" for="point">상위부서번호</label>
							<div class="col-sm-3">
								<!-- 상위부서번호를 이곳에 출력하세요 -->
								<%= dept.getDeptuppercode() %>
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">업무분류</label>
							<div class="col-sm-3">
								<!-- 업무분류를 이곳에 출력하세요 -->
								<%= dept.getJob_category()%>
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">관리자</label>
							<div class="col-sm-3">
								<input type="text"
							 value="<%= dept.getMgr_id()%>" name="mgr_id"/>
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">부서주소</label>
							<div class="col-sm-3">
								<!-- 부서주소를 이곳에 출력하세요 -->
								<input type="text"
							 value="<%= dept.getDeptaddr()%>" name="deptaddr"/>
							</div>
						</div>
						<div class="form-group">
							<!-- 등급-->
							<label class="control-label col-sm-2" for="grade">전화번호</label>
							<div class="col-sm-3">
								<!-- 전화번호를 이곳에 출력하세요 -->
								<input type="text"
							 value="<%= dept.getDepttel()%>" name="depttel"/>
							</div>
						</div>
						<div class="form-group">
							<!-- Button -->
							<div class="col-sm-3 col-sm-offset-2">
								<input type="submit" value="수정" class="btn btn-success" />
							</div>
						</div>
					</fieldset>
				</form>
			</div>
</body>
</html>

deptlist.jsp

<%@page import="com.multi.erp.dept.DeptDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ 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>
<head>
<title>Bootstrap Example</title>
<meta charset="UTF-8">
<link rel="stylesheet"
	href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script
	src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>

</head>
<body>
	<%
	ArrayList<DeptDTO> deptlist = (ArrayList<DeptDTO>)request.getAttribute("deptlist");
	%>
			<div >
				<h3>부서목록</h3>
				<div style="padding-top: 30px">
					
					<table class="table">
						<thead>
							<tr>
								<th>부서번호</th>
								<th>부서명</th>
								<th>부서생성일</th>
								<th>부서레벨</th>
								<th>부서스텝</th>
								<th>상위부서번호</th>
								<th>업무분류</th>
								<th>관리자</th>
								<th>부서주소</th>
								<th>전화번호</th>
								<th>삭제</th>
							</tr>
						</thead>
						<tbody>
						<%for(DeptDTO dept:deptlist){ %>
								<tr>
									<td><a href="/erp/dept/read?deptno=<%=dept.getDeptno() %>&action=READ"><%=dept.getDeptno() %></a></td>
									<td><%=dept.getDeptname() %></td>
									<td><%=dept.getDeptStartDay() %></td>
									<td><%=dept.getDeptlevel() %></td>
									<td><%=dept.getDeptstep() %></td>
									<td><%=dept.getDeptuppercode() %></td>
									<td><%=dept.getJob_category() %></td>
									<td><%=dept.getMgr_id() %></td>
									<td><%=dept.getDeptaddr() %></td>
									<td><%=dept.getDepttel() %></td>
									<td><a
										href="/erp/dept/delete.do?deptno=<%=dept.getDeptno() %>">삭제</a></td>
								</tr>
							<%} %>
						</tbody>
					</table>
				</div>
			
			</div>
</body>
</html>

ConnectionPoolTest 프로젝트

JUnitTest01

public class JUnitTest01 {

	@Test
	public void test() {
		//주어진 객체가 널인지 체크
		//assertNotNull(new String());
		
		//첫번째값과 두번째 값 같은지 비교
		assertEquals("1", "1");
	}

}

SpringJUnitTest

//@RunWith는 단위테스트 실행방법을 확장해서 스프링과 연동해서 작업하기 위한 환경을 설정
@RunWith(SpringJUnit4ClassRunner.class)
//@ContextConfiguration : 스프링설정파일의 위치를 정의하기 위한 어노테이션
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"})
//@WebAppConfiguration는 스프링레거시프로젝트에서 스프링MVC테스트환경을 셋팅하기 위해서 설정하는 어노테이션
@WebAppConfiguration
public class SpringJUnitTest {
	@Autowired
	DataSource ds;
	@Test
	public void test() {
		assertNotNull(ds);
		System.out.println(ds);
		Connection con;
		try {
			con = ds.getConnection();
			System.out.println(con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

ConnectionPoolTest

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/appServlet/servlet-context.xml"})
@WebAppConfiguration
public class ConnectionPoolTest {
	@Autowired
	DataSource ds;
	@Test
	public void test() {
		System.out.println(ds);
		try {
			StopWatch stopWatch = new StopWatch();
			stopWatch.start();
			Connection con1 = ds.getConnection();
			Connection con2 = ds.getConnection();
			Connection con3 = ds.getConnection();
			Connection con4 = ds.getConnection();
			Connection con5 = ds.getConnection();
			Connection con6 = ds.getConnection();
			Connection con7 = ds.getConnection();
			Connection con8 = ds.getConnection();
			Connection con9 = ds.getConnection();
			Connection con10 = ds.getConnection();
			stopWatch.stop();
			System.out.println(stopWatch.prettyPrint());
			System.out.println("=============================");
			System.out.println(con1);
			System.out.println(con2);
			System.out.println(con3);
			System.out.println(con4);
			System.out.println(con5);
			System.out.println(con6);
			System.out.println(con7);
			System.out.println(con8);
			System.out.println(con9);
			System.out.println(con10);
			System.out.println("=============================");
			Connection con11 = ds.getConnection();
			System.out.println(con11);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

servlet-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<annotation-driven />

	<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
	<resources mapping="/resources/**" location="/resources/" />

	<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
	<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<beans:property name="prefix" value="/WEB-INF/views/" />
		<beans:property name="suffix" value=".jsp" />
	</beans:bean>
	
	<context:component-scan base-package="com.mult.pool" />
	
	
	<!-- jdbc에서 DriverManager방식으로 커넥션을 생성해서 관리(요청이 들어오면 커넥션을 만들어서 넘겨주는 방식) -->
	<!-- <beans:bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
		<beans:property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
		<beans:property name="username" value="erp"/>
		<beans:property name="password" value="erp"/>
	</beans:bean> -->
	<!--===============================커넥션풀 등록==============================  -->
	 <beans:bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
		<beans:property name="jdbcUrl" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
		<beans:property name="username" value="erp"/>
		<beans:property name="password" value="erp"/>
		<beans:property name="maximumPoolSize" value="20"/>
	</beans:bean>
	<beans:bean id="ds" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
		<beans:constructor-arg ref="hikariConfig"/>
	</beans:bean>
	<!--===============================스프링JDBC연동을 위해서 필요한 라이브러리==============================  -->
	<!--sql을 실행할 수 있는 기능을 제공하는 클래스  -->
	<beans:bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<beans:constructor-arg ref="ds"/>
	</beans:bean>
	
	
</beans:beans>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.mult</groupId>
	<artifactId>pool</artifactId>
	<name>ConnectionPoolTest</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>5.2.7.RELEASE</org.springframework-version>
		<org.aspectj-version>1.6.10</org.aspectj-version>
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
			<exclusions>
				<!-- Exclude Commons Logging in favor of SLF4j -->
				<exclusion>
					<groupId>commons-logging</groupId>
					<artifactId>commons-logging</artifactId>
				 </exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
				
		<!-- AspectJ -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>	
		
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${org.slf4j-version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>jcl-over-slf4j</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.15</version>
			<exclusions>
				<exclusion>
					<groupId>javax.mail</groupId>
					<artifactId>mail</artifactId>
				</exclusion>
				<exclusion>
					<groupId>javax.jms</groupId>
					<artifactId>jms</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jdmk</groupId>
					<artifactId>jmxtools</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jmx</groupId>
					<artifactId>jmxri</artifactId>
				</exclusion>
			</exclusions>
			<scope>runtime</scope>
		</dependency>

		<!-- @Inject -->
		<dependency>
			<groupId>javax.inject</groupId>
			<artifactId>javax.inject</artifactId>
			<version>1</version>
		</dependency>
				
		<!-- Servlet -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>4.0.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>2.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
	
		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.13.2</version>
			<scope>test</scope>
		</dependency> 
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>5.2.7.RELEASE</version>
			<scope>test</scope>
		</dependency>
		
		<!-- spring jdbc JdbcTemplate이용해서 작업-->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.2.7.RELEASE</version>
		</dependency>
		<!-- 오라클드라이버 -->
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.4</version>
		</dependency>
		
		<!-- HikariCP -->
		<dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>3.4.5</version>
		</dependency>
		       
	</dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                        <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

미션

-erp에 커넥션풀을 설정(HikariCP로 설정)
=>라이브러리추가
=>spring 설정파일에 추가

• 기본커넥션은 15개
• test(ConnectionPoolTest.java)
  JUnit으로 테스트하기
  DataSource가 널이 아닌지 테스트
  DataSource로부터 커넥션 객체 한 개를 만들어서 출력해보기

  	=>라이브러리추가
  	=>junit test case를 추가하고 셋팅(3가지)

ConnectionPoolTest

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/config/spring-config.xml"})
@WebAppConfiguration
public class ConnectionPoolTest {
	@Autowired
	DataSource ds;
	@Autowired
	JdbcTemplate template;
	@Test
	public void test() {
		System.out.println("================");
		assertNotNull(ds);
		Connection con;
		try {
			con = ds.getConnection();
			System.out.println(con);
			System.out.println(template);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

jstl

MultiCheck.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<head><title>EL implicit Object Test</title></head>
<body>
	<h2> client data 처리 연습 </h2>

	<form method='post'  
	action='/erp/resources/jstl/jstl_choose.jsp'>
	
		이름<input type='text' name='name'><br>
		주소<input type='text' name='addr'><br><p>
		나이<input type='text' name='age'><br><p>
		등급<input type='text' name='grade'><br><p>

		다음중 현재사용중인 소프트웨어를 선택하여 주세요<br>
		<input type='checkbox' name='sw' value='hwp'>hwp<br>
		<input type='checkbox' name='sw' value='MS/WORD'>MS/WORD<br>
		<input type='checkbox' name='sw' value='EXCEL'>EXCEL<br><p>

		<input type='submit' value='전송'>
		<input type='reset' value='취소'>
	
	</form>

</body>
</html>

jstl_if.jsp

JSTL(JavaServerPage Standard Tag Library) - 자바표준태그라이브러리

• 기본 자바코드를 태그로 사용할 수 있도록 지원
• EL과 같이 사용
• JSTL을 사용하기 위해서 태그라이브러리 추가(taglib)
• 접두사 c로 정의하기 때문에 c:XXXX태그를 이용해서 작업
• 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>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	 <h3>c:if사용 - 자바의 if문 (true인 경우만 체크)</h3>
	 1. 전송된 파라미터의 name을 체크해서 name이 홍길동이면 "홍길동님 환영합니다." 메시지 출력하기
	 <h3>${param.name }</h3>
	 <h3>${param.addr }</h3>
	 <c:if test="${param.name=='홍길동' }">
	 	<h3>${param.name }님 환영합니다.</h3>
	 </c:if>
	 
	 2. age를 체크해서 age가 70이상이면 실버를 출력하세요<br>
	 <c:if test="${param.age>=70 }">
	 	<h3>silver</h3>
	 </c:if>
	 
	 3. age를 체크해서 age가 10~20사이이면 청소년을 출력하세요
	 <c:if test="${param.age>=10 && param.age<20 }">
	 	<h3>청소년</h3>
	 </c:if>
</body>
</html>

jstl_choose.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>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>자바의 if~else(if~else if)를 표현</h2>
	<h2>컨트롤러가 공유해준 데이터의 체크(컨트롤러에서 공유한 user객체가 널인지 체크하기)
		c:when이 if, c:otherwise가 else에 해당
		if = c:when
		else if = c:when
		else = c:otherwise
	</h2>
	<!-- EL이 주로 하는 일은 컨트롤러가 공유해준 공유객체(컨트롤러에서 공유하는 공유명이 EL에서 객체명이 됨)를 엑세스 -->
	<c:choose>
		<c:when test="${user==null }">
			<h3>user객체는 널이다.</h3>
		</c:when>
		<c:otherwise>
			<h3>user객체는 널이 아니다.</h3>
		</c:otherwise>
	</c:choose>
    <h2>c:choose가 if~else표현가능</h2>
	<h3>age변수가 20세 미만이면 청소년입니다. 20세 이상이면 성인입니다.</h3>
	<c:choose>
		<c:when test="${param.age>=20 }">
			<h3>${param.name }님은 성인입니다.</h3>
		</c:when>
		<c:when test="${param.age<20 }">
			<h3>${param.name }님은 청소년입니다.</h3>
		</c:when>
	</c:choose>

	<!-- grade가 A이면 <h3>A등급</h3> B는 B등급, C는 C등급 나머지는 기타등급으로 출력하기-->
	<c:choose>
		<c:when test="${param.grade=='A' || param.grade=='a'}">
			<h3>A등급</h3>
		</c:when>
		<c:when test="${param.grade=='B' || param.grade=='b'}">
			<h3>B등급</h3>
		</c:when>
		<c:when test="${param.grade=='C' || param.grade=='c'}">
			<h3>C등급</h3>
		</c:when>
		<c:otherwise>
			<h3>기타등급</h3>
		</c:otherwise>
	</c:choose>
</body>
</html>

jstl_basicfor

<%@ 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>
<head>
<meta charset="UTF-8">
<title>jstl_basicfor</title>
</head>
<body>
	<h2>반복작업</h2>
	<hr/>
	<c:forEach var="num" begin="1" end="10" step="2" varStatus="mystatus">
		<h3>${num }, index=${mystatus.index }, first=${mystatus.first },
		last=${mystatus.last }, begin=${mystatus.begin }, end=${mystatus.end }
		, step=${mystatus.step }</h3>
	</c:forEach>

	<h2>7단출력</h2>
	<hr/>
	<c:forEach var="i" begin="1" end="9">
		<h3>7*${i }=${7*i }</h3>
	</c:forEach>

</body>
</html>

JSTLDTO

public class JSTLDTO {
	private String id;
	private String pass;
	
	public JSTLDTO() {
		
	}

	public JSTLDTO(String id, String pass) {
		super();
		this.id = id;
		this.pass = pass;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPass() {
		return pass;
	}

	public void setPass(String pass) {
		this.pass = pass;
	}

	@Override
	public String toString() {
		return "JSTLDTO [id=" + id + ", pass=" + pass + "]";
	}
	
}

jstl_collection_foreach.jsp

<%@page import="test.JSTLDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ 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>
<head>
<meta charset="UTF-8">
<title>jstl_collection_foreach</title>
</head>
<body>
	<h1>컬렉션에 저장된 데이터에 접근하기(가장 많이 사용)</h1>
	<h2>jsp가 하는 일은 컨트롤러에서 공유한 attribute를 꺼내서 출력</h2>
	<h2>1. 배열</h2>
	<%
		String[] arr = {"java", "servlet", "jsp", "spring"};
	%>
	<c:forEach var="data" items="<%=arr %>">
		<h3>${data }</h3>
	</c:forEach>
	
	<h2>2. ArrayList(컨트롤러에서 공유한 데이터)</h2>
	<%
		ArrayList<String> list = new ArrayList<String>();
		list.add("하둡");
		list.add("스파크");
		list.add("몽고디비");
		list.add("피그");
		request.setAttribute("list", list);//컨트롤러에서 공유된 데이터가 있다는 가정
	%>
	<c:forEach var="data" items="${list }" varStatus="status">
		<h3>${data }, current=>${status.current }, index=>${status.index }</h3>
	</c:forEach>
	
	<h2>3. ArrayList에 DTO가 저장된 경우</h2>
	<%
		ArrayList<JSTLDTO> userlist = new ArrayList<JSTLDTO>();
		userlist.add(new JSTLDTO("jang","1234"));
		userlist.add(new JSTLDTO("bts","1234"));
		userlist.add(new JSTLDTO("kang","1234"));
		userlist.add(new JSTLDTO("kim","1234"));
		//컨트롤러에서 공유돈 데이터라 가정
		request.setAttribute("userlist", userlist);
	%>
	
	
	<hr/><hr/><hr/><hr/>
	<h3>jsp코드</h3>
	<%
		ArrayList<JSTLDTO> jspuserlist = (ArrayList<JSTLDTO>)request.getAttribute("userlist");
		for(JSTLDTO dto:jspuserlist){%>
			<h3><%=dto.getId() %>:<%=dto.getPass() %></h3>
		<%} %>
		
		
	<hr/><hr/><hr/><hr/>
	<h3>jstl코드</h3>
	<c:forEach var="user" items="${userlist }">
		<h3>${user.id }:${user.pass }</h3>
	</c:forEach>
</body>
</html>

jstl 미션

dept에서 list출력하는 jsp파일을 EL과 JSTL을 이용해서 작업할 수 있도록 수정하세요(실행화면, deptlist.jsp첨부하기)

deptlist.jsp

<%@page import="com.multi.erp.dept.DeptDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ 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>
<head>
<title>Bootstrap Example</title>
<meta charset="UTF-8">
<link rel="stylesheet"
	href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
<script
	src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.0/jquery.min.js"></script>
<script
	src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>

</head>
<body>
	
			<div >
				<h3>부서목록JSTL</h3>
				<div style="padding-top: 30px">
					
					<table class="table">
						<thead>
							<tr>
								<th>부서번호</th>
								<th>부서명</th>
								<th>부서생성일</th>
								<th>부서레벨</th>
								<th>부서스텝</th>
								<th>상위부서번호</th>
								<th>업무분류</th>
								<th>관리자</th>
								<th>부서주소</th>
								<th>전화번호</th>
								<th>삭제</th>
							</tr>
						</thead>
						<tbody>
						<%-- items는 컨트롤러에서 공유한 공유명을 정의하고 이 공유명이 EL에서 사용할 수 있는 객체 --%>
						<c:forEach var="dept" items="${deptlist }">
								<tr>
									<td><a href="/erp/dept/read?deptno=${dept.deptno}&action=READ">${dept.deptno}</a></td>
									<td>${dept.deptname}</td>
									<td>${dept.deptStartDay}</td>
									<td>${dept.deptlevel}</td>
									<td>${dept.deptstep}</td>
									<td>${dept.deptuppercode}</td>
									<td>${dept.job_category}</td>
									<td>${dept.mgr_id}</td>
									<td>${dept.deptaddr}</td>
									<td>${dept.depttel}</td>
									<td><a
										href="/erp/dept/delete.do?deptno=${dept.deptno}">삭제</a></td>
								</tr>
							</c:forEach>
						</tbody>
					</table>
				</div>
			
			</div>
</body>
</html>

본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.