스프링 Mysql 연동 초기셋팅.

 

porm.xml

 

		<!-- 여기서부터 추가 -->
		
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>

		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		
		<!-- maridb setting -->
		
		<dependency>
		    <groupId>org.mariadb.jdbc</groupId>
		    <artifactId>mariadb-java-client</artifactId>
		    <version>2.3.0</version>
		</dependency>
		

		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.4.6</version>
		</dependency>
		
		
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>1.3.2</version>
		</dependency>
		
		<dependency>
		    <groupId>org.bgee.log4jdbc-log4j2</groupId>
		    <artifactId>log4jdbc-log4j2-jdbc4</artifactId>
		    <version>1.16</version>
		</dependency>
        
        		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>    
	

 

java version 수정

 

	<properties>
		<java-version>1.8</java-version>
		<org.springframework-version>4.3.8.RELEASE</org.springframework-version>
		<org.aspectj-version>1.8.9</org.aspectj-version>
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>

 

src/main/resources

 

mappers 생성 -> SQL xml 파일 위치

 

파일 추가

log4jdbc.log4j2.properties

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
	<include resource="org/springframework/boot/logging/logback/base.xml"></include>
	
	<!-- log4jdbc-log4j2 -->
	<logger name="jdbc.sqlonly" 		level="DEBUG"></logger>
	<logger name="jdbc.sqltiming" 		level="DEBUG"></logger>
	<logger name="jdbc.audit" 			level="DEBUG"></logger>
	<logger name="jdbc.resultset" 		level="DEBUG"></logger>
	<logger name="jdbc.resultsettable" 	level="DEBUG"></logger>
	<logger name="jdbc.connection" 		level="DEBUG"></logger>
</configuration>

 

mybatis-config.xml

<?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>
		<package name="com.peta.domain"/>
	</typeAliases>
</configuration>

 

package 는 맞는 이름으로 설정.

 

 

src/main/resources

mappers xml 파일 생성

boardMapper.xml

<?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.peta.mapper.BoardMapper">
  
  	<insert id="create">
  	insert into tb1_board (title,content,writer) value (#{title},#{content},#{writer})
  	</insert>
  
  	<select id="read" resultType="com.peta.domain.BoardVO">
  	select bno,title,content,writer,regdate,viewcnt from tb1_board where bno=#{bno}
  	</select>
  	
  	<update id="update">
  	update tb1_board set title=#{title}, content =#{content} where bno=#{bno}
  	</update>
  
  	<delete id="delete">
  	delete from tb1_board where bno=#{bno}
  	</delete>
  
  	<select id="listAll" resultType="com.peta.domain.BoardVO">
  	<![CDATA[
  	select bno,title,content,writer,regdate,viewcnt from tb1_board where bno > 0 order by bno desc, regdate desc
  	]]>
  	</select>
  	
  	<select id="listPage" resultType="BoardVO">
  	<![CDATA[
  	select bno, title, content, writer, regdate, viewcnt from tb1_board where bno > 0 
  	order by bno desc, regdate desc limit #{page},10
  	]]>
  	</select>
  	
  	<select id="listCriteria" resultType="BoardVO">
  		<![CDATA[
  		select bno, title, content, writer, regdate, viewcnt from tb1_board where bno > 0 
  		order by bno desc, regdate desc limit #{pageStart}, #{perPageNum}
  		]]>
  	</select>
  	
  	<select id="countPaging" resultType="int">
  		<![CDATA[
  		select count(bno) from tb1_board where bno > 0
  		]]>
  	</select>
	<sql id="search">
  		<if test ="searchType !=null">
  			<if test="searchType = 't'.toString()">
  				and title like CONCAT('%',#{keyword},'%')
  			</if>
  			<if test="searchType = 'c'.toString()">
  				and content like CONCAT('%',#{keyword},'%')
  			</if>
  			<if test="searchType = 'w'.toString()">
  				and writer like CONCAT('%',#{keyword},'%')
  			</if>
  			<if test="searchType = 'tc'.toString()">
  				and (title like CONCAT('%',#{keyword},'%') OR content like CONCAT('%',#{keyword},'%'))
  			</if>
  			<if test="searchType = 'cw'.toString()">
  				and (content like CONCAT('%',#{keyword},'%') OR writer like CONCAT('%',#{keyword},'%'))
  			</if>
  			<if test="searchType = 'tcw'.toString()">
  				and (title like CONCAT('%',#{keyword},'%') OR content like CONCAT('%',#{keyword},'%')
  				OR writer like CONCAT('%',#{keyword},'%'))
  			</if>
  		</if>
	</sql>
  		
  	<select id="listSearch" resultType="BoardVO">
  		<![CDATA[
  		select * from tb1_board where bno > 0 
  		]]>
		<include refid="search"></include>
  		<![CDATA[
  		order by bno desc limit #{pageStart}, #{perPageNum}
  		]]>
  		
  	</select>
  	
  	<select id="listSearchCount" resultType="int">
  	<![CDATA[
  	select count(bno) from tb1_board where bno > 0
  	]]>
  	<include refid="search"></include>
  	</select>
 </mapper>

 

src/main/webapp/WEB-INF/spring/appServlet/servlet-content.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.peta.controller" />
	
	
	
</beans:beans>

 

src/main/webapp/WEB-INF/views/web.xml

 

	<filter>
		<filter-name>encoding</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
			<init-param>
				<param-name>encoding</param-name>
				<param-value>UTF-8</param-value>
			</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encoding</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

encoding 추가

 

src/main/webapp/WEB-INF/spring/root-context.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"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	xsi:schemaLocation="http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
		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 -->
		
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   		<!-- <property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property> -->
        <property name="driverClassName" value="org.mariadb.jdbc.Driver"></property>
        <property name="url" value="jdbc:mariadb://127.0.0.1:3306/book_ex?useSSL=false"></property>
        <property name="username" value="id"></property>
        <property name="password" value="password"></property>
    </bean>
    
   	<bean id="SqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="configLocation" value="classpath:/mybatis-config.xml"/>
		<property name="mapperLocations" value="classpath:/mappers/*Mapper.xml" />
	</bean>
	
	
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"

		destroy-method="clearCache">

		<constructor-arg name="sqlSessionFactory" ref="SqlSessionFactory" />

	</bean> 
	<context:component-scan base-package="com.peta.persistence"></context:component-scan>
	<context:component-scan base-package="com.peta.service"></context:component-scan>
</beans>

 

JAVA 버전 변경

 

 

JUNIT 테스트시 추가.

 

 

test 

Mapper.xml 파일 모두 주석처리후.

 

src/test/java/DataTest.java

package com.peta.home;

import java.sql.Connection;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations= {"file:src/main/webapp/WEB-INF/spring/**/root-context.xml"})
public class DataTest {
	@Inject
	private DataSource ds;
	
	@Test
	public void testConection()throws Exception{
		try(Connection con = ds.getConnection()){
			System.out.println("디비연결성공");
			System.out.println(con);
		}catch(Exception e) {
			System.out.println("디비연결실패");
			e.printStackTrace();
		}
		
	}
	
}

디비 연결 완료

 

jsp 양식 추가

<!DOCTYPE html>

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

<html>
	<head>
		<meta charset="utf-8">
		<title>Welcome</title>
	</head> 
	<body>

	</body>
</html>