One-to-One Model Overview

In database design, the one-to-one model (One-to-One Relationship) is a type of entity relationship used to represent that a record in one table can only be associated with one record in another table.

Characteristics of One-to-One Relationships

  • Uniqueness: Each row in table A can only be associated with one row in table B, and vice versa
  • Strict Mapping: The data relationship between two tables is one-to-one
  • Logical Level: Usually used to separate storage of different attributes of an entity

Common Application Scenarios

  • Data Splitting: Separating frequently accessed fields from infrequently accessed fields for performance optimization
  • Permission Control: Storing user and role permission configurations
  • Extended Features: Storing extended data of the main table in another table

Advantages Analysis

  • Modular Design: Dividing data into different tables for easier maintenance and management
  • Improved Security: Sensitive information can be stored separately
  • Performance Optimization: Splitting tables reduces the amount of data in a single table

Disadvantages Analysis

  • Increased Query Complexity: Requires table join queries (JOIN)
  • Increased Maintenance Difficulty: Requires strict design and maintenance of table relationships
  • Higher Transaction Management Cost: Cross-table operations need to be handled within transactions

Query Model

The relationship between the user table and order table is: one user has multiple orders, one order belongs to only one user. Query an order and also query the user to which that order belongs.

Create Tables

CREATE TABLE `wzk_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ordertime` varchar(255) DEFAULT NULL,
  `total` double DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `wzk_user` (
  `id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `birthday` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Query Statement

select * from wzk_orders o, wzk_user u where o.uid=u.id

Java Class Definitions

WzkOrder.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WzkOrder {
    private int id;
    private Date ordertime;
    private double total;
    private WzkUser user;
}

WzkUser.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class WzkUser {
    private int id;
    private String username;
    private String password;
    private Date birthday;
}

Mapper Interface and XML

OrderMapper.java

public interface OrderMapper {
    List<WzkOrder> findAll();
}

OrderMapper.xml

Method One:

<?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.OrderMapper">
    <!-- Method One -->
    <resultMap id="orderMap" type="icu.wzk.model.WzkOrder">
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select * from wzk_orders o, wzk_user u where o.uid=u.id
    </select>
</mapper>

Method Two (using association):

<!-- Method Two -->
<resultMap id="orderMap" type="icu.wzk.model.WzkOrder">
    <result property="id" column="id"></result>
    <result property="ordertime" column="ordertime"></result>
    <result property="total" column="total"></result>
    <association property="user" javaType="icu.wzk.model.WzkUser">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap>

SqlMapConfig.xml Configuration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties>
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://172.16.1.130:3306/wzk-mybatis?characterEncoding=utf-8"/>
        <property name="user" value="hive"/>
        <property name="password" value="hive@wzk.icu"/>
    </properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driverClass}"/>
                <property name="url" value="${jdbcUrl}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper.xml"/>
        <!-- Newly added OrderMapper -->
        <mapper resource="OrderMapper.xml"/>
    </mappers>
</configuration>

Test Code

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

Running Results

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

Notes

  • Data Consistency: Must ensure referential integrity between table A and table B, avoiding orphaned records
  • Index Design: Create indexes on foreign key columns to improve join query performance
  • Scenario Adaptation: If there is no obvious one-to-one relationship requirement, it is recommended to avoid using it to simplify design