Spring06 - 12/09

Controller12

package org.zerock.controller.p05controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.zerock.controller.p05controller.bean.Bean17;
import org.zerock.controller.p05controller.bean.Bean18;
import org.zerock.mapper.p05mapper.Mapper03;

import lombok.Setter;

@Controller
@RequestMapping("/cont12")
public class Controller12 {
	
	@Setter(onMethod_ = @Autowired)
	private Mapper03 mapper;
	
	@RequestMapping("/met01")
	public void method01() {
		String customerName = "ironman";
		String contactName = "tony";
		
		int cnt = mapper.insertCustomer(customerName, contactName);
		System.out.println(cnt);
	}
	
	@RequestMapping("/met02")
	public void method02() {
		String supplierName = "captain";
		String contactName = "steve";
		
		int cnt = mapper.insertSupplier(supplierName, contactName);
		System.out.println(cnt);
		
	}
	
	@RequestMapping("/met03")
	public void method03() {
		// 2. request 분석/가공
		Bean17 bean = new Bean17();
		bean.setContactName("peter");
		bean.setCustomerName("spiderman");
		bean.setAddress("queens");
		bean.setCity("ny");
		bean.setPostalCode("2222");
		bean.setCountry("usa");
		
		// 3. business logic
		mapper.insertCustomer2(bean);
		
	}
	
	@RequestMapping("/met04")
	public void method04() {
		// 2. bean작성 (Bean18)
		Bean18 bean = new Bean18();
		bean.setAddress("seoul");
		bean.setCity("busan");
		bean.setCountry("korea");
		bean.setPostalCode("1111");
		bean.setPhone("00000");
		bean.setContactName("donald");
		bean.setSupplierName("trump");
		
		
		// 3. mapper 실행
		mapper.insertSupplier2(bean);
		
	}
	
	
	@RequestMapping("/met05")
	public void method05() {
		// 2
		Bean17 bean = new Bean17();
		bean.setAddress("gangnam");
		bean.setCity("seoul");
		bean.setContactName("marvel");
		bean.setCustomerName("danvers");
		bean.setCountry("france");
		bean.setPostalCode("99999");
		
		// 3.
		// insert 하기 전 id
		System.out.println(bean.getId()); // null or 0
		
		mapper.insertCustomer3(bean);
		
		System.out.println(bean.getId()); // key
	}
	
	@RequestMapping("/met06") 
	public void method06() {
		// 2
		Bean18 bean = new Bean18();
		bean.setAddress("yeoksam");
		bean.setCity("incheon");
		bean.setContactName("deadpool");
		bean.setSupplierName("wade");
		bean.setCountry("uk");
		bean.setPostalCode("3333");
		bean.setPhone("111");
		
		// 3
		System.out.println(bean.getSupplierID()); // null or 0
		mapper.insertSupplier3(bean);
		System.out.println(bean.getSupplierID()); // key
	}
	
	
	@RequestMapping("/met07")
	public void method07() {
		// 2
		Bean17 bean = new Bean17();
		bean.setId(117);
		bean.setContactName("widow");
		bean.setCustomerName("nat");
		bean.setAddress("jongro");
		bean.setCity("dokdo");
		bean.setCountry("korea");
		bean.setPostalCode("77777");
		
		// 3
		int cnt = mapper.updateCustomer(bean);
		System.out.println(cnt);
	}
	
	@RequestMapping("/met08")
	public void method08() {
		Bean18 bean = new Bean18();
		bean.setSupplierID(45);
		bean.setAddress("gangnam");
		bean.setCity("busan");
		bean.setContactName("wolverine");
		bean.setCountry("canada");
		bean.setPhone("0000000");
		bean.setPostalCode("99999");
		bean.setSupplierName("logan");
		
		int cnt = mapper.updateSupplier(bean);
		
		
	}
	
	@RequestMapping("/met09")
	public void method09(Integer id) {
		int cnt = mapper.deleteCustomer(id);
	}
	
	// TODO : delete Supplier by id
	@RequestMapping("/met10")
	public void method10(Integer id) {
		int cnt = mapper.deleteSupplier(id);
	}
}

