[코드로 배우는 스프링 웹 프로젝트] java spring MySQL 연동 방법

1. MySQL 데이터 베이스 및 테이블 생성

create database samplespring_mvc;

use samplespring_mvc;

create table book (
	book_id int unsigned not null auto_increment primary key,
    title varchar(200) not null,
    category varchar(200) not null default '',
    price int null,
    insert_date datetime not null default now()
) collate='utf8mb4_general_ci';

desc book;

 

 

2. pom.xml에 DB 연결을 위한 내용 추가

<?xml version="1.0" encoding="UTF-8"?>
	... (생략) ...
	<dependencies>
		... (생략) ...
		<!-- Servlet -->
        ... (생략) ...
    	<!-- 내용 추가 -->
		<!-- database -->
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.4</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>2.0.4</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.7.0</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc -->
		<dependency>
			<groupId>org.bgee.log4jdbc-log4j2</groupId>
			<artifactId>log4jdbc-log4j2-jdbc4</artifactId>
			<version>1.16</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.32</version>
		</dependency>
		
		<!-- Test -->
       ... (생략) ...
	</dependencies>
    ... (생략) ...
</project>

 

 

3. root-context.xml 내용 수정 (src → main → webapp → WEB-INF → spring → root-context.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	...(생략)...	
	<!-- 내용 추가 -->
	<!-- for mysql -->
	<bean id="dataSource"
	class="org.apache.commons.dbcp2.BasicDataSource"
	destroy-method="close">
	<property name="driverClassName"
	value="com.mysql.cj.jdbc.Driver" />
	<property name="url"
	value="jdbc:mysql://localhost:3306/samplespring_mvc?serverTimezone=UTC&amp;useSSL=false&amp;
	useUnicode=true&amp;characterEncoding=UTF-8" />
	<property name="username" value="sampleuser" />
	<property name="password" value="1234" />	
	</bean>
	
	<bean id="sqlSessionFactory" 
	class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="mapperLocations" value="classpath:/sqlmap/**/*_SQL.xml" />
	</bean>
	
	<bean id="sqlSessionTemplate"
	class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>
		
</beans>

 

 

4. MySQL에서 사용자 권한 부여하기(Users and Privileges)

1. Login
(1) Login Name : sampleuser
(2) password : 1234
   → 비밀번호 설정하지 않으면 500 에러가 나타납니다.

2. Schema Privileges → Add Entry → Selected schema : samplespring_mvc → 모든 조건 권한 부여(모두 체크)

 

 

DB에 연동을 하기 위해서 MySQL에서 DB와 Table을 생성했습니다.

또한 DB와 연결하기 위한 xml 파일 내용들을 수정하고 사용자 권한을 부여했습니다.

 

이렇게하면 기본 셋팅은 완료가 되네요!!

 

많은 분들의 피드백은 언제나 환영합니다!  많은 댓글 부탁드려요~~