[Spring] 3. 웹사이트 만들기 ② : DB 연동 부분 구현, 고객 관리, 개인 회원 정보 조회까지

https://github.com/upcake/Class_Examples

교육 중에 작성한 예제들은 깃허브에 올려두고 있습니다. 

gif 파일은 클릭해서 보는 것이 정확합니다.

---

 - 웹사이트 만들기 ② : DB 연동 부분 구현, 고객 관리, 개인 회원 정보 조회까지 -

▲작동 화면

 

 - src/main/resources 경로에 data 패키지를 만들고 db.properties 파일을 만든다.

 

db.driver = oracle.jdbc.driver.OracleDriver
db.url = jdbc:oracle:thin:@localhost:1521/xe
db.username = hanul
db.password = 0000

▲db.properties

 - src/main/resources/data/db.properties

 

 - data 패키지에 Spring Bean Configuration 파일을 만든다.

 

 - default.xml - Namespace 탭 - context 체크

 

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

<!-- context : 외부 파일을 사용 할 수 있도록 경로를 설정 -->
<context:property-placeholder location="classpath:data/db.properties" />

<!-- 빈을 등록해놓을 설정 파일 -->
<!-- id를 지정하면 다른 빈에서 참조할 수 있음 -->
<bean id="dbcp" class="org.apache.commons.dbcp.BasicDataSource">
	<!-- property : 데이터를 담을 필드를 의미 -->
	<property name="driverClassName" value="${db.driver}" />
	<property name="url" value="${db.url}" />
	<property name="username" value="${db.username}" />
	<property name="password" value="${db.password}" />
</bean>

<!-- myBatis 등록하는 빈 -->
<!-- DB연결, 쿼리문 역할 -->
<bean id="factory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dbcp"></property>
	<property name="mapperLocations" value="classpath:sqlmap/*-mapper.xml"></property>
</bean>

<!-- Setter가 있다면 property로 필드에 데이터를 넣을 수 있지만 (자동 완성 기능으로 확인가능) -->
<!-- Setter가 없다면 생성자를 만들어서 데이터를 넣는다. -->
<bean class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg name="sqlSessionFactory"  ref="factory"/>
</bean>

</beans>

▲default.xml

- src/main/resources/data/default.xml

 

 - root-context.xml - Namespace 탭 - context 체크

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
	<import resource="classpath:data/*.xml" />
		
	<context:component-scan base-package="customer"/>
</beans>

▲root-context.xml

 - src\main\webapp\WEB-INF\spring\root-context.xml

 

package customer;

import java.util.List;

public interface CustomerService {
	//CRUD(Create/Read/Update/Delete)
	//인터페이스이므로 기본적으로 abstract 속성이 붙어서 추상 메서드가 된다.
	//고객 정보 삽입 저장
	void customer_insert(CustomerVO vo);
		
	//목록 조회
	List<CustomerVO> customer_list();
	
	//상세(1건) 조회
	CustomerVO customer_detail(int id);
	
	//고객 정보 변경 저장
	void customer_update(CustomerVO vo);
	
	//고객 정보 삭제
	void customer_delete(int id);
}

▲CustomerService.java

 - src/main/java/customer/CustomerService.java

 

package customer;

public class CustomerVO {
	private int id, no;
	private String name, gender, email, phone;
	
	public int getNo() {
		return no;
	}
	public void setNo(int no) {
		this.no = no;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
}

▲CustomerVO.java

 - src/main/java/customer/CustomerVO.java

 

package customer;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service	//서비스 역할을 하는 클래스를 객체로 만들어둘때 사용
public class CustomerServiceImpl implements CustomerService {
	@Autowired private CustomDAO dao;

	@Override
	public void customer_insert(CustomerVO vo) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<CustomerVO> customer_list() {
		return dao.customer_list();
	}

	@Override
	public CustomerVO customer_detail(int id) {
		return dao.customer_detail(id);
	}

