如何在Hibernate中编写内连接查询获取三张表的全部记录？

Hey there! Let's break down why your inner join isn't pulling all records from your three tables, and fix up the query to get everything you need.

The Core Issue with Inner Join #

An INNER JOIN only returns rows where there are matching records across all joined tables. So if a candidate has no address on file, or no fitness data, their record gets dropped entirely from the result set. To grab every record from Personal_Info (along with any associated Address/Fitness data when it exists), you need to use LEFT JOINs (left outer joins).

Fixed Query (HQL or Native SQL) #

First, let's confirm your table relationships:

• Personal_Info uses CandidateID as its primary key
• Address links to it via CandidateID (foreign key)
• Fitness links via UserID (foreign key, assuming this maps to Personal_Info.CandidateID—adjust if your schema uses a different mapping!)

Option 1: HQL (Using Entity Associations)

If your Hibernate entities are mapped with relationships (e.g., Personal_Info has a @OneToOne to Address and Fitness), this clean HQL query will work:

public void getAllRecords() {
    Session currentSession = sessionFactory.getCurrentSession();
    
    // Left join ensures we keep all Personal_Info records, even if no Address/Fitness exists
    String hql = "SELECT pi, a, f FROM Personal_Info pi " +
                 "LEFT JOIN pi.address a " +
                 "LEFT JOIN pi.fitness f";
    
    Query query = currentSession.createQuery(hql);
    List<Object[]> results = query.list();
    
    // Process each row of results
    for (Object[] row : results) {
        Personal_Info personalInfo = (Personal_Info) row[0];
        Address address = (Address) row[1]; // Will be null if no match
        Fitness fitness = (Fitness) row[2]; // Will be null if no match
        
        // Add your data handling logic here
    }
}

Option 2: Native SQL (If No Entity Relationships)

If you haven't set up entity associations, use a native SQL query with explicit join conditions:

public void getAllRecords() {
    Session currentSession = sessionFactory.getCurrentSession();
    
    String sql = "SELECT pi.*, a.*, f.* FROM Personal_Info pi " +
                 "LEFT JOIN Address a ON pi.CandidateID = a.CandidateID " +
                 "LEFT JOIN Fitness f ON pi.CandidateID = f.UserID";
    
    SQLQuery query = currentSession.createSQLQuery(sql);
    query.addEntity(Personal_Info.class);
    query.addEntity(Address.class);
    query.addEntity(Fitness.class);
    
    List<Object[]> results = query.list();
    
    // Same processing logic as above
}

Key Notes #

• LEFT JOIN Behavior: This will return every record from Personal_Info, with null values for Address or Fitness columns where there's no matching data.
• Fitness Association: Double-check that Fitness.UserID correctly maps to Personal_Info.CandidateID—if your schema uses a different key, update the ON clause in the native SQL, or adjust the entity mapping for HQL.
• Full Outer Join: If you need all records from all three tables (including Address/Fitness entries that don't link to any Personal_Info), HQL doesn't support full outer joins directly. You'd need to use a UNION of left and right joins in native SQL for that edge case.

内容的提问来源于stack exchange，提问作者JayeshB