SQL转HQL遇QuerySyntaxException错误，求正确改写方案

Hey there! Let's work through converting your SQL to HQL and fix that QuerySyntaxException together. That error almost always comes from HQL encountering something it doesn't recognize—usually mismatches between raw database table/column names and your Hibernate entity class/properties, or using old-school SQL join syntax that HQL doesn't prefer.

Key Differences to Remember First #

HQL works with your Hibernate entity classes and their properties, not directly with database tables and columns. So first, double-check that you're referencing your entity class names (e.g., if your products_base table maps to a Java class named ProductsBase, use that) and their camelCase properties (like productId instead of PRODUCT_ID).

Step-by-Step Conversion #

Let's rewrite your query properly, assuming you have standard Hibernate entities defined (I'll make reasonable assumptions for missing parts of your WHERE clause):

1. Replace table names with entity class names (entities are usually singular, not plural):

   • products_base → ProductsBase (your entity class name)
   • CATEGORY_TRANSLATIONS → CategoryTranslation
   • PRODUCT_TRANSLATIONS → ProductTranslation

2. Use explicit JOINs instead of comma-separated tables
   HQL discourages the old SQL comma-join syntax because it's harder to read and can trip up Hibernate's parser. Instead, use explicit JOIN with association conditions (you can use ON if you're on Hibernate 5.1+ or leverage defined entity relationships).

3. Swap database columns for entity properties
   Replace snake_case columns with the camelCase properties from your entities (e.g., PRODUCT_ID → productId, CATEGORY_NAME → categoryName).

Example HQL Query #

SELECT pb.productId, pb.productName, pb.listPrice, ct.categoryName, ct.categoryId
FROM ProductsBase pb
JOIN CategoryTranslation ct ON pb.categoryId = ct.categoryId
JOIN ProductTranslation pt ON pb.productId = pt.productId
-- Add your remaining WHERE conditions here (e.g., locale filters, status checks)
-- WHERE pt.locale = 'en' AND ct.locale = 'en'

If You Have Defined Entity Relationships #

If you've set up relationships in your entities (like a @ManyToOne in ProductsBase pointing to CategoryTranslation), you can simplify the query even more with implicit joins:

SELECT pb.productId, pb.productName, pb.listPrice, pb.category.categoryName, pb.category.categoryId
FROM ProductsBase pb
JOIN pb.productTranslations pt
WHERE pt.locale = 'en'

Common Fixes for the Unexpected Token Error #

• Check case sensitivity: HQL is case-sensitive for entity and property names. If your entity is named productsBase (lowercase first letter), using ProductsBase will throw an error.
• Avoid SQL-specific keywords: Don't use database-specific functions or syntax that HQL doesn't support (e.g., certain vendor-specific aggregate functions) without registering them first.
• Fix typos: A missing comma, misspelled property name, or extra character is often the culprit. Double-check every entity and property reference.

If you're still stuck, sharing the full WHERE clause and snippets of your entity class definitions would help narrow down the issue further!

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