	@Override
	public void customer_update(CustomerVO vo) {
		// TODO Auto-generated method stub

	}

	@Override
	public void customer_delete(int id) {
		// TODO Auto-generated method stub

	}

}

▲CustomerServiceImpl.java

 - src/main/java/customer/CustomerServiceImpl.java

 

package customer;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

//어노테이션은 클래스를 객체로 생성하는 역할을 해줌
//@Component는 다용도, 특정역할이 있으면 역할군에 맞는 어노테이션을 사용한다.
@Repository	//저장소 역할을 하는 클래스를 객체로 만들때 사용
public class CustomDAO implements CustomerService {
	@Autowired private SqlSession sql;	
	//SqlSession : PreparedStatement와 표현 방법이 다를뿐 같은 기능을 한다.
	//Autowired : 메모리에 올려둔 주소들이 자동으로 연결 됨

	@Override
	public void customer_insert(CustomerVO vo) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<CustomerVO> customer_list() {
		//customer.mapper라는 맵퍼에서 id가 list인 곳을 찾는다
		return sql.selectList("customer.mapper.list");
	}

	@Override
	public CustomerVO customer_detail(int id) {
		return sql.selectOne("customer.mapper.detail", id);
	}

	@Override
	public void customer_update(CustomerVO vo) {
		// TODO Auto-generated method stub

	}

	@Override
	public void customer_delete(int id) {
		// TODO Auto-generated method stub

	}

}

▲CustomDAO.java

 - src/main/customer/CustomDAO.java

 

package com.hanul.iot;

import java.util.List;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import customer.CustomerServiceImpl;
import customer.CustomerVO;

@Controller
public class CustomerController {
	@Autowired private CustomerServiceImpl service;
	//컨트롤러 - 서비스 - DAO - 맵퍼 순으로 연결된다.
	
	//고객 관리 목록 화면
	@RequestMapping("/list.cu")
	public String list(HttpSession session, Model model) {
		//서블릿에서는 request에서 getsession으로 세션을 가져왔다면,
		//스프링에서는 바로 세션에 접근할 수 있게 HttpSession을 지원한다.
		
		//category 어트리뷰트의 값에 따라 active 속성을 결정한다.
		session.setAttribute("category", "cu");		//카테고리 어트리뷰트에 cu를 설정
		List<CustomerVO> list = service.customer_list();
		model.addAttribute("list", list);
		return "customer/list";
	}
		
	//고객 상세 화면 요청
	@RequestMapping("/detail.cu")
	public String detail(int id, Model model) {
		//선택한 고객 정보를 DB에 조회해와서
		CustomerVO vo = service.customer_detail(id);
		//화면에 출력할 수 있도록 Model에 담는다.
		//원래는 string타입으로 담겨야하지만 스프링에서는 자동으로 형변환이 되서 int타입으로 담긴다.
		
		model.addAttribute("vo", vo);
		return "customer/detail";
	}
	
	//신규 고객 등록 화면 요청
	public String customer() {
		return "customer/new";
	}
}

▲CustomerController.java

 - src/main/java/com/hanul/iot/CustomerController.java

 

 - resources에 sqlmap 패키지 생성

 

 - 맵퍼 문서 타입을 mybatis 홈페이지에서 가져온다.

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="customer.mapper">
	<!-- resultType : 반환될 데이터타입 -->
	<select id="detail" resultType="customer.CustomerVO">
		SELECT * FROM customer WHERE id=#{id }
	</select>

	<!-- 애스터리스크(*)를 쓰면 ROWNUM 등의 다른 필드를 쓸 수가 없다. -->
	<select id="list" resultType="customer.CustomerVO">
		SELECT ROWNUM no, c.* FROM (SELECT * FROM customer ORDER BY name) c
	</select>
</mapper>

 

▲customer-mapper.xml

