Mybatis update on a composite keys table tutorial with MySQL (series 1)
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)