[SPRING]#79 도서 쇼핑몰 구현 (DB 연동4)

JDBC 연동을 위한 환경 설정하기

실습 진행 중인 도서 쇼핑몰 애플리케이션에 데이터베이스를 연동시켜 보겠습니다.

우선 스프링 MVC 설정 파일에 환경 설정을 작성해 보겠습니다.

---

pom.xml

JDBC 연동 관련 의존 라이브러리를 추가해줍니다.

<?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.springmvc</groupId>
	<artifactId>controller</artifactId>
	<name>BookMarket</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>11</java-version>
		<org.springframework-version>5.3.19</org.springframework-version>
		<org.aspectj-version>1.9.9.1</org.aspectj-version>
		<org.slf4j-version>1.7.36</org.slf4j-version>
		<security.version>5.6.3</security.version>
		<commons-fileupload-version>1.4</commons-fileupload-version>
		<commons-io-version>2.11.0</commons-io-version>
		<org.apache.tiles-version>3.0.8</org.apache.tiles-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>
		
		<!-- Spring Security -->
        <dependency>
        	<groupId>org.springframework.security</groupId>
        	<artifactId>spring-security-web</artifactId>
        	<version>${security.version}</version>
    	</dependency>
    	<dependency>
        	<groupId>org.springframework.security</groupId>
        	<artifactId>spring-security-config</artifactId>
        	<version>${security.version}</version>
    	</dependency>
    	
    	<!-- File Upload -->
    	<dependency>
    		<groupId>commons-fileupload</groupId>
    		<artifactId>commons-fileupload</artifactId>
    		<version>${commons-fileupload-version}</version>
    	</dependency>
    	<dependency>
    		<groupId>commons-io</groupId>
    		<artifactId>commons-io</artifactId>
    		<version>${commons-io-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>
		
		<!-- Validation -->
		<dependency>
			<groupId>javax.validation</groupId>
			<artifactId>validation-api</artifactId>
			<version>2.0.1.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>5.4.2.Final</version>
		</dependency>
		
		<!-- Web Flow -->
		<dependency>
			<groupId>org.springframework.webflow</groupId>
			<artifactId>spring-webflow</artifactId>
			<version>2.5.1.RELEASE</version>
		</dependency>
		
		<!-- Tiles -->
		<dependency>
			<groupId>org.apache.tiles</groupId>
			<artifactId>tiles-servlet</artifactId>
			<version>${org.apache.tiles-version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.tiles</groupId>
			<artifactId>tiles-jsp</artifactId>
			<version>${org.apache.tiles-version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.tiles</groupId>
			<artifactId>tiles-extras</artifactId>
			<version>${org.apache.tiles-version}</version>
		</dependency>
		
		<!-- MySQL -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.5.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.24</version>
		</dependency>
	
		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.7</version>
			<scope>test</scope>
		</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.6</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>

---

servlet-context.xml

DataSource 설정과 JdbcTemplate 빈 객체를 등록합니다.

<?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"
	xmlns:webflow="http://www.springframework.org/schema/webflow-config"
	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
		http://www.springframework.org/schema/webflow-config
		http://www.springframework.org/schema/webflow-config/spring-webflow-config.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<annotation-driven enable-matrix-variables="true" validator="validator"/>

	<!-- 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.springmvc.*" />
	
	<beans:bean id="multipartResolver"
				class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<beans:property name="maxUploadSize" value="10240000"/>			
	</beans:bean>
	
	<beans:bean id="messageSource"
				class="org.springframework.context.support.ResourceBundleMessageSource">
		<beans:property name="basename" value="messages"/>
		<beans:property name="defaultEncoding" value="UTF-8"/>			
	</beans:bean>
	
	<beans:bean id="localeResolver"
				class="org.springframework.web.servlet.i18n.SessionLocaleResolver">
		<beans:property name="defaultLocale" value="ko"/>			
	</beans:bean>
	
	<interceptors>
		<beans:bean class="com.springmvc.interceptor.MonitoringInterceptor"/>
		<beans:bean class="com.springmvc.interceptor.AuditingInterceptor"/>
		<beans:bean class="org.springframework.web.servlet.i18n.LocaleChangeInterceptor">
			<beans:property name="paramName" value="language"/>
		</beans:bean>
	</interceptors>
	
	<beans:bean id="validator"
				class="org.springframework.validation.beanvalidation.LocalValidatorFactoryBean">
		<beans:property name="validationMessageSource" ref="messageSource"/>			
	</beans:bean>
	
	<beans:bean id="unitsInStockValidator"
				class="com.springmvc.validator.UnitsInStockValidator"/>
	<beans:bean id="bookValidator" class="com.springmvc.validator.BookValidator">
		<beans:property name="springValidators">
			<beans:set>
				<beans:ref bean="unitsInStockValidator"/>
			</beans:set>
		</beans:property>
	</beans:bean>
	
	<webflow:flow-registry id="flowRegistry"
			 flow-builder-services="flowBuilderServices">
		<webflow:flow-location path="/WEB-INF/flows/order/order-flow.xml" id="order"/>
	</webflow:flow-registry>
	
	<webflow:flow-executor id="flowExecutor" flow-registry="flowRegistry"/>
	
	<beans:bean id="flowHandlerMapping"
				class="org.springframework.webflow.mvc.servlet.FlowHandlerMapping">
		<beans:property name="flowRegistry" ref="flowRegistry"/>			
	</beans:bean>
	
	<beans:bean id="flowHandlerAdapter"
				class="org.springframework.webflow.mvc.servlet.FlowHandlerAdapter">
		<beans:property name="flowExecutor" ref="flowExecutor"/>			
	</beans:bean>
	
	<beans:bean id="tilesViewResolver"
				class="org.springframework.web.servlet.view.UrlBasedViewResolver">
		<beans:property name="viewClass"
						value="org.springframework.web.servlet.view.tiles3.TilesView"/>
		<beans:property name="order" value="1"/>
	</beans:bean>
	
	<beans:bean id="tilesConfigurer"
				class="org.springframework.web.servlet.view.tiles3.TilesConfigurer">
		<beans:property name="definitions" value="/WEB-INF/tiles/tiles.xml"/>	
	</beans:bean>
	
	<webflow:flow-builder-services id="flowBuilderServices"
			 view-factory-creator="viewFactoryCreator"/>
	<beans:bean id="viewFactoryCreator"
				class="org.springframework.webflow.mvc.builder.MvcViewFactoryCreator">
		<beans:property name="viewResolvers" ref="tilesViewResolver"/>			
	</beans:bean>
	
	<beans:bean id="dataSource"
	            class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<beans:property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
		<beans:property name="url" value="jdbc:mysql://localhost:3306/springmvcDB?serverTimezone=UTC"/>
		<beans:property name="username" value="root"/>
		<beans:property name="password" value="9290"/>            
	</beans:bean>
	
	<beans:bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<beans:property name="dataSource" ref="dataSource"/>
	</beans:bean>
	
</beans:beans>

JDBC 드라이버 클래스를 설정합니다.

JDBC 접속 데이터베이스 이름을 설정합니다.

MySQL관리자의 ID와 비밀번호를 설정합니다.

 

JdbcTemplate 빈 객체를 등록합니다.

---

BookRepositoryImpl.java

JdbcTemplate 클래스의 속성에 대한 Setter() 메소드를 추가합니다.

package com.springmvc.repository;

import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import com.springmvc.domain.Book;
import com.springmvc.exception.BookIdException;

import java.util.ArrayList;
import java.util.HashSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookRepositoryImpl implements BookRepository {
	private List<Book> listOfBooks = new ArrayList<Book>();
	private JdbcTemplate template;
	
	@Autowired
	public void setJdbctemplate(DataSource dataSource) {
		this.template = new JdbcTemplate(dataSource);
	}
	
	public BookRepositoryImpl() {
		Book book1 = new Book("ISBN1234","C#교과서",30000);
		book1.setAuthor("박용준");
		book1.setDescription(
				"C#교과서는 생에 첫 프로그래밍 언어로 C#을 시작하는 독자를 대상으로 한다. 특히 응용프로그래머를 위한 C# 입문서로, C#을 사용하여 게임(유니티), 웹, 모바일, IoT 등을 개발할 때 필요한 C# 기초 문법을 익히고 기본기를 탄탄하게 다지는 것이 목적이다."
				);
		book1.setPublisher("길벗");
		book1.setCategory("IT전문서");
		book1.setUnitsInStock(1000);
		book1.setReleaseDate("2020/01/01");
		
		Book book2 = new Book("ISBN1235", "Node.js교과서",36000);
		book2.setAuthor("조현영");
		book2.setDescription(
				"이 책은 프런트부터 서버, 데이터베이스, 배포까지 아우르는 광범위한 내용을 다룬다. 군더더기 없는 직관적인 설명으로 기본 개념을 확실히 이해하고, 노드와 기능과 생태계를 사용해 보면서 실제로 동작하는 서버를 만들어보자."
				);
		book2.setPublisher("길벗");
		book2.setCategory("IT전문서");
		book2.setUnitsInStock(1000);
		book2.setReleaseDate("2020/02/02");
		
		Book book3 = new Book("ISBN1236", "어도비 XD CC 2020",25000);
		book3.setAuthor("김두한");
		book3.setDescription(
				"어도비 XD 프로그램을 통해 UI/UX 디자인을 배우고자 하는 예비 디자이너의 눈높이에 맞게 기본적인 도구를 활용한 아이콘 디자인과 웹&앱 페이지 디자인, UI 디자인, 앱 디자인에 애니메이션과 인터랙션을 적용한 프로토타입을 학습합니다."
				);
		book3.setPublisher("길벗");
		book3.setCategory("IT활용서");
		book3.setUnitsInStock(1000);
		book3.setReleaseDate("2020/03/03");
		
		listOfBooks.add(book1);
		listOfBooks.add(book2);
		listOfBooks.add(book3);		
	}
	
	@Override
	public List<Book> getAllBookList() {
		return listOfBooks;
	}
	
	public List<Book> getBookListByCategory(String category) {
		List<Book> booksByCategory = new ArrayList<Book>();
		for(int i=0;i<listOfBooks.size();i++) {
			Book book = listOfBooks.get(i);
			if(category.equalsIgnoreCase(book.getCategory()))
				booksByCategory.add(book);
		}
		return booksByCategory;
	}
	
	public Set<Book> getBookListByFilter(Map<String, List<String>> filter) {
		Set<Book> booksByPublisher = new HashSet<Book>();
		Set<Book> booksByCategory = new HashSet<Book>();
		Set<String> booksByFilter = filter.keySet();
		
		if(booksByFilter.contains("publisher")) {
			for(int j=0;j<filter.get("publisher").size();j++) {
				String publisherName = filter.get("publisher").get(j);
				for(int i=0;i<listOfBooks.size();i++) {
					Book book = listOfBooks.get(i);
					
					if(publisherName.equalsIgnoreCase(book.getPublisher()))
						booksByPublisher.add(book);
				}
			}
		}
		
		if(booksByFilter.contains("category")) {
			for(int i=0;i<filter.get("category").size();i++) {
				String category = filter.get("category").get(i);
				List<Book> list = getBookListByCategory(category);
				booksByCategory.addAll(list);
			}
		}
		
		booksByCategory.retainAll(booksByPublisher);
		return booksByCategory;
	}
	
	public Book getBookById(String bookId) {
		Book bookInfo = null;
		for (int i=0;i<listOfBooks.size();i++) {
			Book book = listOfBooks.get(i);
			if(book!=null&&book.getBookId()!=null&&book.getBookId().equals(bookId)) {
				bookInfo = book;
				break;
			}
		}
		if(bookInfo==null)
			throw new BookIdException(bookId);
			return bookInfo;
	}
	
	public void setNewBook(Book book) {
		listOfBooks.add(book);
	}
}

JdbcTemplate 클래스를 사용하기 위해서 JdbcTemplate 객체 타입의 jdbcTemplate를 선언합니다.

 

setJdbctemplate() 메소드는 데이터베이스 연동을 위해 스프링 MVC 설정 파일에

DateSource를 JdbcTemplate 클래스에 전달합니다.