 - src/main/resources/sqlmap/customer-mapper.xml

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>list JSP</title>
</head>
<body>
<jsp:include page="/WEB-INF/views/include/header.jsp"></jsp:include>
<div id="content">
	<h3>고객 목록</h3>
	<!-- CSS 파일을 변경 후에 바로 Refresh가 안된다면 common.css 파일로 들어가서 바로 Refresh 해주면 적용 된다. -->
	<table class='w-pct60'>
		<tr>
			<th class='w-px60'>번호</th>
			<th class='w-px200'>고객명</th>
			<th>전화번호</th>
		</tr>
		<!-- for(꺼낸 배열 변수를 담을 새로운 변수 (String x) : 배열 변수(list)) -->
		<!-- items : 배열 변수 -->
		<!-- var : 꺼낸 배열 변수를 담을 새로운 변수 -->
		<core:forEach items="${list }" var="vo">
			<tr>
				<td>${vo.no }</td>
				<td><a href='detail.cu?id=${vo.id}'>${vo.name }</a></td>
				<td>${vo.phone }</td>
			</tr>
		</core:forEach>
	</table>
</div>
<jsp:include page="/WEB-INF/views/include/footer.jsp"></jsp:include>
</body>
</html>

▲list.jsp

- src/main/webapp/WEB-INF/views/customer/list.jsp

 

 - iot 프로젝트 우클릭 - Build Path - Project Facets - Java 버전을 1.8로 변경

 

<?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.hanul</groupId>
	<artifactId>iot</artifactId>
	<name>iot</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>5.2.2.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>servlet-api</artifactId>
			<version>2.5</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.7</version>
			<scope>test</scope>
		</dependency>       
		
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<!-- 스프링에서 JDBC를 사용하기 위한 dependency -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>5.2.2.RELEASE</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
		<!-- DBCP dependency -->
		<dependency>
		    <groupId>commons-dbcp</groupId>
		    <artifactId>commons-dbcp</artifactId>
		    <version>1.4</version>
		</dependency>
	
	
		<!-- https://mvnrepository.com/artifact/com.oracle.ojdbc/ojdbc8 -->
		<!-- 오라클용 jdbc ojdbc8 -->
		<dependency>
		    <groupId>com.oracle.ojdbc</groupId>
		    <artifactId>ojdbc8</artifactId>
		    <version>19.3.0.0</version>
		</dependency>
			
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<!-- Mybatis 프레임워크를 사용하기 위한 dependency -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.5.3</version>
		</dependency>
			
			
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<!-- MyBatis 스프링 dependency -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>2.0.3</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.6</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>

 

▲pom.xml

 - 스프링 버전을 5.2.2, 메이븐 플러그인 source를 1.8로 변경

 

@charset "UTF-8";
@import url('https://fonts.googleapis.com/css2?family=Noto+Sans+KR&display=swap');

body {
	margin: 0 auto;
	text-align: center;
	font-size: 16px;
	font-family: 'Noto Sans KR', sans-serif;
}

a:link, a:visited {
	text-decoration: none;
	color: #000;
}

#content {
	padding: 20px 0;
	min-width: 1024px;	/* 창의 최소 크기 지정 */
}

img {
	vertical-align: middle;	/* 세로축 가운데 정렬 */
}

table {
	width: 80%;
	margin: 0 auto;
	border: 1px solid;
	border-collapse: collapse;	/* 테두리 겹침 설정 collapse: 겹치지 않게 처리 */
}

table th, table td {
	border: 1px solid;
	padding: 5px 10px;
}

table td a:hover { font-weight: bold; }

.btnSet { margin-top: 20px;	}

a.btn-fill, a.btn-empty {
	text-align: center;
	padding: 3px 10px;
	border:1px solid #3367d6;
	border-radius: 3px;
	box-shadow: 2px 2px 3px;
	/* 오른쪽, 아래쪽, 번진 정도 */
}

a.btn-fill { 
	background-color: #3367d6;
	color: #fff;
}

a.btn-empty { 
	background-color: #fff;
	color: #3367d6
}

