Spring05 - 12/08(controller09~11)

TimeMapper (전체)

package org.zerock.mapper.p05mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;
import org.zerock.controller.p05controller.bean.Bean09;
import org.zerock.controller.p05controller.bean.Bean10;
import org.zerock.controller.p05controller.bean.Bean11;
import org.zerock.controller.p05controller.bean.Bean12;
import org.zerock.controller.p05controller.bean.Bean13;
import org.zerock.controller.p05controller.bean.Bean14;
import org.zerock.controller.p05controller.bean.Bean15;

public interface TimeMapper {

	@Select("SELECT NOW()")
	public String getTime();

	@Select("SELECT CustomerName FROM Customers WHERE CustomerID = 1")
	public String getCustomerName();

	@Select("SELECT LastName FROM Employees WHERE EmployeeID = 1")
	public String getLastName();

	@Select("SELECT CustomerName FROM Customers WHERE CustomerID = #{id}")
	public String getCustomerNameById(Integer id);

	@Select("SELECT LastName FROM Employees WHERE EmployeeID = #{id}")
	public String getLastNameById(Integer id);

	@Select("SELECT CustomerName FROM Customers")
	public List<String> getCustomerNames();

	// 직원 테이블의 LastName 들을 조회하는 쿼리를 실행하는 메소드(getLastNames) 작성
	@Select("SELECT LastName FROM Employees")
	public List<String> getLastNames();

	@Select("SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = 1")
	public Bean09 getEmployeeName();

	@Select("SELECT customerName, contactName FROM Customers WHERE CustomerID = 1")
	public Bean10 getName();

	@Select("SELECT customerID AS id, customerName FROM Customers WHERE CustomerID = 1")
	public Bean11 getCustomerInfo();

	@Select("SELECT EmployeeID id, LastName lname, FirstName fname FROM Employees WHERE EmployeeID = 2")
	public Bean12 getEmployeeInfo();

	@Select("SELECT EmployeeID, LastName, FirstName, BirthDate, Photo, Notes FROM Employees WHERE EmployeeID = #{id}")
	public Bean13 getEmployeeById(Integer id);

	@Select("SELECT CustomerID, CustomerName, ContactName,"
			+ " City, Address, Country, PostalCode "
			+ "FROM Customers "
			+ "WHERE CustomerID = #{id}")
	public Bean14 getCustomerById(Integer id);

	@Select("SELECT CustomerID, CustomerName, ContactName,"
			+ " City, Address, Country, PostalCode "
			+ "FROM Customers")
	public List<Bean14> getCustomers();

	@Select("SELECT EmployeeID, LastName, FirstName, BirthDate, Photo, Notes FROM Employees")
	public List<Bean13> getEmployees();
	
	@Select("SELECT p.ProductName, c.CategoryName, p.Unit, p.Price " + 
			"FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID")
	public List<Bean15> getProductsInfo();
	
	public List<Bean15> getProductsInfo2();
}

SELECT CustomerName FROM Customers 와 같은 코드는 List<String>

SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = 1 와 같은 코드는 Bean

SELECT EmployeeID, LastName, FirstName, BirthDate, Photo, Notes FROM Employees 와 같은 코드는 List<Bean>

 

Controller09

method09

	@RequestMapping("/met09")
	public void method09() {
		Bean09 name = mapper.getEmployeeName();
		System.out.println(name);
	}

Bean09 객체 name 에 mapper.getEmployeeName() 에서 반환한 Bean09 를 저장, 출력

 

controller.p05controller.bean

Bean09

package org.zerock.controller.p05controller.bean;

import lombok.Data;

@Data
public class Bean09 {

	private Integer employeeID;
	private String lastName;
	private String firstName;
}

 

요청: cont09/met09

 

method10

연습

	@RequestMapping("/met10")
	public void method10() {
		Bean10 name = mapper.getName();
		System.out.println(name); // 1번 고객의 CustomerName, ContactName이 출력되도록 코드 작성
	}

Bean10

@Data
public class Bean10 {

	private String customerName;
	private String contactName;
}

 

method11

	@RequestMapping("/met11")
	public void method11() {
		System.out.println(mapper.getCustomerInfo());
	}

두줄로 작성하던거 한줄로 작성

Bean11

@Data
public class Bean11 {
	private Integer id;
	private String customerName;
}

