java - 如何构造 SQL 语句以避免过多的连接

Question

我正在尝试构建一个选择语句，该语句将允许我在应用程序启动时构建本地应用程序缓存。我的表结构如下所示；

我知道要获得一些帮助，我必须展示我已经尝试过的内容，但这非常

我正在使用 hibernate ，这应该会让事情变得更容易，但我真的遇到了障碍，我能想到的唯一方法是选择所有字段，然后对表 id 匹配进行左外连接，但是问题使用这种方法，一个用户可以拥有 0-1 张名片/0-1 张社交卡、0-1 张个人名片，这意味着当我离开外部连接时，用户可能会丢失一些数据。个人信息/登录详细信息和设备数据都是 1:1 映射

是否有更有效的方法在 hibernate 中进行选择，这将使我能够轻松构建缓存？

理想情况下，我想构造一个对象:

public class User {

    private BusinessCard businessCard;
    private SocialCard socialCard;
    private PersonalCard personalCard;

    private PersonalInformation personalInformation;
    private LoginDetails loginDetails;
    private DeviceData deviceData;


    public BusinessCard getBusinessCard() {
        return businessCard;
    }

    public void setBusinessCard(BusinessCard businessCard) {
        this.businessCard = businessCard;
    }

    public SocialCard getSocialCard() {
        return socialCard;
    }

    public void setSocialCard(SocialCard socialCard) {
        this.socialCard = socialCard;
    }

    public PersonalCard getPersonalCard() {
        return personalCard;
    }

    public void setPersonalCard(PersonalCard personalCard) {
        this.personalCard = personalCard;
    }

    public PersonalInformation getPersonalInformation() {
        return personalInformation;
    }

    public void setPersonalInformation(PersonalInformation personalInformation) {
        this.personalInformation = personalInformation;
    }

    public LoginDetails getLoginDetails() {
        return loginDetails;
    }

    public void setLoginDetails(LoginDetails loginDetails) {
        this.loginDetails = loginDetails;
    }

    public DeviceData getDeviceData() {
        return deviceData;
    }

    public void setDeviceData(DeviceData deviceData) {
        this.deviceData = deviceData;
    }

}

谢谢

Answer 1

来自Hibernate Community Documentation - Charpter 7 - Association Mappings :

7.2.1. Many-to-one

A unidirectional many-to-one association is the most common kind of unidirectional association.

<class name="Person">
    <id name="id" column="personId">
        <generator class="native"/>
    </id>
    <many-to-one name="address" 
        column="addressId"
        not-null="true"/>
</class>

<class name="Address">
    <id name="id" column="addressId">
        <generator class="native"/>
    </id>
</class>

create table Person ( 
    personId bigint not null primary key
    , addressId bigint not null 
)

create table Address ( 
    addressId bigint not null primary key 
)

7.2.2. One-to-one

A unidirectional one-to-one association on a foreign key is almost identical. The only difference is the column unique constraint.

<class name="Person">
    <id name="id" column="personId">
        <generator class="native"/>
    </id>
    <many-to-one name="address" 
        column="addressId" 
        unique="true"
        not-null="true"/>
</class>

<class name="Address">
    <id name="id" column="addressId">
        <generator class="native"/>
    </id>
</class>

create table Person ( 
    personId bigint not null primary key
    , addressId bigint not null unique 
)

create table Address ( 
    addressId bigint not null primary key 
)

这就是映射一对一关系的方式。请注意，除了唯一约束之外，这与多对一映射相同。

现在已经映射了您的类，您的 Person 类应如下所示。

public class Person {
    public Address getAddress() { return address; }
    public void setAddress(Address address) { this.address = address; }

    public int getId() { return id; }
    protected void setId(int id) { this.id = id; }

    private Address address;
    private int id;
}

回答您的问题

这完全取决于您希望如何保存数据。正如所表达的，我会将 BusinessCard、SocialCard 和 PersonalCard 的身份放入 User 表中，以便每个用户只能存在一张卡。

<class name "User" table="Users">
    <id name="id" column="userId">
        <generator class="native" />
    </id>
    <many-to-one name="businessCard"
        column="businessCardId"
        unique="true"
        not-null="true" />
    <many-to-one name="socialCard"
        column="socialCardId"
        unique="true"
        not-null="true" />
    <many-to-one name="personalCard"
        column="personalCardId"
        unqiue="true"
        not-null="true" />
</class>

<class name="BusinessCard" table="BusinessCards">
    <id name="id" column="businessCardId">
        <generator class="native" />
    </id>
</class>

<class name="SocialCard" table="SocialCards">
    <id name="id" column="socialCardId">
        <generator class="native" />
    </id>
</class>

<class name="PersonalCard" table="PersonalCards">
    <id name="id" column="personalCardId">
        <generator class="native" />
    </id>
</class>