Spring Boot + MySQL 연동

Java

Spring Boot + MySQL 연동

OSC131 2019. 9. 14. 02:38

728x90

Spring Boot + MySQL 연동

작성일자 : 2019.09.14

환경 : Spring Boot 2.1 MySQL 5.7 + Gradle, MyBatis

1. 연동

1.1 ) Dependency 추가

build.gradle

...

dependencies {

...

compile("org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2")
compile("commons-dbcp:commons-dbcp:1.4")
compile("mysql:mysql-connector-java:5.0.8")

...

}

1.2 ) Config

DBConfig.java

package com.example.demo;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
public class DBConfig {

@Bean
public DataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();

// DB 정보 ( 환경에 맞게 수정 필요 )
   dataSource.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/rmsdb"); // jdbc:mysql://'domain':'port'/'DataBaseName'
  dataSource.setUsername("root"); // ID
  dataSource.setPassword("qweqweqwe"); // PW

return dataSource;
}

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);

   sqlSessionFactory.setMapperLocations(
    new PathMatchingResourcePatternResolver().getResources("classpath*:com/example/demo/*.xml")
   ); // Mapper 위치 설정

return (SqlSessionFactory)sqlSessionFactory.getObject();
}

@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}

}

Mybatis는 SqlSession이라는 자바 인터페이스를 이용

SqlSessionFactory에서 SqlSession를 생성

SqlSessionTemplate은 SqlSession을 구현하고 코드에서 SqlSession를 대체하는 역할

1.3 ) 연동 확인

Test Controller 생성 후 설정한 Bean 정보 출력

package com.example.demo;

import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class TestController {

@Autowired
DataSource dataSource;

@Autowired
SqlSessionFactory sqlSessionFactory;

@Autowired
SqlSessionTemplate sqlSessionTemplate;

@RequestMapping("/check")
public void test() throws SQLException {
   System.out.println(dataSource.getClass());
   System.out.println(sqlSessionFactory.getClass());
   System.out.println(sqlSessionTemplate.getClass());
   System.out.println(dataSource.getConnection());
}
}

콘솔 확인

2. 기본 CRUD 세팅

MyBatis의 Mapper 연결 방식은

1. Mapper namespace를 직접 선언하여 연결하는 방식과

2. Mapper Interface를 활용하는 방식이 존재

해당 글에서는 Mapper namesapce를 직접 사용

TestDto.java

package com.example.demo;

public class TestDto {

String id;

String comment;

public String getComment() {

return comment;

}

public String getId() {

return id;

}

public void setComment(String comment) {

this.comment = comment;

}

public void setId(String id) {

this.id = id;

}

TestDao.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">

SELECT *

FROM

testtable

</select>

SELECT *

FROM

testtable

WHERE

id = #{id}

</select>

INSERT INTO testtable ()

VALUES (

#{id},

#{comment}

)

</insert>

UPDATE testtable

SET

comment = #{comment}

WHERE

id = #{id}

</update>

DELETE

FROM

testtable

WHERE

id = #{id}

</delete>

</mapper>

SqlSessionFactory의 Mapper 설정 위치 아래에 생성

mapper에서의 namespace 및 id는 DAO에서 Parameter로 사용

TestDao.java

package com.example.demo;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Repository;

@Repository("TestDao")

public class TestDao {

@Autowired

private SqlSessionTemplate sqlSessionTemplate;

public List<TestDto> selectList(){

return sqlSessionTemplate.selectList("com.example.demo.TestDao.selectList");

};

public TestDto selectOne(TestDto testDto){

return sqlSessionTemplate.selectOne("com.example.demo.TestDao.selectOne",testDto);

};

public int create(TestDto testDto){

return sqlSessionTemplate.insert("com.example.demo.TestDao.create",testDto);

};

public int update(TestDto testDto){

return sqlSessionTemplate.update("com.example.demo.TestDao.update",testDto);

};

public int delete(TestDto testDto){

return sqlSessionTemplate.delete("com.example.demo.TestDao.delete",testDto);

};

}

TestController.java

package com.example.demo;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionTemplate;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

@Controller

public class TestController {

@Autowired

DataSource dataSource;

@Autowired

SqlSessionFactory sqlSessionFactory;

@Autowired

SqlSessionTemplate sqlSessionTemplate;

@Autowired

TestDao testDao;

@RequestMapping("/check")

public String test() throws SQLException {

System.out.println(dataSource.getClass());

System.out.println(sqlSessionFactory.getClass());

System.out.println(sqlSessionTemplate.getClass());

System.out.println(dataSource.getConnection());

return "test";

}

@RequestMapping("/1")

public String create() {

TestDto testDto = new TestDto();

testDto.setId("1");

testDto.setComment("Comment");

testDao.create(testDto);

return "create";

}

@RequestMapping("/2")

public String read() {

TestDto testDto = new TestDto();

testDto.setId("1");

System.out.println(testDao.selectList());

System.out.println(testDao.selectOne(testDto));

return "read";

}

@RequestMapping("/3")

public String update() {

TestDto testDto = new TestDto();

testDto.setId("1");

testDto.setComment("update");

testDao.update(testDto);

return "update";

}

@RequestMapping("/4")

public String delete() {

TestDto testDto = new TestDto();

testDto.setId("1");

testDao.delete(testDto);

return "delete";

}

DB Schema

CREATE TABLE `testtable` (
  `id` varchar(45) NOT NULL,
  `comment` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

---------

위 설정 후 Controller로의 URL 접근을 통해 CRUD 기능 동작 확인 가능

1. Create

2. Read

각각 List와 Obejct 형태

3. Update

4. Delete

Project 구조

-----------

Mapper Interface 활용 방식

in java

@Mapper
public interface MapperInterface {
List selectList();
TestDto selectOne(TestDto testDto);
int create(TestDto testDto);
int update(TestDto testDto);
int delete(TestDto testDto);
}

In .xml

<mapper namespace="com.example.demo.MapperInterface">

...

</mapper>

이 경우 SqlSessionTemplate, SqlSessionFactory 설정은 필요 X

xml 없이 Mapper Interface에 SQL을 설정하는 방법도 있다

ex)

@Mapper
public interface MapperAnnotation {
@Select("SELECT * FROM testtable")
List selectList();

@Select("SELECT * FROM testtable WHERE id = #{id}")
TestDto selectOne(TestDto testDto);

@Insert("INSERT INTO testtable () VALUES (#{id}, #{comment})")
int create(TestDto testDto);

@Update("UPDATE testtable SET comment = #{comment} WHERE id = #{id}")
int update(TestDto testDto);

@Delete("DELETE FROM testtable WHERE id = #{id}")
int delete(TestDto testDto);
}

728x90