MyBatis Annotation-Based SQL Configuration: Complete Guide with Examples

Project Setup and Dependencies

The tutorial begins by creating a Maven project with four dependencies: MySQL Connector/J 8.0.28, MyBatis 3.5.9, Log4j 1.2.17, and JUnit 4.13.2 (test scope). After adding these to pom.xml, a Maven refresh downloads the required JARs.

Project Structure

The project follows a standard layered architecture: com.jobs.bean — entity classes com.jobs.mapper — MyBatis mapper interfaces com.jobs.mapper.sql — SQL provider classes for complex dynamic queries com.jobs.service — business logic using MyBatis APIs resources — configuration files: jdbc.properties, log4j.properties,

MyBatisConfig.xml

test

— JUnit test class

employeeTest

Configuration Files

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testdb
username=root
password=123456

log4j.properties

log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%p [%t] - %m%n

MyBatisConfig.xml

The core configuration loads jdbc.properties, sets Log4j as the logging implementation, defines a type alias package ( com.jobs.bean), configures a pooled JDBC data source with transaction manager type JDBC, and registers mappers by scanning the com.jobs.mapper package. The only difference from XML-based SQL configuration is the <mappers> section, which uses <package name="com.jobs.mapper"/> instead of individual XML mapper files.

Database and Entity

A MySQL testdb database with an employee table is created:

CREATE TABLE employee (
  e_id int(11) NOT NULL AUTO_INCREMENT,
  e_name varchar(50) DEFAULT NULL,
  e_age int(11) DEFAULT NULL,
  PRIMARY KEY (e_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The employee entity class deliberately uses different field names ( id, name, age) than the database columns ( e_id, e_name, e_age) to demonstrate result mapping.

SQL Provider for Complex Queries

The employeeMapperSQL class provides two methods that build dynamic SQL strings programmatically, avoiding verbose annotations: getSelectConditionSQL(employee ele) — builds a WHERE clause with optional id, name (LIKE), and age conditions, appending ORDER BY e_id DESC. getSelectByIdsSQL(List<Integer> ids) — constructs an IN clause from a list of IDs.

Mapper Interface with Annotations

The employeeMapper interface uses the following annotations:

@Results(id="employee_map", value={@Result(column="e_id", property="id"), @Result(column="e_name", property="name"), @Result(column="e_age", property="age")})

— defines the column-to-property mapping once.

@Select("select e_id,e_name,e_age from employee order by e_id")

+ @Results for selectAll().

@Select("select e_id,e_name,e_age from employee where e_id = #{id}")

+ @ResultMap("employee_map") for selectById(Integer id).

@Insert("insert into employee(e_id,e_name,e_age) values(#{id},#{name},#{age})")

for insert(employee ele).

@Update("update employee SET e_name=#{name},e_age=#{age} where e_id = #{id}")

for update(employee ele). @Delete("delete FROM employee where e_id = #{id}") for delete(Integer id).

@SelectProvider(type=employeeMapperSQL.class, method="getSelectConditionSQL")

+ @ResultMap("employee_map") for selectCondition(employee ele).

@SelectProvider(type=employeeMapperSQL.class, method="getSelectByIdsSQL")

+ @ResultMap("employee_map") for selectByIds(List<Integer> ids).

The @ResultMap annotation reuses the mapping defined in @Results, eliminating repetition.

Service Layer

The employeeService class encapsulates the boilerplate MyBatis session management: loading MyBatisConfig.xml via Resources.getResourceAsStream, building a SqlSessionFactory, opening a session with openSession(true) for auto-commit, obtaining the mapper proxy via sqlSession.getMapper(employeeMapper.class), executing the mapper method, and closing resources in a finally block. Each CRUD and query method follows this identical pattern.

JUnit Tests

The employeeTest class verifies all operations: selectAll() — prints all employees. selectById(3) — retrieves a single employee. insert(new employee(6, "任天蓬", 26)) — adds a record. update(new employee(6, "任天蓬", 16)) — modifies the inserted record. selectCondition() — demonstrates dynamic WHERE clause with name LIKE "%任%" (commented id/age conditions show combinability). selectByIds(List.of(1,2,3)) — tests the IN clause. delete(6) — removes the test record.

The author notes that Spring integration later eliminates most of this manual session-handling code.