数据库关系是指数据库中不同表之间的关系或连接方式。数据库关系可以分为以下几种类型：

一对一关系（One-to-One）：一个实体只能与另一个实体关联，而另一个实体也只能与一个实体关联。

一对多关系（One-to-Many）：一个实体可以与多个实体关联，而多个实体只能与一个实体关联。

多对多关系（Many-to-Many）：多个实体可以与多个实体关联。

在数据库中，一对一关系常用于将大型的表拆分为多个较小的表，以提高查询效率和减少冗余数据。一对多关系常用于将关系型数据库中的实体拆分为多个相关联的表。多对一关系常用于表示一个实体对应多个实体的情况，比如一个部门对应多个员工。多对多关系用于表示多个实体之间的复杂关系，需要使用中间表来记录实体之间的关联关系。

数据库关系的定义和实现都是通过使用外键来实现的。外键是一种指向其他表的字段，它用于建立表与表之间的关联关系。通过使用外键，可以在对一个表进行查询时，同时获取与之相关联的其他表的数据。

1.2实现多表查询的准备

建立用户表

建立订单表

建立订单详情表

创建和表对应的java类

用户类


package com.ba.domain;
 
/**
 * 用户类
 */
public class User {
    private int id;
    private String username;
    private String password;
    private String gender;
    private String hobby;
    private String address;
    private String remark;
    private Order order;
 
    public User() {
    }
 
    public User(String username, String password, String gender, String hobby, String address, String remark) {
        this.username = username;
        this.password = password;
        this.gender = gender;
        this.hobby = hobby;
        this.address = address;
        this.remark = remark;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public String getGender() {
        return gender;
    }
 
    public void setGender(String gender) {
        this.gender = gender;
    }
 
    public String getHobby() {
        return hobby;
    }
 
    public void setHobby(String hobby) {
        this.hobby = hobby;
    }
 
    public String getAddress() {
        return address;
    }
 
    public void setAddress(String address) {
        this.address = address;
    }
 
    public String getRemark() {
        return remark;
    }
 
    public void setRemark(String remark) {
        this.remark = remark;
    }
 
 
    public Order getOrder() {
        return order;
    }
 
    public void setOrder(Order order) {
        this.order = order;
    }
}

订单类


package com.ba.domain;
 
import java.util.List;
 
/**
 * 订单类
 */
public class Order {
    private int o_id;
    private String o_name;
    private int o_count;
    private User user;
    private List<OrderDetails> orderDetailsList;
 
 
    public int getO_id() {
        return o_id;
    }
 
    public void setO_id(int o_id) {
        this.o_id = o_id;
    }
 
    public String getO_name() {
        return o_name;
    }
 
    public void setO_name(String o_name) {
        this.o_name = o_name;
    }
 
    public int getO_count() {
        return o_count;
    }
 
    public void setO_count(int o_count) {
        this.o_count = o_count;
    }
 
    public User getUser() {
        return user;
    }
 
    public void setUser(User user) {
        this.user = user;
    }
 
    public List<OrderDetails> getOrderDetailsList() {
        return orderDetailsList;
    }
 
    public void setOrderDetailsList(List<OrderDetails> orderDetailsList) {
        this.orderDetailsList = orderDetailsList;
    }
}

订单详情类


package com.ba.domain;
 
import java.util.List;
 
/**
 * 订单详情类
 */
public class OrderDetails {
    private int details_id;
    private String trade_name;
    private int unit_price;
    private List<Order> orderList;
 
    public int getDetails_id() {
        return details_id;
    }
 
    public void setDetails_id(int details_id) {
        this.details_id = details_id;
    }
 
    public String getTrade_name() {
        return trade_name;
    }
 
    public void setTrade_name(String trade_name) {
        this.trade_name = trade_name;
    }
 
    public int getUnit_price() {
        return unit_price;
    }
 
    public void setUnit_price(int unit_price) {
        this.unit_price = unit_price;
    }
 
    public List<Order> getOrderList() {
        return orderList;
    }
 
    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }
}

1.3一对一和一对多

一个订单只会有一个用户（一对一关系）

一个用户可以用多个订单（一对多关系）

接口中写方法


package com.ba.mapper;
 
import com.ba.domain.Order;
import com.ba.domain.QueryUser;
import com.ba.domain.User;
 
import java.util.List;
 
public interface UserMapper {
    public List<Order> findUserOrder();
}

UserMapper中手动映射关系


<resultMap id="userorder" type="com.ba.domain.Order">
        <id column="o_id" property="o_id"></id>
        <result column="o_name" property="o_name"></result>
        <result column="o_count" property="o_count"></result>
 
        <association property="user" javaType="com.ba.domain.User">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
 
    <select id="findUserOrder" resultMap="userorder">
    SELECT o.o_id,o.o_name,o.o_count,u.id,u.username,u.address FROM orders o JOIN USER u ON u.id = o.u_id
    </select>

测试类


@Test
    public void test9() {
        SqlSession sqlSession = ssf.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Order> userOrder = mapper.findUserOrder();
        for (Order order: userOrder) {
            System.out.println(order.getO_id()+","+order.getO_name()+","+order.getO_count()+","+order.getUser().getId()+","+order.getUser().getUsername()+","+order.getUser().getAddress());
        }
 
    }

运行结果

1.4多对多

一个用户可以有多个订单而一个订单又可以有多个商品简单来说多对多就是至少两个一对多的关系组成的

找到关系中的主体（Order）

接口中写方法


package com.ba.mapper;
 
import com.ba.domain.Order;
import com.ba.domain.QueryOrderUser;
 
import java.util.List;
 
public interface OrderMapper {
    public List<Order> findOrderUserOrderDetails();
}

OrderMapper中手动映射


<!--查询用户所有订单的详细信息-->
    <resultMap id="OrderUserOrderDetails" type="com.ba.domain.Order">
        <id column="o_id" property="o_id"></id>
        <result column="o_name" property="o_name"></result>
        <result column="o_count" property="o_count"></result>
        <association property="user" javaType="User">
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
        </association>
        <collection property="orderDetailsList" ofType="com.ba.domain.OrderDetails">
            <id column="details_id" property="details_id"></id>
            <result column="trade_name" property="trade_name"></result>
            <result column="unit_price" property="unit_price"></result>
        </collection>
    </resultMap>
 
    <select id="findOrderUserOrderDetails" resultMap="OrderUserOrderDetails">
        SELECT
            o.o_id,
            o.o_name,
            o.o_count,
            u.username,
            u.address,
            od.details_id,
            od.trade_name,
            od.unit_price
          FROM
            orders o,
            orderdetails od,
            USER u
          WHERE
            u.id=o.u_id
          AND
            od.o_id=o.o_id
    </select>

测试类


//多对多
    @Test
    public void test8() {
        SqlSession sqlSession = ssf.openSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderUserOrderDetails = mapper.findOrderUserOrderDetails();
        for (Order orderUserOrderDetail : orderUserOrderDetails) {
            System.out.println(orderUserOrderDetail.getO_id()+","+orderUserOrderDetail.getO_count()+","+orderUserOrderDetail.getO_count()+","+orderUserOrderDetail.getUser().getUsername()+","+orderUserOrderDetail.getUser().getAddress());
            List<OrderDetails> orderDetailsList = orderUserOrderDetail.getOrderDetailsList();
            for (OrderDetails orderDetail : orderDetailsList) {
                System.out.println("\t"+orderDetail.getDetails_id()+","+orderDetail.getTrade_name()+","+orderDetail.getUnit_price());
            }
        }
    }

运行结果

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/煮酒与君饮/article/detail/830557