spring mybatis mysql 연결 예제

■ eclipse Package Explorer 캡쳐

 
 
 

■ maven pom.xml

<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.0http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>net.iotinfra</groupId>
  <artifactId>pilot.mybatistest</artifactId>
  <version>0.0.1-SNAPSHOT</version>
 
  <properties>
    <java-version>1.7</java-version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
              
    <org.springframework-version>4.2.5.RELEASE</org.springframework-version>
              
    <!-- Test -->
    <junit.version>4.11</junit.version>
    <mockito-all.version>1.10.19</mockito-all.version>
  </properties>
       
  <dependencies>
    <!-- Spring -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</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-web</artifactId>
      <version>${org.springframework-version}</version>
    </dependency>

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


    <!-- Servlet -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
    </dependency>
              
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.2</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>

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

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.7</version>
      <scope>test</scope>
    </dependency>
              
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>${org.springframework-version}</version>
      <scope>test</scope>
    </dependency>
       
  </dependencies>
</project>

 
 
 

■ servlet-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:task="http://www.springframework.org/schema/task"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:mvc="http://www.springframework.org/schema/mvc"
  xmlns:p="http://www.springframework.org/schema/p"
  xmlns:util="http://www.springframework.org/schema/util"
  xsi:schemaLocation="
http://www.springframework.org/schema/mvchttp://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/taskhttp://www.springframework.org/schema/task/spring-task.xsd
http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/utilhttp://www.springframework.org/schema/util/spring-util.xsd
">
  <context:component-scan base-package="pilot.mybatistest"/>
</beans>

 
 
 

■ datasource-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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
    ">
  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/nextdb"></property>
    <property name="username" value="nextman"></property>
    <property name="password" value="pass"></property>
  </bean>
   
</beans>

 
 
 

■ 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>
  <settings>
    <setting name="mapUnderscoreToCamelCase" value="true" />
  </settings>
</configuration>

 
 
 

■ mapper-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"
       xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/mvchttp://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/utilhttp://www.springframework.org/schema/util/spring-util.xsd">
       
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="mapperLocations" value="classpath:pilot/mybatistest/mapper/*Mapper.xml"/>
    <property name="configLocation" value="classpath:config/mybatis-config.xml" />
  </bean>
  <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory"/>
  </bean>
</beans>

 
 
 

■ mysql 테이블 생성 스크립트, nextdb 데이터베이스에 person 테이블 생성

create table `nextdb`.`person` (
  `pid` int(10) not null auto_increment primary key,
  `age` int(10),
  `name` varchar(10),
  `gubun` char(1),
  `use_yn` char(1)
)
engine=InnoDB
default character set = utf8;

 
 
 

■ person 테이블에 샘플 데이터 입력

insert into person ( name, age, gubun, use_yn ) values ( '강감찬', 23, 'F', 'Y');
insert into person ( name, age, gubun, use_yn ) values ( '아이유', 22, 'F', 'Y');
insert into person ( name, age, gubun, use_yn ) values ( '이선희', 53, 'F', 'Y');
insert into person ( name, age, gubun, use_yn ) values ( '이다윗', 35, 'M', 'Y');
insert into person ( name, age, gubun, use_yn ) values ( '나훈아', 73, 'M', 'N');
insert into person ( name, age, gubun, use_yn ) values ( '김범수', 42, 'M', 'Y');
insert into person ( name, age, gubun, use_yn ) values ( '이문세', 60, 'M', 'N');
insert into person ( name, age, gubun, use_yn ) values ( '조수미', 58, 'F', 'N');

 
 
 

■ Mapper 파일 생성(PersonMapper.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="mapper.PersonDAO">
<select id="listPerson"
    parameterType="pilot.mybatistest.dto.Person"
       resultType="pilot.mybatistest.dto.Person">
       SELECT /* PersonMapper.listPerson */
              PID
            , NAME
            , AGE
            , GUBUN
            , USE_YN
         FROM PERSON
        WHERE GUBUN = #{gubun}
</select>
</mapper>

 
 
 

■ 데이터 전송을 위한 Person Object 생성(Person.java)

package pilot.mybatistest.dto;
public class Person {
  private String pid;
  private int age;
  private String name;
  private String gubun;
  private String useYn;
  public String getPid() {
    return pid;
  }
  public void setPid(String pid) {
    this.pid = pid;
  }
  public int getAge() {
    return age;
  }
  public void setAge(int age) {
    this.age = age;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getGubun() {
    return gubun;
  }
  public void setGubun(String gubun) {
    this.gubun = gubun;
  }
  public String getUseYn() {
    return useYn;
  }
  public void setUseYn(String useYn) {
    this.useYn = useYn;
  }
}

 
 
 

■ DAO클래스 생성(PersonDAO.java)

package pilot.mybatistest.dao;
import java.util.List;
import pilot.mybatistest.dto.Person;
public interface PersonDAO {
  public List<Person> listPerson(Person params);
}

 
 
 

■ Service 인터페이스 생성(PersonService.java)

package pilot.mybatistest.service;
import java.util.List;
import pilot.mybatistest.dto.Person;
public interface PersonService {
  public List<Person> listPerson(Person params);
}

 
 
 

■ Service 클래스 생성(PersonServiceImpl.java)

package pilot.mybatistest.service.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import pilot.mybatistest.dao.PersonDAO;
import pilot.mybatistest.dto.Person;
import pilot.mybatistest.service.PersonService;
@Service
public class PersonServiceImpl implements PersonService {
  @Autowired
  private SqlSession sqlSession;
       
  public List<Person> listPerson(Person params) {        
    final PersonDAO personDAO = sqlSession.getMapper(PersonDAO.class);
    return personDAO.listPerson(params);
  }
}

 
 
 

■ JUnit Service 테스트 클래스 (MyBatisTest.java)

package pilot.mybatistest.service;
import static org.junit.Assert.assertTrue;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import pilot.mybatistest.dto.Person;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {
  "classpath:config/datasource-context.xml",
  "classpath:config/servlet-context.xml",
  "classpath:config/mapper-context.xml"
})
@WebAppConfiguration
public class MyBatisTest {
  @Resource
  private PersonService personService;
       
  private Person params;
       
  @Before
  public void setUp() throws Exception {
    params = new Person();
    params.setGubun("F");
  }
       
  @Test
  public void testListPerson() {
    List<Person> list = personService.listPerson(params);
    for( Person person : list ) {
      System.out.println(
        person.getName() +"\t"+
        person.getAge() +"\t"+
        person.getGubun() +"\t"+
        person.getUseYn());
    }
    assertTrue(true);
  }
}

 
 
 

■ 실행 결과