MyBatis tips

Conditional update query with MyBatis

Sample code for conditional update (testing for a value using if) when using annotation based MyBatis mapper interface in Java.

COLUMN_1 and COLUMN_2 are updated with values from field1 and field2 respectively only if they are not null. DataUpdateParams class will have all the fields in parameters like field1, field2, name, id and userId with corresponding getters/setters.

Note that the query needs to be enclosed within tag.

The example uses syntax for SQL server but works similar for other databases too.

public static String UPDATE_MY_DATA = 
            "<script>" +
                "  UPDATE B " + 
                " <set> " + 
                    "<if test='field1 != null'>A.COLUMN_1 = #{field1, jdbcType=VARCHAR},</if>" +
                    "<if test='field2 != null'>A.COLUMN_2 = #{field2, jdbcType=VARCHAR},</if>" +
                    "A.UPDATE_ID = #{userId, jdbcType=VARCHAR}, " + 
                    "A.UPDATE_TIMESTAMP = GETDATE() " + 
                " </set>" +
                "  FROM TABLE_1 A " + 
                "  INNER JOIN TABLE_2 B ON B.B_ID = A.B_ID " + 
                "  WHERE B.NAME = #{name, jdbcType=VARCHAR} " +                 
                "  AND A.A_ID = #{id, jdbcType=BIGINT}" +
            "</script>";

    @Update(UPDATE_MY_DATA)
    public void updateMyData(DataUpdateParams params);

For-each loop

Below example (with xml file) showing how to use a foreach loop in mybatis. This can have several use cases - For eg., when dynamically forming IN clause for select, update or delete query.

 <delete id="deleteUsers">
        DELETE
        FROM USER
        WHERE USER_ID IN (
                <foreach item="userName" collection="list" separator="," open="(" close=")">
                    '${userName}'
                </foreach>
            )
        )
   </delete>

It can be invoked from java code something similar to below -

   List<String> userNames = Arrays.asList("first_user", "second_user");
   int deletedCount = session.delete("mapper.deleteUsers", userNames);

Inserting to a table with foreign key value generated from a different insert

Assuming we are inserting to EMPLOYEE_ADDRESS table with the EMPLOYEE_ID generated when inserting to EMPLOYEE table
Mapper interface

@Repository
@Mapper
public interface EmployeeMapper {

    final String INSERT_EMPLOYEE = "INSERT INTO EMPLOYEE(FNAME, LNAME, DEPT, INSERT_ID, INSERT_TIMESTAMP)"
            + "VALUES(#{fName}, #{lName}, #{dept}, #{userId}, GETDATE())";

    final String INSERT_EMPLOYEE_ADDRESS = "INSERT INTO EMPLOYEE_ADDRESS(EMPLOYEE_ID, ADDRESS_1, ADDRESS_2, CITY, STATE, ZIP, INSERT_ID, INSERT_TIMESTAMP)"
            + "VALUES(#{employeeId}, #{address1}, #{address2}, #{city}, #{state}, #{zip}, #{userId}, GETDATE())";

    @Insert(INSERT_EMPLOYEE)
    //This stores the generated key EMPLOYEE_ID into the property employeeId
    @Options(useGeneratedKeys=true, keyProperty="employeeId", keyColumn="EMPLOYEE_ID")
    void insertEmployee(Employee employee);

    @Insert(INSERT_EMPLOYEE_ADDRESS)
    void insertEmployeeAddress(EmployeeAddress employeeAddress);

}

Calling program

employeeMapper.insertEmployee(employee);
employeeAddress.setEmployeeId(employee.getEmployeeId()); //Generated Employee id that was set by MyBatis in employee object is set to employeeAddress
employeeMapper.insertEmployeeAddress(employeeAddress);