[스프링 프레임워크 입문] 과제

1. Owner 조회 시 LastName 이 아닌 FirstName 으로 조회

<!-- First name 조회란 생성 -->
<div class="control-group" id="firstNameGroup">
    <label class="col-sm-2 control-label">First name</label>
    <div class="col-sm-10">
    	<input class="form-control" th:field="*${firstName}" size="30" maxlength="80"/>
        <span class="help-inline">
        	<div th:if="${#fields.hasAnyErrors()}">
            	<p th:each="err : ${#fields.allErrors()}" th:text="${err}">Error</p>
            </div>
        </span>
    </div>
</div>

// OwnerController.java
@GetMapping("/owners")
public String processFindForm(Owner owner, BindingResult result, Map<String, Object> model) {
    // find owners result collection
    Collection<Owner> results = null;
    
    // empty result value
    String emptyVal = "";
    
    // allow parameterless GET request for /owners to return all records
    if (owner.getLastName() == null || "".equals(owner.getLastName())) {
    	owner.setLastName("");	// empty string signifies broadest possible search
        
        // find owners by firstname
        results = this.owners.findByFirstName(owner.getFirstName());
        // empty value
        emptyVal = "firstName";
    } else if (owner.getFirstName() == null || "".equals(owner.getFirstName())) {
    	owner.setFirstName("");	// empty string signifies broadest possible search
        
        // find owners by lastname
        results = this.owners.findByLastName(owner.getLastName());
        // empty value
        emptyVal = "lastName";
    }
    
    if (results.isEmpty()) {
    	// no owners found
        result.rejectValue(emptyVal, "notFound", "not found");
        return "owners/findOwners";
    } else if (results.size() == 1) {
    	// 1 owner found
        owner = results.iterator().next();
        return "redirect:/owners/" + owner.getId();
    } else {
    	// multiple owners found
        model.put("selections", results);
        return "owners/ownersList";
    }
}

// OwnerRepository.java
@Query("SELECT DISTINCT owner FROM Owner owner left join fetch owner.pets WHERE owner.firstName LIKE %:firstName%")
@Transactional(readOnly=true)
Collection<Owner> findByFirstName(@Param("firstName") String firstName);

* LastName 과 FirstName 동시 검색은 불가능

  추가할 경우 OwnerController에 Parameter 체크 로직 추가 및 OwnerRepository에 쿼리 동작 함수 추가 필요

 

2. Owner 조회 시 정확히 일치하는게 아닌 키워드 유무에 따른 조회

키워드 조회 방식은 조회 쿼리 내 LIKE 검색 시 앞,뒤 LIKE를 걸어주면 된다.

-- firstName
SELECT DISTINCT owner FROM Owner owner left join fetch owner.pets WHERE owner.firstName LIKE %:firstName%;
-- lastName
SELECT DISTINCT owner FROM Owner owner left join fetch owner.pets WHERE owner.lastName LIKE %:lastName%;

* :변수명 이 Parameter 입력값이다. 그래서 % 를 : 앞에 붙여야 한다.

 

3. Owner 객체에 Age 추가

// Owner.java
private Integer age;

public Integer getAge() {
    return age;
}

public void setAge(Integer age) {
    this.age = age;
}

// application.properties
// 사용할 DB 설정, schma.sql, data.sql 경로 설정
database=hsqldb
spring.datasource.schema=classpath*:db/${database}/schema.sql
spring.datasource.data=classpath*:db/${database}/data.sql

-- schema.sql
-- owners 테이블에 AGE 추가
CREATE TABLE owners {
    id INTEGER IDENTITY PRIMARY KEY
    , first_name VARCHAR(30)
    , last_name VARCHAR_IGNORECASE(30)
    , age INTEGER
    , address VARCHAR(255)
    , city VARCHAR(80)
    , telephone VARCHAR(20)
}

-- data.sql
-- Data Insert 시 owners 테이블 컬럼 수에 맞게 Data 컬럼값 명시
INSERT INTO owners VALUES (1, 'George', 'Franklin', 20, '110 W.', 'Madison', '6085551023');
INSERT INTO owners VALUES (2, 'Betty', 'Davis', 20, '638 Cardinal Ave.', 'Sun Prairie', '6085551749');
INSERT INTO owners VALUES (3, 'Eduardo', 'Rodriguez', 20, '2693 Commerce St.', 'McFarland', '6085558763');
INSERT INTO owners VALUES (4, 'Harold', 'Davis', 20, '563 Friendly St.', 'Windsor', '6085553198');
INSERT INTO owners VALUES (5, 'Peter', 'McTavish', 20, '2387 S. Fair Way', 'Madison', '6085552765');

<!-- createOrUpdateOwnerForm.html -->
<div class="form-group has-feedback">
    <input th:replace="~{fragments/inputField :: input ('Age', 'age', 'text')}"/>
    ...
</div>

<!-- ownerDetails.html -->
<table class="table table-striped" th:object="${owner}">
    <tr>
    	<th>Age</th>
        <td th:text="*{age}"></td>
    </tr>
    ...
</table>

<!-- ownersList.html -->
<table id="owners" class="table table-strped">
    <thead>
    	<tr>
        	<th style="width: 100px;">Age</th>
            ...
        </tr>
    </thead>
    <tbody>
    	<tr th:each="owner : ${selections}">
        	<td th:text="${owner.age}"/>
            ...
        </tr>
    </tbody>
</table>