mapper 에서 customerID AS id 로 작성함, 그 외에는 위 09, 10 과 동일

 

method12

	@RequestMapping("/met12")
	public void method12() {
		System.out.println(mapper.getEmployeeInfo()); // id, last name, first name 출력
	}

Bean12

@Data
public class Bean12 {
	private Integer id;
	private String lname; // last name
	private String fname; // first name
}

---

method13

	@RequestMapping("/met13")
	public void method13(Integer id) {
		System.out.println(mapper.getEmployeeById(id));
	}

id 를 받아서 mapper.getEmployeeById 메소드에 전달해야 함

Bean13

@Data
public class Bean13 {
	private Integer employeeID;
	private String lastName;
	private String firstName;
	private String photo;
	private String notes;
	private LocalDate birthDate;
}

요청 : cont09/met13?id=3

 

 

method14

	@RequestMapping("/met14")
	public void method14(Integer id) {
		//      mapper에 getCustomerById 메소드 (Bean14 리턴) 만들기
		//      Bean14빈의 property가 Customers 테이블의 컬럼과 매치되도록 작성
		System.out.println(mapper.getCustomerById(id));
	}

 

Bean14

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

---

method15

	@RequestMapping("/met15")
	public void method15() {
		List<Bean14> list = mapper.getCustomers();
		
		for (Bean14 item : list) {
			System.out.println(item);
		}
	}

List<Bean>

list 에 메소드에서 반환된 리스트 저장

향상된 for 문을 이용해 리스트에서 값을 꺼내어 출력

 

Bean14

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

 

요청 : cont09/met15

 

method16

	@RequestMapping("/met16")
	public void method16() {
		List<Bean13> list = mapper.getEmployees();
		
		for (Bean13 item : list) {
			System.out.println(item);
		}
	}

method17

	@RequestMapping("/met17")
	public void method17() {
		List<Bean15> list = mapper.getProductsInfo();
		
		for (Bean15 item : list) {
			System.out.println(item);
		}
	}

 

Bean15

@Data
public class Bean15 {
	private String productName;
	private String categoryName;
	private String unit;
	private Double price;
}

---

mapper 패키지 안에 new - file 만들기 - name: TimeMapper.xml

 

https://mybatis.org/mybatis-3/ko/getting-started.html

해당 코드 복사하여 TimeMapper.xml 에 붙여넣기

TimeMapper.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.TimeMapper">
	<!-- namespace 속성 : 인터페이스명 -->
	<select id="getProductsInfo2"
		resultType="org.zerock.controller.p05controller.bean.Bean15">
		<!-- id 속성 : 메소드명 -->
		<!-- resultType 속성 : 컬럼이 매핑될 빈 이름 -->
		SELECT
		p.ProductName, c.CategoryName, p.Unit, p.Price
		FROM
		Products p
		JOIN
		Categories c ON p.CategoryID = c.CategoryID
	</select>
</mapper>

<mapper namespace-="인터페이스 경로">

<select id="메소드명" resultType="bean 경로">

코드

</select>

</mapper>

---

method18

	@RequestMapping("/met18")
	public void method18() {
		List<Bean15> list = mapper.getProductsInfo2();
		
		for (Bean15 item : list) {
			System.out.println(item);
		}
	}

인터페이스 TimeMapper 에서 annotation 을 쓰지 않고 xml 에 작성

인터페이스와 이름은 같고 확장자는 xml

(복잡하면 xml, 간단하면 annotation 으로 작성하는 게 좋다)

요청 : cont09/met18

---

Controller10

package org.zerock.controller.p05controller;

import java.util.List;

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.Bean09;
import org.zerock.controller.p05controller.bean.Bean16;
import org.zerock.mapper.p05mapper.Mapper01;

import lombok.Setter;

@Controller
@RequestMapping("/cont10")
public class Controller10 {

	@Setter(onMethod_ = @Autowired)
	private Mapper01 mapper;

	@RequestMapping("/met01")
	public void method01() {
		System.out.println(mapper.getSupplierOne());
	}

	@RequestMapping("/met02")
	public void method02() {
		// SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 3
		// Mapper01.java에 getEmployeeNameOne 메소드 작성
		// 위 메소드 리턴 타입 Bean09 사용
		// Mapper01.xml 에 새 <select> 요소 작성

		// 이 메소드에서 getEmployeeNameOne 메소드 실행 결과(Bean09) 출력

		Bean09 bean = mapper.getEmployeeNameOne();
		System.out.println(bean);
	}

