This article covers fundamental database operations using MyBatis, including delete, insert, update, and select. It also discusses parameter placeholders, primary key retrieval, and XML mapping.

Delete by Primary Key

@Delete("delete from emp where id = #{id}")
public void delete(Integer id);

Test method:

@Test
public void testDelete(){
    empMapper.delete(16);
    System.out.println("Deleted successfully");
}

Log output will show the executed SQL.

Parameter Placeholders

• #{} : Uses prepared statement, safe from SQL injection.
• ${} : Direct string substitution, unsafe. Avoid using it.

Insert

@Insert("insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " +
        "values (#{username},#{name},#{gender}, #{image},#{job}, #{entrydate},#{deptId}, #{createTime}, #{updateTime})")
public void insert(Emp emp);

Note: Use camelCase property names to match Java fields.

Test method:

@Test
public void testInsert(){
    Emp emp = new Emp();
    emp.setUsername("Tom3");
    emp.setName("Tom3");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000,1,1));
    emp.setCreateTime(LocalDateTime.now());
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);
    empMapper.insert(emp);
}

Retrieving Auto-Generated Primary Key

Add @Options(useGeneratedKeys = true, keyProperty = "id") to the insert method. The generated ID will be set back to the entity's id field.

empMapper.insert(emp);
System.out.println(emp.getId());

Update

@Update("update emp set username =#{username}, name=#{name}, gender=#{gender}, image=#{image}, " +
        "job=#{job}, entrydate=#{entrydate}, dept_id=#{deptId}, update_time=#{updateTime} where id = #{id}")
public void update(Emp emp);

Test method:

@Test
public void testUpdate(){
    Emp emp = new Emp();
    emp.setId(20);
    emp.setUsername("Tom1");
    emp.setName("Tom1");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000,1,1));
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(3);
    empMapper.update(emp);
}

Select

Select by ID

@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);

Test:

@Test
public void testGetById(){
    Emp emp = empMapper.getById(20);
    System.out.println(emp);
}

Common issue: If column names in the database use underscores and Java fields use camelCase, the result may not be mapped correctly. This can be solved by enabling mybatis.configuration.map-underscore-to-camel-case=true in application.properties.

Select with Conditions

Avoid using ${} for string concatenation inside LIKE because it can introduce SQL injection. Instead, use CONCAT:

@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} " +
        "and entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);

Test:

@Test
public void testList(){
    List<Emp> empList = empMapper.list("Zhang", (short) 1,
            LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
    System.out.println(empList);
}

XML Mapping

For complex queries, you can use XML mapping files instead of annotations. Create a mapper XML file in the corresponding resource directory. The XML namespace should match the mapper interface fully qualified name.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.EmpMapper">
    <!-- SQL statements here -->
</mapper>

Tip: XML mapping is more maintainable for dynamic SQL with <if>, <where>, <set>, etc.