.w-pct60 { width: 60% }
.w-pct70 { width: 70% }
.w-pct80 { width: 80% }
.w-px40 { width: 40px }
.w-px60 { width: 60px }
.w-px80 { width: 80px }
.w-px100 { width: 100px }
.w-px120 { width: 120px }
.w-px140 { width: 140px }
.w-px160 { width: 160px }
.w-px180 { width: 180px }
.w-px200 { width: 200px }

▲common.css

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %>
<!-- 스프링은 따로 라이브러리를 설치하지 않아도 다운 받을 라이브러리를 설정할 수 있다. -->
<!-- 그 중에 jstl 라이브러리도 있기 때문에 사용할 수 있는것 -->
<!-- 라이브러리 목록은 iot/pom.xml, c:\사용자\.m2에서 확인가능 -->

<link rel="stylesheet" type="text/css" href="css/common.css?v=<%=new java.util.Date().getTime() %>">
<!-- rel : 형태는 스타일 시트, 타입은 텍스트로된 css -->
<!-- 주소 뒤에 ?v=로 버전관리를 할 수 있다 -->
<!-- Date()가 임포트가 안될경우 패키지명을 직접 지정해도 된다. -->

<style>
header ul, header ul li {
	margin: 0;
	padding: 0;
	display: inline;
}

header .category {
	font-size: 18px;
}

header .category ul li:not(:first-child) { /* 첫번째 li만 빼고 지정 */
	padding-left: 30px;
}

header .category ul li a:hover, header .category ul li a.active {
	font-weight: bold;
	color: #0000cd;
}

</style>
<header style="border-bottom: 1px solid #ccc; padding: 15px 0; text-align: left">
	<div class="category" style="margin-left: 100px;"> 
		<ul>
			<li><a href="<core:url value='/' />"><img src="img/logo.png" /></a></li>
			<li><a href='list.cu' ${category eq 'cu' ? "class='active'" : '' } >고객 관리</a></li>
			<li><a href='list.no'>공지사항</a></li>
			<li><a href='list.bo'>방명록</a></li>
			<li><a href='list.da'>공공 데이터</a></li>
		</ul>
	</div>
</header>

▲header.jsp

 

<%@ 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>
</head>
<body>
<jsp:include page="/WEB-INF/views/include/header.jsp" />
<div id="content">
	<h3>[ ${vo.name } ]고객 정보</h3>
	<table class='w-pct60'>
		<tr>
			<th class='w-px160'>성별</th>
			<td>${vo.gender }</td>
		</tr>
		<tr>
			<th>이메일</th>
			<td>${vo.email }</td>
		</tr>
		<tr>
			<th>전화번호</th>
			<td>${vo.phone }</td>
		</tr>
	</table>
	<div class='btnSet'>
		<a class='btn-fill' href="list.cu">고객 목록</a>
		<a class='btn-fill' href="new.cu">신규 고객</a>
		<a class='btn-fill'>수정</a>
		<a class='btn-fill'>삭제</a>
	</div>
</div>
<jsp:include page="/WEB-INF/views/include/footer.jsp" />
</body>
</html>

▲detail.jsp

 

<%@ 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>
</head>
<body>
<jsp:include page="/WEB-INF/views/include/header.jsp" />
<div id="content">
	<h3>신규 고객</h3>
	<form>
		<table class='w-pct60'>
			<tr>
				<th class='w-px160'>성명</th>
				<td><input type="text" name="name" /></td>
			</tr>
			<tr>
				<th>성별</th>
				<td><input type="text" name="name" /></td>
			</tr>
			<tr>
				<th>이메일</th>
				<td><input type="text" name="name" /></td>
			</tr>
			<tr>
				<th>전화번호</th>
				<td><input type="text" name="name" /></td>
			</tr>
			
		</table>
	</form>
</div>
<jsp:include page="/WEB-INF/views/include/footer.jsp" />
</body>
</html>

▲new.jsp