	@RequestMapping("/met03")
	public void method03() {
		List<Bean09> list = mapper.getEmployeeNameList();

//		list.forEach(n -> System.out.println(n));

		for (Bean09 n : list) {
			System.out.println(n);
		}
	}

	@RequestMapping("/met04")
	public void method04() {

//		mapper.getSupplierList().forEach(s -> System.out.println(s));

		List<Bean16> list = mapper.getSupplierList();

		for (Bean16 s : list) {
			System.out.println(s);
		}
	}
	
	@RequestMapping("/met05")
	public void method05() {
		System.out.println(mapper.getEmployeeLastNameOne());
	}
	
	@RequestMapping("/met06")
	public void method06() {
		System.out.println(mapper.getEmployeeFirstNameOne());// 
	}
	
	@RequestMapping("/met07")
	public void method07() {
		List<String> list = mapper.getEmployeeLastNameList();
		
		for (String lname : list) {
			System.out.println(lname);
		}
	}
}

Mapper01.java

package org.zerock.mapper.p05mapper;

import java.util.List;

import org.zerock.controller.p05controller.bean.Bean09;
import org.zerock.controller.p05controller.bean.Bean16;

public interface Mapper01 {

	public Bean16 getSupplierOne();
	
	public Bean09 getEmployeeNameOne();
	
	public List<Bean09> getEmployeeNameList();
	
	public List<Bean16> getSupplierList();
	
	public String getEmployeeLastNameOne();

	public String getEmployeeFirstNameOne();

	public List<String> getEmployeeLastNameList();
}

Mapper01.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.Mapper01">
	<!-- namespace 속성 : 인터페이스명 -->

	<select id="getSupplierOne" 
		resultType="org.zerock.controller.p05controller.bean.Bean16">
	SELECT
	       SupplierName,
	       ContactName
	FROM
	       Suppliers
	WHERE
	       SupplierID = 10
	</select>
	
	<select id="getEmployeeNameOne" 
		resultType="org.zerock.controller.p05controller.bean.Bean09">
	SELECT 
	      LastName, 
	      FirstName 
	FROM 
	      Employees 
	WHERE 
	      EmployeeID = 3
	</select>
	
	<select id="getEmployeeNameList" 
		resultType="org.zerock.controller.p05controller.bean.Bean09">
	SELECT 
		LastName,
		FirstName
	FROM
		Employees
	</select>
	
	<select id="getSupplierList"
		resultType="org.zerock.controller.p05controller.bean.Bean16">
	SELECT
	       SupplierName,
	       ContactName
	FROM
	       Suppliers
	</select>
	
	<select id="getEmployeeLastNameOne"
		resultType="string">
	SELECT
		LastName
	FROM
		Employees
	WHERE
		EmployeeID = 1
	</select>
	
	<select id="getEmployeeFirstNameOne" resultType="string">
	SELECT FirstName 
	FROM Employees 
	WHERE EmployeeID = 1
	</select>
	
	<select id="getEmployeeLastNameList" resultType="string">
	SELECT LastName
	FROM Employees
	</select>
</mapper>

 

method01

	@RequestMapping("/met01")
	public void method01() {
		System.out.println(mapper.getSupplierOne());
	}

Mapper01

	public Bean16 getSupplierOne();

	<select id="getSupplierOne" 
		resultType="org.zerock.controller.p05controller.bean.Bean16">
	SELECT
	       SupplierName,
	       ContactName
	FROM
	       Suppliers
	WHERE
	       SupplierID = 10
	</select>

 

Bean16

@Data
public class Bean16 {
	private String supplierName;
	private String contactName;
}

요청: cont10/met01

 

method02

	@RequestMapping("/met02")
	public void method02() {
		// SELECT LastName, FirstName FROM Employees WHERE EmployeeID = 3
		// Mapper01.java에 getEmployeeNameOne 메소드 작성
		// 위 메소드 리턴 타입 Bean09 사용
		// Mapper01.xml 에 새 <select> 요소 작성

		// 이 메소드에서 getEmployeeNameOne 메소드 실행 결과(Bean09) 출력

		Bean09 bean = mapper.getEmployeeNameOne();
		System.out.println(bean);
	}

 

