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);
    }
}