C.LAST_NAME,
C.BIRTH_DATE,
T.ID AS CONTACT_TEL_ID,
T.TEL_TYPE,
T.TEL_NUMBER,
H.HOBBY_ID
FROM CONTACT C
LEFT OUTER JOIN CONTACT_TEL_DETAIL T ON C.ID = T.CONTACT_ID
LEFT OUTER JOIN CONTACT_HOBBY_DETAIL H ON C.ID = H.CONTACT_ID
<where>
<if test="firstName != null">
first_name = #{firstName}
</if>
<if test="lastName != null">
AND last_name = #{lastName}
</if>
</where>
</select>
From Listing 11-29, the magic happens in the <where> tag, which will be handled by MyBatis in
composing the dynamic SQL intelligently. For example, it will first test whether the firstName attribute
in the SearchCriteria being passed in is null. If not, it will add the clause WHERE FIRST_NAME = and the
attribute into the query accordingly. The same thing happens for the attribute lastName. One fancy thing
is that if you do not provide the first name, MyBatis will intelligent remove the AND operator from the
WHERE clause using the last name. If both attributes are null, MyBatis will simply drop the WHERE clause
entirely. Listing 11-30 shows the implementation of the findByFirstNameAndLastName() method in the
ContactServiceImpl class.
Listing 11-30. Implementing the findByFirstNameAndLastName() Method
package com.apress.prospring3.ch11.service.mybatis;
// Import statements omitted
@Service("contactService")
@Repository
@Transactional
public class ContactServiceImpl implements ContactService {
// Other code omitted
@Transactional(readOnly=true)
public List<Contact> findByFirstNameAndLastName(String firstName,
String lastName) {
log.info("Finding contact with first name: " + firstName
+ " and last name: " + lastName);
Contact contact = new Contact();
contact.setFirstName(firstName);
contact.setLastName(lastName);
SearchCriteria criteria = new SearchCriteria();
criteria.setFirstName(firstName);
criteria.setLastName(lastName);
List<Contact> contacts = contactMapper.findByFirstNameAndLastName(criteria);
for (Contact contactTemp: contacts) {
Search WWH :
