Mybatis saveOrUpdate on a composite keys table tutorial with MySQL (series 2)
For domain POJO, database schema and more details, check out my earlier post: Mybatis update on a composite keys table tutorial with MySQL (series 1)
Let’s start by taking a look at our ‘protagonist’: saveOrUpdate method, sitting in the TitleService class. He’s very simple though. We receive a Title object from the upper layer, then check if it already exist within the DB, for avoiding duplication.
@Service public class TitleService { @Autowired TitleMapper titleMapper; @Autowired EmployeeMapper employeeMapper; public Boolean insertTitle(Title title){ return titleMapper.insertTile(title); } public Boolean updateTitle(Title oldTitle, Title newTitle){ return titleMapper.updateTile(oldTitle, newTitle); } public Boolean saveOrUpdate(Title title){ Title tempTitle = checkIfTitleExist(title); if (tempTitle == null){ return insertTitle(title); } return updateTitle(tempTitle, title); }
You will notice that I call a local method checkIfTitleExist again ( counting last tutorial). Here it is.
public Title checkIfTitleExist(Title title){ List<Title> titles = findTitlesForEmployee(employeeMapper.findEmployeeById(title.getEmp_no())); if (!titles.isEmpty()){ for (Title each : titles) { if (each.isSQLEquals(title)){ return each; } } return null; } return null; } }
I loop through all titles that an employee has in the DB. If one is matched, quit the for loop and return that matching title. If no title is matched, return null.
Here is the isSQLEquals method in the Title domain POJO.
public class Title { private Integer emp_no; private String titleName; private Date fromDate; private Date toDate; // Getters and Setters public Boolean isSQLEquals(Title title){ if ((this.getEmp_no().intValue() == title.getEmp_no().intValue()) && (this.getTitleName().equals(title.getTitleName())) && (this.getFromDate().toString().equals(title.getFromDate().toString()))){ return true; } return false; } }
I compare three properties between two Title objects, since these are the composite keys columns in our DB. It does look silly that we only have 4 properties, but 3 of them are composite keys. It gonna make more sense when we have 10 properties or more, however the logic is the same.
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 checkTitleIfExist() throws Exception { Title ifExistTitle = titleService.checkIfTitleExist(originalTitle); assertNotNull(ifExistTitle); assertEquals(testEmp_no, ifExistTitle.getEmp_no()); assertEquals("Boy Scout", ifExistTitle.getTitleName()); assertEquals(setupDate.toString(), ifExistTitle.getFromDate().toString()); } @Test public void checkTitleIfExistFail() throws Exception { originalTitleDiffDates = new Title(); originalTitleDiffDates.setEmp_no(testEmp_no); originalTitleDiffDates.setTitleName("Boy Scout"); originalTitleDiffDates.setFromDate(currentDate); // fromDate is different originalTitleDiffDates.setToDate(currentDate); assertNull(titleService.checkIfTitleExist(originalTitleDiffDates)); } @Test public void saveOrUpdate() throws Exception { originalTitleDiffToDate = new Title(); originalTitleDiffToDate.setEmp_no(testEmp_no); originalTitleDiffToDate.setTitleName("Boy Scout"); originalTitleDiffToDate.setFromDate(setupDate); originalTitleDiffToDate.setToDate(currentDate); // toDate is different titleService.saveOrUpdate(originalTitleDiffToDate); Title tempTitle = titleService.checkIfTitleExist(originalTitleDiffToDate); assertNotNull(tempTitle); assertEquals(currentDate.toString(), tempTitle.getToDate().toString()); titleService.saveOrUpdate(originalTitle); } }