MyBatis Annotation Development - One-to-Many and Many-to-...

One-to-Many

Query Model

The relationship between user table and order table: one user has multiple orders, one order belongs to one user. The one-to-many query requirement is: query a user, and simultaneously query the orders that user has.

One-to-Many Advantages

• Clear data with explicit relationships
• Data integrity can be enforced through foreign key constraints
• Flexible queries, rich data can be obtained through join tables

One-to-Many Disadvantages

• For complex queries, may involve multiple joins (JOIN), resulting in slightly lower performance
• Data model is tightly coupled; when table structure changes, the impact range is larger

Write Code

UserMapper

@Select("select * from wzk_user")
@Results({
        @Result(id = true, property = "id", column = "id"),
        @Result(property = "username", column = "username"),
        @Result(property = "password", column = "password"),
        @Result(property = "birthday", column = "birthday"),
        @Result(property = "roleList", column = "id", javaType = List.class,
                many = @Many(select = "icu.wzk.mapper.OrderMapper.findByUserIdWithAnnotation"))
})
List<WzkUser> findAllUserAndOderWithAnnotation();

OrderMapper

@Select("select * from wzk_orders where id = #{id}")
List<WzkOrder> findByUserIdWithAnnotation(int id);

Call Code

public class WzkIcu12 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<WzkUser> dataList = userMapper.findAllUserAndOderWithAnnotation();
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Test Results

WzkUser(id=1, username=wzk, password=icu, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkOrder(id=1, ordertime=Mon Nov 11 00:00:00 CST 2024, total=100.0, user=null)])
WzkUser(id=2, username=wzk2, password=icu2, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkOrder(id=2, ordertime=Mon Nov 11 00:00:00 CST 2024, total=200.0, user=null)])

---

Many-to-Many

Query Model

The relationship between user table and role table: one user has multiple roles, one role is used by multiple users. The many-to-many query requirement is: query users and simultaneously query all roles of that user.

In database design, a “many-to-many” relationship means records in two tables can be associated with each other, with multiple records associated with multiple records. For example, the relationship between a “student” table and a “course” table may be many-to-many.

To implement many-to-many relationships, an intermediate table (association table) is usually used. This intermediate table acts as a bridge, connecting records from both tables through their primary keys.

Characteristics of Many-to-Many Relationships

• Bidirectionality: A can be associated with multiple B, and B can also be associated with multiple A
• Requires Intermediate Table: To represent this relationship, an intermediate table is usually used to maintain the association
• High Flexibility: Many-to-many relationships are very suitable for expressing complex business logic

Extensions of Many-to-Many Relationships

• Adding Extra Fields: Intermediate table can be extended with more functionality, such as adding timestamps, grade fields for course enrollment
• Index Optimization: Add indexes to foreign keys in intermediate table to improve query performance
• ORM Framework Support: Modern frameworks support automatic management of many-to-many relationships

Write Code

UserMapper

@Select("select * from wzk_user")
@Results({
        @Result(id = true,property = "id",column = "id"),
        @Result(property = "username",column = "username"),
        @Result(property = "password",column = "password"),
        @Result(property = "birthday",column = "birthday"),
        @Result(property = "roleList",column = "id",
                javaType = List.class,
                many = @Many(select = "icu.wzk.mapper.RoleMapper.findByUserIdWithAnnotation"))
})
List<WzkUser> findAllUserAndRoleWithAnnotation();

RoleMapper

public interface RoleMapper {
    @Select("select * from wzk_role r, wzk_user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
    List<WzkRole> findByUserIdWithAnnotation(int uid);
}

Call Code

public class WzkIcu13 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                .build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<WzkUser> dataList = userMapper.findAllUserAndRoleWithAnnotation();
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Test Results

WzkUser(id=1, username=wzk, password=icu, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkRole(id=1, rolename=ADMIN)])
WzkUser(id=2, username=wzk2, password=icu2, birthday=Mon Nov 11 00:00:00 CST 2024, orderList=null, roleList=[WzkRole(id=2, rolename=USER)])

---

Advantages and Disadvantages of Annotation Mode

Advantages

• Simple Development: No need for complex XML configuration files
• Centralized Code: SQL statements and logic written together for easy maintenance
• Convenience: Suitable for simple CRUD operations with less code

Disadvantages

• Poor Readability: SQL statements embedded in code may be difficult to read
• Complex SQL Hard to Maintain: For complex SQL, annotation mode is not as clear as XML
• Limited Extensibility: Not easy to support dynamic SQL and advanced features