mysql(mariaDB)+spring+mybatis 연결

*** mysql(mariaDB)+Spring 연결

1. pom.xml 라이브러리 추가

<!-- db -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

<!-- jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>${org.springframework-version}</version>
</dependency>

 

2. webapp/WEB-INF/spring/root-context.xml 수정

   - root-context.xml 하단 namespaces 탭에서 (aop, beans, context, jdbc) 체크

   - source탭에서 위에 namespaces 추가된 애들 확인

xmlns:aop="http://www.springframework.org/schema/aop"
          xmlns:context="http://www.springframework.org/schema/context"
          xmlns:jdbc="http://www.springframework.org/schema/jdbc"
          xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
              http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
              http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd

         ......

    

   - spring-jdbc모듈의 클래스를 이용하여 dataSource 추가

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" lazy-init="false">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/{db명}}" />
    <property name="username" value="{아이디}}" />
    <property name="password" value="{패스워드}" />
</bean>

     

 

3. src/test/java 에 DataSourceTest.java 클래스 생성 하고 소스추가

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/**/*.xml"})
public class DataSourceTest {

    @Inject
    private DataSource ds;

    @Test
    public void testConnection() throws Exception {
        try (Connection con = ds.getConnection()) {
        	System.out.println(con);
        } catch (Exception e) {
        	e.printStackTrace();
        }
    }
}

 

4. 3번에 생성된 클래스를 JUnit 실행

   - 오류가 생기면 junit을 dependency에 추가하자

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

   - 콘솔에 com.mysql.jdbc.JDBC4Connection@6e535154 라고 커넥팅 내용이 출력되면 성공​ 

 

 

*** spring+mybatis 연동 / 사용 예제

1. pom.xml 파일 마우스 우클릭 -> maven -> add dependency를 클릭 후 내용입력

   [내용입력 -> org.mybatis mybatis 선택]

   group Id : org.mybatis

   artifact Id : mybatis

   version : 3.2.7

   ...prefix or pattern (*) : mybatis

 

   [내용입력 -> org.mybatis mybatis-spring 선택]

   group Id : org.mybatis

   artifact Id : mybatis-spring

   version : 1.2.2

   ...prefix or pattern (*) : mybatis-spring

 

   [내용입력 -> mysql mysql-connector-java 선택]

   group Id : mysql

   artifact Id : mysql-connector-java

   version : 5.1.32

   ...prefix or pattern (*) : mysql-connector-java

 

   [내용입력 -> org.springframework spring-jdbc 선택]

   group Id : org.springframework

   artifact Id : spring-jdbc

   version : 4.0.6.RELEASE

   ...prefix or pattern (*) : spring-jdbc

 

2. 1번과 같이 dependency를 추가해주면 pom.xml source에 아래와 같은 내용이 추가된다.

<!-- Mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.7</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.2.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.32</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.0.6.RELEASE</version>
</dependency>

 

3. webapp/WEB-INF/spring/root-context.xml에 namespace를 추가

   - beans, jdbc, mvc, mybatis-spring 체크

   [source에 내용 추가]   

<bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:8080/dbname"></property>
    <property name="username" value="root"></property>
    <property name="password" value="1234"></property>
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    	<property name="configLocation" value="classpath:mybatis/mybatis-config.xml">
    </property>
</bean>

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="dataSource"></property>
</bean>

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg  ref="sqlSessionFactory"></constructor-arg>
</bean>

 

4. src -> main -> resources 폴더에서 마우스 우클리갛여 new->other->java->package 선택

   - mybatis로 생성

 

5. 4번에서 생성한 mybatis마우스 우클릭하여 new->other->xml->xml file선택하여 mapper.xml과 mybatis.config.xml 2개를 생성하고 내용추가

   [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>
	<mappers>
  		<mapper resource="mybatis/mapper.xml" />
	</mappers >
</configuration>

 

   [mapper.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= "userControlMapper" >
    <select id ="selectSample" parameterType="java.util.HashMap" resultType= "java.util.HashMap">
        select *
        from {테이블명}
    </select >
</mapper>

 

6. java 소스에 연동

   [HomeController.java]

@Controller
public class HomeController {

	private static final Logger logger = LoggerFactory.getLogger(HomeController.class);

    @Autowired  // 추가
    private SqlSession sqlSession;  // 추가

    /**
    * Simply selects the home view to render by returning its name.
    */
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String home(Locale locale, Model model) {
        logger.info("Welcome home! The client locale is {}.", locale);

        Date date = new Date();
        DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);

        String formattedDate = dateFormat.format(date);
        model.addAttribute("serverTime", formattedDate );

        List<HashMap<String, String>> outputs = sqlSession.selectList("userControlMapper.selectSample");  // 추가
        model.addAttribute("showDB", outputs.toString());  // 추가

        return "home";
    }

}

 

  [home.jsp]

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page session="false"%>
<%@ page contentType="text/html; charset=UTF-8"%>
<html>
    <head>
    	<title>Home</title>
    </head>
    <body>
        <h1>
        Hello world!
        </h1>

        <P>  The time on the server is ${serverTime}. </P>
        <p> This is my Database </p>
        <p> ${showDB}</p>
    </body>
</html>

 

7. (번외) 6번처럼 데이터를 불러오면 하나의 스트링형태로 뿌려주는 문제가 있다. ModelAndView를 사용하자

   [기존 HomeController.java 수정 -> 6번 참조]

@RequestMapping(value = "/", method = RequestMethod.GET)
public ModelAndView home(Locale locale, Model model) {
    logger.info("Welcome home! The client locale is {}.", locale);

    Date date = new Date();
    DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);

    String formattedDate = dateFormat.format(date);

    model.addAttribute("serverTime", formattedDate );

    List<HashMap<String, String>> outputs = sqlSession.selectList("userControlMapper.selectSample");
    ModelAndView mav=new ModelAndView("home","m" , outputs);
    return mav;
}

 

    [6번의 home.jsp 수정(=ex2)]

<table border="1">
    <tr>
        <td>번호</td>
        <td>Vrms</td>
        <td>Irms</td>
        <td>Watt</td>
        <td>Pf</td>
        <td>Wh</td>
        <td>Date</td>
    </tr>
    <c:forEach var="mo" items="${m}">
        <tr>
            <td><c:out value="${mo.id}"></c:out></td>
            <td><c:out value="${mo.Vrms}"></c:out></td>
            <td><c:out value="${mo.Irms}"></c:out></td>
            <td><c:out value="${mo.Watt}"></c:out></td>
            <td><c:out value="${mo.Pf}"></c:out></td>
            <td><c:out value="${mo.Wh}"></c:out></td>
            <td><c:out value="${mo.date}"></c:out></td>
        </tr>
    </c:forEach>
</table>

 

-- 그 외 문제점

   # localhost로 접속하면 되는데 ip를 적어주면 안되는 현상

     -> root 계정은 locallhost나 127.0.0.1로만 접속되고 ip를 직접 적게되면 id를 만들어서 접근해야 한다.​