This “titles” table is the target of this tutorial. For those who prefer a create table statement, this is the SQL:

CREATE TABLE `titles` (
  `emp_no` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

First, our very simple one-to-one data mapping domain POJO, with the FK ’emp_no’ property:

public class Title {

    private Integer emp_no;
    private String titleName;
    private Date fromDate;
    private Date toDate;

    //  Setters and getters.
}

 

Then, these are the TitleService class and TitleMapper interface. The key is the use of the @Param MyBatis annotations in the interface.

@Service
public class TitleService {

    @Autowired
    TitleMapper titleMapper;

    public void updateTitle(Title oldTitle, Title newTitle){
        titleMapper.updateTile(oldTitle, newTitle);
    }

}

public interface TitleMapper {

    void updateTile(@Param("oldTitle") Title oldTitle, @Param("newTitle") Title newTitle);

}

 

Last, the XML mapper. We use the oldTitle object’s composite keys data to target the correct row, then update it with the new data from the newTitle object.

    <update id="updateTile">
        UPDATE titles SET
          title = #{newTitle.titleName},
          from_date = #{newTitle.fromDate},
          to_date = #{newTitle.toDate}
          WHERE emp_no = #{oldTitle.emp_no} AND title = #{oldTitle.titleName} AND from_date = #{oldTitle.fromDate}
    </update>

 

Here is a integration test with MySQL.

@RunWith(SpringRunner.class)
@SpringBootTest
public class TitleServiceTest {

    @Autowired
    TitleService titleService;

    Calendar cal;
    Date currentDate;
    Date setupDate;
    Integer testEmp_no;
    Title originalTitle;
    Title newTitle;
    Title originalTitleDiffDates;
    Title originalTitleDiffToDate;

    @Before
    public void setUp() throws Exception {
        cal = Calendar.getInstance();
        cal.set(cal.YEAR, 2016);
        cal.set(cal.MONTH, cal.DECEMBER);
        cal.set(cal.DATE, 25);
        cal.set(cal.HOUR_OF_DAY, 0);
        cal.set(cal.MINUTE, 0);
        cal.set(cal.SECOND, 0);
        cal.set(cal.MILLISECOND, 0);

        testEmp_no = new Integer(500006);
        setupDate = new Date(cal.getTime().getTime());
        currentDate = new Date(System.currentTimeMillis());

        originalTitle = new Title();
        originalTitle.setEmp_no(testEmp_no);
        originalTitle.setTitleName("Boy Scout");
        originalTitle.setFromDate(setupDate);
        originalTitle.setToDate(setupDate);

        newTitle = new Title();
        newTitle.setEmp_no(testEmp_no);
        newTitle.setTitleName("Med Doctor");
        newTitle.setFromDate(currentDate);
        newTitle.setToDate(currentDate);
    }

    @Test
    public void updateTitle() throws Exception {
        titleService.updateTitle(originalTitle, newTitle);
        Title ifExistTitle = titleService.checkIfTitleExist(newTitle);

        assertNotNull(ifExistTitle);
        assertEquals(testEmp_no, ifExistTitle.getEmp_no());
        assertEquals("Med Doctor", ifExistTitle.getTitleName());
        assertEquals(currentDate.toString(), ifExistTitle.getFromDate().toString());

        titleService.updateTitle(newTitle, originalTitle);
    }
}

 

 

Check out my next series for saveOrUpdate implementation and also the checkIfTitleExist method in the test:

Mybatis saveOrUpdate on a composite keys table tutorial with MySQL (series 2)