course 2021/Spring
Spring05 - 12/08(controller09~11)
코딩하는토끼
2021. 12. 21. 05:01
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 에서 <> 는 태그를 작성하는 데 사용, 부등호로 사용하고 싶다면 위에 작성한 것처럼 작성하면 된다!