Mapper03.java

package org.zerock.mapper.p05mapper;

import org.apache.ibatis.annotations.Param;
import org.zerock.controller.p05controller.bean.Bean17;
import org.zerock.controller.p05controller.bean.Bean18;

public interface Mapper03 {

	public int insertCustomer(@Param("customerName") String customerName,
			@Param("contactName") String contactName);

	public int insertSupplier(@Param("supplierName") String supplierName, 
			@Param("contactName") String contactName);
	
	public int insertCustomer2(Bean17 bean);

	public int insertSupplier2(Bean18 bean);

	public int insertCustomer3(Bean17 bean);

	public int insertSupplier3(Bean18 bean);
	
	public int updateCustomer(Bean17 bean);

	public int updateSupplier(Bean18 bean);
	
	public int deleteCustomer(Integer id);

	public int deleteSupplier(Integer id);
}

Mapper03.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="org.zerock.mapper.p05mapper.Mapper03">

	<insert id="insertCustomer">
	INSERT INTO Customers (CustomerName, ContactName)
	VALUES (#{customerName}, #{contactName})
	</insert>
	
	<insert id="insertSupplier">
	INSERT INTO Suppliers (SupplierName, ContactName)
	VALUES (#{supplierName}, #{contactName})
	</insert>
	
	<insert id="insertCustomer2">
	INSERT INTO Customers (CustomerName, ContactName, Address, PostalCode, Country, City)
	VALUES (#{customerName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city})
	</insert>
	
	<insert id="insertSupplier2">
	INSERT INTO Suppliers (SupplierName, ContactName, Address, PostalCode, Country, City, Phone)
	VALUES (#{supplierName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city}, #{phone})
	</insert>
	
	<insert id="insertCustomer3" useGeneratedKeys="true" keyProperty="id" keyColumn="customerID">
	INSERT INTO Customers (CustomerName, ContactName, Address, PostalCode, Country, City)
	VALUES (#{customerName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city})
	</insert>
	
	<insert id="insertSupplier3" useGeneratedKeys="true" keyProperty="supplierID" keyColumn="supplierID">
	INSERT INTO Suppliers (SupplierName, ContactName, Address, PostalCode, Country, City, Phone)
	VALUES (#{supplierName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city}, #{phone})
	</insert>
	
	<update id="updateCustomer">
	UPDATE Customers
	SET
	   CustomerName = #{customerName},
	   ContactName = #{contactName},
	   Address = #{address},
	   PostalCode = #{postalCode},
	   Country = #{country},
	   City = #{city}
	WHERE
	   CustomerID = #{id}
	</update>
	
	<update id="updateSupplier">
	UPDATE Suppliers
	SET
		SupplierName = #{supplierName},
		ContactName = #{contactName},
		Address = #{address},
		City = #{city},
		PostalCode = #{postalCode},
		Country = #{country},
		Phone = #{phone}
	WHERE
		SupplierID = #{supplierID}
	</update>
	
	<delete id="deleteCustomer">
	DELETE FROM Customers WHERE CustomerId = #{id}
	</delete>
	
	<delete id="deleteSupplier">
	DELETE FROM Suppliers WHERE SupplierId = #{id}
	</delete>
	
</mapper>

 

method01

	@RequestMapping("/met01")
	public void method01() {
		String customerName = "ironman";
		String contactName = "tony";
		
		int cnt = mapper.insertCustomer(customerName, contactName);
		System.out.println(cnt);
	}

	public int insertCustomer(@Param("customerName") String customerName,
			@Param("contactName") String contactName);

	<insert id="insertCustomer">
	INSERT INTO Customers (CustomerName, ContactName)
	VALUES (#{customerName}, #{contactName})
	</insert>

이번에는 SELECT 아닌 INSERT

mapper 메소드 insertCustomer 에서 매개변수 두개 (customerName, contactName) 받아 insert, 리턴타입은 int?

→ executeUpdate() : INSERT, UPDATE, DELETE 된 row 수 count

mapper.java 에서 매개변수 앞에 annotation @Param("") 작성

mapper.xml 에서 select 아닌 insert 태그 작성, id 는 메소드명

요청 : cont12/met01

 

method02

	@RequestMapping("/met02")
	public void method02() {
		String supplierName = "captain";
		String contactName = "steve";
		
		int cnt = mapper.insertSupplier(supplierName, contactName);
		System.out.println(cnt);
		
	}

	public int insertSupplier(@Param("supplierName") String supplierName, 
			@Param("contactName") String contactName);

	<insert id="insertSupplier">
	INSERT INTO Suppliers (SupplierName, ContactName)
	VALUES (#{supplierName}, #{contactName})
	</insert>

01 과 동일

---

method03

	@RequestMapping("/met03")
	public void method03() {
		// 2. request 분석/가공
		Bean17 bean = new Bean17();
		bean.setContactName("peter");
		bean.setCustomerName("spiderman");
		bean.setAddress("queens");
		bean.setCity("ny");
		bean.setPostalCode("2222");
		bean.setCountry("usa");
		
		// 3. business logic
		mapper.insertCustomer2(bean);
		
	}

	public int insertCustomer2(Bean17 bean);

	<insert id="insertCustomer2">
	INSERT INTO Customers (CustomerName, ContactName, Address, PostalCode, Country, City)
	VALUES (#{customerName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city})
	</insert>

@Data
public class Bean17 {
	private Integer id;
	private String customerName;
	private String contactName;
	private String city;
	private String address;
	private String postalCode;
	private String country;
}

bean 이용하여 insert

method03 에서 Bean17 객체 bean 생성, setter 메소드로 값을 저장, 전달

이번에는 cnt 출력하지 않고, 그냥 mapper.insertCustomer2(bean) 실행

 

method04

	@RequestMapping("/met04")
	public void method04() {
		// 2. bean작성 (Bean18)
		Bean18 bean = new Bean18();
		bean.setAddress("seoul");
		bean.setCity("busan");
		bean.setCountry("korea");
		bean.setPostalCode("1111");
		bean.setPhone("00000");
		bean.setContactName("donald");
		bean.setSupplierName("trump");
		
		
		// 3. mapper 실행
		mapper.insertSupplier2(bean);
		
	}

	public int insertSupplier2(Bean18 bean);

	<insert id="insertSupplier2">
	INSERT INTO Suppliers (SupplierName, ContactName, Address, PostalCode, Country, City, Phone)
	VALUES (#{supplierName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city}, #{phone})
	</insert>

@Data
public class Bean18 {
	private Integer supplierID;
	private String supplierName;
	private String contactName;
	private String address;
	private String city;
	private String country;
	private String postalCode;
	private String phone;
}

03 과 동일

---

insert 하자마자 key 를 얻는 방법을 알아보자 (두 가지를 동시에 넣는 경우, insert 하고 select 하면 다른 것을 얻을 수도 있다)

method05

	@RequestMapping("/met05")
	public void method05() {
		// 2
		Bean17 bean = new Bean17();
		bean.setAddress("gangnam");
		bean.setCity("seoul");
		bean.setContactName("marvel");
		bean.setCustomerName("danvers");
		bean.setCountry("france");
		bean.setPostalCode("99999");
		
		// 3.
		// insert 하기 전 id
		System.out.println(bean.getId()); // null or 0
		
		mapper.insertCustomer3(bean);
		
		System.out.println(bean.getId()); // key
	}

	public int insertCustomer3(Bean17 bean);

	<insert id="insertCustomer3" useGeneratedKeys="true" keyProperty="id" keyColumn="customerID">
	INSERT INTO Customers (CustomerName, ContactName, Address, PostalCode, Country, City)
	VALUES (#{customerName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city})
	</insert>

@Data
public class Bean17 {
	private Integer id;
	private String customerName;
	private String contactName;
	private String city;
	private String address;
	private String postalCode;
	private String country;
}

System.out.println(bean.getId()); - 내가 넣은 row 의 id (key) 를 알 수 있음

요청 : cont12/met05

 

method06

	@RequestMapping("/met06") 
	public void method06() {
		// 2
		Bean18 bean = new Bean18();
		bean.setAddress("yeoksam");
		bean.setCity("incheon");
		bean.setContactName("deadpool");
		bean.setSupplierName("wade");
		bean.setCountry("uk");
		bean.setPostalCode("3333");
		bean.setPhone("111");
		
		// 3
		System.out.println(bean.getSupplierID()); // null or 0
		mapper.insertSupplier3(bean);
		System.out.println(bean.getSupplierID()); // key
	}

	public int insertSupplier3(Bean18 bean);

	<insert id="insertSupplier3" useGeneratedKeys="true" keyProperty="supplierID" keyColumn="supplierID">
	INSERT INTO Suppliers (SupplierName, ContactName, Address, PostalCode, Country, City, Phone)
	VALUES (#{supplierName}, #{contactName}, #{address}, #{postalCode}, #{country}, #{city}, #{phone})
	</insert>

@Data
public class Bean18 {
	private Integer supplierID;
	private String supplierName;
	private String contactName;
	private String address;
	private String city;
	private String country;
	private String postalCode;
	private String phone;
}

05 와 동일

---

method07

	@RequestMapping("/met07")
	public void method07() {
		// 2
		Bean17 bean = new Bean17();
		bean.setId(94);
		bean.setContactName("widow");
		bean.setCustomerName("nat");
		bean.setAddress("jongro");
		bean.setCity("dokdo");
		bean.setCountry("korea");
		bean.setPostalCode("77777");
		
		// 3
		int cnt = mapper.updateCustomer(bean);
		System.out.println(cnt);
	}

	public int updateCustomer(Bean17 bean);

	<update id="updateCustomer">
	UPDATE Customers
	SET
	   CustomerName = #{customerName},
	   ContactName = #{contactName},
	   Address = #{address},
	   PostalCode = #{postalCode},
	   Country = #{country},
	   City = #{city}
	WHERE
	   CustomerID = #{id}
	</update>

@Data
public class Bean17 {
	private Integer id;
	private String customerName;
	private String contactName;
	private String city;
	private String address;
	private String postalCode;
	private String country;
}

이번에는 UPDATE

bean 으로 저장하여 전달하고, cnt 출력함

mapper.xml 에 update 태그 작성

요청 : cont12/met07

 

method08

	@RequestMapping("/met08")
	public void method08() {
		Bean18 bean = new Bean18();
		bean.setSupplierID(45);
		bean.setAddress("gangnam");
		bean.setCity("busan");
		bean.setContactName("wolverine");
		bean.setCountry("canada");
		bean.setPhone("0000000");
		bean.setPostalCode("99999");
		bean.setSupplierName("logan");
		
		int cnt = mapper.updateSupplier(bean);
		
		
	}

	public int updateSupplier(Bean18 bean);

	<update id="updateSupplier">
	UPDATE Suppliers
	SET
		SupplierName = #{supplierName},
		ContactName = #{contactName},
		Address = #{address},
		City = #{city},
		PostalCode = #{postalCode},
		Country = #{country},
		Phone = #{phone}
	WHERE
		SupplierID = #{supplierID}
	</update>

@Data
public class Bean18 {
	private Integer supplierID;
	private String supplierName;
	private String contactName;
	private String address;
	private String city;
	private String country;
	private String postalCode;
	private String phone;
}

07 과 동일

---

method09

	@RequestMapping("/met09")
	public void method09(Integer id) {
		int cnt = mapper.deleteCustomer(id);
	}

	public int deleteCustomer(Integer id);

	<delete id="deleteCustomer">
	DELETE FROM Customers WHERE CustomerId = #{id}
	</delete>

이번에는 DELETE

cnt

id 를 쿼리스트링으로 전달하여 해당 id 의 row 삭제 

mapper.xml 에 delete 태그 작성

요청 : cont12/met09?id=96

 

method10

	// TODO : delete Supplier by id
	@RequestMapping("/met10")
	public void method10(Integer id) {
		int cnt = mapper.deleteSupplier(id);
	}

	public int deleteSupplier(Integer id);

	<delete id="deleteSupplier">
	DELETE FROM Suppliers WHERE SupplierId = #{id}
	</delete>

09 와 동일

---

< project >

164~267p

1. 테이블 만들기

173p (교재는 오라클 기준이라서 다름)

mysql

2. 이클립스

org.zerock.controller.project1

org.zerock.domain.project1

org.zerock.mapper.project1

org.zerock.service.project1

즉 controller, domain, mapper, service 네 개의 패키지 생성

domain 패키지 우클릭 - new - class 생성 (name : BoardVO)

VO : Value Object

BoardVO

package org.zerock.domain.project1;

import java.time.LocalDateTime;
import java.time.ZoneId;

import lombok.Data;

@Data
public class BoardVO {
	private Integer id;
	private String title;
	private String content;
	private String writer;
	private LocalDateTime inserted;
	private LocalDateTime updated;
	private String nickName;
	
}

mapper 패키지 우클릭 - new - interface 생성 (name : BoardMapper)

mapper 패키지 우클릭 - new - file 생성 (name : BoardMapper.xml)

BoardMapper.java

package org.zerock.mapper.project1;

import java.util.List;

import org.zerock.domain.project1.BoardVO;

public interface BoardMapper {
	
	// 모든 게시물 조회
	public List<BoardVO> getList(); 
	
	// 새 게시물 입력 & get generated key
	public int insert(BoardVO board); 
	
	// id(pk)로 하나의 게시물 조회
	public BoardVO read(Integer id); 
	
	// id(pk)로 하나의 게시물 삭제
	public int delete(Integer id);
	
	// 하나의 게시물 수정
	public int update(BoardVO board);
	
}

BoardMapper.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="org.zerock.mapper.project1.BoardMapper">

	<select id="getList"
		resultType="org.zerock.domain.project1.BoardVO">
		SELECT
		id, title, content, writer, inserted, updated
		FROM
		Board
		ORDER BY id DESC
	</select>
	<select id="read"
		resultType="org.zerock.domain.project1.BoardVO">
		SELECT
		id, title, content, writer, inserted, updated
		FROM
		Board
		WHERE id = #{id}
	</select>

	<insert id="insert" useGeneratedKeys="true" keyColumn="id"
		keyProperty="id">
		INSERT INTO
		Board (title, content, writer)
		VALUES
		(#{title}, #{content}, #{writer})

	</insert>

	<update id="update">
		UPDATE
		Board
		SET
		title = #{title}, content = #{content},
		writer = #{writer}, updated = NOW()
		WHERE id = #{id}
	</update>

	<delete id="delete">
		DELETE FROM Board WHERE id = #{id}
	</delete>
</mapper>

 

> 테스트 해보기

src/test/java 폴더에 새패키지 생성(BoardMapper.java 파일 패키지명 복붙)

패키지 우클릭 - new - JUnit Test Case 생성 (name: BoardMapperTest)

* 실행해보면 다음과 같이 fail ↓

 

> pom.xml 수정

maven repository 에서  spring test 검색하여 spring testcontext framework 

https://mvnrepository.com/artifact/org.springframework/spring-test

버전무관하게 코드 복사하여 pom.xml 에 붙여넣기

 

> root-context.xml 수정

/WEB-INF/db.properties → classpath:db.properties

근데 그러면 서버 실행 안됨

 

BoardMapperTest

package org.zerock.mapper.project1;

import static org.junit.Assert.*;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.zerock.domain.project1.BoardVO;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")

public class BoardMapperTest {
	
	@Autowired
	public BoardMapper mapper;

	@Test
	public void mapperTest() {
		assertNotNull(mapper);
	}
	
	@Test
	public void insertTest() { 
		BoardVO vo = new BoardVO();
		vo.setTitle("test 용 제목");
		vo.setContent("test 용 본문");
		vo.setWriter("tester");

		int cnt = mapper.insert(vo);

		assertEquals(1, cnt);
	}

}

@Test annotation 붙여서 테스트 해볼 수 있다

 

테스트 결과 확인