如何将无关联多表SQL查询转换为Hibernate Criteria查询

Alright, let's work through converting your query to Hibernate Criteria API. The tricky part here is that your tables don't have mapped associations in Hibernate, but we can still replicate the left joins using explicit join conditions with the Criteria API.

First, let's assume you have Hibernate entities mapped to each table:

• Table1 maps to table1
• Table2 maps to table2
• Table3 maps to table3

Your original SQL for reference:

select * from table1 aa 
left join table2 ab on aa.sequence = ab.newsequence 
left join table3 ac on ab.poNumber= ac.number 
where ab.date is null;

Here's the Criteria Implementation #

We'll use createAlias to set up the left joins with custom conditions, then add the date is null filter. Since we're pulling data from multiple unrelated tables, we'll also use a result transformer to handle the combined output.

// Start with the main entity (Table1) and alias it "aa"
Criteria criteria = session.createCriteria(Table1.class, "aa");

// Left join Table2 as "ab" using the sequence/newsequence match
criteria.createAlias("Table2", "ab", JoinType.LEFT_OUTER_JOIN,
    Restrictions.eqProperty("aa.sequence", "ab.newsequence"));

// Left join Table3 as "ac" using poNumber/number match
criteria.createAlias("Table3", "ac", JoinType.LEFT_OUTER_JOIN,
    Restrictions.eqProperty("ab.poNumber", "ac.number"));

// Add the where clause: filter rows where Table2's date is null
criteria.add(Restrictions.isNull("ab.date"));

// Handle the result set (since we're selecting * from 3 tables)
// Option 1: Get results as a map of alias+property to value
criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);

// Option 2: Map to a custom DTO (create a class with fields for all columns you need)
// criteria.setResultTransformer(new AliasToBeanResultTransformer(YourCombinedDTO.class));

// Execute the query
List<?> results = criteria.list();

Quick Notes to Keep in Mind: #

• JoinType.LEFT_OUTER_JOIN matches the LEFT JOIN behavior from your original SQL—this ensures we keep all rows from table1 even if there's no match in table2 or table3.
• Restrictions.eqProperty is used here because we're comparing properties across different aliases (since there's no mapped association between the entities).
• The result transformer is essential: without it, Hibernate would only return Table1 instances, ignoring data from table2 and table3. Using ALIAS_TO_ENTITY_MAP gives you a flexible map where you can access values using keys like aa.sequence or ab.poNumber.
• If you don't need every column (instead of select *), you can use projections to pick specific fields:

  criteria.setProjection(Projections.projectionList()
      .add(Projections.property("aa.id"), "table1Id")
      .add(Projections.property("ab.newsequence"), "table2Sequence")
      // Add any other columns you need here
  );
  

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