구조
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
<!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
<?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
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";
}
*/
}
'FrameWork > Spring Framework' 카테고리의 다른 글
spring-ver1 (0) | 2024.07.26 |
---|---|
Spring FrameWork 다운로드 / 초기세팅 (0) | 2024.07.25 |