Mapper01

	public Bean09 getEmployeeNameOne();

	<select id="getEmployeeNameOne" 
		resultType="org.zerock.controller.p05controller.bean.Bean09">
	SELECT 
	      LastName, 
	      FirstName 
	FROM 
	      Employees 
	WHERE 
	      EmployeeID = 3
	</select>

 

Bean09

@Data
public class Bean09 {

	private Integer employeeID;
	private String lastName;
	private String firstName;
}

---

method03

	@RequestMapping("/met03")
	public void method03() {
		List<Bean09> list = mapper.getEmployeeNameList();

//		list.forEach(n -> System.out.println(n));

		for (Bean09 n : list) {
			System.out.println(n);
		}
	}

List<Bean>, 향상된 for 문

Mapper01

	public List<Bean09> getEmployeeNameList();

	<select id="getEmployeeNameList" 
		resultType="org.zerock.controller.p05controller.bean.Bean09">
	SELECT 
		LastName,
		FirstName
	FROM
		Employees
	</select>

 

method04

	@RequestMapping("/met04")
	public void method04() {

//		mapper.getSupplierList().forEach(s -> System.out.println(s));

		List<Bean16> list = mapper.getSupplierList();

		for (Bean16 s : list) {
			System.out.println(s);
		}
	}

Mapper01

	public List<Bean16> getSupplierList();

	<select id="getSupplierList"
		resultType="org.zerock.controller.p05controller.bean.Bean16">
	SELECT
	       SupplierName,
	       ContactName
	FROM
	       Suppliers
	</select>

Bean16

@Data
public class Bean16 {
	private String supplierName;
	private String contactName;
}

---

method05

	@RequestMapping("/met05")
	public void method05() {
		System.out.println(mapper.getEmployeeLastNameOne());
	}

Mapper01

	public String getEmployeeLastNameOne();

	<select id="getEmployeeLastNameOne"
		resultType="string">
	SELECT
		LastName
	FROM
		Employees
	WHERE
		EmployeeID = 1
	</select>

String 을 string 으로 쓸 수 있음 ↓

https://mybatis.org/mybatis-3/ko/configuration.html#typeAliases

요청 : cont10/met05

 

method06

	@RequestMapping("/met06")
	public void method06() {
		System.out.println(mapper.getEmployeeFirstNameOne());
	}

Mapper01

	public String getEmployeeFirstNameOne();

	<select id="getEmployeeFirstNameOne" resultType="string">
	SELECT FirstName 
	FROM Employees 
	WHERE EmployeeID = 1
	</select>

---

