과제3

mybatis과 spring 프로젝트 연결하기

mysql 사용

1. pom.xml에 mybatis 관련 설정 추가

연결할 때 필요한 jar파일을 maven에서 자동으로 다운로드해준다

		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.23</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.3</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>2.0.3</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.11.0</version>
		</dependency>  

2. src/main/resources 폴더에 mybatis-config.xml 만들기

DTO를 alias명으로 등록한다

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
	"http://mybatis.org/dtd/mybatis-3-config.dtd">
	
<configuration>
	<typeAliases>
		<typeAlias type="com.ssafy.guestbook.model.Product" alias="product" />
	</typeAliases>
</configuration>

3. root-context.xml 수정

conext scan 사용 x => mybatis-config.xml에서 model 등록함
bean에다 property로 driverClass/url/username/password 등록x => jndi사용해서 등록
sqlSessionFactoryBean에서 sql문이 들어있는 product.xml 등록

<?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-4.3.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
	
	<!-- MyBatis-Spring 설정 -->
	<bean id="ds" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="java:comp/env/jdbc/test"></property>
	</bean>
	
	<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="ds"></property>
		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
		<property name="mapperLocations">
			<list>
				<value>classpath:product.xml</value>
			</list>
		</property>
	</bean>
	
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactoryBean"></constructor-arg>
	</bean>
	
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="ds" />
	</bean>
	
	<tx:annotation-driven transaction-manager="transactionManager"/>
		
</beans>

4. 3에서 <bean id="ds"> 관련파일 /webapp/META-INF/context.xml 생성

db연결 정보를 작성한다
name은 root-context.xml의 name이 jndiName인 property value 정보로 들어간다
maxTotal="100" maxIdle="30" maxWaitMillis="10000"
최대 100개/최소30개 connection pool 구동 & 10000ms 동안 대기하면서 개수조절

<?xml version="1.0" encoding="UTF-8"?>
<Context>
	<Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource" 
			maxTotal="100" maxIdle="30" maxWaitMillis="10000" 
			username="ssafy" password="ssafy" driverClassName="com.mysql.cj.jdbc.Driver" 	
			url="jdbc:mysql://localhost:3306/ssafyweb?serverTimezone=UTC&amp;useUniCode=yes&amp;characterEncoding=UTF-8"/> 
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
</Context>

5. 3에서 <property name="mapperLocations"> 관련파일 src/main/resources 폴더에 product.xml 생성

관련 sql문을 작성한다
parameterType : 전달 파라미터 타입 1개만 전달가능!!
==> 데이터가 많은 경우 map으로 전달!!
resultType : 리턴하는 데이터 타입 & 1개만 전달가능!!

<?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="com.ssafy.model.repository.ProductRepo">

	<select id="selectAll" resultType="product">
		select id, name, price, description 
		from Product
	</select>

	<select id="select" parameterType="String"  resultType="product">
		select id, name, price, description 
		from Product 
		where id = #{id}
	</select>
	
	<insert id="insert" parameterType="product">
		insert into Product (id,name,price,description) 
		values(#{id},#{name},#{price},#{description})
	</insert>
	
	<update id="update" parameterType="product">
		update product 
		set name = #{name} ,price = #{price},description = #{description}
		where id = #{id}
	</update>
	
	<delete id="delete" parameterType="String">
		delete from product 
		where id=#{id}
	</delete>
</mapper>

6. ProductServiceImpl 수정

dao클래스를 불러와서 sql문 실행한다

package com.ssafy.model.service;

import java.sql.SQLException;
import java.util.List;

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

import com.ssafy.model.dto.Product;
import com.ssafy.model.repository.ProductRepo;

@Service("productRepo")
public class ProductServiceImpl implements ProductService {
	@Autowired
	private SqlSession sqlSession;

	@Override
	public List<Product> selectAll() throws SQLException {
		return sqlSession.getMapper(ProductRepo.class).selectAll();
	}

	@Override
	public Product select(String id) throws SQLException {
		return sqlSession.getMapper(ProductRepo.class).select(id);
	}

	@Override
	public int insert(Product product) throws SQLException {
		try {
			sqlSession.getMapper(ProductRepo.class).insert(product);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return 1;
	}

	@Override
	public int update(Product product) throws SQLException {
		try {
			sqlSession.getMapper(ProductRepo.class).update(product);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return 1;
	}

	@Override
	public int delete(String id) throws SQLException {
		try {
			sqlSession.getMapper(ProductRepo.class).delete(id);
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return 1;
	}

}

filter(utf-8설정)는 web.xml에, interceptor(로그인상태확인)는 servlet-context.xml, aop(로깅/보안)는 root-context.xml