One-to-Many

Basic Introduction

In database design, one-to-many (One-to-Many) relationship is one of the most common relationship models. It is typically used to represent that one entity (a record in a table) can be associated with multiple records of another entity.

A one-to-many relationship means one entity (a record in the parent table) can be associated with multiple entities (multiple records in the child table), while each record in the child table can only be associated with one record in the parent table.

One-to-Many Advantages

Data is clear and relationships are explicit.

Data integrity can be enforced through foreign key constraints.

Queries are flexible, and rich data can be obtained through join tables.

One-to-Many Disadvantages

For complex queries, multiple joins (JOIN) may be involved, resulting in slightly lower performance.

The data model is tightly coupled; when table structure changes, the impact range is larger.

Query Model

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

Implementation Method

Database Constraints

Foreign key constraints (FOREIGN KEY) ensure that foreign key values in the child table must be existing primary key values in the parent table.

Cascade Operations

ON DELETE CASCADE: If a record in the parent table is deleted, related records in the child table are also deleted.

ON UPDATE CASCADE: If the primary key in the parent table is updated, foreign keys in the child table are also updated accordingly.

Corresponding Characteristics

  • Unidirectionality: One side is “one”, the other side is “many”.
  • Association Constraint: Each record on the many side can only be associated with one record on the one side, but one side can be associated with multiple records.
  • Typical Scenarios: User and orders (one user can have multiple orders), school and students (one school can have multiple students).

Query Statement

select *,o.id oid from wzk_user u left join wzk_orders o on u.id=o.uid;

Create Classes

WzkUser

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WzkUser {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    private List<WzkOrder> orderList;
}

UserMapper

public interface UserMapper {
    List<WzkUser> findAll();
}

UserMapper.xml

<?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="icu.wzk.mapper.UserMapper">
    <resultMap id="userMap" type="icu.wzk.model.WzkUser">
        <result column="id" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
        <collection property="orderList" ofType="icu.wzk.model.WzkOrder">
            <result column="oid" property="id"></result>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select *,o.id oid from wzk_user u left join wzk_orders o on u.id=o.uid;
    </select>
</mapper>

sqlMapConfig.xml

<mappers>
    <mapper resource="mapper.xml"/>
    <mapper resource="OrderMapper.xml"/>
    <mapper resource="UserMapper.xml"/>
</mappers>

Write Code

public class WzkIcu09 {
    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.findAll();
        dataList.forEach(System.out::println);
        sqlSession.close();
    }
}

Running Results

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

Optimization and Notes

Index Optimization

Add indexes to foreign key fields to improve query performance.

Data Integrity

Foreign key constraints ensure data consistency, but may reduce performance in high-concurrency scenarios. Therefore, you can choose to maintain integrity through application logic.

Design Extensibility

Consider whether it will transform into a many-to-many relationship in the future (for example: one order contains multiple products). Reserve extensibility space in the design.

Temporary Summary

In summary, the one-to-many model is one of the most basic and commonly used relationships in relational databases. It clearly expresses hierarchical relationships between entities. Through reasonable table structure design, query optimization, and indexing, these data relationships can be efficiently managed and operated.