method07

	@RequestMapping("/met07")
	public void method07() {
		List<String> list = mapper.getEmployeeLastNameList();
		
		for (String lname : list) {
			System.out.println(lname);
		}

Mapper01

	public List<String> getEmployeeLastNameList();

	<select id="getEmployeeLastNameList" resultType="string">
	SELECT LastName
	FROM Employees
	</select>

요청 : cont10/met07

---

Controller11

package org.zerock.controller.p05controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.zerock.mapper.p05mapper.Mapper02;

import lombok.Setter;

@Controller
@RequestMapping("/cont11")
public class Controller11 {

	@Setter(onMethod_ = @Autowired)
	private Mapper02 mapper;
	
	@RequestMapping("/met01")
	public void method01(Integer id) {
		String name = mapper.getLastNameById(id);
		System.out.println(name);
	}
	
	@RequestMapping("/met02")
	public void method02(Integer id) {
		String customerName = mapper.getCustomerNameById(id);
		
		System.out.println(customerName);
	}
	
	// /cont11/met03?category=condiments
	@RequestMapping("/met03")
	public void method03(String category) {
		List<String> productNames = mapper.getProductNamesByCategory(category);
		
		for (String p : productNames) {
			System.out.println(p);
		}
	}
	
	// /cont11/met04?category=Beverages&price=10 
	@RequestMapping("/met04")
	public void method04(String category, Double price) {
		List<String> productNames = mapper.getProductNamesByCategoryAndPrice(category, price);
		
		for (String p : productNames) {
			System.out.println(p);
		}
	}
	
	
	// /cont11/met05?city=Berlin&country=Germany
	@RequestMapping("/met05")
	public void method05(String city, String country) {
		List<String> supplierNames = mapper.getSupplierNamesByCityAndCountry(city, country);
		
		for (String s : supplierNames) {
			System.out.println(s);
		}
	}
}

Mapper02.java

package org.zerock.mapper.p05mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

public interface Mapper02 {

	String getLastNameById(Integer id);

	String getCustomerNameById(Integer id);

	List<String> getProductNamesByCategory(String category);

	List<String> getProductNamesByCategoryAndPrice(@Param("category") String category, @Param("price") Double price);

	List<String> getSupplierNamesByCityAndCountry(@Param("city") String city, @Param("country") String country);

}

Mapper02.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.Mapper02">
	<select id="getLastNameById" resultType="string">
	SELECT LastName
	FROM Employees
	WHERE EmployeeID = #{id}
	</select>
	
	<select id="getCustomerNameById" resultType="string">
	SELECT customerName
	FROM Customers
	WHERE customerID = #{id}
	</select>
	
	<select id="getProductNamesByCategory" resultType="string">
	SELECT p.ProductName 
	FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
	WHERE c.CategoryName = #{category}
	</select>
	
	<select id="getProductNamesByCategoryAndPrice" resultType="string">
	<![CDATA[      
	SELECT p.ProductName 
	FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
	WHERE c.CategoryName = #{category} AND p.price < #{price}
	]]>
	</select>
	
	<select id="getSupplierNamesByCityAndCountry" resultType="string">
	SELECT SupplierName
	FROM Suppliers
	WHERE City = #{city} AND Country = #{country}
	</select>
</mapper>

 

method01

	@RequestMapping("/met01")
	public void method01(Integer id) {
		String name = mapper.getLastNameById(id);
		System.out.println(name);
	}

Mapper02

	String getLastNameById(Integer id);

	<select id="getLastNameById" resultType="string">
	SELECT LastName
	FROM Employees
	WHERE EmployeeID = #{id}
	</select>

id 쿼리 스트링으로 전달

요청 : cont11/met01?id=3

 

method02

	@RequestMapping("/met02")
	public void method02(Integer id) {
		String customerName = mapper.getCustomerNameById(id);
		
		System.out.println(customerName);
	}

Mapper02

	String getCustomerNameById(Integer id);

	<select id="getCustomerNameById" resultType="string">
	SELECT customerName
	FROM Customers
	WHERE customerID = #{id}
	</select>

---

method03

	// /cont11/met03?category=condiments
	@RequestMapping("/met03")
	public void method03(String category) {
		List<String> productNames = mapper.getProductNamesByCategory(category);
		
		for (String p : productNames) {
			System.out.println(p);
		}
	}

Mapper02

	List<String> getProductNamesByCategory(String category);

	<select id="getProductNamesByCategory" resultType="string">
	SELECT p.ProductName 
	FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
	WHERE c.CategoryName = #{category}
	</select>

category 쿼리 스트링으로 전달

요청 : cont11/met03?category=condiments

---

method04

	// /cont11/met04?category=Beverages&price=10 
	@RequestMapping("/met04")
	public void method04(String category, Double price) {
		List<String> productNames = mapper.getProductNamesByCategoryAndPrice(category, price);
		
		for (String p : productNames) {
			System.out.println(p);
		}
	}

Mapper02

	List<String> getProductNamesByCategoryAndPrice(@Param("category") String category, @Param("price") Double price);

	<select id="getProductNamesByCategoryAndPrice" resultType="string">
	<![CDATA[      
	SELECT p.ProductName 
	FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
	WHERE c.CategoryName = #{category} AND p.price < #{price}
	]]>
	</select>

으아악 이게 뭐야

 

method04, method05

Mapper02

 

method04 는 category, price, method05 는 city, country 를 전달받아 쿼리 완성

mapper.java 에서 @Param("category") 와 같은 어노테이션 작성

파라미터가 2개 이상인 경우!!

 

mapper.xml 에서 <![CDATA[   ]]> ?

→ <![CDATA[   ]]> 안에 작성된 것들은 모두 텍스트로 취급됨

.xml 에서 <> 는 태그를 작성하는 데 사용, 부등호로 사용하고 싶다면 위에 작성한 것처럼 작성하면 된다!