FrameWork/Spring Framework
스프링프레임워크 + MySQL 연동 - spring-ver2
print(blue)
2024. 7. 29. 17:06
구조
MySQL 연동
MySQL 과 스프링프레임워크를 연동해줘야 함
메이븐에서 설치할 것
https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.33
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
https://mvnrepository.com/artifact/org.springframework/spring-jdbc/5.2.25.RELEASE
bean 만들기
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/ver2?useSSL=false&serverTimezone=UTC" />
</bean>
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/ver2?useSSL=false&serverTimezone=UTC" />
</bean>
C:\Users\hi\.m2\repository\com\mysql\mysql-connector-j\8.0.33
프로젝트에 있다보니까 재실행할 때마다 실행 중지 실행 중지 계속 이러다 보니 문제가 생겨서
톰캣쪽에 두는 것이 깔끔
마이바티스
https://mvnrepository.com/artifact/org.mybatis/mybatis
https://mvnrepository.com/artifact/org.mybatis/mybatis-spring
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.1.2</version>
</dependency>
xml 파일로 쿼리를 작성할 수 있게 됨
현재 작업할 땐 리파지토리 씀
https://mybatis.org/mybatis-3/ko/getting-started.html
mybatis – 마이바티스 3 | 시작하기
mybatis.org
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
마이바티스를 쓴다는 것을 알려줘야함
https://mybatis.org/mybatis-3/ko/sqlmap-xml.html
mybatis – 마이바티스 3 | 매퍼 XML 파일
Mapper XML 파일 마이바티스의 가장 큰 장점은 매핑구문이다. 이건 간혹 마법을 부리는 것처럼 보일 수 있다. SQL Map XML 파일은 상대적으로 간단하다. 더군다나 동일한 기능의 JDBC 코드와 비교하면
mybatis.org
<?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 https://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- Root Context: defines shared resources visible to all other web components -->
<!-- 프레임워크 실행 후 만들고 싶은 빈 여기다가 작성 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/ver2?useSSL=false&serverTimezone=UTC" />
<property name="username" value="root" />
<property name="password" value="1111" />
</bean>
<!--
<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" /> name은 setter ref는 위에있는 것 참조
</bean>
-->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!-- resources 부터 인식이 됨 -->
<property name="mapperLocations" value="classpath:/ver2/mapper/member-mapper.xml" />
</bean>
<!-- 컨트롤러에 들어갈거라 repository 로 함 -->
<bean id="repository" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="mapperInterface" value="himedia.spring.ver2.repository.MemberRepository" />
<property name="sqlSessionFactory" ref="sqlSessionFactoryBean" />
</bean>
</beans>
config
https://mybatis.org/mybatis-3/configuration.html
mybatis – MyBatis 3 | Configuration
JDBC – This configuration simply makes use of the JDBC commit and rollback facilities directly. It relies on the connection retrieved from the dataSource to manage the scope of the transaction. By default, it enables auto-commit when closing the connecti
mybatis.org
1. Model
1-1. DTO
package himedia.spring.ver2.dto;
public class Member {
private Long id;
private String name;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
1-2. Repository
package himedia.spring.ver2.repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Repository;
import himedia.spring.ver2.dto.Member;
// @Primary
// @Repository
public class MemberJdbcRepository implements MemberRepository {
// [DI] 의존성 주입 ==================================================
// [방법 1] 생성자를 통한 의존성 주입
// private final DataSource dataSource;
//
// // root-context.xml 에서 생성
// // 매개변수로 들어와 this 로 값 넣어주고 필드에 설정
// @Autowired
// public MemberJdbcRepository(DataSource dataSource) {
// this.dataSource = dataSource;
// System.out.println("[repository] MemberJdbcRepository 실행됨!!!");
// }
// ===================================================================
// [방법 2] 필드를 통한 의존성 주입
@Autowired
private DataSource dataSource;
public MemberJdbcRepository() {
System.out.println("[repository] MemberJdbcRepository 실행됨!!!"
+ "==> field 를 통한 의존성 투입");
}
@Override
public Long save(Member member) {
String sql = "insert into member(name) values(?)";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, member.getName());
pstmt.executeUpdate();
rs = pstmt.getGeneratedKeys();
if(rs.next()) {
member.setId(rs.getLong(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(pstmt != null)
pstmt.close();
if(conn != null)
conn.close();
} catch(Exception e2) {
e2.printStackTrace();
}
}
return member.getId();
}
@Override
public Optional<Member> findById(Long id) {
String sql = "select * from member where id = ?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
Member member = new Member();
member.setId(rs.getLong("id"));
member.setName(rs.getString("name"));
return Optional.of(member);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(conn != null)
conn.close();
} catch(Exception e2) {
e2.printStackTrace();
}
}
return Optional.empty();
}
@Override
public Optional<Member> findByName(String name) {
String sql = "select * from member where name = ?";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
rs = pstmt.executeQuery();
if (rs.next()) {
Member member = new Member();
member.setId(rs.getLong("id"));
member.setName(rs.getString("name"));
return Optional.of(member);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(conn != null)
conn.close();
} catch(Exception e2) {
e2.printStackTrace();
}
}
return Optional.empty();
}
@Override
public List<Member> findAll() {
String sql = "select * from member";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Member> members = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
members = new ArrayList<>();
while (rs.next()) {
Member member = new Member();
member.setId(rs.getLong("id"));
member.setName(rs.getString("name"));
members.add(member);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null)
rs.close();
if(pstmt != null)
pstmt.close();
if(conn != null)
conn.close();
} catch(Exception e2) {
e2.printStackTrace();
}
}
return members;
}
}
package himedia.spring.ver2.repository;
//import java.util.HashMap;
//import java.util.Map;
//import org.springframework.jdbc.core.PreparedStatementSetter;
import java.util.List;
import java.util.Optional;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import himedia.spring.ver2.dto.Member;
// @Primary
// @Repository
public class MemberJdbcTemplateRepository implements MemberRepository {
private final JdbcTemplate jdbcTemplate;
// @Autowired
// public MemberJdbcTemplateRepository(JdbcTemplate jdbcTemplate) {
// System.out.println("[repository] MemberJdbcTemplateRepository 실행됨 !");
// this.jdbcTemplate = jdbcTemplate;
// }
public MemberJdbcTemplateRepository(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
System.out.println("[repository] MemberJdbcTemplateRepository 실행됨 !");
}
@Override
public Long save(Member member) {
System.out.println(">> save() 실행 시작 >>");
// insert into member(name) values(?);
SimpleJdbcInsert insertActor = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("member") // insert into table
.usingGeneratedKeyColumns("id"); // auto_increment
// // 매개변수로 받은 member 에서 이름을 추출하여 HashMap 에 저장 =================
// Map<String, Object> parameters = new HashMap<String, Object> ();
// parameters.put("name", member.getName());
// Long key = insertActor.executeAndReturnKey(parameters).longValue(); // DB 에 데이터를 추가 + 자동 생성된 key의 값을 반환
// // =============================================================================
// 위 3줄 코드를 1줄로 끝남
Long key = insertActor.executeAndReturnKey(new BeanPropertySqlParameterSource(member)).longValue();
member.setId(key);
System.out.println(">> save() 실행 종료 >>");
return member.getId();
}
// 방법 1
@Override
public Optional<Member> findById(Long id) {
List<Member> result = jdbcTemplate.query("select * from member where id = ?",
new BeanPropertyRowMapper<Member> (Member.class), id);
return result.stream().findAny();
}
// 방법 2 : 바로 리턴
@Override
public Optional<Member> findByName(String name) {
String qyery = "select * from member where name like ?";
return jdbcTemplate.query(qyery,
new BeanPropertyRowMapper<Member> (Member.class), name)
.stream().findAny();
}
@Override
public List<Member> findAll() {
return jdbcTemplate.query("select * from member", new BeanPropertyRowMapper<Member> (Member.class));
}
}
package himedia.spring.ver2.repository;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import org.springframework.stereotype.Repository;
import himedia.spring.ver2.dto.Member;
// @Repository
public class MemberMemoryRepository implements MemberRepository {
private static Map<Long, Member> store = new HashMap<Long, Member> ();
private static Long sequence = 0L;
public MemberMemoryRepository() {
System.out.println("[repository] MemberMemoryRepository");
}
@Override
public Long save(Member member) {
member.setId(++sequence);
store.put(member.getId(), member);
System.out.println("저장 완료!");
return member.getId();
}
@Override
public Optional<Member> findById(Long id) {
return Optional.ofNullable(store.get(id));
}
@Override
public Optional<Member> findByName(String name) {
return store.values().stream()
.filter(n -> n.getName().equals(name)) // 람다식 8버전
.findAny();
}
@Override
public List<Member> findAll() {
return new ArrayList<Member> (store.values());
}
}
package himedia.spring.ver2.repository;
import java.util.List;
import java.util.Optional;
import himedia.spring.ver2.dto.Member;
public interface MemberRepository {
// Member save(Member member);
Long save(Member member);
Optional<Member> findById(Long id);
Optional<Member> findByName(String name);
List<Member> findAll();
}
2. View
spring-ver2/src/main/webapp/WEB-INF/views
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>행복한 마켓</title>
</head>
<body>
<h1>행복한 마켓</h1>
<div>회원 관리</div>
<div>
<a href="member/new">회원 가입</a>
<a href="member/list">회원 목록</a>
</div>
<form action="member/search">
<fieldset>
<legend>[검색하기]</legend>
<div>
<label>아이디로 검색 : </label>
<input type="text" name="id" placeholder="아이디를 입력하세요 ...">
</div>
<div>
<label>이름으로 검색 : </label>
<input type="text" name="name" placeholder="이름을 입력하세요 ...">
</div>
<input type="submit" value="검색">
</fieldset>
</form>
</body>
</html>spring-ver2/src/main/webapp/WEB-INF/views/member
<%@page import="java.util.List"%>
<%@page import="himedia.spring.ver2.dto.Member"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>[memberList.jsp] 회원 목록</title>
<style type="text/css">
table {border-collapse: collapse;}
tr, td, th {border: 2px solid #999;}
</style>
</head>
<body>
<%-- <%
List<Member> members = (List<Member>) request.getAttribute("members");
System.out.println("members : " + members);
System.out.println("members.size() : " + members.size());
%> --%>
<h1>회원 목록</h1>
<table>
<thead>
<tr>
<th>번호</th>
<th>이름</th>
</tr>
</thead>
<tbody>
<c:forEach var="member" items="${members}">
<tr>
<th>${member.id}</th>
<th>${member.name}</th>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>[search.jsp] 검색 결과</title>
</head>
<body>
<h1>검색 결과</h1>
<div>
<span>아이디 : </span>
<span>${member.id}</span>
</div>
<div>
<span>이름 : </span>
<span>${member.name}</span>
</div>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>[newForm.jsp] 회원가입</title>
</head>
<body>
<%
String context = request.getContextPath();
System.out.println("context >> " + context); // context >> /ver2
%>
<h1>회원가입</h1>
<!-- [방법 1] 상대 주소 -->
<!-- <form action="join" method="get"> -->
<!-- [방법 2]절대 주소 -->
<%-- <form action="<%= context %>/member/join" method="post"> --%>
<form method="post">
<label>이름 : </label>
<input type="text" name="name" placeholder=""이름을 입력하세요...">
<input type="submit" value="입력완료">
</form>
</body>
</html>
spring-ver2/src/main/resources/ver2/mapper
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="himedia.spring.ver2.repository.MemberRepository">
<insert id="save" keyProperty="id" useGeneratedKeys="true" parameterType="himedia.spring.ver2.dto.Member">
<!-- 대문자 변경 : Query 블록 지정 후, 컨트롤 + 쉬프트 + x -->
<!-- error : There is no getter for property named 'NAME' in 'class himedia.spring.ver2.dto.Member' -->
<!-- getter 실행 -->
<!-- INSERT INTO MEMBER(NAME) VALUES(#{NAME}) -->
INSERT INTO MEMBER(NAME) VALUES(#{name})
</insert>
<!-- getter 가 아닌 매개변수 id, name -->
<select id="findById" parameterType="Long" resultType="himedia.spring.ver2.dto.Member">
SELECT * FROM MEMBER WHERE ID = #{id}
</select>
<select id="findByName" parameterType="String" resultType="himedia.spring.ver2.dto.Member">
SELECT * FROM MEMBER WHERE NAME LIKE #{name}
</select>
<select id="findAll">
SELECT * FROM MEMBER
</select>
</mapper>3. Controller
package himedia.spring.ver2.controller;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
@Controller
public class HomeController {
@RequestMapping("/")
public String index() {
return "index"; // 논리뷰
}
}
package himedia.spring.ver2.controller;
import java.util.List;
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.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import himedia.spring.ver2.dto.Member;
import himedia.spring.ver2.repository.MemberRepository;
// import org.springframework.web.bind.annotation.RequestMethod;
@Controller
@RequestMapping("/member")
public class MemberController {
private final MemberRepository repository;
// [DI] : 의존성 주입
// 단축키 : 알트 + 쉬프트 + s + o
@Autowired
public MemberController(MemberRepository repository) {
this.repository = repository;
System.out.println("[controller] MemberController 실행됨");
}
// @RequestMapping(value = "/new", method = RequestMethod.GET)
@GetMapping("/new")
public String memberNew() {
return "member/newForm";
}
// PRG 패턴
// @RequestMapping(value = "/new", method = RequestMethod.POST)
@PostMapping("/new")
// public String postNew(@RequestParam("name") String name) {
// System.out.println("[POST] /member/new 요청 : " + name);
public String postNew(@ModelAttribute("member") Member member) {
System.out.println("[POST] /member/new 요청 : " + member);
System.out.println("[save() 호출 전]member.getName() : " + member.getName());
System.out.println("[save() 호출 전]member.getId() : " + member.getId());
Long result = repository.save(member);
System.out.println("save() 리턴값 >> " + result);
System.out.println("[save() 호출 후]member.getName() : " + member.getName());
// return "index";
return "redirect:/";
}
// // @RequestMapping(value = "/list", method = RequestMethod.GET)
// @GetMapping("/list")
// public String memberList(Model model) {
// List<Member> members = repository.findAll();
// model.addAttribute("members", members);
// return "member/memberList";
// }
@GetMapping("/list")
public ModelAndView memberList() { // 리턴 타입도 ModelAndView 로 변경
System.out.println("memberList() 실행 ! : ModelAndView");
List<Member> members = repository.findAll();
ModelAndView mv = new ModelAndView(); // model, view 관리
mv.addObject("members", members);
mv.setViewName("member/memberList"); // return 에 있던 논리뷰 이 쪽에 작성
return mv;
}
@GetMapping("/search")
public String memberSearch(@ModelAttribute("member") Member member, Model model) {
System.out.println("membersearch() 실행 ");
System.out.printf("아이디 : %s, 이름 : %s\n", member.getId(), member.getName());
Member searchMember = new Member();
if(member.getId() != null) {
searchMember = repository.findById(member.getId()).get();
} else if(!member.getName().isEmpty()) {
searchMember = repository.findByName(member.getName()).get();
}
model.addAttribute("member", searchMember);
return "member/memberSearch";
}
/* [공부용]
@RequestMapping(value = "/join", method = RequestMethod.GET)
public String join(@RequestParam("name") String name) {
System.out.println("이름 : " + name);
return "index";
}
@RequestMapping(value = "/join", method = RequestMethod.POST)
public String postJoin(@RequestParam("name") String name) {
System.out.println("[POST] /member/join 요청 : " + name);
return "index";
}
*/
}