[Spring Boot] MySQL 연결

1. Dependency 추가

dependencies {
	implementation 'mysql:mysql-connector-java' // MySQL
	implementation 'org.mybatis.spring.boot.mybatis-spring-boot-starter' // MyBatis
}

2. application.properties

# DataSource
spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/{DBNAME}?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username={USERNAME}
spring.datasource.password={PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

2. config 추가

DataSourceConfig.java

package com.project.togetus.config;

import javax.sql.DataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class DataSourceConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }
    
}

MySQLConfig.java

package com.project.togetus.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

@Configuration
@MapperScan("com.project.togetus.dao")
public class MySQLConfig {

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

        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver ();
        sessionFactory.setMapperLocations(resolver.getResources("classpath:mappers/*.xml"));

        Resource myBatisConfig = new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml");
        sessionFactory.setConfigLocation(myBatisConfig);

        return sessionFactory.getObject();
    }
    
}

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

3. 연결 작업

UserDTO.java

package com.project.togetus.dto;

import lombok.Data;

@Data
public class UserDTO {
    int id;
    String user_id;
    String name;
    String status;
    String created_at;
    String modified_at;
}

UserMapper.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.project.togetus.dao.UserDAO">
    <select id="getUser" resultType="com.project.togetus.dto.UserDTO">
        select
            id,
            user_id,
            name,
            status,
            created_at,
            modified_at
        from
            TB_USER
    </select>
</mapper>

userDAO.java

package com.project.togetus.dao;

import java.util.List;

import com.project.togetus.dto.UserDTO;

public interface UserDAO {
    List<UserDTO> getUser();
    
}

userController.java

@RestController
@RequestMapping("/api")
public class userController {
    @Autowired
    private UserDAO userDAO;

    @GetMapping("/user")
    public List<UserDTO> getUser() {
        return userDAO.getUser();
    }
}

4. 해치웠다.
   

5. 덤+) .gitignore 추가작업

application.properties

를 추가해주자! 보안은 중요하다 !

package com.project.togetus;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnectionTest {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    private static final String URL = "jdbc:mysql://localhost:3306/{DBNAME}?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false";
    private static final String USER = "{USERNAME}";
    private static final String PASSWORD = "{PASSWORD}";

    @Test
    public void testConnection() throws Exception {
        Class.forName(DRIVER);
        try {
            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

***************************
APPLICATION FAILED TO START
***************************

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class


Action:

Consider the following:
        If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
        If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).

# DataSource
spring.datasource.url=jdbc:mysql://localhost:3306/{DBNAME}?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username={USERNAME}
spring.datasource.password={PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

출처
https://doozi0316.tistory.com/entry/Spring-Boot-MyBatis-MySQL-%EC%97%B0%EB%8F%99-%EB%B0%A9%EB%B2%95
https://fntg.tistory.com/193
https://inasie.github.io/it%EC%9D%BC%EB%B0%98/%EB%A7%88%ED%81%AC%EB%8B%A4%EC%9A%B4-expander-control/