Day45-2 Spring Framework MySQL과 연결하기 Service이용

MySQL 연결하기

 

Connector/J 추가

https://mvnrepository.com/artifact/com.mysql/mysql-connector-j/8.0.32

<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.32</version>
</dependency>

 

 

 

 

pom.xml → 아티팩트에 추가하기 Put into Output Root

<?xml version="1.0" encoding="UTF-8"?>
<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.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.bit</groupId>
    <artifactId>spring</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>16</maven.compiler.source>
        <maven.compiler.target>16</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.25</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.32</version>
        </dependency>

    </dependencies>

</project>

 

 

 

 

dispatcherServlet.xml <bean> 설정해주기 추가하기

<bean class="com.bit.spring.connector.MySqlConnector"> 
</bean>

<?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:mvc="http://www.springframework.org/schema/mvc"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
    <mvc:annotation-driven/>
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/views/"/>
        <property name="suffix" value=".jsp"/>
    </bean>
    <bean class="com.bit.spring.connector.MySqlConnector">
        
    </bean>
    <context:component-scan base-package="com.bit.spring"/>
</beans>

 

 

 

Connector 폴더에 MySqlConnector.java 생성

MySqlConnector.java 

package com.bit.spring.connector;

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

public class MySqlConnector {
    private final String ADDRESS = "jdbc:mysql://localhost/basic";
    private final String USERNAME = "root";
    private final String PASSWORD = "1111";

    public Connection makeConnection() {
        Connection connection = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(ADDRESS, USERNAME, PASSWORD);

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return connection;
    }
}

 

 

 

Servie 폴더에 MemberService.java

MemberService.java

DB저장된 user테이블에서 id 값을 받아서 해당 DTO를 보여주는 형식

package com.bit.spring.service;

import com.bit.spring.connector.MySqlConnector;
import com.bit.spring.model.MemberDTO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Service
public class MemberService {
    private MySqlConnector connector;
    private Connection connection;
    @Autowired
    public MemberService(MySqlConnector connector){
        this.connector = connector;
        connection = this.connector.makeConnection();

    }

    public MemberDTO selectOne(int id){
        String query = "SELECT * FROM `user` WHERE `id` = ?";
        MemberDTO memberDTO = null;

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(query);

            preparedStatement.setInt(1, id);

            ResultSet resultSet = preparedStatement.executeQuery();

            if (resultSet.next()){
                memberDTO = new MemberDTO();
                memberDTO.setId(resultSet.getInt("id"));
                memberDTO.setName(resultSet.getString("username"));

            }

            resultSet.close();
            preparedStatement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return memberDTO;
    }
}

 

 

 

HomeController.java

 

package com.bit.spring.controller;

import com.bit.spring.connector.MySqlConnector;
import com.bit.spring.model.MemberDTO;
import com.bit.spring.service.MemberService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import java.net.http.HttpRequest;

@Controller
public class HomeController {
    MemberService memberService;

    @Autowired
    public HomeController(MemberService memberService){
        this.memberService = memberService;
    }

    @RequestMapping("/")
    public String showIndex(Model model){
        System.out.println("인덱스 화면으로 이동합니다.");
        String name = "관리자";
        model.addAttribute("name" , name);

        return  "index";
    }

    @RequestMapping("/{id}")
    public String showId(Model model, @PathVariable int id){
        model.addAttribute("id", id);

        return "index";
    }

    //get 방식 표현
    @GetMapping("/show")
    public String showInfo(Model model, MemberDTO memberDTO, String rank){
        System.out.println("id : " + memberDTO.getId());
        System.out.println("name : " + memberDTO.getName());
        System.out.println("rank : " + rank);

        model.addAttribute("id", memberDTO.getId());
        model.addAttribute("name", memberDTO.getName());
        model.addAttribute("rank", rank);

        return "show";
    }

}

 

 

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="core" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>인덱스</title>
</head>
<body>
    <h1>HELLO WORLD!!(*/ω＼*)</h1>
    <h2>${name}님 환영합니다!!</h2>
<core:if test="${id ne null}">
    <h3>사용자가 입력한 id : ${id}</h3>
</core:if>
<a href="/show?id=1&name=admin&rank=tutor">!!!show로 이동!!!</a>
</body>
</html>

결과