Spring查询继承失效：多表关联查询未返回预期结果求助

Hey there! As a fellow Spring learner, I totally get how frustrating it is when a query doesn’t return what you expect. Let’s walk through fixing your SQL problem first, then touch on that inheritance issue you mentioned.

First: Fixing the Missing Query Results #

Let’s break down why your current query isn’t returning data:

• Incorrect Join Condition: I noticed this line: b.job_execution_id = bi.job_instance_id — that’s probably a mistake! Typically, BatchJobExecution has a job_instance_id column that links to BatchJobInstance’s job_instance_id, not job_execution_id. Swap that to b.job_instance_id = bi.job_instance_id and see if that fixes the association.

• Time Field Precision Mismatch: When comparing s.heure_debut = b.start_time and s.heure_fin = b.end_time, time fields often have hidden precision (like milliseconds) that don’t match exactly. For example, if one stores 2024-05-20 14:30:00.123 and the other stores 2024-05-20 14:30:00, the equality check will fail. Try truncating the time to the same precision, like:

  DATE_TRUNC('second', s.heure_debut) = DATE_TRUNC('second', b.start_time)
  

  (Adjust the function based on your database — use DATE_FORMAT for MySQL, TRUNC for Oracle.)

• Strict GROUP BY Requirements: Most modern databases require all non-aggregated columns in your SELECT to be in the GROUP BY clause. Your current query selects b.job_instance_id and b.start_time but only groups by b.job_instance_id — add b.start_time to the GROUP BY to avoid errors or silent filtering.

• Test the Base Query First: Temporarily remove the GROUP BY and count(*) to run a simple select of matching rows. If this returns data, the issue is with the aggregation; if not, your join/where conditions are still off.

Here’s a revised version of your query with these fixes:

SELECT b.job_instance_id, b.start_time, count(*) 
FROM SygaOfferRejected s 
JOIN BatchJobExecution b 
  ON DATE_TRUNC('second', s.heure_debut) = DATE_TRUNC('second', b.start_time) 
  AND DATE_TRUNC('second', s.heure_fin) = DATE_TRUNC('second', b.end_time)
JOIN BatchJobInstance bi 
  ON b.job_instance_id = bi.job_instance_id
WHERE bi.job_name = :batchName 
  AND b.status = :batchStatus 
GROUP BY b.job_instance_id, b.start_time

Second: Addressing Inheritance Failure #

Since you mentioned inheritance issues (though you didn’t share details), here are common pitfalls to check:

• Verify Inheritance Strategy: Make sure your parent entity has @Inheritance(strategy = InheritanceType.JOINED/SINGLE_TABLE/TABLE_PER_CLASS) set correctly — the default might not match your database schema.
• Check Entity Annotations: Ensure parent classes are marked with @Entity, and subclasses don’t override critical fields without proper mapping (like @Id).
• JPQL Query for Subclasses: If you’re querying a subclass, use the TYPE() function to filter, e.g., SELECT e FROM ParentEntity e WHERE TYPE(e) = SubclassEntity.

Quick Tip #

Run your query directly in a database client (like pgAdmin, MySQL Workbench) with hardcoded values for :batchName and :batchStatus. If it returns data there, the problem is likely in how Spring is passing parameters (e.g., wrong data types). If not, your database data doesn’t match the conditions you’re expecting.

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